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 

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

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