1

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  
  • Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 8 at 22:22
  • If you will post the requested info, I can help you. #1 problem is you have 64GB RAM and your configuration is asking for 75GB with named Global Variables requests. Awaiting your data to be posted and share the links, please. – Wilson Hauck Sep 15 at 1:26
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.

1

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.
  • 1
    Are you using InnoDB or MyISAM? Show us the common queries, together with SHOW CREATE TABLE. – Rick James Sep 6 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.