# MySQL optimization request **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 26, 2007, 5:30am UTC](https://forums.percona.com/t/mysql-optimization-request/467 "2007-09-26T05:30:55Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![ITDevil](https://avatars.discourse-cdn.com/v4/letter/i/85f322/32.png) [@ITDevil](https://forums.percona.com/u/ITDevil)\ **Post date:** [September 26, 2007, 5:30am UTC](https://forums.percona.com/t/mysql-optimization-request/467/1 "2007-09-26T05:30:55Z")
Server details CPU1: Intel(R) Xeon 2.80GHz ( Dual Core ) CPU2: Intel(R) Xeon 2.80GHz ( Dual Core ) 2 GB DDR ECC RAM 160 GB HDD (WD1600BB-98DWA0, ATA DISK drive) CentOS 5 cPanel/WHM 11 Apache 1.3.39 PHP 4.4.7 mySQL 4.1.22 uname: Linux [s1.xxxxxxxx.com](http://s1.xxxxxxxx.com) 2.6.18-8.1.8.el5 #1 SMP Tue Jul 10 06:50:22 EDT 2007 i686 i686 i386 GNU/Linux ulimit -aH core file size (blocks, -c) 1000000data seg size (kbytes, -d) unlimitedmax nice (-e) 0file size (blocks, -f) unlimitedpending signals (-i) 32767max locked memory (kbytes, -l) 32max memory size (kbytes, -m) unlimitedopen files (-n) 4096pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200max rt priority (-r) 0stack size (kbytes, -s) 8192cpu time (seconds, -t) unlimitedmax user processes (-u) 14335virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited mysqlreport [root@s1 ~]# ./mysqlreportMySQL 4.1.22-standard uptime 5 15:53:37 Wed Sep 26 03:58:29 2007\_\_ Key \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Buffer used 113.26M of 128.00M %Used: 88.49 Current 128.00M %Usage: 100.00Write hit 29.84%Read hit 99.48%** Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Total 19.34M 39.5/sSlow 2.06k 0.0/s %Total: 0.01 %DMS: 0.01DMS 14.01M 28.6/s 72.44** Table Locks \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Waited 448.89k 0.9/s %Total: 2.25Immediate 19.50M 39.9/s** Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Open 1024 of 1024 %Cache: 100.00Opened 156.37k 0.3/s** Connections \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Max used 174 of 500 %Max: 34.80Total 659.37k 1.3/s** Created Temp \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_Disk table 30.84k 0.1/sTable 135.76k 0.3/sFile 704 0.0/s my.cnf [mysqld]datadir=/var/lib/mysqltmpdir=/home/tmpmysqlskip-lockingskip-innodb#skip-networkingsafe-show-databasequery\_cache\_limit=1Mquery\_cache\_size=64Mquery\_cache\_type=1max\_user\_connections=200max\_connections=500interactive\_timeout=10wait\_timeout=20connect\_timeout=20thread\_cache\_size=128key\_buffer=128Mjoin\_buffer=1Mmax\_connect\_errors=20max\_allowed\_packet=16Mtable\_cache=1024record\_buffer=1Msort\_buffer\_size=2Mread\_buffer\_size=2Mread\_rnd\_buffer\_size=2M thread\_concurrency=8myisam\_sort\_buffer\_size=64Mserver-id=1collation-server=latin1\_general\_ci[mysql.server]user=mysqlbasedir=/var/lib[safe\_mysqld]err-log=/var/log/mysqld.logpid-file=/var/lib/mysql/mysql.pidopen\_files\_limit=8192[mysqldump]quickmax\_allowed\_packet=16M[mysql]no-auto-rehash#safe-updates[isamchk]key\_buffer=64Msort\_buffer=64Mread\_buffer=16Mwrite\_buffer=16M[myisamchk]key\_buffer=64Msort\_buffer=64Mread\_buffer=16Mwrite\_buffer=16M[mysqlhotcopy]interactive-timeout Thank You! ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [September 26, 2007, 9:50am UTC](https://forums.percona.com/t/mysql-optimization-request/467/2 "2007-09-26T09:50:30Z")
Do you have any specific that you are having a problem with? Because from the info you provided I can’t really see any problem. ---
**Author:** ![ITDevil](https://avatars.discourse-cdn.com/v4/letter/i/85f322/32.png) [@ITDevil](https://forums.percona.com/u/ITDevil)\ **Post date:** [September 27, 2007, 10:34am UTC](https://forums.percona.com/t/mysql-optimization-request/467/3 "2007-09-27T10:34:05Z")
mysql seems to be eating a lot CPU from 49 - 150% currently 10108 mysql 15 0 439m 270m 2608 S 89 13.3 25:36.71 mysqld and load on server is almost always 2.5 and randomly junps up 25+ any suggestions ---
**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\ **Post date:** [September 27, 2007, 6:05pm UTC](https://forums.percona.com/t/mysql-optimization-request/467/4 "2007-09-27T18:05:36Z")
Suggestions are pretty common in this case: 1. Configuration optimization 2. Queries/indexes optimization For configs optimization you can post (here onr on some pastie site) a results of the following commands: 1. mysql command: SHOW GLOBAL STATUS; 2. mysql command: SHOW GLOBAL VARIABLES; 3. mysql command: SHOW INNODB STATUS; 4. shell command: mysqladmin extended -i120 -r Notice: With the last command, wait for 2 minutes and copy’n’paste second portion of the output.