The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need Help Optimizing my.conf

Discussion in 'Workarounds and Optimization' started by laztrix, Feb 10, 2012.

  1. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    I'm in need of your suggestions.

    Server Specs:

    Intel(R) Core(TM) i7 CPU VPS with two cores avaliable (total 5000 Ghz)
    4 GB RAM
    cPanel as control panel
    MySQL 5.1.56, php 5.3.10

    Zend Engine v2.3.0, eAccelerator v0.9.6.1, ionCube PHP Loader v4.0.9, Zend Guard Loader v3.3, Suhosin v0.9.32.1
    APC

    my.conf is currently set to default:

    Code:
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
    wait_timeout = 28800
    max_allowed_packet = 16777216
    
    php.ini

    Code:
    extension_dir = "/usr/local/lib/php/extensions/no-debug-non-zts-20090626"
    zend_extension = "/usr/local/IonCube/ioncube_loader_lin_5.3.so"
    zend_extension = "/usr/local/Zend/lib/Guard-5.5.0/php-5.3.x/ZendGuardLoader.so"
    extension = "eaccelerator.so"
    extension = "suhosin.so"
    extension = "pdo.so"
    extension = "pdo_sqlite.so"
    extension = "sqlite.so"
    extension = "pdo_mysql.so"
    extension = "apc.so"
    eaccelerator.cache_dir="/tmp/eaccelerator"
    eaccelerator.check_mtime="1"
    eaccelerator.compress="1"
    eaccelerator.compress_level="9"
    eaccelerator.debug="0"
    eaccelerator.enable="1"
    eaccelerator.filter=""
    eaccelerator.optimizer="1"
    eaccelerator.shm_max="0"
    eaccelerator.shm_only="0"
    eaccelerator.shm_prune_period="0"
    eaccelerator.shm_size="16"
    eaccelerator.shm_ttl="0"
     
    apc.enabled = 1
    apc.shm_segments = 1
    apc.shm_size = 128M
    
    This is the output I get with mysqltuner:

    Code:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 20h 48m 10s (4M q [54.192 qps], 262K conn, TX: 130B, RX: 1B)
    [--] Reads / Writes: 82% / 18%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (34% of installed RAM)
    [OK] Slow queries: 0% (90/4M)
    [OK] Highest usage of available connections: 5% (25/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/252.6M
    [OK] Key buffer hit rate: 95.9% (52M cached / 2M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 394K sorts)
    [!!] Joins performed without indexes: 36265
    [!!] Temporary tables created on disk: 46% (62K on disk / 136K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 1M opened)
    [OK] Open file limit used: 1% (37/2K)
    [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
    [!!] InnoDB data size / buffer pool: 728.9M/8.0M
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 728M)
    
    Server load is usually ok but loading website pages become a pain. What would you suggest I set me my.conf and php.ini?

    Thanks.
     
  2. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi laztrix,

    I noticed you have both eaccelerator and APC in you php.ini. You should really pick one or the other.

    Your tuning is going to be a little more complicated since you're using both MyISAM and InnoDB extensively. I can see a lot of changes that need to be made, but I'd like to request more information before we get started so that this takes less time:

    First, please post the top portion of your mysqltuner report. I'd like the "General Statistics" and "Storage Engine Statistics" sections.

    Next, when you server is the most busy, please run these commands and post the output:

    Code:
    # top -n1
    # vmstat 5 5
    Please continue to post output inside of CODE tags, as the vmstat results are impossible to read otherwise.

    Once I have that data, we can get started on the tuning.
     
  3. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Thanks for replying alphawolf50,

    Code:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 625M (Tables: 1115)
    [--] Data in InnoDB tables: 703M (Tables: 405)
    [--] Data in MEMORY tables: 11M (Tables: 17)
    [!!] Total fragmented tables: 439
    
    Code:
    
     1475 root      16   0  5928  624  496 S  2.0  0.0   0:15.79 syslogd
    15501 nobody    15   0 48968 8088  820 S  2.0  0.2   0:39.65 nginx
        1 root      15   0 10368  732  616 S  0.0  0.0   0:00.59 init
     1135 root      15  -4 12636  676  356 S  0.0  0.0   0:00.00 udevd
     1510 named     15   0 13440  732  264 S  0.0  0.0   0:00.00 nsd
     1533 root      18   0 62656 1324  760 S  0.0  0.0   0:00.09 sshd
     1541 root      18   0 21664  932  720 S  0.0  0.0   0:00.00 xinetd
     1890 root      15   0 39360 1644 1128 S  0.0  0.0   0:00.05 pure-ftpd
     1892 root      16   0 37036 1608 1252 S  0.0  0.0   0:00.06 pure-authd
     1900 root      15   0 20888 1192  584 S  0.0  0.0   0:00.28 crond
     1991 root      15   0 28048 2988 1080 S  0.0  0.1   0:00.93 cphulkd
     2009 root      18   0 93012  14m 1820 S  0.0  0.3   0:00.00 cpdavd
     2040 root      15   0 34976 6020 1372 S  0.0  0.1   0:00.48 queueprocd
     3099 root      33  18 24012 2976  980 S  0.0  0.1   0:00.08 cpanellogd
     3152 root      18   0 46760  812  416 S  0.0  0.0   0:00.00 saslauthd
     3153 root      18   0 46760  552  156 S  0.0  0.0   0:00.00 saslauthd
     9292 root      15   0 44612 8132 2220 S  0.0  0.2   0:03.31 tailwatchd
    
    Code:
    
    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
     r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
     0  0      0 3138760      0      0    0    0   139  1009    0  173 43  5 44  8  0
     4  0      0 2232044      0      0    0    0     8    52    0 3497 45  5 31 19  0
     3  0      0 2825780      0      0    0    0     6   226    0 3627 60  6 33  1  0
     1  0      0 3132928      0      0    0    0     0   243    0 4921 56  7 37  0  0
     0  0      0 3134268      0      0    0    0     7    34    0 4657 44  5 49  1  0
    
    I've removed eaccelerator and zendoptimizer (heard that it conflicts with ioncube loader which I need more than it).
     
    #3 laztrix, Feb 12, 2012
    Last edited: Feb 12, 2012
  4. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    My server is going away more often know. I'd appreciate if you guys could help me, thanks.
     
  5. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,447
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    What do you mean going away more?

    If there's some sort of issue with an account on your server, I'm not sure tweaking my.cnf is the best solution to solve that. Helpful sure, but if the account's got some sort of poorly coded script running beating up your servers resources I'd be looking at that account as well. More so if its taking out the server.
     
  6. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    I considered that and checked accounts if thats the case. The softwares are up to date and use their own caching for better performance. I remain with the idea that mysql needs fine tuning to resolve this problem.
     
  7. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    Have you reviewed the WHM > Daily Process Logs to see if any specific users are showing high CPU, memory or MySQL usage?

    Next, you would likely want to enable the general and slow query logs for MySQL to try to go through them to see the highest using databases if you believe it is MySQL. There is a tool called mysqlsla that goes through those logs once enabled to determine database user size. I discuss using mysqlsla in my MySQL Optimization presentation on slide 25 of the PDF file. I discuss enabling general and slow query logs in slides 14 and 15 of that same PDF. The PDF is located at the following location:

    http://www.cpanel.net/2010_Slides/MySQL_Optimization.pdf

    The video presentation is at this location:

    MySQL Optimization | cPanel Video Site

    Thanks!
     
  8. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    I've read that pdf actually and watched that video. Unfortunately I'm not an expert and I'm very very stuck!! I'm desperately trying to get to the bottom of this but failing. Would anyone please look at my posting here and post a my.conf that I can use. I'm very tired and sleepless.
     
  9. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    I really suggest enabling the logging. That matters more than anything. Since you are using MySQL 5.1, you'd put under the [mysqld] section of /etc/my.cnf the following directives:

    Code:
    general_log
    slow_query_log
    After that, restart MySQL and you should see two files in /var/lib/mysql, one called hostname.log and one called hostname-slow.log where hostname is the short name of the server.

    After you've given the log 24 hours to process, then install and use mysqlsla to go through the log files:

    Code:
    cd /root
    wget http://hackmysql.com/scripts/mysqlsla
    chmod 755 mysqlsla*
    mv mysqlsla* mysqlsla
    Usage for checking logs:

    Code:
    /root/mysqlsla -lt general /var/lib/mysql/hostname.log
    /root/mysqlsla -lt slow /var/lib/mysql/hostname-slow.log
    Again, replace hostname with the server's hostname.

    Of note, the general log will become large quickly, so if you don't have a lot of space in /var, you'd probably want to remove the "general_log" line in /etc/my.cnf after 24-48 hours or otherwise symlink the /var/lib/mysql/hostname.log file to a larger partition.

    I've pretty much quit providing suggestions for other /etc/my.cnf directives for a couple of reasons. First, logging is essential and using the logging tool to go through them is part of the process. I don't see most other people recommending it on the forum, but yet my presentation repeatedly emphasized the importance of logging. That's the most vital aspect of optimizing, since you need to help track down high usage databases not just change settings.

    Next, mysqltuner.pl provides specifics when you run it on what values to change. Part of the learning process is not to be given a list of /etc/my.cnf values to use (that's what those templates and guides all do and many times have invalid settings in them). You are learning the directives if you have to change them yourself. The directives are clearly defined in mysqltuner.pl output. If you have specific questions on the output it provides, I and others would be happy to cover those, but this process involves the server administrator to take action on learning how to optimize and go through logs.

    Thanks!
     
  10. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    It seems there is alreay a slow.log in /var/lib/mysql and when I ran ./mysqlsla -lt slow /var/lib/mysql/myhostname-slow.log bunch of data flow out the screen.

    I appreciate you wanting to "teach me how to fish" I really do. Just I'm at a point where I consider hiring someone to optimize my database since I've read tens of pages, made bunch of things that didn't really work.
     
  11. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Under MySQL 5.1, the /etc/my.cnf entry has to either be "slow_query_log" or "slow_query_log_file=/path/name.log" which isn't what you have in your /etc/my.cnf file. You are using the MySQL 5.0 syntax.

    Please consider that I'm trying to provide assistance in what is important here (logging first and foremost), but when I provide direction on changes to make, there's a reason I'm telling you to make that change. It's based on the fact I know the directives used for the various MySQL versions, and using the wrong directive will cause the logging to not function. You cannot use "log-slow-queries" if MySQL is actually MySQL 5.1, since that's a MySQL 5.0 directive. If you do use "slow_query_log" (the correct MySQL 5.1 directive) and restart MySQL, the log file will be called /var/lib/mysql/hostname-slow.log and you'd have to wait 24 hours for the data.

    If you prefer to hire someone to help you to do this action, you might look into our system administrator catalog:

    http://www.cpanel.net/application-catalog/category/cpanelwhm-developers/
     
  12. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    I have posted my mysqltuner results here in this thread. alphawolf50 asked me about additional information which I provided. Given these data what would you suggest my my.conf should look like?

    Thanks.
     
  13. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The results provided initially were those taken less than 24 hours after a MySQL restart:

    Results aren't accurate unless you wait 24 hours. This also gives you the time to get that logging that must be enabled and to install mysqlsla.

    Next, once you have mysqltuner.pl results that are at least 24 hours or longer for MySQL being up, you'll want to view the variables under "Variables to adjust:" section. mysqltuner.pl provides the actual variable name and value to use (greater or equal to). Once you have that information, use the suggestions it provides, then post what you'd put into your /etc/my.cnf file, I am will be more than willing to state if I agree with what you would be using.

    If you don't want someone who will be teaching you how to fish, you are definitely welcome to wait for someone else to come along and provide all the data to you. Providing a series of variables in /etc/my.cnf isn't a be all, end all situation here. The logging will be more useful in telling you the high using databases, which really matters on an active server.
     
  14. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Code:
    [mysqld]
    wait_timeout = 28800
    query_cache_size= 9M
    tmp_table_size=32M
    max_heap_table_size=32M
    innodb_buffer_pool_size= 800M
    key_buffer=1M
    join_buffer_size=1M
    table_cache=2000
    table_definition_cache = 2048
    query_cache_limit=2M
    query_cache_size=32M
    query_cache_type=1
    max_connections=300
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=40
    max_allowed_packet=16M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    max_allowed_packet = 16M
    skip-locking
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    Here it is, thanks.
     
  15. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    Was this based on mysqltuner.pl results, because the last results we have for it were your initial post that had these listed for variables to adjust:

    mysqltuner.pl was used under MySQL being up less than 24 hours at that point. Do you have more recent results for it that show MySQL running more than 24 hours? Otherwise, how did you come about adding all these variables with values that highly differ from mysqltuner.pl suggestions?

    Please note that you even have variables it didn't suggest adding, including ones that aren't named properly such as "key_buffer" which is called "key_buffer_size", "skip-locking" which is the default and now called "skip-external-locking" and shouldn't be in /etc/my.cnf file, "thread_concurrency" which doesn't exist under Linux or FreeBSD and shouldn't be in /etc/my.cnf file. It looks like some online guide with wrong values was used in place of your better /etc/my.cnf that you had originally.

    So, let's do this. Revert the /etc/my.cnf file to the original one without all this other stuff that isn't valid. Add the values mysqltuner.pl originally suggested, which I pasted above. Add the logging lines, and then paste what you have configured. If you need the original /etc/my.cnf results, they were these and I've even cleaned those up to be the correct current syntax in alphabetical order along with adding the right logging lines:

    Code:
    [mysqld]
    general_log
    max_allowed_packet = 16777216
    max_connections=500
    slow_query_log
    wait_timeout = 28800
    The safe-show-databases was removed as it's also default.

    Now, just add the suggestions myslqtuner.pl provided to these values:

    I don't have the exact values, but all you really need to do is copy these into /etc/my.cnf below the others noted, remove the non-numeric parts so you have stuff like "innodb_buffer_pool-size = 728M" and you are set. Then restart MySQL and wait 24 hours before running mysqltuner.pl again.

    When you post back, if you have the mysqlsla results and the mysqltuner.pl results, that would be helpful.

    Thanks!
     
  16. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Mysql was restarted couple hours ago and I just added mysqlsla.

    Here are the mysqltuner results:


    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 596M (Tables: 1115)
    [--] Data in InnoDB tables: 669M (Tables: 403)
    [--] Data in MEMORY tables: 1M (Tables: 17)
    [!!] Total fragmented tables: 421
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 46m 15s (438K q [158.015 qps], 9K conn, TX: 1B, RX: 243M)
    [--] Reads / Writes: 41% / 59%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (34% of installed RAM)
    [OK] Slow queries: 0% (10/438K)
    [OK] Highest usage of available connections: 3% (19/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/249.8M
    [OK] Key buffer hit rate: 97.7% (2M cached / 48K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10K sorts)
    [!!] Joins performed without indexes: 584
    [!!] Temporary tables created on disk: 42% (1K on disk / 2K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 33K opened)
    [OK] Open file limit used: 1% (34/2K)
    [OK] Table locks acquired immediately: 99% (516K immediate / 516K locks)
    [!!] InnoDB data size / buffer pool: 669.1M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 669M)
    I didn't leave a space between the variables and =, is this ok ?

    [mysqld]
    general_log
    max_allowed_packet = 16777216
    max_connections=500
    slow_query_log
    wait_timeout = 28800
    query_cache_size=8M
    join_buffer_size=128
    tmp_table_size=16M
    max_heap_table_size= 16M
    thread_cache_size=4
    table_cache=64
    innodb_buffer_pool_size=728M
     
  17. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Code:
    Count         : 96 (0.31%)
    Connection ID : 132
    Database      : ddd_wrdp1
    Users         :
            eeee_user@localhost : 65.62% (63) of query, 17.77% (5520) of all users
            dddd_wrdp1@localhost : 29.17% (28) of query, 3.67% (1140) of all users
            kkkkk_frm@localhost : 4.17% (4) of query, 0.50% (154) of all users
            aaaaa_gebelik@localhost : 1.04% (1) of query, 0.07% (23) of all users
    
    Query abstract:
    SELECT option_name, option_value FROM wp_options WHERE autoload = 'S'
    
    Query sample:
    SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
    
    ______________________________________________________________________ 009 ___
    Count         : 81 (0.26%)
    Connection ID : 132
    Database      : dddd_wrdp1
    Users         :
            eeee_user@localhost : 83.95% (68) of query, 17.77% (5520) of all users
            dddd_wrdp1@localhost : 12.35% (10) of query, 3.67% (1140) of all users
            kkkk_frm@localhost : 2.47% (2) of query, 0.50% (154) of all users
            aaaa_gebelik@localhost : 1.23% (1) of query, 0.07% (23) of all users
    
    Query abstract:
    SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (N1)
    
    Query sample:
    SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (13561)
    
    ______________________________________________________________________ 010 ___
    Count         : 80 (0.26%)
    Connection ID : 312
    Database      : eeee_vt
    Users         :
            eeee_user@localhost : 78.75% (63) of query, 17.77% (5520) of all users
            kkkk_frm@localhost : 17.50% (14) of query, 0.50% (154) of all users
            dddd_wrdp1@localhost : 3.75% (3) of query, 3.67% (1140) of all users
    
    Code:
    Report for slow logs: /var/lib/mysql/de1-slow.log
    19 queries total, 15 unique
    Sorted by 't_sum'
    Grand Totals: Time 429 s, Lock 0 s, Rows sent 10.56k, Rows Examined 74.54k
    
    
    ______________________________________________________________________ 001 ___
    Count         : 1  (5.26%)
    Time          : 117.468887 s total, 117.468887 s avg, 117.468887 s to 117.468887 s max  (27.38%)
    Lock Time (s) : 267.355 ms total, 267.355 ms avg, 267.355 ms to 267.355 ms max  (99.85%)
    Rows sent     : 3 avg, 3 to 3 max  (0.03%)
    Rows examined : 1.54k avg, 1.54k to 1.54k max  (2.07%)
    Database      :
    Users         :
            root@localhost  : 100.00% (1) of query, 15.79% (3) of all users
    
    Query abstract:
    SET timestamp=N; SELECT engine,SUM(data_length),COUNT(engine) FROM information_schema.tables WHERE table_schema NOT IN (S2) AND engine IS NOT NULL GROUP BY engine ORDER BY engine ASC;
    
    Query sample:
    SET timestamp=1329210400;
    SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
    
    ______________________________________________________________________ 002 ___
    Count         : 4  (21.05%)
    Time          : 84.047582 s total, 21.011896 s avg, 14.928337 s to 23.985125 s max  (19.59%)
    Lock Time (s) : 46 µs total, 12 µs avg, 10 µs to 13 µs max  (0.02%)
    Rows sent     : 0 avg, 0 to 0 max  (0.00%)
    Rows examined : 8.51k avg, 8.32k to 8.59k max  (45.69%)
    Database      : annekkkk_xendb1
    Users         :
            annekkkk_xenuse@localhost  : 100.00% (4) of query, 57.89% (11) of all users
    
    Query abstract:
    SET timestamp=N; DELETE FROM xf_template_compiled;
    
    Query sample:
    SET timestamp=1329207828;
    DELETE FROM xf_template_compiled;
    
    ______________________________________________________________________ 003 ___
    Count         : 1  (5.26%)
    Time          : 41.827316 s total, 41.827316 s avg, 41.827316 s to 41.827316 s max  (9.75%)
    Lock Time (s) : 60 µs total, 60 µs avg, 60 µs to 60 µs max  (0.02%)
    Rows sent     : 1 avg, 1 to 1 max  (0.01%)
    Rows examined : 1.56k avg, 1.56k to 1.56k max  (2.09%)
    Database      :
    Users         :
            root@localhost  : 100.00% (1) of query, 15.79% (3) of all users
    
    Query abstract:
    SET timestamp=N; SELECT ifnull(SUM(index_length),N) FROM information_schema.tables WHERE table_schema NOT IN (S1) AND engine = 'S';
    
    Query sample:
    SET timestamp=1329210454;
    SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM';
    
    ______________________________________________________________________ 004 ___
    Count         : 1  (5.26%)
    Time          : 38.339258 s total, 38.339258 s avg, 38.339258 s to 38.339258 s max  (8.94%)
    Lock Time (s) : 19 µs total, 19 µs avg, 19 µs to 19 µs max  (0.01%)
    Rows sent     : 9.43k avg, 9.43k to 9.43k max  (89.32%)
    Rows examined : 9.43k avg, 9.43k to 9.43k max  (12.65%)
    Database      :
    Users         :
            annekkkk_xenuse@localhost  : 100.00% (1) of query, 57.89% (11) of all users
    
    Query abstract:
    SET timestamp=N; SELECT user_id, position FROM xf_post WHERE thread_id = 'S' ORDER BY position ASC;
    
    Query sample:
    SET timestamp=1329210525;
    SELECT `user_id`, `position`
                                            FROM `xf_post`
                                            WHERE `thread_id` = '2353'
                                            ORDER BY `position` ASC;
    
    ______________________________________________________________________ 005 ___
    Count         : 1  (5.26%)
    Time          : 25.390096 s total, 25.390096 s avg, 25.390096 s to 25.390096 s max  (5.92%)
    Lock Time (s) : 14 µs total, 14 µs avg, 14 µs to 14 µs max  (0.01%)
    Rows sent     : 618 avg, 618 to 618 max  (5.85%)
    Rows examined : 1.24k avg, 1.24k to 1.24k max  (1.66%)
    Database      :
    Users         :
            annekkkk_xenuse@localhost  : 100.00% (1) of query, 57.89% (11) of all users
    
    Query abstract:
    SET timestamp=N; SELECT post_id, thread_id, user_id, message_state, likes, post_date FROM xf_post WHERE thread_id = 'S' ORDER BY position ASC, post_date ASC;
    
    Query sample:
    SET timestamp=1329208964;
    SELECT post_id, thread_id, user_id, message_state, likes, post_date
                            FROM xf_post
                            WHERE thread_id = '2942'
                            ORDER BY position ASC, post_date ASC;
    
    ______________________________________________________________________ 006 ___
    Count         : 2  (10.53%)
    Time          : 20.3956 s total, 10.1978 s avg, 10.094886 s to 10.300714 s max  (4.75%)
    Lock Time (s) : 85 µs total, 43 µs avg, 40 µs to 45 µs max  (0.03%)
    Rows sent     : 0 avg, 0 to 0 max  (0.00%)
    Rows examined : 0 avg, 0 to 0 max  (0.00%)
    Database      : diviks_xf54
    Users         :
            ddddd_xf54user@localhost  : 100.00% (2) of query, 21.05% (4) of all users
    
    Query abstract:
    SET timestamp=N; INSERT INTO xf_search (search_results, result_count, search_type, search_query, search_constraints, search_order, search_grouping, user_results, warnings, user_id, search_date, query_hash) VALUES ('S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S')1;
    
    Query sample:
    SET timestamp=1329207828;
    INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_constraints`, `search_order`, `search_grouping`, `user_results`, `warnings`, `user_id`, `search_date`, `query_hash`) VALUES ('[[\"thread\",31442],[\"thread\",28116],[\"thread\",27605],[\"thread\",25008],[\"thread\",24085],[\"thread\",17304],[\"thread\",29300],[\"thread\",32649],[\"thread\",32637],[\"thread\",32648],[\"thread\",32647],[\"thread\",32645],[\"thread\",32644],[\"thread\",29594],[\"thread\",30206],[\"thread\",32643],[\"thread\",31580],[\"thread\",28058],[\"thread\",32642],[\"thread\",32641],[\"thread\",32640],[\"thread\",32639],[\"thread\",28057],[\"thread\",30319],[\"thread\",30320],[\"thread\",26987],[\"thread\",26988],[\"thread\",28657],[\"thread\",28656],[\"thread\",26981],[\"thread\",26982],[\"thread\",26979],[\"thread\",26980],[\"thread\",13272],[\"thread\",6304],[\"thread\",26985],[\"thread\",26986],[\"thread\",32636],[\"thread\",31671],[\"thread\",31688],[\"thread\",26991],[\"thread\",31715],[\"thread\",32635],[\"thread\",32634],[\"thread\",32633],[\"thread\",32586],[\"thread\",31237],[\"thread\",30291],[\"thread\",31597],[\"thread\",25915],[\"thread\",32607],[\"thread\",32627],[\"thread\",32632],[\"thread\",32631],[\"thread\",32630],[\"thread\",32629],[\"thread\",32628],[\"thread\",32626],[\"thread\",32625],[\"thread\",32624],[\"thread\",32623],[\"thread\",17153],[\"thread\",16559],[\"thread\",26565],[\"thread\",26231],[\"thread\",32622],[\"thread\",32621],[\"thread\",22870],[\"thread\",27394],[\"thread\",29298],[\"thread\",27321],[\"thread\",26772],[\"thread\",31683],[\"thread\",27395],[\"thread\",32620],[\"thread\",32619],[\"thread\",32599],[\"thread\",32618],[\"thread\",32611],[\"thread\",32617],[\"thread\",32616],[\"thread\",32615],[\"thread\",32614],[\"thread\",32613],[\"thread\",32612],[\"thread\",32610],[\"thread\",32609],[\"thread\",32608],[\"thread\",32606],[\"thread\",32605],[\"thread\",32604],[\"thread\",32603],[\"thread\",32602],[\"thread\",32601],[\"thread\",30302],[\"thread\",28358],[\"thread\",32563],[\"thread\",29500],[\"thread\",32447],[\"thread\",32448]]', '100', 'recent-threads', '', '[]', 'date', '0', '', '[]', '0', '1329207818', 'b956e81b6bb80d5de0e3955b72b3cadf');
    
    ______________________________________________________________________ 007 ___
    Count         : 1  (5.26%)
    Time          : 13.958445 s total, 13.958445 s avg, 13.958445 s to 13.958445 s max  (3.25%)
    Lock Time (s) : 10 µs total, 10 µs avg, 10 µs to 10 µs max  (0.00%)
    Rows sent     : 200 avg, 200 to 200 max  (1.89%)
    Rows examined : 400 avg, 400 to 400 max  (0.54%)
    Database      : annekuzu_xendb1
    Users         :
            annekkkk_xenuse@localhost  : 100.00% (1) of query, 57.89% (11) of all users
    
    Query abstract:
    SET timestamp=N; SELECT thread.* , forum.* FROM xf_thread AS thread INNER JOIN xf_forum AS forum ON (forum.node_id = thread.node_id) WHERE (thread.discussion_state IN (S1)) AND (thread.last_post_date > N) AND (forum.find_new = N) ORDER BY thread.last_post_date DESC LIMIT N;
    
    Query sample:
    SET timestamp=1329210163;
    SELECT thread.*
                                            ,
                                            forum.*
                                    FROM xf_thread AS thread
                                            INNER JOIN xf_forum AS forum ON
                                                    (forum.node_id = thread.node_id)
                                    WHERE (thread.discussion_state IN ('visible')) AND (thread.last_post_date > 1328605349) AND (forum.find_new = 1)
                                    ORDER BY thread.last_post_date DESC
                             LIMIT 200;
    
    ______________________________________________________________________ 008 ___
    Count         : 1  (5.26%)
    Time          : 12.270509 s total, 12.270509 s avg, 12.270509 s to 12.270509 s max  (2.86%)
    Lock Time (s) : 25 µs total, 25 µs avg, 25 µs to 25 µs max  (0.01%)
    Rows sent     : 0 avg, 0 to 0 max  (0.00%)
    Rows examined : 1 avg, 1 to 1 max  (0.00%)
    Database      :
    Users         :
            annekkkk_xenuse@localhost  : 100.00% (1) of query, 57.89% (11) of all users
    
    Query abstract:
    SET timestamp=N; UPDATE dark_azucloud_terms_pages SET hits = hits + N, last_clicked=UNIX_TIMESTAMP() WHERE term_id = 'S' AND route = 'S';
    
    Query sample:
    SET timestamp=1329210595;
    update dark_azucloud_terms_pages
                            set hits = hits + 1, last_clicked=UNIX_TIMESTAMP()
                            where term_id = '60430' and route = 'threads/sevgililer-günü-için-40-öneri-sevgililer-gününde-yapılabilecek-40-sürpriz.4634/';
    
    ______________________________________________________________________ 009 ___
    Count         : 1  (5.26%)
    Time          : 11.873738 s total, 11.873738 s avg, 11.873738 s to 11.873738 s max  (2.77%)
    Lock Time (s) : 53 µs total, 53 µs avg, 53 µs to 53 µs max  (0.02%)
    Rows sent     : 1 avg, 1 to 1 max  (0.01%)
    Rows examined : 1.53k avg, 1.53k to 1.53k max  (2.06%)
    Database      :
    Users         :
            root@localhost  : 100.00% (1) of query, 15.79% (3) of all users
    
    Query abstract:
    SET timestamp=N; SELECT COUNT(table_name) FROM information_schema.tables WHERE table_schema NOT IN (S2) AND data_free > N AND NOT engine='S';
    
    Query sample:
    SET timestamp=1329210412;
    SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';
    
    ______________________________________________________________________ 010 ___
    Count         : 1  (5.26%)
    Time          : 11.036391 s total, 11.036391 s avg, 11.036391 s to 11.036391 s max  (2.57%)
    Lock Time (s) : 16 µs total, 16 µs avg, 16 µs to 16 µs max  (0.01%)
    Rows sent     : 200 avg, 200 to 200 max  (1.89%)
    Rows examined : 800 avg, 800 to 800 max  (1.07%)
    Database      :
    Users         :
            annekkkk_xenuse@localhost  : 100.00% (1) of query, 57.89% (11) of all users
    
    Query abstract:
    SET timestamp=N; SELECT thread.* , user.*, IF(user.username IS NULL, thread.username, user.username) AS username, node.title AS node_title, permission.cache_value AS node_permission_cache FROM xf_thread AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) INNER JOIN xf_node AS node ON (node.node_id = thread.node_id) LEFT JOIN xf_permission_cache_content AS permission ON (permission.permission_combination_id = N AND permission.content_type = 'S' AND permission.content_id = thread.node_id) WHERE thread.thread_id IN (N200);
    
    Query sample:
    SET timestamp=1329210573;
    SELECT thread.*
                                    ,
                                            user.*, IF(user.username IS NULL, thread.username, user.username) AS username,
                                            node.title AS node_title,
                                    permission.cache_value AS node_permission_cache
                            FROM xf_thread AS thread
                                            LEFT JOIN xf_user AS user ON
                                                    (user.user_id = thread.user_id)
                                            INNER JOIN xf_node AS node ON
                                                    (node.node_id = thread.node_id)
                                    LEFT JOIN xf_permission_cache_content AS permission
                                            ON (permission.permission_combination_id = 1
                                                    AND permission.content_type = 'node'
                                                    AND permission.content_id = thread.node_id)
                            WHERE thread.thread_id IN (4681, 358, 4698, 2942, 1642, 4643, 4626, 4718, 4683, 4692, 4385, 4717, 4699, 4491, 2639, 4715, 4539, 3394, 2157, 4522, 4716, 4714, 4711, 1843, 3453, 4509, 4604, 4635, 3680, 4713, 4700, 4708, 4706, 4023, 4712, 3177, 4696, 4110, 4710, 4109, 4709, 4607, 4653, 2666, 4671, 3428, 4707, 4705, 4704, 4703, 4026, 4568, 4660, 4702, 4652, 4701, 4673, 4061, 4658, 124, 4697, 4695, 4694, 4691, 4693, 4690, 4677, 4689, 4688, 4687, 4686, 4685, 4682, 4684, 4579, 4680, 4679, 4649, 4678, 4674, 4675, 4659, 4676, 4519, 4666, 4632, 3832, 4667, 4672, 4668, 4665, 4670, 4654, 4669, 4644, 4664, 4657, 4663, 4662, 1623, 4661, 4655, 4613, 4656, 4027, 4650, 4651, 2278, 1975, 4634, 565, 4593, 3837, 144, 709, 146, 4576, 4645, 4625, 4640, 4636, 4646, 4647, 1621, 4648, 4198, 4255, 4595, 4476, 4053, 3655, 1069, 4513, 2074, 4036, 4621, 4642, 2842, 3708, 4354, 3414, 4641, 4444, 2836, 3429, 4638, 2805, 1317, 1582, 4637, 1827, 949, 2613, 4125, 1359, 1072, 3929, 2726, 4639, 3881, 4633, 359, 965, 817, 4622, 2667, 2451, 2425, 1152, 4, 2, 362, 978, 2065, 1505, 1513, 1923, 1989, 2052, 972, 2035, 2167, 2366, 3802, 2208, 2205, 3095, 2285, 960, 258, 2066, 2837, 4624, 1277, 681, 679, 4628, 4629, 4627, 1733);
    
     
  18. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    The log got pretty big, 1 GB. I suspect my space will not be enough for 24 hours.

    Regarding my.conf, I didn't leave a space between the variables and =, is this ok ?

    [mysqld]
    general_log
    max_allowed_packet = 16777216
    max_connections=500
    slow_query_log
    wait_timeout = 28800
    query_cache_size=8M
    join_buffer_size=128
    tmp_table_size=16M
    max_heap_table_size= 16M
    thread_cache_size=4
    table_cache=64
    innodb_buffer_pool_size=728M
     
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    That's fine that you haven't left any space between the = sign and the value. For /etc/my.cnf, the changes appear fine other than you would probably want to change this one:

    table_cache=64

    Under MySQL 5.1, it's called table_open_cache instead so it would be "table_open_cache=64", but mysqltuner.pl doesn't recognize the difference between the variable names. I'm just aware of that change, so I'd suggest revising it. You could always wait until it's been 24 hours and you make further revisions, though, to make that change.

    For the logs, you'll want to wait 24 hours to get more data before running mysqlsla on them. Of note, you'll likely have to wait about 10-20 minutes for it to go through the general log to gather details, since it will be a lot more data for it to analyze.
     
  20. laztrix

    laztrix Active Member

    Joined:
    Feb 7, 2012
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    I posted a portion of the data in my previous post which takes it from the 1 GB log that has been created (general_log) the slow.log is relatively small compared to the other one.

    And I forget to thank you for all your detailed answers, which are more than helpful and paitently written. Please excuse my exhaustion and inpatience Tristian.
     
Loading...

Share This Page