Mysql Config Suggestions - CPU too high

Question

My server (64GB,16CPUs) has mariadb 5.5.60.

The queries are around 500 selects /second and 300 inserts/seconds.

The queries are simple select of auth token and user details.

Both are properly indexed.

The insert query is also very simple.

However, the CPU usage is still high %cpu usage is >600%.

The following is the mysql config.

Please suggest.

max_connections = 5000  
max_user_connections=1000  
key_buffer_size = 10024M  
myisam_sort_buffer_size = 512M  
read_buffer_size = 1G  
table_open_cache = 9000  
thread_cache_size = 900  
wait_timeout = 300  
connect_timeout = 300  
tmp_table_size = 8G  
max_heap_table_size = 4G  
max_allowed_packet = 10G  
net_buffer_length = 2048M  
max_connect_errors = 100  
read_rnd_buffer_size = 2048M   
bulk_insert_buffer_size = 2G  
query_prealloc_size = 512M  
query_alloc_block_size = 128M  
transaction_alloc_block_size = 128M  
transaction_prealloc_size = 128M  
max_write_lock_count = 16  
log-error  
external-locking=FALSE   
open_files_limit=50000  
character-set-server = utf8  
innodb_buffer_pool_size=40G  
table_cache=10G  
innodb_file_per_table=1  
skip-name-resolve  

Answer 1

table_cache=10G

Is the old name for table_open_cache. They limit the number of entries. 10G is unreasonable. 9000 is high, but probably OK.

tmp_table_size = 8G
max_heap_table_size = 4G

are dangerously high. If just a few threads decide to build a big temp table, you could run out of RAM, possibly out of swap space. Set them to no more than 1% of RAM.

These are OK as you have them:

innodb_buffer_pool_size=40G  
key_buffer_size = 10024M 

but it would be better to tune them based on which Engine you are using.

Meanwhile, do not randomly raise config settings; it can cause more harm than good. Swapping is terrible for performance; over committing RAM can lead to swapping.

mariadb 5.5.60 The queries are around 500 selects /second and 300 inserts/seconds

5.5 is rather old; there have been some relevant improvements since then. With that rate of querying, I hope you are using InnoDB; you are pushing the limits of MyISAM.

Are the disks SSDs? If so, that query rate should be no problem. Since you are commenting on CPU, not I/O, I assume I/O is not a problem?

As already metioned, you are using only 6/16ths of available CPU, so there no immediate threat of meltdown. Still, it is good to be concerned.

Both are properly indexed.

Sorry, but most people who say "properly indexed" have never heard of the benefits of 'composite' indexes. Please provide the queries and SHOW CREATE TABLE so we can verify.

Answer 2

Two things:

1. As you posted 600% of CPU usage, I'll guess that you are using Linux, so no worries, actually this means that you are just using 100% of 6 processors, so you still have 10 processors totally free. When you start seeing a CPU usage above 1400% then you can start worrying.
2. More important than point number 1 is the crazy mess you have in your config file. You can't just grow those values with no knowledge of what are you doing. My recommendations is to hire a experienced DBA or learn how to tune a MySQL server properly.