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.

mariadb 10.0.21 very high cpu loads optimization help needed

Discussion in 'Workarounds and Optimization' started by soaringeagle, Oct 24, 2015.

  1. soaringeagle

    soaringeagle Member

    Joined:
    Oct 24, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    cPanel Access Level:
    Root Administrator
    upgraded from mysql 5.6 to mariadb 10.0.21 and cpu use was off the charts high! (8 cores 32 gigs ram dedicated server)
    1st, the cpu usage was topping out at 27.6!
    under low load on mysql it averaged 0.23-0.46 on high loads o.56-1.78 (ish)
    on mariadb its 2.4 with nearly no load and with load 4.6-12.7 or higher

    my.cnf
    Code:
    [mysqld]
    thread_handling=pool-of-threads
    wait_timeout = 1800
    interactive_timeout = 1800
    key_buffer              = 1024M
    max_allowed_packet=268435456
    table_cache             = 1536
    table_definition_cache  = 1536
    open_files_limit=10000
    tmp_table_size          = 512M
    max_heap_table_size     = 512M
    sort_buffer_size        =32M
    read_buffer_size        = 8M
    read_rnd_buffer_size    =32M
    max_connections         = 160
    innodb_buffer_pool_size = 8G
    innodb_flush_method     = O_DIRECT
    ft_min_word_len         = 3
    
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    table_open_cache = 42997
    
    # table_cache            = 1024
    # thread_concurrency     = 8
    thread_cache_size =160
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 5M
    query_cache_size        = 2G
    # innodb_file_per_table
    innodb_log_file_size = 2G
    innodb_buffer_pool_instances = 8
    
    
    mysql tuner
    Code:
    ~]# perl mysqltuner.pl
    >>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.0.21-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in MyISAM tables: 3G (Tables: 2349)
    [--] Data in InnoDB tables: 1G (Tables: 260)
    [!!] Total fragmented tables: 20
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [--] There is 605 basic passwords in the list.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6h 11m 33s (2M q [128.338 qps], 227K conn, TX: 21B, RX: 9B)
    [--] Reads / Writes: 86% / 14%
    [--] Binary logging is disabled
    [--] Total buffers: 11.6G global + 72.4M per thread (160 max threads)
    [OK] Maximum reached memory usage: 15.2G (48.46% of installed RAM)
    [OK] Maximum possible memory usage: 23.0G (73.30% of installed RAM)
    [OK] Slow queries: 0% (35/2M)
    [OK] Highest usage of available connections: 31% (50/160)
    [OK] Aborted connections: 0.39%  (877/227758)
    [OK] Query cache efficiency: 33.1% (986K cached / 2M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (16 temp sorts / 134K sorts)
    [OK] Temporary tables created on disk: 22% (33K on disk / 150K total)
    [OK] Thread cache hit rate: 99% (1K created / 227K connections)
    [OK] Table cache hit rate: 31% (3K open / 10K opened)
    [OK] Open file limit used: 5% (4K/86K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 20.2% (216M used / 1B cache)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/285.8M
    [OK] Read Key buffer hit rate: 96.0% (19M cached / 794K reads)
    [!!] Write Key buffer hit rate: 43.9% (3M cached / 1M writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 8.0G/1.1G
    [OK] InnoDB buffer pool instances: 8
    [!!] InnoDB Used buffer: 38.52% (201949 used/ 524280 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (28002887622 hits/ 28002931983 total                   )
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 26558 writes)
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
    [root@server ~]# pico /etc/my.cnf
    [root@server ~]# perl mysqltuner.pl
    >>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.0.21-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in MyISAM tables: 3G (Tables: 2349)
    [--] Data in InnoDB tables: 1G (Tables: 260)
    [!!] Total fragmented tables: 21
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [--] There is 605 basic passwords in the list.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6h 40m 4s (3M q [134.347 qps], 253K conn, TX: 24B, RX: 10B)
    [--] Reads / Writes: 87% / 13%
    [--] Binary logging is disabled
    [--] Total buffers: 11.6G global + 72.4M per thread (160 max threads)
    [OK] Maximum reached memory usage: 15.2G (48.46% of installed RAM)
    [OK] Maximum possible memory usage: 23.0G (73.30% of installed RAM)
    [OK] Slow queries: 0% (35/3M)
    [OK] Highest usage of available connections: 31% (50/160)
    [OK] Aborted connections: 0.39%  (983/253708)
    [OK] Query cache efficiency: 33.2% (1M cached / 3M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (16 temp sorts / 149K sorts)
    [OK] Temporary tables created on disk: 23% (38K on disk / 166K total)
    [OK] Thread cache hit rate: 99% (1K created / 253K connections)
    [OK] Table cache hit rate: 31% (3K open / 10K opened)
    [OK] Open file limit used: 5% (4K/86K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 20.4% (219M used / 1B cache)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/287.5M
    [OK] Read Key buffer hit rate: 96.2% (20M cached / 794K reads)
    [!!] Write Key buffer hit rate: 42.3% (3M cached / 2M writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 8.0G/1.1G
    [OK] InnoDB buffer pool instances: 8
    [!!] InnoDB Used buffer: 38.64% (202605 used/ 524280 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (30675858673 hits/ 30675903034 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 27984 writes)
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
    
    additional suspicions:
    php settings might be the cause? i seem to have way more connections showing up in top then usual
    but mysql cpu loads are extremely high
    just in case il include phpinfo results
    - Removed -
     
    #1 soaringeagle, Oct 24, 2015
    Last edited by a moderator: Oct 24, 2015
  2. soaringeagle

    soaringeagle Member

    Joined:
    Oct 24, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    cPanel Access Level:
    Root Administrator
    additionally, if upgrading to 10.1.8 might help whats the manual upgrade instructions as my server guys will only suport cpanel upgrades wich will not be released for months.. i can;t live with this high cpu usage for months
     
  3. anton_latvia

    anton_latvia Well-Known Member
    PartnerNOC

    Joined:
    May 11, 2004
    Messages:
    348
    Likes Received:
    3
    Trophy Points:
    18
    Location:
    Latvia
    cPanel Access Level:
    Root Administrator
    well, I also believed that having high limits in MySQL would make server run fast until I run own tests, which showed that average or even default limits would give better performance. Check out my recommendations, I would definitely set most of the limits to lower values..

    Optimizing MySQL, Intermediate results - Norsk Webhotell og Domener
     
  4. soaringeagle

    soaringeagle Member

    Joined:
    Oct 24, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    cPanel Access Level:
    Root Administrator
    testing it but so far it seems horible why do you not have thread cache enabled
     
  5. soaringeagle

    soaringeagle Member

    Joined:
    Oct 24, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    cPanel Access Level:
    Root Administrator
    yea your settings the cpu skyrocketed ram used double what it was using..just to start
    i have tweaked my settings since posting and it has improved but its not yet where id like it to be
    Code:
    [mysqld]
    thread_handling=pool-of-threads
    wait_timeout = 3800
    interactive_timeout = 3800
    key_buffer              = 1024M
    max_allowed_packet=268435456
    
    table_cache             = 1536
    table_definition_cache  = 1536
    open_files_limit=10000
    tmp_table_size          = 512M
    max_heap_table_size     = 512M
    sort_buffer_size        =32M
    read_buffer_size        = 8M
    read_rnd_buffer_size    =32M
    max_connections         = 160
    innodb_buffer_pool_size = 8G
    innodb_flush_method     = O_DIRECT
    ft_min_word_len         = 3
    
    
    myisam-recover         = BACKUP
    table_open_cache = 42997
    
    # table_cache            = 1024
    # thread_concurrency     = 8
    thread_cache_size =160
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 5M
    query_cache_size        = 2G
    # innodb_file_per_table
    innodb_log_file_size = 10M
    innodb_buffer_pool_instances = 8
    
    
    
    
    i might try 1 or 2 lines from yours see if they help
     
  6. anton_latvia

    anton_latvia Well-Known Member
    PartnerNOC

    Joined:
    May 11, 2004
    Messages:
    348
    Likes Received:
    3
    Trophy Points:
    18
    Location:
    Latvia
    cPanel Access Level:
    Root Administrator
    well, my tests might have been far your queries. Thread cache I have remove on first tests, but I agree I was too fast to do that, thanks!. ;) but setting it to 160 seems to be overkill.

    my experience shows, that large key_buffer won't help when you have string selects. I would increate table_cache.. Decrease sort_buffer_size, decrease read_rnd_buffer_size, are you sure you want to have innodb_buffer_pool_size so big?

    table_open_cache vs table_cache seems to be inadequate.. Also query_cache_size of 2Gb is not good. The way query cache works is that on every update mysql will try to clean old data, so it has to look through 2gb.. I really doubt you get it full at any point. Does stats show that?
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may also want to run the MySQL tuner after the service has been up for at least 24 hours to get more accurate results.

    Thank you.
     
  8. soaringeagle

    soaringeagle Member

    Joined:
    Oct 24, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    cPanel Access Level:
    Root Administrator
    i think i got it working good now , although at times i still see high spikes but think that might be more from apache
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page