Re: MySQL is eating all the CPU 100% - Guide me to fix this please

[Mahesh Kumar]

I have not done the heap tmp table so far. reading about it.

top - 13:33:58 up 10:07, 8 users, load average: 1.04, 0.87, 0.83
Tasks: 190 total, 1 running, 189 sleeping, 0 stopped, 0 zombie
%Cpu(s): 7.9 us, 3.9 sy, 0.0 ni, 87.2 id, 0.0 wa, 0.0 hi, 1.0 si, 0.0 st
KiB Mem : 32601524 total, 18824300 free, 7860324 used, 5916900 buff/cache
KiB Swap: 8191996 total, 8191996 free, 0 used. 22767060 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2807 mysql 20 0 37.002g 7.200g 12948 S 87.1 23.2 291:45.08 /usr/sbin/mysqld

Does it sound good or bad?
%cpu is 87.1 in the process list. In the top, it is 7.9
I am confused.


Last three runs

MariaDB [(none)]> SHOW global STATUS LIKE 'key%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 30655743 |
| Key_blocks_used | 112482 |
| Key_blocks_warm | 0 |
| Key_read_requests | 20574624 |
| Key_reads | 108795 |
| Key_write_requests | 394639 |
| Key_writes | 131843 |
+------------------------+----------+

show variables like '%max_conn%';


MariaDB [(none)]> SHOW global STATUS LIKE 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 30538190 |
| Key_blocks_used | 230035 |
| Key_blocks_warm | 3116 |
| Key_read_requests | 113060855 |
| Key_reads | 209681 |
| Key_write_requests | 2233925 |
| Key_writes | 734910 |
+------------------------+-----------+


+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 30456114 |
| Key_blocks_used | 312111 |
| Key_blocks_warm | 3116 |
| Key_read_requests | 241446622 |
| Key_reads | 269380 |
| Key_write_requests | 4795220 |
| Key_writes | 1568869 |
+------------------------+-----------+

key_write_requests is improving. Still behind the safe line