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.

high load problems , i'm out of ideas

Discussion in 'Workarounds and Optimization' started by menntarra_34, Sep 6, 2011.

  1. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello, my server has high load problems...
    I have 2 XEON E5506 cpus which means 2x4 cores = 8 , so when my load is more than 8, i should be worried... And now it is constantly at about 16-24 !!!
    I have 32gb memory. What is strange is that my websites are working fine.
    My memory swap is zero, also my cpu %wa is pretty low!
    So i have no clue what could cause problems, please advise...
    Also here is output from top:
    Code:
    top - 11:55:48 up 5 days, 25 min,  1 user,  load average: 16.63, 16.98, 17.10
    Tasks: 450 total,  13 running, 436 sleeping,   0 stopped,   1 zombie
    Cpu(s): 74.8%us, 18.3%sy,  0.0%ni,  1.5%id,  0.7%wa,  0.2%hi,  4.5%si,  0.0%st
    Mem:  32950216k total, 32827592k used,   122624k free,   143960k buffers
    Swap: 34996216k total,      296k used, 34995920k free, 22197412k cached
    
    as you can see cpu usage is HIGH, what my question is: 18.3% sy is high too, isn't it? If yes, how can i found what is causing this high system usage?
    Thanks in advance


    ps.: here is my mysqltuner.pl script output (NOTE: now you see that mysql had less then 48 hours uptime, but i have the same ouput after several days, just i tried couple of thing recently , that is why you see that, so you can rely on this output, and also NOTE that i have fragmented tables, but that is because high inserts/updates, i can't help it cause my scripts need those updates/inserts...)
    Code:
     >>  MySQLTuner 1.1.2 - Major Hayden <[EMAIL="major@mhtx.net"]major@mhtx.net[/EMAIL]>
     >>  Bug reports, feature requests, and downloads at [URL]http://mysqltuner.com/[/URL]
     >>  Run with '--help' for additional options and output filtering
    -------- 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: 8G (Tables: 370)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 25
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17h 10m 16s (23M q [385.115 qps], 827K conn, TX: 184B, RX: 52B)
    [--] Reads / Writes: 87% / 13%
    [--] Total buffers: 5.8G global + 12.1M per thread (512 max threads)
    [OK] Maximum possible memory usage: 11.9G (37% of installed RAM)
    [OK] Slow queries: 0% (1K/23M)
    [OK] Highest usage of available connections: 38% (199/512)
    [OK] Key buffer size / total MyISAM indexes: 4.0G/2.0G
    [OK] Key buffer hit rate: 100.0% (6B cached / 1M reads)
    [OK] Query cache efficiency: 56.5% (11M cached / 19M selects)
    [!!] Query cache prunes per day: 122125
    [OK] Sorts requiring temporary tables: 1% (7K temp sorts / 698K sorts)
    [OK] Temporary tables created on disk: 16% (23K on disk / 142K total)
    [OK] Thread cache hit rate: 99% (199 created / 827K connections)
    [OK] Table cache hit rate: 29% (1K open / 4K opened)
    [OK] Open file limit used: 22% (1K/7K)
    [OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
    [OK] InnoDB data size / buffer pool: 144.0K/8.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Increasing the query_cache size over 128M may reduce performance
    Variables to adjust:
        query_cache_size (> 1G) [see warning above]
    
    
    Here is my.cnf:
    Code:
    
     skip-external-locking
    skip-name-resolve
     
    key_buffer_size = 4096M
    sort_buffer_size = 4M
    read_buffer_size = 3M
    read_rnd_buffer_size = 4M
    max_allowed_packet = 1M
    thread_stack = 128K
    table_open_cache = 3640
    table_definition_cache = 3640
    open_files_limit = 4544
    #thread_concurrency = 16 //can only be used in solaris system
    thread_cache_size = 320
    max_heap_table_size = 856M
    tmp_table_size = 856M
    query_cache_limit = 128M
    query_cache_size = 1024M
    query_cache_type = 1
    query_cache_min_res_unit= 1K
    join_buffer_size = 1M
    max_connections = 512
    #long_query_time = 10
    #log-slow-queries = /var/log/slowq.log
    #log = /var/log/mysqld.log
    log-error = /var/log/mysqld.error.log
    wait_timeout=6
    connect_timeout=6
    interactive_timeout = 300
    #wait_timeout = 300
    concurrent_insert=1
    low_priority_updates=1
    #skip-bdb
    local-infile=0
    
    
    About apache: i have keepalive on, with keepalive timeout = 1
    And here is my prefork info:
    StartServers 256
    MinSpareServers 256
    MaxSpareServers 256
    ServerLimit 256
    MaxClients 256
    MaxRequestsPerChild 0
     
  2. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    I think your system is spending a lot of time allocating/managing memory, but before we can give a lot of specific recommendations, we need to know a bit about your data/queries. What is the average size of your query result sets? You can find this by first running this SQL statement:
    Code:
    SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Qcache%'
    Then using this formula:
    (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache = size of average result set.

    For the formula above, your query_cache_size is 1073741824 (1G expressed in bytes). You'll want to set your query_cache_min_res_unit to something close to your average result set size. Remember that you'll get an answer expressed in bytes, so you'll have to divide by 1024 to get the number in KB. If your average result set is much larger than 1K, then MySQL is allocating 1K at a time many many times, which is expensive. That's why you want to set this to a size near your average result set's size.

    Other things that may help:

    • myisam_use_mmap (only use on a 64-bit system)
    • Your query_cache_limit is probably way too high. If a lot of your result sets are in the 1K range, then a single 128MB result set can wipe out 131072 of your smaller queries. You may get far fewer Qcache_lowmem_prunes by reducing this number considerably (maybe 3-4MB?).
    • sort_buffer_size: You'll need to experiment on this one, but it may be too large. The sort buffer is allocated for each thread, and there is a threshold at 256K where memory allocation becomes much slower. If you have to filesort a lot of data, your 4M sort buffer might be warranted, but in a lot of cases 256K will perform better. Experiment to find out, and think about rewriting some queries and adjusting indexes where possible.
    • Consider setting concurrent_insert=2 instead of 1. This may fragment your tables more, but in some cases increases performance.
    • Once you've got your query_cache_min_res_unit and query_cache_limit sorted out, consider reducing query_cache_size. The larger the query cache, the more overhead is incurred from maintaining it. It can take a lot of time for mysql to look over 1G of query_cache when invalidating queries due to a table update. Similarly, mysql must look over this 1G of cache for each query in order to see if the query already exists there. The query_cache is single-threaded for some (maybe all, not certain) operations, so it can become a bottleneck if too much time is spent there.

    It's generally recommended to only change one variable at a time and see how performance changes, so do keep that in mind.

    On the apache side, you might want to look at mpm_worker if you run PHP as CGI (suPHP/fcgid).
     
  3. JawadArshad

    JawadArshad Well-Known Member
    PartnerNOC

    Joined:
    Apr 8, 2008
    Messages:
    447
    Likes Received:
    4
    Trophy Points:
    18
    Location:
    PK
    cPanel Access Level:
    DataCenter Provider
    Is there any special reason for high min and maxspareservers values. Do you have too much traffic on the web server. Could you output results from the command below.

    Code:
    service httpd status
    
    Secondly, what are the top processes in terms of CPU. Could you list a few.
     
  4. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    It looks like he's trying to lock the # of servers at 256, since he starts that many, limits it to that many, and sets both the min and max servers to that number as well. His mysqltuner output says that the max connections were 199 in the 17hr period for which he ran it, so it is very likely that he has many simultaneous connections on the web server as well.
     
  5. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    alphawolf50 , thanks for the advices, i'll try them and paste results here, but i have to wait, as it takes time to show real results...

    About my server: it has a lot of traffic, and i wanted to configure prefork to meet that exact traffic, not to have unnecessary connections waiting, that's why i set that to 256, i know that my max is around 800 (32Gb- Mysql memory usage/ avg. user memory consumption)


    i know i have high mysql usage (because i have 350-400 gps), but it does not makes this high load, just here is the example i recorded today:

    mysql uses about 338% CPU, but that is OKAY! Load is 5.72:



    Code:
    top - 16:11:49 up 5 days,  4:41,  1 user,  load average: 5.72, 6.25, 7.04
    Tasks: 448 total,   1 running, 447 sleeping,   0 stopped,   0 zombie
    Cpu(s): 48.4%us, 15.2%sy,  0.0%ni, 20.8%id, 10.8%wa,  0.3%hi,  4.6%si,  0.0%st
    Mem:  32950216k total, 32795368k used,   154848k free,   144844k buffers
    Swap: 34996216k total,      192k used, 34996024k free, 24051068k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     5382 mysql     15   0 5683m 2.8g 4404 S 338.0  8.8 681:55.53 mysqld
    14034 nobody    15   0  254m  31m  11m S 20.5  0.1   1:41.59 httpd
    14160 nobody    16   0  253m  32m  13m D 14.9  0.1   1:37.69 httpd
    14075 nobody    15   0  252m  31m  12m S 12.2  0.1   1:38.79 httpd
     4798 nobody    16   0  658m 343m 288m D 10.9  1.1   1224:49 lighttpd
    14142 nobody    15   0  260m  38m  11m S 10.3  0.1   1:41.42 httpd
    14145 nobody    15   0  251m  31m  12m S  6.3  0.1   1:38.43 httpd

    Here mysql uses: 283% , but load is 13.80 ( it just dropped from 17...)

    Code:
    top - 16:56:54 up 5 days,  5:26,  1 user,  load average: 13.80, 14.37, 11.52
    Tasks: 448 total,  10 running, 438 sleeping,   0 stopped,   0 zombie
    Cpu(s): 61.8%us, 13.5%sy,  0.0%ni, 14.2%id,  4.3%wa,  0.3%hi,  5.9%si,  0.0%st
    Mem:  32950216k total, 32778408k used,   171808k free,   142180k buffers
    Swap: 34996216k total,      192k used, 34996024k free, 23590200k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  RUSER    COMMAND
     5382 mysql     15   0 5922m 3.0g 4408 S 283.9  9.6 795:20.24 mysql    mysqld
    14126 nobody    17   0  254m  33m  13m R 51.0  0.1   2:05.92 nobody   httpd
    14042 nobody    17   0  252m  32m  13m R 41.0  0.1   2:16.45 nobody   httpd
    14185 nobody    15   0  255m  34m  12m S 21.2  0.1   2:17.98 nobody   httpd
    13989 nobody    15   0  259m  38m  16m S 20.8  0.1   2:20.51 nobody   httpd
    14138 nobody    15   0  255m  36m  13m S 18.2  0.1   2:23.18 nobody   httpd
    14060 nobody    15   0  251m  31m  12m S 14.2  0.1   1:53.44 nobody   httpd
    14107 nobody    15   0  253m  32m  13m S 11.2  0.1   2:06.62 nobody   httpd
     4798 nobody    17   0  914m 457m 402m R  9.9  1.4   1237:19 nobody   lighttpd
    With my mysql config, i was all okay since about 2-3 days back, when i realized this kind of high load...

    Additional info: i use "flush query cache" 2 times/hour , what do you think about it ?


    I use PHP as DSO and was advised to use prefork because it is much more reliable, i also read a lot of articles about prefork vs worker...
     
    #5 menntarra_34, Sep 6, 2011
    Last edited: Sep 6, 2011
  6. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    output of "service httpd status"

    Apache Server Status for localhost

    Server Version: Apache/2.2.19 (Unix) mod_ssl/2.2.19
    OpenSSL/0.9.8e-fips-rhel5 mod_auth_passthrough/2.1 mod_bwlimited/1.4
    FrontPage/5.0.2.2635 PHP/5.2.9

    Server Built: Jun 13 2011 08:11:14
    _________________________________________________________________

    Current Time: Tuesday, 06-Sep-2011 19:08:13 EDT
    Restart Time: Tuesday, 06-Sep-2011 13:04:57 EDT
    Parent Server Generation: 4
    Server uptime: 6 hours 3 minutes 15 seconds
    168 requests currently being processed, 88 idle workers

    KCC_KCW_K_CCCK_KKKKCC_C__KK_KK_CKK_K___CK_KKK_KWC_C_CC_CKCKCKK_C
    K__CK_K_KC_K_CKCCK_KCK_WC_C___CKKKC_K_K_K_C_KC_K_C_CCCCKCKKWK_WC
    KC_K_CCKKC___C_CKCC_CCKKCCCCC__CW___CR_CKKC_KCCKCC__CKC___KK___K
    KK__KK_KKKWK_KK__KC_WKC_CC_WC___CW_K_KWWCKW__K_KC_K__C_K_K__W__K

    Scoreboard Key:
    "_" Waiting for Connection, "S" Starting up, "R" Reading Request,
    "W" Sending Reply, "K" Keepalive (read), "D" DNS Lookup,
    "C" Closing connection, "L" Logging, "G" Gracefully finishing,
    "I" Idle cleanup of worker, "." Open slot with no current process


    I'll post more later when i have mysql results...
     
  7. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    okay now i have some results :
    first: i use php with dso , because it is much faster than other handlers, see article for more:
    /http://boomshadow.net/tech/php-handlers/]DSO vs. CGI vs. suPHP vs. FastCGI | BoomShadow.net

    So that is why i use prefork.c !

    in my.cnf i had query_cache_min_res_unit= 1K , cause my query cache becomes 40% fragmented after 30 minutes, and that is the advise of mysqltuner...
    because of this fragmentation i use a "flush query cache" script, which i run 2 times/hour...
    And one additional thing which might changes things: i use memcached for my very instensive queries, i mentioned that in case it's impotant info.
     
  8. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Sometimes mysqltuner is wrong ;)

    Did you calculate the average size of your query result set? Please report it here.

    "FLUSH QUERY CACHE" on a 1GB query cache is likely locking up mysql for a few seconds every half hour. If you enable slow-query-log and and set long-query-time to 1, I'm certain you'll see it listed every time it's called. It would probably be a good idea to disable this until we get your query cache tuned, or you reduce the size of the cache considerably.

    Since you're using memcached for the "intensive" queries, there really isn't much point in having the query_cache_limit set to 128MB. Drop query_cache_limit down to 2MB and see if your query cache efficiency improves. Memcached is usually faster than MySQL's query cache also, so if you can find a way to get all your caching done via memcached, you'd be better off disabling the query cache entirely.

    RE: dso/prefork vs fcgid/worker -- here's a quote from the article you linked: :)

    They go on to say that the drawback is increased memory usage -- which may or may not be true for you. Many people see either similar or reduced memory footprint with fcgid because PHP is external to the Apache process, so the Apache processes that are serving static content can be much smaller. I noticed, however, that you have lighttpd, so I'll assume this is already serving your static content and say that fcgid alone would increase your memory usage. That's where mpm_worker comes in...

    This is what Apache has to say (here):
    Your site falls under the category of needing "a great deal of scalability" I'd say. Worker reduces memory load by spawning multiple threads to handle requests rather than entire processes. After using mpm_worker for a year, I've haven't noticed any stability issues. Note that when they talked about "stability" of worker, they mean that if something manages to crash one thread it will likely crash the entire child process (while other child processes are unaffected). Prefork is considered more "stable" because a crash will probably be limited to the individual server process that crashed. If your apache processes aren't crashing now, they're not likely to start crashing if you switch to worker/fcgid. And if there are any issues, you can always go back to prefork/dso ;)

    The last benefit of fcgid -- if you're running it with suEXEC (which you should), you'll see the PHP processes running under the account name when you run "top". That makes it much easier to track down the site/user that is eating up your CPU cycles, assuming you have multiple accounts on the server.

    Please remember to post the results of your average query result set size when you get a chance.
     
  9. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello, yes, yesterday when i posted the article about fcgi, i was thinking of trying it +worker mpm.
    Is worker or event better, what do you think?

    Another question about fcgi is: which cache is good eaccelerator, or do i have to change to another (xcache etc) ?

    About mysql
    As i saw from the "show status like '%qcache%';" command , with the formula, i should use 35-50KB
    Strange thing is that i monitored the output of this command, to see to amount of cached queries, however at some point i received this message:

    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id: 25825
    Current database: *** NONE ***


    and the cache was reseted....

    ps.: i also want back to sort_buffer_size=256K
     
  10. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Mpm_event is still considered experimental, and I have no personal experience with it. The design is intended to improve upon worker, but YMMV and I can't personally recommend it.
    Eaccelerator will work fine with fcgid.
    Have you already adjusted query_cache_limit?. If not, I think your average query size is being skewed by a couple of extremely large queries that have been cached. Try dropping query_cache_limit to at least 4M if you haven't changed that yet, and then we'll get new readings on the average query size.

    For the query_cache_min_res_unit, let's go back to the default of 4K. We will probably increase that further later, but as your results seem to be pretty large there is no reason to be down at 1K. To give you an idea how expensive that 1K setting is, if you have 300qps and your average result really is 35K, then MySQL could make ~10500 individual 1K allocations per second.

    Regarding the error, were you using phpMyAdmin or the mysql command line? By chance did the error occur at the same time that your "FLUSH QUERY CACHE" script is set to run?
    Okay. We'll monitor that to see if it needs to be increased or not.

    After you've let MySQL run for a while (preferably 24 hours after reboot), please post your updated my.cnf, the output of mysqltuner, and the output of
    Code:
    SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Qcache%' OR Variable_name='Com_select';
    Try to grab the output of the command above while the query cache is pretty full (Qcache_free_memory is small).
     
  11. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    okay, i'll post results with 3 tests:
    -having cache on for all of my sites on server
    -only having cache for the big traffic site (on demand, SQL_CACHE)
    -only not having cache for the big traffic site (SQL_NO_CACHE)
    with query cache size =800mb, 4k min_res_unit, 4mquery cache, and then the load avg, with the average query result set...


    ps.: that error message (the mysql server has gone away) happened without the "flush query" script...

    and it is strange cause query_cache seems to reset from points like: i had 11000 queries, and then reset to 3000, while setting the query_cache_limit=4M, so maybe i have to lower it even to 1MB?
     
    #11 menntarra_34, Sep 7, 2011
    Last edited: Sep 7, 2011
  12. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Changing some variables regarding the query cache will cause it to purge all queries, or at least all queries that don't meet the new limits.

    Unfortunate tip: if you don't reboot MySQL after making these changes, the numbers reported by mysqltuner will be wrong. Even "FLUSH STATUS" won't fix them, as some status variables (such as com_select) are not flushed by this command, so your query cache hit rate will show much lower than it actually is, among other numbers. So unfortunately, if you want accurate numbers you have to reboot MySQL :(. I only bring that up because you were talking about the query cache being reset, which you would have expected if you were rebooting MySQL.
     
  13. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    no, of course, i know that i have to reboot( i do it after every change in my.cnf), but it is different: qcache_queries_in_cache rise to some point (I'm constantly checking output of "SHOW GLOBAL STATUS WHERE Variable_name LIKE 'qcache%' OR Variable_name='com_select';") , and then suddenly at some point it drops , well now i assume it is because there is a bigger query that wipe out small ones, as you said in your first post??? But why is that , if i have a lot of qcache_free_memory ? So i don't know why it keeps dropping back at some points , while there is free memory?

    oh and now i remember why i didn't choose fcgi: i have 3rd party application that can't be run with fcgi... a lot of things has answers, but i just didn't remember, it was a year ago, when i wanted to change :) I'll check fcgi performance later, and decide to look for a replacement for that app...
     
    #13 menntarra_34, Sep 7, 2011
    Last edited: Sep 7, 2011
  14. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    i did the 3 tests:
    when only big site was used by query cache: poor results, load even went up to 120
    when only big site di not use query cache: it was okay, but not that special
    when all of my site used: nearly the same as when the big site did not use...
    so i decided to test it further with all my sites having query cache on.
    results:

    Code:
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | Com_select              | 3703878   |
    | Qcache_free_blocks      | 13344     |
    | Qcache_free_memory      | 996153200 |
    | Qcache_hits             | 5157337   |
    | Qcache_inserts          | 3587551   |
    | Qcache_lowmem_prunes    | 137448    |
    | Qcache_not_cached       | 100782    |
    | Qcache_queries_in_cache | 10628     |
    | Qcache_total_blocks     | 38712     |
    +-------------------------+-----------+
    
    mysqltuner.pl:
    Code:
    
     >>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- 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: 8G (Tables: 371)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 16
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10h 42m 4s (10M q [280.218 qps], 459K conn, TX: 71B, RX: 18B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 6.3G global + 8.4M per thread (512 max threads)
    [OK] Maximum possible memory usage: 10.5G (33% of installed RAM)
    [OK] Slow queries: 0% (1K/10M)
    [OK] Highest usage of available connections: 56% (291/512)
    [OK] Key buffer size / total MyISAM indexes: 4.0G/2.4G
    [OK] Key buffer hit rate: 99.9% (3B cached / 2M reads)
    [OK] Query cache efficiency: 58.2% (5M cached / 8M selects)
    [!!] Query cache prunes per day: 320626
    [!!] Sorts requiring temporary tables: 41% (143K temp sorts / 340K sorts)
    [OK] Temporary tables created on disk: 18% (16K on disk / 88K total)
    [OK] Thread cache hit rate: 99% (291 created / 459K connections)
    [OK] Table cache hit rate: 68% (1K open / 2K opened)
    [OK] Open file limit used: 41% (1K/4K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
    [OK] InnoDB data size / buffer pool: 144.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Increasing the query_cache size over 128M may reduce performance
    Variables to adjust:
        query_cache_size (> 1G) [see warning above]
        sort_buffer_size (> 256K)
        read_rnd_buffer_size (> 4M)
    
    Primer Tuner:
    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.1.56 x86_64
    
    Uptime = 0 days 10 hrs 40 min 17 sec
    Avg. qps = 279
    Total Questions = 10745869
    Threads Connected = 101
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 1535 out of 10746373 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 320
    Current threads_cached = 177
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 512
    Current threads_connected = 115
    Historic max_used_connections = 291
    The number of used connections is 56% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 208 K
    Current InnoDB data space = 144 K
    Current InnoDB buffer pool free = 90 %
    Current innodb_buffer_pool_size = 8 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 7.86 G
    Configured Max Per-thread Buffers : 4.18 G
    Configured Max Global Buffers : 5.48 G
    Configured Max Memory Limit : 9.67 G
    Physical Memory : 31.42 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 2.42 G
    Current key_buffer_size = 4.00 G
    Key cache miss rate is 1 : 1582
    Key buffer free ratio = 48 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 1.47 G
    Current query_cache_used = 720 M
    Current query_cache_limit = 2 M
    Current Query cache Memory fill ratio = 47.66 %
    Current query_cache_min_res_unit = 4 K
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 256 K
    Current read_rnd_buffer_size = 4 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 1.00 M
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly
    
    OPEN FILES LIMIT
    Current open_files_limit = 4544 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 1512 tables
    Current table_definition_cache = 3640 tables
    You have a total of 403 tables
    You have 1504 open tables.
    Current table_cache hit rate is 83%
    , while 99% of your table cache is in use
    You should probably increase your table_cache
    
    TEMP TABLES
    Current max_heap_table_size = 856 M
    Current tmp_table_size = 856 M
    Of 72209 temp tables, 18% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 3 M
    Current table scan ratio = 11281 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 350
    You may benefit from selective use of InnoDB.
    my.cnf:
    Code:
    skip-external-locking
    skip-name-resolve
    myisam_use_mmap=1  # only use on a 64-bit system
    
    key_buffer_size         = 4096M
    sort_buffer_size        = 256K
    read_buffer_size        = 3M
    read_rnd_buffer_size    = 4M
    max_allowed_packet      = 32M
    thread_stack            = 128K
    table_open_cache        = 1512
    table_definition_cache  = 3640
    open_files_limit        = 4544
    #thread_concurrency      = 16 # can only be used in solaris system
    thread_cache_size       = 320
    max_heap_table_size     = 856M
    tmp_table_size          = 856M
    query_cache_limit       = 2M
    query_cache_size        = 1512M
    query_cache_type        = 1
    query_cache_min_res_unit= 4K
    join_buffer_size        = 1M
    max_connections         = 512
    #long_query_time         = 10
    #log-slow-queries        = /var/log/slowq.log
    #log = /var/log/mysqld.log
    log-error = /var/log/mysqld.error.log
    wait_timeout=6
    connect_timeout=6
    interactive_timeout = 300
    #wait_timeout = 300
    concurrent_insert=2
    low_priority_updates=1
    #skip-bdb
    local-infile=0
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    no-auto-rehash
    [isamchk]
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    [myisamchk]
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M  
     
  15. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    I think the reason that Qcache_queries_in_cache suddenly drops on you is due to the MySQL query cache's course invalidation. Basically if you were to have a table with a million rows and one single value in one row changes, every single cached query that references that table must be removed from the cache. This is where a healthy understanding of the query cache helps. Mysqltuner will tell you to increase query_cache_size until Qcache_lowmem_prunes remains 0 -- while this seems logical it can be quite harmful. The query cache uses a LRU (least recently used) strategy for pruning, so the "one-time/unique" queries tend to get pruned first. This isn't a big deal because they're called very rarely, so it doesn't help you to have them sitting in the cache. What is harmful is increasing the query cache so that it stores many many queries that will never be called again, just to have them wiped out in an instant when their table changes. Ideally you could keep those queries out of the cache to begin with by adding SQL_NO_CACHE to their select statements, but that's not always possible with 3rd party software. The next best thing is to size the query cache so that your most commonly-used queries remain in cache, with enough room for the other queries to cycle through the cache without purging the important ones. My recommendations below will include reducing query_cache_size for this reason.

    I'll also recommend reducing key_buffer_size. MySQL stores MyISAM indexes here, but uses the OS filesystem cache for the actual MyISAM data. By reducing your key_buffer_size we'll open up more RAM for the OS to use to cache your data.

    Please make the following changes:

    • key_buffer_size=3072M
    • sort_buffer_size=512K
    • table_open_cache=2048
    • query_cache_limit=1M
    • query_cache_size=1024M
    • query_cache_min_res_unit=8K
    • comment out open_files_limit, MySQL will set this appropriately based on other settings.
    • comment out thread_stack, unless you had a specific reason for reducing this from the default of 256K.

    Please let that run for 24hrs and then post mysqltuner, tuning-primer, and the SHOW STATUS command I gave you.
     
  16. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I did the changes and restarted server, and will post results...

    SO if for example:
    There is a "SELECT" query where there are two tables joined, and one of the tables' entries gets updated.Will this query be flushed from the cache, even if the other tables' rows hasn't been touched?


    Well, maybe because of this situation, it might be profitable to have a database where all my writes (update/insert/replace) queries updates the tables, and to have another db where all the reads (selects) are performed, and then for example once or twice a day, the two database would be synchronized. But my DB is so big, so then during the synchronization time, there would be a third database, and it would be used during sync :)) Or is it totally foolish thinking?
     
  17. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Yes, precisely. All queries referencing the table with the changed value will be flushed.
    I don't think that specific solution would be very beneficial - but something similar might be. If you have an OLTP database that you do a lot of reporting from, it could be beneficial to move historic/aggregate data into an OLAP database. Note that this doesn't so much help the query cache situation, but improves the performance of your OLTP system by reducing MySQL's working set. Query cache probably wouldn't be very useful for the OLAP database, but could still be for the OLTP.
     
  18. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    After 19 hours tuning primer:
    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.1.56 x86_64
    
    Uptime = 0 days 18 hrs 49 min 33 sec
    Avg. qps = 328
    Total Questions = 22253301
    Threads Connected = 90
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 3829 out of 22253382 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 320
    Current threads_cached = 207
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 512
    Current threads_connected = 107
    Historic max_used_connections = 312
    The number of used connections is 60% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 208 K
    Current InnoDB data space = 144 K
    Current InnoDB buffer pool free = 90 %
    Current innodb_buffer_pool_size = 8 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 5.67 G
    Configured Max Per-thread Buffers : 4.37 G
    Configured Max Global Buffers : 3.00 G
    Configured Max Memory Limit : 7.38 G
    Physical Memory : 31.42 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 2.44 G
    Current key_buffer_size = 2.00 G
    Key cache miss rate is 1 : 4171
    Key buffer free ratio = 6 %
    You could increase key_buffer_size
    It is safe to raise this up to 1/4 of total system memory;
    assuming this is a dedicated database server.
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 1.00 G
    Current query_cache_used = 830 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 81.12 %
    Current query_cache_min_res_unit = 8 K
    However, 965175 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 512 K
    Current read_rnd_buffer_size = 4 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 1.00 M
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly
    
    OPEN FILES LIMIT
    Current open_files_limit = 4618 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 2048 tables
    Current table_definition_cache = 3640 tables
    You have a total of 403 tables
    You have 1743 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 856 M
    Current tmp_table_size = 856 M
    Of 130561 temp tables, 17% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 3 M
    Current table scan ratio = 9265 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 426
    You may benefit from selective use of InnoDB.
    

    mysqltuner:

    Code:
    
     >>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- 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: 8G (Tables: 371)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 87
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 19h 4m 55s (22M q [328.346 qps], 907K conn, TX: 167B, RX: 63B)
    [--] Reads / Writes: 91% / 9%
    [--] Total buffers: 3.8G global + 8.8M per thread (512 max threads)
    [OK] Maximum possible memory usage: 8.2G (26% of installed RAM)
    [OK] Slow queries: 0% (3K/22M)
    [OK] Highest usage of available connections: 60% (312/512)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/2.4G
    [OK] Key buffer hit rate: 100.0% (6B cached / 1M reads)
    [OK] Query cache efficiency: 58.4% (11M cached / 19M selects)
    [!!] Query cache prunes per day: 1223309
    [!!] Sorts requiring temporary tables: 29% (203K temp sorts / 699K sorts)
    [OK] Temporary tables created on disk: 17% (28K on disk / 160K total)
    [OK] Thread cache hit rate: 99% (312 created / 907K connections)
    [OK] Table cache hit rate: 58% (1K open / 2K opened)
    [OK] Open file limit used: 46% (2K/4K)
    [OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
    [OK] InnoDB data size / buffer pool: 144.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Increasing the query_cache size over 128M may reduce performance
    Variables to adjust:
        query_cache_size (> 1G) [see warning above]
        sort_buffer_size (> 512K)
        read_rnd_buffer_size (> 4M)
    

    my.cnf:
    Code:
    skip-external-locking
    skip-name-resolve
    myisam_use_mmap=1  # only use on a 64-bit system
    
    key_buffer_size         = 2048M
    sort_buffer_size        = 512K
    read_buffer_size        = 3M
    read_rnd_buffer_size    = 4M
    max_allowed_packet      = 32M
    #thread_stack            = 128K
    table_open_cache        = 2048
    table_definition_cache  = 3640
    #open_files_limit        = 4544
    #thread_concurrency      = 16 # can only be used in solaris system
    thread_cache_size       = 320
    max_heap_table_size     = 856M
    tmp_table_size          = 856M
    query_cache_limit       = 1M
    query_cache_size        = 1024M
    query_cache_type        = 1
    query_cache_min_res_unit= 8K
    join_buffer_size        = 1M
    max_connections         = 512
    #long_query_time         = 10
    #log-slow-queries        = /var/log/slowq.log
    #log = /var/log/mysqld.log
    log-error = /var/log/mysqld.error.log
    wait_timeout=6
    connect_timeout=6
    interactive_timeout = 300
    #wait_timeout = 300
    concurrent_insert=2
    low_priority_updates=1
    #skip-bdb
    local-infile=0
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    no-auto-rehash
    [isamchk]
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    [myisamchk]
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M  
    
    and the SHOW Global status command:
    Code:
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | Com_select              | 7955476   |
    | Qcache_free_blocks      | 19798     |
    | Qcache_free_memory      | 349274232 |
    | Qcache_hits             | 11170357  |
    | Qcache_inserts          | 7720944   |
    | Qcache_lowmem_prunes    | 972630    |
    | Qcache_not_cached       | 232338    |
    | Qcache_queries_in_cache | 34957     |
    | Qcache_total_blocks     | 94575     |
    +-------------------------+-----------+
    

    I wanted to ask another thing, i was suggested, that maybe i could try to install varnish, a lot of people say it is much faster, here is an article in cpanel forum: http://forums.cpanel.net/f189/varnish-http-accellerator-cpanel-84621.html
     
    #18 menntarra_34, Sep 9, 2011
    Last edited: Sep 9, 2011
  19. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    I don't have any experience with varnish, so you're on your own there :)

    I wasn't available yesterday to look at this, so if you haven't rebooted mysql again please post just the mysqltuner and SHOW STATUS outputs again. I'd like to see how we're doing after >24hrs.

    Was there a reason you dropped key_buffer_size all the way down to 2048M? Your indexes are 2.4G, which is why I chose 3072M for this setting. Since you have plenty of ram, the lowest I'd go is 2560M.
     
  20. menntarra_34

    menntarra_34 Active Member

    Joined:
    Sep 6, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello, i tried varnish, but it turned out, i'm in no need of it right now. It didn't change my load, so it shows as well that my real problem is mysql..., i changed key_buffer to 3000yesterday, i have 3 hours till the 24 hour period, so i'll post results soon...


    What the problem is with my queries is the following:
    I tell you the structure of my search script:

    Code:
    ###Tables are like this:
    ###tables with their coloumns:
    
    ###big_table(id,value1,value2,value3 etc...)
    ###search_words(id,words)
    ###connection(search_words_id,big_table_id)
    
    ###Our goal is to get values from big_table!
    
    $keywords are like this: "plane car football"
    
    $keywords = explode(" ", $keyword);
    
     foreach ($keywords as $value) {
    
    SELECT big_table_id FROM search_words AS t LEFT JOIN connection AS tv ON tv.search_words_id = t.id WHERE words = '". $value ."' AND big_table_id IS NOT NULL
    
                    $result = mysql_query($query);
                    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                      $big_id_results[$value][] = $row[0];
                    }
                        
    }
    
    if (count($big_id_results) == 1) {
                  $all_ids = array_shift($big_id_results);
                }
                else {
                  $all_ids = call_user_func_array('array_intersect', $big_id_results);
                }
                
    ###And lastly we get results  from big table, with the the ids:
    
     $query = "SELECT * FROM big_table WHERE active=1 AND id IN (".implode(", ",$all_ids).") LIMIT 0 , 30";

    So basicly this is my search algorithm, if you can think of anything better , don't hesitate to tell me...
    My long queries are because of this last query:
    $query = "SELECT * FROM big_table WHERE active=1 AND id IN (".implode(", ",$all_ids).") LIMIT 0 , 30";

    Because sometimes "$all_ids" have like 250.000 ids separated by comma...
    So if you think of a better logicly made search table structrure please tell me :))

    With this query I have "ORDER by"-s as well , so that's why the sort_buffer_size needs lot of KB-s
     
    #20 menntarra_34, Sep 11, 2011
    Last edited: Sep 11, 2011
Loading...

Share This Page