Skip to main content

mariadb 10.0.21 very high cpu loads optimization help needed

soaringeagle
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
[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
~]# perl mysqltuner.pl >> MySQLTuner 1.6.1 - Major Hayden >> 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 >> 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 -

Comments

8 comments

Date Votes
  • soaringeagle
    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
    0
  • anton_latvia
    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
    0
  • soaringeagle
    testing it but so far it seems horible why do you not have thread cache enabled
    0
  • soaringeagle
    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
    [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
    0
  • anton_latvia
    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?
    0
  • cPanelMichael
    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.
    0
  • soaringeagle
    i think i got it working good now, although at times i still see high spikes but think that might be more from apache
    0
  • cPanelMichael
    The following thread might help if the load spikes stem from Apache: Troubleshooting high server loads on Linux servers Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post