MySQL optimization request

Server details

CPU1: Intel® Xeon 2.80GHz ( Dual Core )
CPU2: Intel® 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 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!

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.

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

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.