Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

MySQL optimization request

ITDevilITDevil EntrantInactive User Role Beginner
Server details

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

Linux 2.6.18-8.1.8.el5 #1 SMP Tue Jul 10 06:50:22 EDT 2007 i686 i686 i386 GNU/Linux</pre>

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</pre>


[[email protected] ~]# ./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</pre>


[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</pre>

Thank You!


  • sterinsterin Mentor Inactive User Role Contributor
    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.
  • ITDevilITDevil Entrant Inactive User Role Beginner
    mysql seems to be eating a lot CPU

    from 49 - 150%


    10108 mysql 15 0 439m 270m 2608 S 89 13.3 25:36.71 mysqld</pre>

    and load on server is almost always 2.5 and randomly junps up 25+

    any suggestions
  • scoundrelscoundrel Contributor Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.