# cpu load 100%

**URL:** <https://forums.percona.com/t/cpu-load-100/555>\
**Category:** Other MySQL® Questions\
**Created:** [December 5, 2007, 12:11pm UTC](https://forums.percona.com/t/cpu-load-100/555 "2007-12-05T12:11:30Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![mesti](https://avatars.discourse-cdn.com/v4/letter/m/898d66/32.png) [@mesti](https://forums.percona.com/u/mesti)\
**Post date:** [December 5, 2007, 12:11pm UTC](https://forums.percona.com/t/cpu-load-100/555/1 "2007-12-05T12:11:30Z")

</div>

I have a debian server with mysql 4.1  
1 gb ddr2 ram, intel 3.200 cpu  
cpu load always 100%

mysql\> SHOW GLOBAL VARIABLES;±--------------------------------±-----------------------------+| Variable\_name | Value |±--------------------------------±-----------------------------+| 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\_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 || 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 | 5 || 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 | NO || have\_compress | YES || have\_crypt | YES || have\_csv | YES || have\_example\_engine | NO || have\_geometry | YES || have\_innodb | YES || have\_isam | YES || have\_ndbcluster | DISABLED || have\_openssl | NO || 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 | 1048576 || innodb\_autoextend\_increment | 8 || innodb\_buffer\_pool\_awe\_mem\_mb | 0 || innodb\_buffer\_pool\_size | 8388608 || innodb\_data\_file\_path | ibdata1:10M:autoextend || 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 | 1048576 || 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 | 33554432 || 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 | ON || log\_error | || log\_slave\_updates | OFF || log\_slow\_queries | OFF || log\_update | OFF || log\_warnings | 1 || long\_query\_time | 4 || low\_priority\_updates | OFF || lower\_case\_file\_system | OFF || lower\_case\_table\_names | 0 || max\_allowed\_packet | 1073740800 || max\_binlog\_cache\_size | 4294967295 || max\_binlog\_size | 104857600 || max\_connect\_errors | 10 || max\_connections | 300 || 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\_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 || ndb\_autoincrement\_prefetch\_sz | 32 || ndb\_force\_send | ON || ndb\_use\_exact\_count | ON || ndb\_use\_transactions | OFF || net\_buffer\_length | 16384 || net\_read\_timeout | 30 || net\_retry\_count | 10 || net\_write\_timeout | 60 || new | OFF || old\_passwords | OFF || open\_files\_limit | 1510 || pid\_file | /var/run/mysqld/mysqld.pid || port | 3306 || preload\_buffer\_size | 32768 || protocol\_version | 10 || query\_alloc\_block\_size | 8192 || query\_cache\_limit | 16777216 || query\_cache\_min\_res\_unit | 4096 || query\_cache\_size | 102400 || query\_cache\_type | ON || query\_cache\_wlock\_invalidate | OFF || query\_prealloc\_size | 8192 || range\_alloc\_block\_size | 2048 || read\_buffer\_size | 131072 || read\_only | OFF || read\_rnd\_buffer\_size | 262144 || relay\_log\_purge | ON || relay\_log\_space\_limit | 0 || rpl\_recovery\_rank | 0 || secure\_auth | OFF || server\_id | 1 || 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/run/mysqld/mysqld.sock || sort\_buffer\_size | 2097144 || 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 | CET || table\_cache | 512 || table\_type | MyISAM || thread\_cache\_size | 8 || thread\_stack | 262144 || time\_format | %H:%i:%s || time\_zone | SYSTEM || tmp\_table\_size | 33554432 || tmpdir | /tmp || transaction\_alloc\_block\_size | 8192 || transaction\_prealloc\_size | 4096 || tx\_isolation | REPEATABLE-READ || version | 4.1.15-Debian\_0.dotdeb.4-log || version\_comment | Source distribution || version\_compile\_machine | i386 || version\_compile\_os | pc-linux-gnu || wait\_timeout | 28800 |±--------------------------------±-----------------------------+189 rows in set (0.01 sec)

my.cnf:

# \* Basic Settings#user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplanguage = /usr/share/mysql/englishskip-external-locking## localhost which is more compatible and is not less secure.#bind-address = 127.0.0.1## \* Fine Tuning#key\_buffer = 32Mmax\_allowed\_packet = 1Gthread\_stack = 256Kthread\_cache\_size = 8max\_connections = 300table\_cache = 512thread\_concurrency = 20## \* Query Cache Configuration#query\_cache\_limit = 16Mquery\_cache\_size = 100K## \* Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.#log = /var/log/mysql/mysql.log## Error logging goes to syslog. This is a Debian improvement :)## Here you can see queries with especially long duration#log\_slow\_queries = /var/log/mysql/mysql-slow.loglong\_query\_time = 4log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about# other settings you may need to change.#server-id = 1log\_bin = /var/log/mysql/mysql-bin.log# WARNING: Using expire\_logs\_days without bin\_log crashes the server! See README.Debian!expire\_logs\_days = 5max\_binlog\_size = 100M#binlog\_do\_db = include\_database\_name#binlog\_ignore\_db = include\_database\_name## \* BerkeleyDB## Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.skip-bdb## \* InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.#skip-innodb## \* Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem[mysqldump]quickquote-namesmax\_allowed\_packet = 612M[mysql]#no-auto-rehash # faster start of mysql but no tab completition[isamchk]key\_buffer = 128M

please help..)

sorry for bad english…

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [December 5, 2007, 3:01pm UTC](https://forums.percona.com/t/cpu-load-100/555/2 "2007-12-05T15:01:40Z")

</div>

Some immediate thoughts:  
1.  
How large is your DB?  
Because your setting:  
max\_allowed\_packet = 1G  
seems very odd.  
It is incredibly huge while the rest of your values are very small.

1. 

Post the output from SHOW STATUS so that we can see what your database is actually doing.

---

<div class="post-metadata">

**Author:** ![mesti](https://avatars.discourse-cdn.com/v4/letter/m/898d66/32.png) [@mesti](https://forums.percona.com/u/mesti)\
**Post date:** [December 5, 2007, 3:36pm UTC](https://forums.percona.com/t/cpu-load-100/555/3 "2007-12-05T15:36:09Z")

</div>

mysql\> SHOW STATUS ;±---------------------------±-----------+| Variable\_name | Value |±---------------------------±-----------+| Aborted\_clients | 3540 || Aborted\_connects | 2 || Binlog\_cache\_disk\_use | 0 || Binlog\_cache\_use | 0 || Bytes\_received | 49348886 || Bytes\_sent | 2512562367 || Com\_admin\_commands | 5252 || Com\_alter\_db | 0 || Com\_alter\_table | 0 || Com\_analyze | 0 || Com\_backup\_table | 0 || Com\_begin | 0 || Com\_change\_db | 18806 || 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 | 767 || 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 | 3926 || Com\_insert\_select | 0 || Com\_kill | 0 || Com\_load | 0 || Com\_load\_master\_data | 0 || Com\_load\_master\_table | 0 || Com\_lock\_tables | 4 || Com\_optimize | 7 || 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 | 265725 || Com\_set\_option | 428 || Com\_show\_binlog\_events | 0 || Com\_show\_binlogs | 1 || Com\_show\_charsets | 13 || Com\_show\_collations | 13 || Com\_show\_column\_types | 0 || Com\_show\_create\_db | 0 || Com\_show\_create\_table | 372 || Com\_show\_databases | 13 || Com\_show\_errors | 0 || Com\_show\_fields | 248 || Com\_show\_grants | 7 || 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 | 0 || Com\_show\_slave\_hosts | 0 || Com\_show\_slave\_status | 0 || Com\_show\_status | 2 || Com\_show\_storage\_engines | 0 || Com\_show\_tables | 395 || Com\_show\_variables | 35 || 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 | 4 || Com\_update | 34342 || Com\_update\_multi | 3481 || Connections | 13117 || Created\_tmp\_disk\_tables | 13 || Created\_tmp\_files | 9 || Created\_tmp\_tables | 24347 || Delayed\_errors | 0 || Delayed\_insert\_threads | 0 || Delayed\_writes | 0 || Flush\_commands | 1 || Handler\_commit | 0 || Handler\_delete | 2366 || Handler\_discover | 0 || Handler\_read\_first | 9433 || Handler\_read\_key | 565826 || Handler\_read\_next | 33616984 || Handler\_read\_prev | 14422 || Handler\_read\_rnd | 1588681 || Handler\_read\_rnd\_next | 987408255 || Handler\_rollback | 0 || Handler\_update | 105713 || Handler\_write | 65458 || Key\_blocks\_not\_flushed | 0 || Key\_blocks\_unused | 52225 || Key\_blocks\_used | 5765 || Key\_read\_requests | 4927747 || Key\_reads | 21262 || Key\_write\_requests | 61873 || Key\_writes | 39608 || Max\_used\_connections | 242 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 398 || Open\_streams | 0 || Open\_tables | 234 || Opened\_tables | 712 || Qcache\_free\_blocks | 2 || Qcache\_free\_memory | 137312 || Qcache\_hits | 230520 || Qcache\_inserts | 224278 || Qcache\_lowmem\_prunes | 143039 || Qcache\_not\_cached | 41170 || Qcache\_queries\_in\_cache | 54 || Qcache\_total\_blocks | 116 || Questions | 570348 || Rpl\_status | NULL || Select\_full\_join | 57 || Select\_full\_range\_join | 0 || Select\_range | 5734 || Select\_range\_check | 0 || Select\_scan | 39636 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 27 || Slow\_queries | 39979 || Sort\_merge\_passes | 3 || Sort\_range | 6516 || Sort\_rows | 1761065 || Sort\_scan | 25291 || Table\_locks\_immediate | 366575 || Table\_locks\_waited | 2787 || Threads\_cached | 0 || Threads\_connected | 107 || Threads\_created | 1053 || Threads\_running | 3 || Uptime | 4214 |±---------------------------±-----------+163 rows in set (0.00 sec)

my DB is 300mb

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [December 5, 2007, 7:32pm UTC](https://forums.percona.com/t/cpu-load-100/555/4 "2007-12-05T19:32:09Z")

</div>

OK, this:  
Select\_full\_join | 57

Tells us that you have at least one JOIN that doesn’t use an index at all.

| Select\_scan | 39636 |  
Tells us that about 1/10 of your queries does a table scan.

So I suggest that you turn on the slow query log and start examining what queries ends up there. Because you seem to lack some important indexes in your database.

Another thing that is good to know for you is that if you add the column that you order by as the last column in a combined index the DBMS can use that index to retrieve the rows in sorted order. And that avoids the need for sorting the rows later in the query execution. Although it only works on some types of queries it is worthwhile to check out.

---

<div class="post-metadata">

**Author:** ![mesti](https://avatars.discourse-cdn.com/v4/letter/m/898d66/32.png) [@mesti](https://forums.percona.com/u/mesti)\
**Post date:** [December 6, 2007, 1:16pm UTC](https://forums.percona.com/t/cpu-load-100/555/5 "2007-12-06T13:16:02Z")

</div>

You would write it down in detail that what make with commands because I do not understand it reallya

sorry for the bad english…( confused:

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [December 6, 2007, 6:54pm UTC](https://forums.percona.com/t/cpu-load-100/555/6 "2007-12-06T18:54:40Z")

</div>

Add these rows to your my.cnf file:

loglog\_slow\_querieslong\_query\_time=1log\_long\_formatlog\_output=FILE

Then read about the slow query here:  
[URL=“http&#58;&#47;&#47;[MySQL :: MySQL 8.0 Reference Manual :: 5.4.5 The Slow Query Log](http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html)”][http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html[/URL]](http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html%5B/URL%5D)

Then you are able to see in the slow query log which queries that are slow.

---

<div class="post-metadata">

**Author:** ![mesti](https://avatars.discourse-cdn.com/v4/letter/m/898d66/32.png) [@mesti](https://forums.percona.com/u/mesti)\
**Post date:** [December 7, 2007, 9:56am UTC](https://forums.percona.com/t/cpu-load-100/555/7 "2007-12-07T09:56:36Z")

</div>

I put it in but:

mpm:~# /etc/init.d/mysql restart  
Stopping MySQL database server: mysqld.  
Starting MySQL database server: mysqld…failed.  
Please take a look at the syslog.  
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed  
error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysq ld.sock’ (2)’  
Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!

:S

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [December 7, 2007, 10:07am UTC](https://forums.percona.com/t/cpu-load-100/555/8 "2007-12-07T10:07:32Z")

</div>

And what does your syslog file say?

And what does it say in the mysql error log file?  
Which is usually placed at:  
/var/lib/mysql/[hostname].err

---

<div class="post-metadata">

**Author:** ![mesti](https://avatars.discourse-cdn.com/v4/letter/m/898d66/32.png) [@mesti](https://forums.percona.com/u/mesti)\
**Post date:** [December 9, 2007, 2:13pm UTC](https://forums.percona.com/t/cpu-load-100/555/9 "2007-12-09T14:13:06Z")

</div>

I not found in /var/lib/mysql/ nothing

I run the tuning-primer.sh

mpm:~# ./tuning-primer.sh – MYSQL PERFORMANCE TUNING PRIMER – - By: Matthew Montgomery -MySQL Version 4.1.15-Debian\_0.dotdeb.4-log i386Uptime = 1 days 23 hrs 7 min 5 secAvg. qps = 100Total Questions = 17099276Threads Connected = 180Warning: Server has not been running for at least 48hrs.It may not be safe to use these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:[http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.htmlVisit](http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.htmlVisit) [http://www.mysql.com/products/enterprise/advisors.htmlfor](http://www.mysql.com/products/enterprise/advisors.htmlfor) info about MySQL’s Enterprise Monitoring and Advisory ServiceSLOW QUERIESCurrent long\_query\_time = 4 sec.You have 590528 out of 17110206 that take longer than 4 sec. to completeThe slow query log is NOT enabled.Your long\_query\_time seems to be fineWORKER THREADSCurrent thread\_cache\_size = 8Current threads\_cached = 7Current threads\_per\_sec = 0Historic threads\_per\_sec = 0Your thread\_cache\_size is fineMAX CONNECTIONSCurrent max\_connections = 400Current threads\_connected = 171Historic max\_used\_connections = 277The number of used connections is 69% of the configured maximum.Your max\_connections variable seems to be fine.MEMORY USAGEMax Memory Ever Allocated : 3 GConfigured Max Per-thread Buffers : 4 GConfigured Max Global Buffers : 650 MConfigured Max Memory Limit : 5 GPhysical Memory : 1002.26 MMax memory limit exceeds 90% of physical memoryKEY BUFFERCurrent MyISAM index space = 30 MCurrent key\_buffer\_size = 512 MKey cache miss rate is 1 : 289Key buffer fill ratio = 2.00 %Your key\_buffer\_size seems to be too high.Perhaps you can use these resources elsewhereQUERY CACHEQuery cache is enabledCurrent query\_cache\_size = 128 MCurrent query\_cache\_used = 45 MCurrent query\_cache\_limit = 1 MCurrent Query cache Memory fill ratio = 35.22 %Current query\_cache\_min\_res\_unit = 4 KQuery Cache is 21 % fragmentedRun “FLUSH QUERY CACHE” periodically to defragment the query cache memoryIf you have many small queries lower ‘query\_cache\_min\_res\_unit’ to reduce fragmentation.MySQL won’t cache query results that are larger than query\_cache\_limit in sizeSORT OPERATIONSCurrent sort\_buffer\_size = 2 MCurrent record/read\_rnd\_buffer\_size = 7 MSort buffer seems to be fineJOINSCurrent join\_buffer\_size = 132.00 KYou have had 880 queries where a join could not use an index properlyYou should enable "log-queries-not-using-indexes"Then look for non indexed joins in the slow query log.If you are unable to optimize your queries you may want to increase yourjoin\_buffer\_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join\_buffer\_size whenANY joins not using indexes are found.OPEN FILES LIMITCurrent open\_files\_limit = 2010 filesThe open\_files\_limit should typically be set to at least 2x-3xthat of table\_cache if you have heavy MyISAM usage.Your open\_files\_limit value seems to be fineTABLE CACHECurrent table\_cache value = 512 tablesYou have a total of 435 tablesYou have 475 open tables.The table\_cache value seems to be fineTEMP TABLESCurrent max\_heap\_table\_size = 16 MCurrent tmp\_table\_size = 32 MOf 760854 temp tables, 0% were created on diskEffective in-memory tmp\_table\_size is limited to max\_heap\_table\_size.Created disk tmp tables ratio seems fineTABLE SCANSCurrent read\_buffer\_size = 1 MCurrent table scan ratio = 263 : 1read\_buffer\_size seems to be fineTABLE LOCKINGCurrent Lock Wait ratio = 1 : 121You may benefit from selective use of InnoDB.If you have long running SELECT’s against MyISAM tables and performfrequent updates consider setting ‘low\_priority\_updates=1’

useful onto something these informations?

---

<div class="post-metadata">

**Author:** ![yumkoolaid](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@yumkoolaid](https://forums.percona.com/u/yumkoolaid)\
**Post date:** [January 22, 2008, 1:05pm UTC](https://forums.percona.com/t/cpu-load-100/555/10 "2008-01-22T13:05:36Z")

</div>

I’m having a 100% CPU load problem, here is my extended-status:

| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 47 |  
| 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 | 20 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 1 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 20 |  
| Com\_show\_triggers | 420 |  
| Com\_show\_variables | 3 |  
| Com\_show\_warnings | 0 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 0 |  
| Com\_stmt\_execute | 0 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 0 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 0 |  
| Com\_unlock\_tables | 8 |  
| Com\_update | 688258 |  
| Com\_update\_multi | 0 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 1049870 |  
| Created\_tmp\_disk\_tables | 904 |  
| Created\_tmp\_files | 5 |  
| Created\_tmp\_tables | 115028 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 1 |  
| Handler\_commit | 100608 |  
| Handler\_delete | 100748 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 247460 |  
| Handler\_read\_key | 7975322 |  
| Handler\_read\_next | 598651518 |  
| Handler\_read\_prev | 0 |  
| Handler\_read\_rnd | 3186674 |  
| Handler\_read\_rnd\_next | 336080470215 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 1768219 |  
| Handler\_write | 3458345 |  
| Innodb\_buffer\_pool\_pages\_data | 511 |  
| Innodb\_buffer\_pool\_pages\_dirty | 8 |  
| Innodb\_buffer\_pool\_pages\_flushed | 106578 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_latched | 61 |  
| Innodb\_buffer\_pool\_pages\_misc | 1 |  
| Innodb\_buffer\_pool\_pages\_total | 512 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 364929 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 9057576 |  
| Innodb\_buffer\_pool\_read\_requests | 4112664707 |  
| Innodb\_buffer\_pool\_reads | 3576220 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 703111 |  
| Innodb\_data\_fsyncs | 119576 |  
| Innodb\_data\_pending\_fsyncs | 1 |  
| Innodb\_data\_pending\_reads | 1 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 1826147913728 |  
| Innodb\_data\_reads | 11085477 |  
| Innodb\_data\_writes | 202873 |  
| Innodb\_data\_written | 3599413248 |  
| Innodb\_dblwr\_pages\_written | 106586 |  
| Innodb\_dblwr\_writes | 13943 |  
| Innodb\_log\_waits | 0 |  
| Innodb\_log\_write\_requests | 118546 |  
| Innodb\_log\_writes | 85221 |  
| Innodb\_os\_log\_fsyncs | 91712 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 103616000 |  
| Innodb\_page\_size | 16384 |  
| Innodb\_pages\_created | 3232 |  
| Innodb\_pages\_read | 111459049 |  
| Innodb\_pages\_written | 106578 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 734 |  
| Innodb\_row\_lock\_time\_avg | 7 |  
| Innodb\_row\_lock\_time\_max | 77 |  
| Innodb\_row\_lock\_waits | 99 |  
| Innodb\_rows\_deleted | 0 |  
| Innodb\_rows\_inserted | 120665 |  
| Innodb\_rows\_read | 27997512861 |  
| Innodb\_rows\_updated | 81024 |  
| Key\_blocks\_not\_flushed | 0 |  
| Key\_blocks\_unused | 244225 |  
| Key\_blocks\_used | 93697 |  
| Key\_read\_requests | 72199803 |  
| Key\_reads | 532073 |  
| Key\_write\_requests | 2016215 |  
| Key\_writes | 1860529 |  
| Last\_query\_cost | 0.000000 |  
| Max\_used\_connections | 251 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 1070 |  
| Open\_streams | 0 |  
| Open\_tables | 879 |  
| Opened\_tables | 1293 |  
| Prepared\_stmt\_count | 0 |  
| Qcache\_free\_blocks | 4 |  
| Qcache\_free\_memory | 1023296 |  
| Qcache\_hits | 0 |  
| Qcache\_inserts | 10 |  
| Qcache\_lowmem\_prunes | 0 |  
| Qcache\_not\_cached | 4850590 |  
| Qcache\_queries\_in\_cache | 10 |  
| Qcache\_total\_blocks | 17 |  
| Questions | 8123575 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 11226 |  
| Select\_full\_range\_join | 0 |  
| Select\_range | 78562 |  
| Select\_range\_check | 0 |  
| Select\_scan | 2596597 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | OFF |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 2602764 |  
| Sort\_merge\_passes | 0 |  
| Sort\_range | 197715 |  
| Sort\_rows | 3356236 |  
| Sort\_scan | 210944 |  
| Table\_locks\_immediate | 5883022 |  
| Table\_locks\_waited | 153360 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 199 |  
| Threads\_connected | 52 |  
| Threads\_created | 251 |  
| Threads\_running | 23 |  
| Uptime | 63080 |  
| Uptime\_since\_flush\_status | 63080 |  
±----------------------------------±--------------+

I am logging slow queries, and the log file is about 600mb now, I am tailing it and it just scrolls and scrolls and scrolls with data…

Any help would be appreciated.

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [January 24, 2008, 12:45pm UTC](https://forums.percona.com/t/cpu-load-100/555/11 "2008-01-24T12:45:29Z")

</div>

OK, you might have indexes but are the queries written so that MySQL can use them?

This figure:

| [B]Quote:[/B] |
| 

| Handler\_read\_rnd\_next | 336080470215 |

 |

Indicates that you have seem to have a lot of table scans.

Test the queries in your slow query log with the EXPLAIN keyword in front of them and check the execution plan.  
If you want help to interpret the output from EXPLAIN you can post it here.

---

<div class="post-metadata">

**Author:** ![yumkoolaid](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@yumkoolaid](https://forums.percona.com/u/yumkoolaid)\
**Post date:** [January 24, 2008, 4:58pm UTC](https://forums.percona.com/t/cpu-load-100/555/12 "2008-01-24T16:58:29Z")

</div>

Alright, here’s one reply to EXPLAIN:

ID: 1 Select Type: SIMPLE Table: gu\_tracker\_query Type: ALL Rows: 363028 Extra: Using where

possible\_keys, keys, key\_len and ref are all null.

Thanks again for your time.