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