High CPU usage by mariadb even after allocated more memory to mariadb?

Question

I am using aws server r4.4xlarge and allocated more than 60GB to database only still i am facing issue of high cpu usage.

I have around 10 big table each of approximate size of 18GB with integer primary key. all tables are innodb and I have optimised the innodb also by adding this to default config file.

innodb_buffer_pool_size=60G #(max 60 % of your Memory if and only if Memory >= 8G) innodb_log_file_size=15G #(min 25% of innodb_buffer_pool_size) innodb_flush_log_at_trx_commit=2 

also while debugging in mysql i have doubt on some query but still i want mariadb utilise instance properly. most of process in sleep state but some are in query state for quite long period.

+---------+-----------------+-----------------+------------------+---------+------+----------------+-----------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +---------+-----------------+-----------------+------------------+---------+------+----------------+-----------------+----------+ | 5415663 | root | localhost | test_db | Query | 0 | Sorting result | SELECT XXXXX | 0.000 | 

I need suggestion regarding should i have to still optimise some thing, get best out of mariadb.

Tasks: 339 total, 1 running, 338 sleeping, 0 stopped, 0 zombie %Cpu(s): 13.0 us, 0.1 sy, 0.0 ni, 86.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 12582731+total, 40068792 free, 72185768 used, 13572756 buff/cache KiB Swap: 0 total, 0 free, 0 used. 51496540 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 126064 mysql 20 0 65.336g 0.063t 8968 S 205.6 53.5 51004:26 mysqld 1346 rabbitmq 20 0 9804208 1.022g 4228 S 2.3 0.9 1336:27 beam.smp 1488 redis 20 0 604496 513216 2236 S 1.0 0.4 389:01.86 redis-server 7064 www-data 20 0 450464 38604 30744 S 0.3 0.0 0:00.93 php-fpm7.0 9226 www-data 20 0 383204 37932 29336 S 0.3 0.0 0:00.66 php-fpm7.0 9404 www-data 20 0 376000 36820 29016 S 0.3 0.0 0:00.57 php-fpm7.0 

Answer 1

200% isn't a lot of CPU when you have 16 cores. 200% is two full cores (amongst the 16 you have in an r4.4xlarge). Considering the amount of memory you have in use, MariaDB is certainly up to something. The fact that you are only using 2 of your 16 cores, I'd say you were under utilising CPU.

Use the htop utility to better see mariaDB's use of the cores.

Answer 2

There was a bug https://jira.mariadb.org/browse/MDEV-15016 . That bug got fixed. Use latest version (10.2.13, 10.3.5) and verify everything is OK again.

Answer 3

most of process in sleep state ...

And that's perfectly normal, especially if your application is using Connection Pooling.

... but some are in query state for quite long period.
I need suggestion regarding should i have to still optimise some thing ...

As a rule, Databases don't run slowly.
The Queries inside them do.

The example you showed was a select doing a sort. Because you've given your database lots of memory to work with, it will try to do sorts "in-memory" but doing that takes CPU power - which is what you're seeing.

Investigate [the Explain Plan for] this query and look to make improvements there.

As I'm fond of saying:

• You can spend several days fiddling about with database and server-level settings and improve "performance" by - maybe - 2 or 3 percentage points.
• You can spend the same time building cleaner table structures, adding proper indexing, etc., and improve "performance" by - at least - 2 or 3 Orders of Magnitude.