GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL optimization request

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL optimization request

    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 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=64Mqu ery_cache_type=1max_user_connections=200max_connec tions=500interactive_timeout=10wait_timeout=20conn ect_timeout=20thread_cache_size=128key_buffer=128M join_buffer=1Mmax_connect_errors=20max_allowed_pac ket=16Mtable_cache=1024record_buffer=1Msort_buffer _size=2Mread_buffer_size=2Mread_rnd_buffer_size=2M thread_concurrency=8myisam_sort_buffer_size=64Mser ver-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!

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment

        Working...
        X