# spike CPU usage

**URL:** <https://forums.percona.com/t/spike-cpu-usage/1245>\
**Category:** Other MySQL® Questions\
**Created:** [August 25, 2009, 10:55am UTC](https://forums.percona.com/t/spike-cpu-usage/1245 "2009-08-25T10:55:44Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![mrpink](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mrpink/32/817_2.png) [@mrpink](https://forums.percona.com/u/mrpink)\
**Post date:** [August 25, 2009, 10:55am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/1 "2009-08-25T10:55:44Z")

</div>

Hi,

I am experiencing a issue with the Mysqld process spiking its CPU usage, I was investigating a reported issue with performance with our application and noticed in top that it was jumping to 300%+ for short bursts. There are a few Mysql querys in the slow log that took a long time but theres only three max per day

here are the variables (they have been optimized)

±--------------------------------±------------------------ ----+  
| Variable\_name | Value |  
±--------------------------------±------------------------ ----+  
| auto\_increment\_increment | 1 |  
| auto\_increment\_offset | 1 |  
| automatic\_sp\_privileges | ON |  
| back\_log | 50 |  
| basedir | /usr/ |  
| binlog\_cache\_size | 32768 |  
| bulk\_insert\_buffer\_size | 8388608 |  
| character\_set\_client | latin1 |  
| character\_set\_connection | latin1 |  
| character\_set\_database | latin1 |  
| character\_set\_filesystem | binary |  
| character\_set\_results | latin1 |  
| character\_set\_server | latin1 |  
| character\_set\_system | utf8 |  
| character\_sets\_dir | /usr/share/mysql/charsets/ |  
| collation\_connection | latin1\_swedish\_ci |  
| collation\_database | latin1\_swedish\_ci |  
| collation\_server | latin1\_swedish\_ci |  
| completion\_type | 0 |  
| concurrent\_insert | 1 |  
| connect\_timeout | 5 |  
| datadir | /var/lib/mysql/ |  
| date\_format | %Y-%m-%d |  
| datetime\_format | %Y-%m-%d %H:%i:%s |  
| default\_week\_format | 0 |  
| delay\_key\_write | ON |  
| delayed\_insert\_limit | 100 |  
| delayed\_insert\_timeout | 300 |  
| delayed\_queue\_size | 1000 |  
| div\_precision\_increment | 4 |  
| keep\_files\_on\_create | OFF |  
| engine\_condition\_pushdown | OFF |  
| expire\_logs\_days | 10 |  
| flush | OFF |  
| flush\_time | 0 |  
| ft\_boolean\_syntax | + -\>\<()~\*:“”&| |  
| ft\_max\_word\_len | 84 |  
| ft\_min\_word\_len | 4 |  
| ft\_query\_expansion\_limit | 20 |  
| ft\_stopword\_file | (built-in) |  
| group\_concat\_max\_len | 1024 |  
| have\_archive | YES |  
| have\_bdb | NO |  
| have\_blackhole\_engine | YES |  
| have\_compress | YES |  
| have\_crypt | YES |  
| have\_csv | YES |  
| have\_dynamic\_loading | YES |  
| have\_example\_engine | NO |  
| have\_federated\_engine | YES |  
| have\_geometry | YES |  
| have\_innodb | YES |  
| have\_isam | NO |  
| have\_merge\_engine | YES |  
| have\_ndbcluster | DISABLED |  
| have\_openssl | DISABLED |  
| have\_ssl | DISABLED |  
| have\_query\_cache | YES |  
| have\_raid | NO |  
| have\_rtree\_keys | YES |  
| have\_symlink | YES |  
| hostname | db01 |  
| init\_connect | |  
| init\_file | |  
| init\_slave | |  
| innodb\_additional\_mem\_pool\_size | 1048576 |  
| innodb\_autoextend\_increment | 8 |  
| innodb\_buffer\_pool\_awe\_mem\_mb | 0 |  
| innodb\_buffer\_pool\_size | 2147483648 |  
| innodb\_checksums | ON |  
| innodb\_commit\_concurrency | 0 |  
| innodb\_concurrency\_tickets | 500 |  
| innodb\_data\_file\_path | ibdata1:10M:autoextend |  
| innodb\_data\_home\_dir | |  
| innodb\_doublewrite | ON |  
| innodb\_fast\_shutdown | 1 |  
| innodb\_file\_io\_threads | 4 |  
| innodb\_file\_per\_table | OFF |  
| innodb\_flush\_log\_at\_trx\_commit | 2 |  
| innodb\_flush\_method | |  
| innodb\_force\_recovery | 0 |  
| innodb\_lock\_wait\_timeout | 50 |  
| innodb\_locks\_unsafe\_for\_binlog | OFF |  
| innodb\_log\_arch\_dir | /var/lib/mysql/ |  
| innodb\_log\_archive | OFF |  
| innodb\_log\_buffer\_size | 8388608 |  
| innodb\_log\_file\_size | 524288000 |  
| innodb\_log\_files\_in\_group | 2 |  
| innodb\_log\_group\_home\_dir | /var/lib/mysql/ |  
| innodb\_max\_dirty\_pages\_pct | 90 |  
| innodb\_max\_purge\_lag | 0 |  
| innodb\_mirrored\_log\_groups | 1 |  
| innodb\_open\_files | 300 |  
| innodb\_rollback\_on\_timeout | OFF |  
| innodb\_support\_xa | ON |  
| innodb\_sync\_spin\_loops | 20 |  
| innodb\_table\_locks | ON |  
| innodb\_thread\_concurrency | 8 |  
| innodb\_thread\_sleep\_delay | 10000 |  
| interactive\_timeout | 28800 |  
| join\_buffer\_size | 131072 |  
| key\_buffer\_size | 16777216 |  
| key\_cache\_age\_threshold | 300 |  
| key\_cache\_block\_size | 1024 |  
| key\_cache\_division\_limit | 100 |  
| language | /usr/share/mysql/english/ |  
| large\_files\_support | ON |  
| large\_page\_size | 0 |  
| large\_pages | OFF |  
| lc\_time\_names | en\_US |  
| license | GPL |  
| local\_infile | ON |  
| locked\_in\_memory | OFF |  
| log | OFF |  
| log\_bin | ON |  
| log\_bin\_trust\_function\_creators | OFF |  
| log\_error | |  
| log\_queries\_not\_using\_indexes | OFF |  
| log\_slave\_updates | OFF |  
| log\_slow\_queries | ON |  
| log\_warnings | 1 |  
| long\_query\_time | 10 |  
| low\_priority\_updates | OFF |  
| lower\_case\_file\_system | OFF |  
| lower\_case\_table\_names | 0 |  
| max\_allowed\_packet | 16776192 |  
| max\_binlog\_cache\_size | 18446744073709551615 |  
| max\_binlog\_size | 524288000 |  
| max\_connect\_errors | 10 |  
| max\_connections | 1000 |  
| max\_delayed\_threads | 20 |  
| max\_error\_count | 64 |  
| max\_heap\_table\_size | 16777216 |  
| max\_insert\_delayed\_threads | 20 |  
| max\_join\_size | 18446744073709551615 |  
| max\_length\_for\_sort\_data | 1024 |  
| max\_prepared\_stmt\_count | 16382 |  
| max\_relay\_log\_size | 0 |  
| max\_seeks\_for\_key | 18446744073709551615 |  
| max\_sort\_length | 1024 |  
| max\_sp\_recursion\_depth | 0 |  
| max\_tmp\_tables | 32 |  
| max\_user\_connections | 0 |  
| max\_write\_lock\_count | 18446744073709551615 |  
| multi\_range\_count | 256 |  
| myisam\_data\_pointer\_size | 6 |  
| myisam\_max\_sort\_file\_size | 9223372036854775807 |  
| myisam\_recover\_options | OFF |  
| myisam\_repair\_threads | 1 |  
| myisam\_sort\_buffer\_size | 268435456 |  
| myisam\_stats\_method | nulls\_unequal |  
| ndb\_autoincrement\_prefetch\_sz | 32 |  
| ndb\_force\_send | ON |  
| ndb\_use\_exact\_count | ON |  
| ndb\_use\_transactions | ON |  
| ndb\_cache\_check\_time | 0 |  
| ndb\_connectstring | |  
| net\_buffer\_length | 16384 |  
| net\_read\_timeout | 30 |  
| net\_retry\_count | 10 |  
| net\_write\_timeout | 60 |  
| new | OFF |  
| old\_passwords | ON |  
| open\_files\_limit | 5000 |  
| optimizer\_prune\_level | 1 |  
| optimizer\_search\_depth | 62 |  
| pid\_file | /var/run/mysqld/mysqld.pid |  
| port | 3306 |  
| preload\_buffer\_size | 32768 |  
| profiling | OFF |  
| profiling\_history\_size | 15 |  
| protocol\_version | 10 |  
| query\_alloc\_block\_size | 8192 |  
| query\_cache\_limit | 1048576 |  
| query\_cache\_min\_res\_unit | 4096 |  
| query\_cache\_size | 134217728 |  
| query\_cache\_type | ON |  
| query\_cache\_wlock\_invalidate | OFF |  
| query\_prealloc\_size | 8192 |  
| range\_alloc\_block\_size | 2048 |  
| read\_buffer\_size | 8384512 |  
| read\_only | OFF |  
| read\_rnd\_buffer\_size | 33550336 |  
| relay\_log\_purge | ON |  
| relay\_log\_space\_limit | 0 |  
| rpl\_recovery\_rank | 0 |  
| secure\_auth | OFF |  
| secure\_file\_priv | |  
| server\_id | 1 |  
| skip\_external\_locking | ON |  
| skip\_networking | OFF |  
| skip\_show\_database | OFF |  
| slave\_compressed\_protocol | OFF |  
| slave\_load\_tmpdir | /tmp/ |  
| slave\_net\_timeout | 3600 |  
| slave\_skip\_errors | OFF |  
| slave\_transaction\_retries | 10 |  
| slow\_launch\_time | 2 |  
| socket | /var/run/mysqld/mysqld.sock |  
| sort\_buffer\_size | 8388600 |  
| sql\_big\_selects | ON |  
| sql\_mode | |  
| sql\_notes | ON |  
| sql\_warnings | OFF | |  
| storage\_engine | InnoDB |  
| sync\_binlog | 0 |  
| sync\_frm | ON |  
| system\_time\_zone | BST |  
| table\_cache | 1024 |  
| table\_lock\_wait\_timeout | 50 |  
| table\_type | InnoDB |  
| thread\_cache\_size | 32 |  
| thread\_stack | 131072 |  
| time\_format | %H:%i:%s |  
| time\_zone | SYSTEM |  
| timed\_mutexes | OFF |  
| tmp\_table\_size | 33554432 |  
| tmpdir | /tmp |  
| transaction\_alloc\_block\_size | 8192 |  
| transaction\_prealloc\_size | 4096 |  
| tx\_isolation | REPEATABLE-READ |  
| updatable\_views\_with\_limit | YES |  
| version | 5.0.51a-3ubuntu5.1-log |  
| version\_comment | (Ubuntu) |  
| version\_compile\_machine | x86\_64 |  
| version\_compile\_os | debian-linux-gnu |  
| wait\_timeout | 28800 |  
±--------------------------------±------------------------ ----+  
232 rows in set (0.00 sec)

That actual server load average is actually really low (under 1)

I am really a novice when it comes to performance tuning Mysql so its likely that it is something blazingly obvious

EDIT Apologies for the lack of indentation not sure why it is doing that

Thanks,  
Will

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 25, 2009, 11:09am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/2 "2009-08-25T11:09:57Z")

</div>

You expect us to tell the cause based on just your configuration?

---

<div class="post-metadata">

**Author:** ![mrpink](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mrpink/32/817_2.png) [@mrpink](https://forums.percona.com/u/mrpink)\
**Post date:** [August 25, 2009, 11:20am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/3 "2009-08-25T11:20:57Z")

</div>

No actually I didn’t but it maybe possible that certain variables may not be correct causing issues, it certainly isn’t a hardware issue as everything is fine from what I can see (apart from high memory usage but I have always been told this is normal) I imagine there are others out there who have experienced the same issues and could suggest some things to try and resolve the issue.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 25, 2009, 1:17pm UTC](https://forums.percona.com/t/spike-cpu-usage/1245/4 "2009-08-25T13:17:15Z")

</div>

It are your queries, most likely.

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [August 25, 2009, 2:59pm UTC](https://forums.percona.com/t/spike-cpu-usage/1245/5 "2009-08-25T14:59:16Z")

</div>

Please set log\_queries\_not\_using\_indexes to ON, and change long query time to 2

Does this cpu usage go with higher hdd i/o ?  
And could You say which queries were running at high cpu usage ?

---

<div class="post-metadata">

**Author:** ![mrpink](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mrpink/32/817_2.png) [@mrpink](https://forums.percona.com/u/mrpink)\
**Post date:** [August 26, 2009, 7:23am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/6 "2009-08-26T07:23:45Z")

</div>

The slow query log is filled up with lots of query’s with a query time of under a second now I presume there ones that don’t use a index?

The spike happens so quickly (under a second) that it doesn’t appear to affect/be effected by anything else

Here is the top output

top - 13:48:25 up 134 days, 23:31, 4 users, load average: 1.04, 0.88, 0.79  
Tasks: 105 total, 1 running, 104 sleeping, 0 stopped, 0 zombie  
Cpu(s): 27.6%us, 0.6%sy, 0.0%ni, 70.7%id, 0.4%wa, 0.0%hi, 0.7%si, 0.0%st  
Mem: 8190008k total, 8141204k used, 48804k free, 147032k buffers  
Swap: 11622988k total, 444712k used, 11178276k free, 774492k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND  
7601 root 20 0 3290m 2.5g 5308 S 2 32.2 2357:04 java  
3396 mysql 20 0 2745m 2.5g 6232 S 112 31.9 1993:01 mysqld

Which apart from the high memory usage looks fine to me

Thanks for your suggestions

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [August 26, 2009, 9:08am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/7 "2009-08-26T09:08:19Z")

</div>

Right, rows\_examined  
So, mysql reads 317 rows, but query limits them to 2.

---

<div class="post-metadata">

**Author:** ![mrpink](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mrpink/32/817_2.png) [@mrpink](https://forums.percona.com/u/mrpink)\
**Post date:** [August 26, 2009, 9:12am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/8 "2009-08-26T09:12:38Z")

</div>

Is that a bad thing I really wouldn’t know? Looking at the query’s they all appear to be the same 1 or 2 rows sent but lots of Rows examined

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [August 26, 2009, 9:32am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/9 "2009-08-26T09:32:57Z")

</div>

Rows\_sent and Rows\_examined should be as near as possible. The best case is rows\_sent = rows\_examined. The worst case is when mysql has to examine all the rows.

---

<div class="post-metadata">

**Author:** ![mrpink](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mrpink/32/817_2.png) [@mrpink](https://forums.percona.com/u/mrpink)\
**Post date:** [August 26, 2009, 9:48am UTC](https://forums.percona.com/t/spike-cpu-usage/1245/10 "2009-08-26T09:48:20Z")

</div>

ok I see now thanks for the help