# mysql 4.1.16 cpu 99%

**URL:** <https://forums.percona.com/t/mysql-4-1-16-cpu-99/609>\
**Category:** Other MySQL® Questions\
**Created:** [January 24, 2008, 5:09am UTC](https://forums.percona.com/t/mysql-4-1-16-cpu-99/609 "2008-01-24T05:09:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![webservice](https://avatars.discourse-cdn.com/v4/letter/w/278dde/32.png) [@webservice](https://forums.percona.com/u/webservice)\
**Post date:** [January 24, 2008, 5:09am UTC](https://forums.percona.com/t/mysql-4-1-16-cpu-99/609/1 "2008-01-24T05:09:06Z")

</div>

Hello,

i have an eshop which is built on mysql/php  
the server is dedicated so this is the only site that is hosted.  
last days even with one page visit the load of mysql is at the top (99% cput usage).

i attach some details about the mysql

SHOW GLOBAL VARIABLES

±--------------------------------±------------------------------------------------------------+| Variable\_name | Value |±--------------------------------±------------------------------------------------------------+| back\_log | 50 || basedir | /usr/ || bdb\_cache\_size | 8388600 || bdb\_home | /var/lib/mysql/ || bdb\_log\_buffer\_size | 32768 || bdb\_logdir | || bdb\_max\_lock | 10000 || bdb\_shared\_data | OFF || bdb\_tmpdir | /tmp/ || binlog\_cache\_size | 32768 || bulk\_insert\_buffer\_size | 8388608 || character\_set\_client | utf8 || character\_set\_connection | utf8 || character\_set\_database | utf8 || character\_set\_results | utf8 || character\_set\_server | utf8 || character\_set\_system | utf8 || character\_sets\_dir | /usr/share/mysql/charsets/ || collation\_connection | utf8\_general\_ci || collation\_database | utf8\_general\_ci || collation\_server | utf8\_general\_ci || concurrent\_insert | ON || 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 || expire\_logs\_days | 0 || 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 | NO || have\_bdb | YES || have\_blackhole\_engine | NO || have\_compress | YES || have\_crypt | YES || have\_csv | NO || have\_example\_engine | NO || have\_geometry | YES || have\_innodb | YES || have\_isam | YES || have\_ndbcluster | NO || have\_openssl | YES || have\_query\_cache | YES || have\_raid | NO || have\_rtree\_keys | YES || have\_symlink | YES || init\_connect | || init\_file | || init\_slave | || innodb\_additional\_mem\_pool\_size | 2097152 || innodb\_autoextend\_increment | 8 || innodb\_buffer\_pool\_awe\_mem\_mb | 0 || innodb\_buffer\_pool\_size | 16777216 || innodb\_data\_file\_path | ibdata1:10M:autoextend:max:128M || innodb\_data\_home\_dir | || innodb\_fast\_shutdown | ON || innodb\_file\_io\_threads | 4 || innodb\_file\_per\_table | OFF || innodb\_flush\_log\_at\_trx\_commit | 1 || innodb\_flush\_method | || innodb\_force\_recovery | 0 || innodb\_lock\_wait\_timeout | 50 || innodb\_locks\_unsafe\_for\_binlog | OFF || innodb\_log\_arch\_dir | || innodb\_log\_archive | OFF || innodb\_log\_buffer\_size | 8388608 || innodb\_log\_file\_size | 5242880 || innodb\_log\_files\_in\_group | 2 || innodb\_log\_group\_home\_dir | ./ || innodb\_max\_dirty\_pages\_pct | 90 || innodb\_max\_purge\_lag | 0 || innodb\_mirrored\_log\_groups | 1 || innodb\_open\_files | 300 || innodb\_table\_locks | ON || innodb\_thread\_concurrency | 8 || 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 || license | GPL || local\_infile | ON || locked\_in\_memory | OFF || log | OFF || log\_bin | OFF || log\_error | || log\_slave\_updates | OFF || log\_slow\_queries | ON || log\_update | OFF || log\_warnings | 1 || long\_query\_time | 10 || low\_priority\_updates | OFF || lower\_case\_file\_system | OFF || lower\_case\_table\_names | 0 || max\_allowed\_packet | 1047552 || max\_binlog\_cache\_size | 4294967295 || max\_binlog\_size | 1073741824 || max\_connect\_errors | 10 || max\_connections | 100 || max\_delayed\_threads | 20 || max\_error\_count | 64 || max\_heap\_table\_size | 16777216 || max\_insert\_delayed\_threads | 20 || max\_join\_size | 4294967295 || max\_length\_for\_sort\_data | 1024 || max\_relay\_log\_size | 0 || max\_seeks\_for\_key | 4294967295 || max\_sort\_length | 1024 || max\_tmp\_tables | 32 || max\_user\_connections | 0 || max\_write\_lock\_count | 4294967295 || myisam\_data\_pointer\_size | 4 || myisam\_max\_extra\_sort\_file\_size | 2147483648 || myisam\_max\_sort\_file\_size | 2147483647 || myisam\_recover\_options | OFF || myisam\_repair\_threads | 1 || myisam\_sort\_buffer\_size | 8388608 || myisam\_stats\_method | nulls\_unequal || net\_buffer\_length | 8192 || net\_read\_timeout | 30 || net\_retry\_count | 10 || net\_write\_timeout | 60 || new | OFF || old\_passwords | ON || open\_files\_limit | 1024 || pid\_file | /var/run/mysqld/mysqld.pid || port | 3306 || preload\_buffer\_size | 32768 || protocol\_version | 10 || query\_alloc\_block\_size | 8192 || query\_cache\_limit | 1048576 || query\_cache\_min\_res\_unit | 4096 || query\_cache\_size | 0 || query\_cache\_type | ON || query\_cache\_wlock\_invalidate | OFF || query\_prealloc\_size | 8192 || range\_alloc\_block\_size | 2048 || read\_buffer\_size | 258048 || read\_only | OFF || read\_rnd\_buffer\_size | 520192 || relay\_log\_purge | ON || relay\_log\_space\_limit | 0 || rpl\_recovery\_rank | 0 || secure\_auth | OFF || server\_id | 0 || skip\_external\_locking | ON || skip\_networking | OFF || skip\_show\_database | OFF || slave\_net\_timeout | 3600 || slave\_transaction\_retries | 0 || slow\_launch\_time | 2 || socket | /var/lib/mysql/mysql.sock || sort\_buffer\_size | 524280 || sql\_mode | || sql\_notes | ON || sql\_warnings | ON || storage\_engine | MyISAM || sync\_binlog | 0 || sync\_frm | ON || sync\_replication | 0 || sync\_replication\_slave\_id | 0 || sync\_replication\_timeout | 0 || system\_time\_zone | EET || table\_cache | 64 || table\_type | MyISAM || thread\_cache\_size | 0 || thread\_stack | 196608 || time\_format | %H:%i:%s || time\_zone | SYSTEM || tmp\_table\_size | 33554432 || tmpdir | || transaction\_alloc\_block\_size | 8192 || transaction\_prealloc\_size | 4096 || tx\_isolation | REPEATABLE-READ || version | 4.1.16-log || version\_bdb | Sleepycat Software: Berkeley DB 4.1.24: (November 30, 2005) || version\_comment | Source distribution || version\_compile\_machine | i386 || version\_compile\_os | redhat-linux-gnu || wait\_timeout | 28800 |±--------------------------------±------------------------------------------------------------+

SHOW STATUS

±-------------------------------±----------+| Variable\_name | Value |±-------------------------------±----------+| Aborted\_clients | 20 || Aborted\_connects | 0 || Binlog\_cache\_disk\_use | 0 || Binlog\_cache\_use | 0 || Bytes\_received | 23226253 || Bytes\_sent | 94984695 || Com\_admin\_commands | 11 || Com\_alter\_db | 0 || Com\_alter\_table | 0 || Com\_analyze | 0 || Com\_backup\_table | 0 || Com\_begin | 0 || Com\_change\_db | 363 || Com\_change\_master | 0 || Com\_check | 0 || Com\_checksum | 0 || Com\_commit | 0 || Com\_create\_db | 0 || Com\_create\_function | 0 || Com\_create\_index | 0 || Com\_create\_table | 0 || Com\_dealloc\_sql | 0 || Com\_delete | 95 || Com\_delete\_multi | 0 || Com\_do | 0 || Com\_drop\_db | 0 || Com\_drop\_function | 0 || Com\_drop\_index | 0 || Com\_drop\_table | 0 || Com\_drop\_user | 0 || Com\_execute\_sql | 0 || Com\_flush | 0 || Com\_grant | 0 || Com\_ha\_close | 0 || Com\_ha\_open | 0 || Com\_ha\_read | 0 || Com\_help | 0 || Com\_insert | 105 || Com\_insert\_select | 0 || Com\_kill | 1 || Com\_load | 0 || Com\_load\_master\_data | 0 || Com\_load\_master\_table | 0 || Com\_lock\_tables | 0 || Com\_optimize | 0 || Com\_preload\_keys | 0 || Com\_prepare\_sql | 0 || Com\_purge | 0 || Com\_purge\_before\_date | 0 || Com\_rename\_table | 0 || Com\_repair | 0 || Com\_replace | 0 || Com\_replace\_select | 0 || Com\_reset | 0 || Com\_restore\_table | 0 || Com\_revoke | 0 || Com\_revoke\_all | 0 || Com\_rollback | 0 || Com\_savepoint | 0 || Com\_select | 102413 || Com\_set\_option | 1443 || Com\_show\_binlog\_events | 0 || Com\_show\_binlogs | 7 || Com\_show\_charsets | 11 || Com\_show\_collations | 11 || Com\_show\_column\_types | 0 || Com\_show\_create\_db | 0 || Com\_show\_create\_table | 0 || Com\_show\_databases | 2 || Com\_show\_errors | 0 || Com\_show\_fields | 15 || Com\_show\_grants | 2 || Com\_show\_innodb\_status | 0 || Com\_show\_keys | 0 || Com\_show\_logs | 0 || Com\_show\_master\_status | 0 || Com\_show\_ndb\_status | 0 || Com\_show\_new\_master | 0 || Com\_show\_open\_tables | 0 || Com\_show\_privileges | 0 || Com\_show\_processlist | 6 || Com\_show\_slave\_hosts | 0 || Com\_show\_slave\_status | 0 || Com\_show\_status | 18 || Com\_show\_storage\_engines | 0 || Com\_show\_tables | 16 || Com\_show\_variables | 25 || Com\_show\_warnings | 0 || Com\_slave\_start | 0 || Com\_slave\_stop | 0 || Com\_stmt\_close | 0 || Com\_stmt\_execute | 0 || Com\_stmt\_prepare | 0 || Com\_stmt\_reset | 0 || Com\_stmt\_send\_long\_data | 0 || Com\_truncate | 0 || Com\_unlock\_tables | 0 || Com\_update | 195 || Com\_update\_multi | 0 || Connections | 367 || Created\_tmp\_disk\_tables | 169 || Created\_tmp\_files | 79 || Created\_tmp\_tables | 17674 || Delayed\_errors | 0 || Delayed\_insert\_threads | 0 || Delayed\_writes | 0 || Flush\_commands | 1 || Handler\_commit | 0 || Handler\_delete | 100 || Handler\_discover | 0 || Handler\_read\_first | 22167 || Handler\_read\_key | 47776143 || Handler\_read\_next | 116788949 || Handler\_read\_prev | 0 || Handler\_read\_rnd | 621157 || Handler\_read\_rnd\_next | 8756265 || Handler\_rollback | 0 || Handler\_update | 1162 || Handler\_write | 859434 || Key\_blocks\_not\_flushed | 0 || Key\_blocks\_unused | 14289 || Key\_blocks\_used | 585 || Key\_read\_requests | 101997074 || Key\_reads | 2262 || Key\_write\_requests | 6614 || Key\_writes | 700 || Max\_used\_connections | 63 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 83 || Open\_streams | 0 || Open\_tables | 71 || Opened\_tables | 1397 || Qcache\_free\_blocks | 0 || Qcache\_free\_memory | 0 || Qcache\_hits | 0 || Qcache\_inserts | 0 || Qcache\_lowmem\_prunes | 0 || Qcache\_not\_cached | 0 || Qcache\_queries\_in\_cache | 0 || Qcache\_total\_blocks | 0 || Questions | 105467 || Rpl\_status | NULL || Select\_full\_join | 759 || Select\_full\_range\_join | 7 || Select\_range | 220 || Select\_range\_check | 0 || Select\_scan | 28214 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 19 || Slow\_queries | 42 || Sort\_merge\_passes | 38 || Sort\_range | 22 || Sort\_rows | 803494 || Sort\_scan | 20554 || Ssl\_accept\_renegotiates | 0 || Ssl\_accepts | 0 || Ssl\_callback\_cache\_hits | 0 || Ssl\_cipher | || Ssl\_cipher\_list | || Ssl\_client\_connects | 0 || Ssl\_connect\_renegotiates | 0 || Ssl\_ctx\_verify\_depth | 0 || Ssl\_ctx\_verify\_mode | 0 || Ssl\_default\_timeout | 0 || Ssl\_finished\_accepts | 0 || Ssl\_finished\_connects | 0 || Ssl\_session\_cache\_hits | 0 || Ssl\_session\_cache\_misses | 0 || Ssl\_session\_cache\_mode | NONE || Ssl\_session\_cache\_overflows | 0 || Ssl\_session\_cache\_size | 0 || Ssl\_session\_cache\_timeouts | 0 || Ssl\_sessions\_reused | 0 || Ssl\_used\_session\_cache\_entries | 0 || Ssl\_verify\_depth | 0 || Ssl\_verify\_mode | 0 || Ssl\_version | || Table\_locks\_immediate | 155673 || Table\_locks\_waited | 119 || Threads\_cached | 0 || Threads\_connected | 57 || Threads\_created | 366 || Threads\_running | 26 || Uptime | 5066 |±-------------------------------±----------+

can anyone help me with what happens? thanks in advance!

---

<div class="post-metadata">

**Author:** ![erkules](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@erkules](https://forums.percona.com/u/erkules)\
**Post date:** [January 24, 2008, 7:17am UTC](https://forums.percona.com/t/mysql-4-1-16-cpu-99/609/2 "2008-01-24T07:17:40Z")

</div>

| Questions | 105467 |

| Select\_full\_join | 759 |  
| Select\_scan | 28214 |  
| Sort\_scan | 20554 |

Have a look @ your Queries. I think you should optimize them.

---

<div class="post-metadata">

**Author:** ![webservice](https://avatars.discourse-cdn.com/v4/letter/w/278dde/32.png) [@webservice](https://forums.percona.com/u/webservice)\
**Post date:** [January 24, 2008, 7:19am UTC](https://forums.percona.com/t/mysql-4-1-16-cpu-99/609/3 "2008-01-24T07:19:28Z")

</div>

Yes you are right and i know that the queries need to be optimized but without having changed the code for months. only the last few days i face this problem so hard.

is there any other reason about this?

---

<div class="post-metadata">

**Author:** ![webservice](https://avatars.discourse-cdn.com/v4/letter/w/278dde/32.png) [@webservice](https://forums.percona.com/u/webservice)\
**Post date:** [January 24, 2008, 7:42am UTC](https://forums.percona.com/t/mysql-4-1-16-cpu-99/609/4 "2008-01-24T07:42:37Z")

</div>

Yes you are right again but when now i start to run the site (enabling the first page) the server is unreachable in few seconds…

so now a i can not provide sth like that…

---

<div class="post-metadata">

**Author:** ![erkules](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@erkules](https://forums.percona.com/u/erkules)\
**Post date:** [January 24, 2008, 7:53am UTC](https://forums.percona.com/t/mysql-4-1-16-cpu-99/609/5 "2008-01-24T07:53:14Z")

</div>

Look into the error logs etc.  
Its hard to guess:-(