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