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.

MySQL & High Server Load

Discussion in 'Workarounds and Optimization' started by steliosd, Nov 25, 2013.

  1. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    I have a hexacore dedicated server, with 64GB RAM, running CentOS 6.4.

    Vendor: GenuineIntel
    Name: Intel(R) Core(TM) i7-3930K CPU @ 3.20GHz
    Speed: 1200.000 MHz
    Cache: 12288 KB

    The problem I am dealing with is that mysql consumes up to 600% of my cpu (server load up to 70!) every five minutes when the record are taking place.This situation last about 1-2 minutes before the server load comes back to normal at 4-5.
    I need some help with the configuration of my.cnf file.

    Code:
    [mysqld]
    max_connections=250
    max_user_connections=150
    
    query_cache_type=1
    query_cache_size=256M
    query_cache_limit=70M
    
    tmp_table_size=8M
    max_heap_table_size=8M
    
    thread_cache_size=64
    table_open_cache=1024
    
    wait_timeout=300
    interactive_timeout=300
    
    innodb_file_per_table=1
    innodb_buffer_pool_size=4G
    innodb_log_file_size=512M
    
    default-storage-engine=MyISAM
    local-infile=0
    max_allowed_packet=64M
    log-slow-queries=/var/lib/mysql/slow.log
    open_files_limit=2846
    
    table_cache=4096
    join_buffer_size=4M
    
    key_buffer_size=2M
    
    

    Here is the output of mysqltuner:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  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.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 348M (Tables: 319)
    [--] Data in InnoDB tables: 615M (Tables: 389)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 8)
    [!!] Total fragmented tables: 35
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 19h 41m 29s (185M q [562.959 qps], 2M conn, TX: 1909B, RX: 62B)
    [--] Reads / Writes: 53% / 47%
    [--] Total buffers: 4.3G global + 6.6M per thread (250 max threads)
    [OK] Maximum possible memory usage: 5.9G (9% of installed RAM)
    [OK] Slow queries: 0% (40K/185M)
    [OK] Highest usage of available connections: 61% (154/250)
    [!!] Key buffer size / total MyISAM indexes: 2.0M/530.2M
    [!!] Key buffer hit rate: 92.5% (39M cached / 2M reads)
    [OK] Query cache efficiency: 90.1% (139M cached / 155M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (13 temp sorts / 2M sorts)
    [!!] Joins performed without indexes: 6374
    [OK] Temporary tables created on disk: 24% (11K on disk / 45K total)
    [OK] Thread cache hit rate: 97% (56K created / 2M connections)
    [OK] Table cache hit rate: 99% (2K open / 2K opened)
    [OK] Open file limit used: 11% (943/8K)
    [OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
    [OK] InnoDB data size / buffer pool: 615.2M/4.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        key_buffer_size (> 530.2M)
        join_buffer_size (> 4.0M, or always use indexes with joins)
    
    
    Thanks in advance
     
  2. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have just changed the key_buffer_size to 768M and join_buffer_size to 8M according to mysqltuner recommendations.
     
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    key_buffer_size increase will make huge difference if those queries run on MyISAM tables

    join_buffer_size lower to 1M
    8M there wont make any difference, it will only take more memory

    You can also adjust
    tmp_table_size=50M
    max_heap_table_size=50M


    query_cache_type=1
    query_cache_size=75M
    query_cache_limit=1M

    long_query_time=0.1

    the rest you can do is review slow queries, and optimize them
    especially if you have some custom code, or CSM with third party plugins
     
  4. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks a lot thinkbot for your help!
    I have made the changes you suggested and I will post the results tomorrow.
    Are there any other optimizations I can do for innodb tables?I had to remove innodb_log_file_size=512M because it crashed my website.
    In /var/lib/mysql/*.err file the only error that comes up repeatedly is this:

     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    If you want to increase innodb_log_file_size, you need to remove old logfiles first like this

    rm -rf /var/lib/mysql/ib_logfile*

    and then restart mysql


    but before for innodb you can set this:
    innodb_log_file_size = 300M
    innodb_log_buffer_size = 20M
    innodb_flush_method = O_DIRECT
    innodb_flush_log_at_trx_commit = 2
    innodb_old_blocks_time = 1000

    Your main used tables are Myisam or Innodb ?
     
  6. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks for the update!I have applied the changes and I am waiting.
    My main used tables are Innodb.
    Actually there is only one Innodb table that uses slow queries which take up to 25 sec to complete.

    slow_query.png
     
  7. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    On a second thought, there is the Innodb table I mentioned before with 4 million rows and a MyISAM table with 9,7 million rows.
    Both of them are important (especially the first one) and data are being recorded to them every 5 minutes.
    Shouldn't I set innodb_log_file_size = 600M according to this post?

     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    it wont make a difference much

    Please add index on drawDate column in kouponidb_kino_wins_xls table
    This will speed up the slow query a lot

    you can also review the rest of slow queries like that:

    cd /root/install
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/slow.log > slow.txt

    and copy slow.txt here
     
  9. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    The slow.txt is 5MB large and I can't post it here.Can you pm me your email so I can send it to you?
    Here is my new mysqltuner output:

    Code:
    root@host [/]# ./mysqltuner.pl 
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  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.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 349M (Tables: 319)
    [--] Data in InnoDB tables: 574M (Tables: 389)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 8)
    [!!] Total fragmented tables: 35
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 2m 40s (49M q [568.306 qps], 766K conn, TX: 527B, RX: 23B)
    [--] Reads / Writes: 55% / 45%
    [--] Total buffers: 6.2G global + 3.6M per thread (250 max threads)
    [OK] Maximum possible memory usage: 7.0G (11% of installed RAM)
    [OK] Slow queries: 0% (83K/49M)
    [OK] Highest usage of available connections: 60% (152/250)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/533.3M
    [OK] Key buffer hit rate: 99.2% (6M cached / 52K reads)
    [OK] Query cache efficiency: 88.2% (35M cached / 40M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 695K sorts)
    [!!] Joins performed without indexes: 338
    [!!] Temporary tables created on disk: 32% (3K on disk / 9K total)
    [OK] Thread cache hit rate: 98% (11K created / 766K connections)
    [OK] Table cache hit rate: 89% (1K open / 2K opened)
    [OK] Open file limit used: 11% (941/8K)
    [OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
    [OK] InnoDB data size / buffer pool: 574.5M/4.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        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
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
    
    I have added the index you suggested and I am going to increase tmp_table_size and max_heap_table_size to 100M.

    Server load has decreased a lot but I still get a 27-30 sometimes.On the other hand, mysql process doesn't hit 700% of the cpu anymore.
     
  10. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Forget about the last thing I wrote.It still hits 600% of cpu sometimes.
    I don't understand how come when in top processes mysql reaches 600% of cpu, server load is at 5-7 and when server load is at 25-30, mysql reaches only 20% of cpu?
     
  11. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
  12. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    As you see here

    # 1 0x656F8265D013DC88 8647502.2474 89.9% 349356 24.7527 2.04 SELECT kouponidb_kino_wins_xls
    # 2 0xAA2D53B32C14D11C 540788.7260 5.6% 13809 39.1620 37.49 SELECT kouponidb_prognosi_xml kouponidb_prognosi_xml_diff
    # 3 0x5F621334B41BA193 317440.8073 3.3% 9640 32.9295 5.68 SELECT kouponi_xml_diff kouponi_xml
    # 6 0xAB1809319AF87956 13279.4833 0.1% 847 15.6783 9.25 SELECT kouponidb_prognosi_xml kouponidb_prognosi_xml_diff

    almost 90% of mysql time from slow mysql queries is taken by 1 query, its executed 349356 times

    It's SELECT * FROM kouponidb_kino_wins_xls WHERE drawDate = '2013-11-27'
    from database masterbe_app

    On median each execution returns over 10 rows, 95% 246 rows, just returning those rows to client takes some time, not to mention selecting them from median 4,06 M rows, 95% 16 M rows
    Median time of execution of this query is 24s, and 95% is 118s

    Once you added the index, selection would be much faster; difference should be huge, but sending rows to client still takes some time.

    Please check in code what this query is supposed to do,
    from my experience, it might be used to return number of rows, if so, use SELECT count(*) as total FROM instead, this wont need any rows to be returned, and query will execute immediately

    If this query is used not to return count of rows, but some actuall rows, you can limit columns that needs to be returned
    like. SELECT id, date FROM kouponidb_kino_wins_xls

    to send only those columns that are actually used by the script


    Second thing, check in code logic, if its really needed to run this queries this many times, maybe it doesn't change often, and it can be cached

    Next queries are more complicated and explaining how to optimize them on forums would take too long time; the most CPU is coused by the 1 one



    "Forget about the last thing I wrote.It still hits 600% of cpu sometimes.
    I don't understand how come when in top processes mysql reaches 600% of cpu, server load is at 5-7 and when server load is at 25-30, mysql reaches only 20% of cpu?"

    when cpu is used at 600% becouse of mysql, this in your case probably means tons of CPU intensive rows scanning
    second, load 25-30, and mysql cpu only 20% - in your case its probably becouse too high I/O on HDD, you can put here result of top command when load goes to 25-30 re more
     
    #12 thinkbot, Nov 28, 2013
    Last edited: Nov 28, 2013
  13. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    The drawDate index made things a lot faster indeed.Does it matter that this array has 4 more columns as indexes?
    I checked the query and I need all of the columns of the array kouponidb_kino_wins_xls.The query also need to run every 5 minutes so I don't think it can be cached.
    Now the situation of the server is this.Every 5 minutes that information is available and the query is being executed, server load goes up to 30 max for about a minute.The rest of the time server load is below 10.
    Is there any other option for the my.cnf file?

    Here are the screenshots for top command.
    View attachment Archive.zip
     
    #13 steliosd, Nov 29, 2013
    Last edited: Nov 29, 2013
  14. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi everybody,
    my server load problem continues and I would appreciate some more help.Here are mysqltuner and my.cnf output:

    Code:
    root@host [/]# ./mysqltuner.pl 
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  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.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 357M (Tables: 336)
    [--] Data in InnoDB tables: 615M (Tables: 390)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 8)
    [!!] Total fragmented tables: 42
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 19h 47m 1s (244M q [585.613 qps], 3M conn, TX: 2567B, RX: 116B)
    [--] Reads / Writes: 55% / 45%
    [--] Total buffers: 6.7G global + 4.6M per thread (250 max threads)
    [OK] Maximum possible memory usage: 7.8G (12% of installed RAM)
    [OK] Slow queries: 0% (174K/244M)
    [OK] Highest usage of available connections: 60% (152/250)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/549.9M
    [OK] Key buffer hit rate: 99.9% (40M cached / 55K reads)
    [OK] Query cache efficiency: 88.3% (177M cached / 201M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (29 temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 6856
    [OK] Temporary tables created on disk: 19% (41K on disk / 217K total)
    [OK] Thread cache hit rate: 98% (48K created / 3M connections)
    [!!] Table cache hit rate: 0% (2K open / 1M opened)
    [OK] Open file limit used: 5% (996/16K)
    [OK] Table locks acquired immediately: 99% (45M immediate / 45M locks)
    [OK] InnoDB data size / buffer pool: 615.7M/4.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 8192)
    
    
    Code:
    [mysqld]
    max_connections=250
    max_user_connections=150
    
    query_cache_type=1
    query_cache_size=150M
    query_cache_limit=1M
    
    long_query_time=0.1
    
    tmp_table_size=512M
    max_heap_table_size=512M
    
    thread_cache_size=64
    table_open_cache=1024
    
    wait_timeout=300
    interactive_timeout=300
    
    innodb_file_per_table=1
    innodb_buffer_pool_size=4G
    
    innodb_log_file_size = 600M
    innodb_log_buffer_size = 30M
    innodb_flush_method = O_DIRECT
    innodb_flush_log_at_trx_commit = 2
    innodb_old_blocks_time = 1000
    
    default-storage-engine=MyISAM
    local-infile=0
    max_allowed_packet=64M
    log-slow-queries=/var/lib/mysql/slow.log
    open_files_limit=3800
    
    table_cache=16384
    join_buffer_size=2M
    
    key_buffer_size=2G
    Thanks in advance.
     
  15. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Your mysql settings are fine, the script (queries) and database structure needs optimizations

    Please generate slow log once again
     
  16. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Nothing has changed.
    I noticed that table_cache_hit_rate is now 0% ( [!!] Table cache hit rate: 0% (2K open / 1M opened) )!
    I increased table_cache to 16384 but it didn't help.
    If the logged in users are about 60-70 everything works fine.But most time of the day logged in users are about 100 so the server load hits 40/12.
     
  17. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Did you optimize those queries that I wrote ? I mean indexes

    Your queries used temporary tables, some of them on disk, and that doesn't scale, some used row by row scan or expensive filesort on big number of rows, thats all couses high I/O, and won't scale when visitors count grows

    This is why I asked for new slow log to see the difference, you can also run

    explain extended query
    like
    explain extended SELECT * FROM kouponidb_kino_wins_xls WHERE drawDate = '2013-11-27'

    for top queries in slow log, and copy it here so I can review
     
  18. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hope I got it right:


    Here is how my.cnf looks right now:
    Code:
    [mysqld]
    max_connections=250
    max_user_connections=150
    
    query_cache_type=1
    query_cache_size=150M
    query_cache_limit=1M
    
    long_query_time=0.1
    
    tmp_table_size=512M
    max_heap_table_size=512M
    
    thread_cache_size=64
    table_open_cache=4096
    
    wait_timeout=300
    interactive_timeout=300
    
    innodb_file_per_table=1
    innodb_buffer_pool_size=4G
    
    innodb_log_file_size = 600M
    innodb_log_buffer_size = 30M
    innodb_flush_method = O_DIRECT
    innodb_flush_log_at_trx_commit = 2
    innodb_old_blocks_time = 1000
    
    default-storage-engine=MyISAM
    local-infile=0
    max_allowed_packet=64M
    log-slow-queries=/var/lib/mysql/slow.log
    open_files_limit=3800
    
    table_cache=4096
    join_buffer_size=8M
    
    key_buffer_size=2G
    
     
    #18 steliosd, Dec 16, 2013
    Last edited: Dec 16, 2013
  19. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yes, this one is correct, index added properly, query returns 16988 rows, ok
    But what about the rest of the queries that Ive mentioned ? can you run explain extended on them too ?

    btw. you can remove table_cache from my.cnf its the same as table_open_cache
     
  20. steliosd

    steliosd Active Member

    Joined:
    Nov 25, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Ok I removed it.
    I thought that only this one was the query causing so much trouble.In which other queries do you refer?

    Mysql process is always at 700-1000% of cpu now!
     
    #20 steliosd, Dec 16, 2013
    Last edited: Dec 16, 2013
Loading...

Share This Page