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 <[email protected]>
>>  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
[[email protected] ~]# pico /etc/my.cnf
[[email protected] ~]# perl mysqltuner.pl
>>  MySQLTuner 1.6.1 - Major Hayden <[email protected]>
>>  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 -

 

[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

 

[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

 

[soaringeagle]

testing it but so far it seems horible why do you not have thread cache enabled

 

[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

 

[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?

 

[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.

 

[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

 

[cPanelMichael]

The following thread might help if the load spikes stem from Apache:

Troubleshooting high server loads on Linux servers

Thank you.