# MySQL Performance Degrades after hitting ~100% CPU Load

**URL:** <https://forums.percona.com/t/mysql-performance-degrades-after-hitting-100-cpu-load/1862>\
**Category:** Other MySQL® Questions\
**Created:** [August 9, 2012, 10:31am UTC](https://forums.percona.com/t/mysql-performance-degrades-after-hitting-100-cpu-load/1862 "2012-08-09T10:31:56Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![EZboy](https://avatars.discourse-cdn.com/v4/letter/e/9dc877/32.png) [@EZboy](https://forums.percona.com/u/EZboy)\
**Post date:** [August 9, 2012, 10:31am UTC](https://forums.percona.com/t/mysql-performance-degrades-after-hitting-100-cpu-load/1862/1 "2012-08-09T10:31:56Z")

</div>

Hi everyone,

I am having some interesting performance issues with MySQL installation and looking to get some suggestions as I have exhausted any ways to troubleshoot I know.

First of all, info:  
OS - Fedora 2.6.40.4-5.fc15.x86\_64  
MySQL - mysql-5.5.27-linux2.6-x86\_64  
Hardware - HP DL580g7, 2 x E74820,64Gb ram, Raid 10 storage with 1Gb flash buffer

my.cnf:

[mysqld]port = 3306socket = /tmp/mysql.sockdefault-storage-engine = myisamtmpdir = /var/tmp/tmpmysql #Logslog=/var/log/mysql.loggeneral\_log = 0slow\_query\_log = 1long\_query\_time = 10skip-external-lockingconcurrent\_insert = 2 thread\_concurrency = 16thread\_cache\_size = 8key\_buffer\_size = 28Gmax\_allowed\_packet = 32Mtable\_open\_cache = 2048sort\_buffer\_size = 2Mread\_buffer\_size = 2Mjoin\_buffer\_size = 128Mread\_rnd\_buffer\_size = 32Mmyisam\_sort\_buffer\_size = 2Gmyisam\_max\_sort\_file\_size = 8Gbulk\_insert\_buffer\_size = 64M query\_cache\_size = 128Mquery\_cache\_limit = 64Mtmp\_table\_size = 256Mmax\_tmp\_tables = 86max\_heap\_table\_size = 2Gback\_log = 70max\_connections = 500max\_connect\_errors = 10#InnoDBinnodb\_thread\_concurrency = 16innodb\_write\_io\_threads = 8innodb\_read\_io\_threads = 8innodb\_buffer\_pool\_size = 4Ginnodb\_data\_home\_dir = /usr/local/mysql/datainnodb\_data\_file\_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb\_log\_group\_home\_dir = /usr/local/mysql/datainnodb\_additional\_mem\_pool\_size = 20Minnodb\_log\_file\_size = 1Ginnodb\_log\_buffer\_size = 8Minnodb\_flush\_method=O\_DIRECTinnodb\_flush\_log\_at\_trx\_commit = 0innodb\_lock\_wait\_timeout = 120

* * *

So the issue I am having is that MySQL would suddenly lose performance executing queries after running in heavy load for couple of hours. The same queries start running 10 -100 times slower. After restart everything is back to normal.

This behaviour got more noticeable with addition of load. When running 1K queries per second, this used to never happen, 1.5K qps - would happen once a week, 2K qps - happens every day or more often.  
With this load the server is often at 100%, but this doesn’t not explain why performance does not recover after the load is removed.

Now about query types:  
60% selects on myisam tables  
25% inserts/updates on myisam tables  
15% innodb tables

Around 50-60 concurrent connections

The most common query executed:

SELECT ( 4 \* HOUR( a.date ) + FLOOR( MINUTE( a.date ) /15 ) ) AS period, SUM( a.mv ) AS count, DATE( date ) AS dateFROM table aWHERE a.date \> DATE\_SUB( ‘2012-08-08 16:14:55’, INTERVAL 10MINUTE )AND a.date \<= '2012-08-08 16:44:58’AND location = 'some\_location’AND …GROUP BY period

It looks like I am hitting a bottleneck somewhere, but can not exactly pinpoint the spot. Things i have checked:

Proper index - yes  
Tmp tables - all in memory  
Key cache - always enough  
Query execution plan does not change when problem occurs  
iostat - storage is loaded 10% at most  
CPU - hits ~ 100%, but goes back to 10%-20% with mysql degraded performance  
Ram - 36G out of 64G used by MySQL, the rest is for OS, never goes to swap

What else can I check or do? Any suggestions are appreciated!  
Thanks.

---

<div class="post-metadata">

**Author:** ![EZboy](https://avatars.discourse-cdn.com/v4/letter/e/9dc877/32.png) [@EZboy](https://forums.percona.com/u/EZboy)\
**Post date:** [August 9, 2012, 2:56pm UTC](https://forums.percona.com/t/mysql-performance-degrades-after-hitting-100-cpu-load/1862/2 "2012-08-09T14:56:37Z")

</div>

After some digging on internet and trying some things, I found that executing “FLUSH TABLES” seems to restore performance close to original levels temporarily. Any thoughts about that?

---

<div class="post-metadata">

**Author:** ![EZboy](https://avatars.discourse-cdn.com/v4/letter/e/9dc877/32.png) [@EZboy](https://forums.percona.com/u/EZboy)\
**Post date:** [August 9, 2012, 7:52pm UTC](https://forums.percona.com/t/mysql-performance-degrades-after-hitting-100-cpu-load/1862/3 "2012-08-09T19:52:47Z")

</div>

Just wanted to add the screenshot of “top” to show the database load. The database is at that load level approximately 50% of the time. It shows 32 Cpu, but really its 2 - 8 core cpu’s + hyper-threading.  
Is it possible MySQL does not have enough cpu cycles left to clean up some internal queues/caches … and “flush tables” explicitly does that?