The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

tweaking mysql and php for one site

Discussion in 'Workarounds and Optimization' started by vivithemage, Oct 2, 2014.

  1. vivithemage

    vivithemage Member

    Joined:
    Jul 2, 2009
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    I have a VPS with 1 site, wordpress. I keep seeing oodles of PHP processes and a heavy memory/cpu mysqld. I was wondering if anyone could help me tweak my mysql and php for wordpress? The site is really slow, and takes a pretty long time to load! When she maxes out on memory, it never loads.

    my.cnf:

    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=23430
    mysqltuner:

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.17
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 64M (Tables: 160)
    [--] Data in InnoDB tables: 1M (Tables: 117)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2m 46s (19K q [116.127 qps], 38 conn, TX: 113M, RX: 10M)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
    [OK] Maximum possible memory usage: 338.9M (11% of installed RAM)
    [OK] Slow queries: 0% (0/19K)
    [OK] Highest usage of available connections: 6% (10/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/4.9M
    [OK] Key buffer hit rate: 99.9% (1M cached / 1K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 7% (25 temp sorts / 347 sorts)
    [!!] Temporary tables created on disk: 43% (76 on disk / 174 total)
    [OK] Thread cache hit rate: 73% (10 created / 38 connections)
    [OK] Table cache hit rate: 91% (80 open / 87 opened)
    [OK] Open file limit used: 0% (51/23K)
    [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
    [OK] InnoDB buffer pool / data size: 128.0M/1.9M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_type (=1)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. vivithemage

    vivithemage Member

    Joined:
    Jul 2, 2009
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    I played around with some settings that the tuner advised, but still no difference:

    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=23430
    
    
    query_cache_type=1
    tmp_table_size=128M
    max_heap_table_size=64M
    query_cache_limit=128M
    query_cache_size=32M
    thread_cache_size=1024
    sort_buffer_size=2M
    read_rnd_buffer_size=2M
    

    Code:
    
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.17
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 64M (Tables: 160)
    [--] Data in InnoDB tables: 1M (Tables: 117)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 22s (3K q [175.864 qps], 16 conn, TX: 4M, RX: 2M)
    [--] Reads / Writes: 66% / 34%
    [--] Total buffers: 248.0M global + 4.6M per thread (151 max threads)
    [OK] Maximum possible memory usage: 946.4M (33% of installed RAM)
    [OK] Slow queries: 0% (0/3K)
    [OK] Highest usage of available connections: 6% (10/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/4.9M
    [OK] Key buffer hit rate: 97.9% (26K cached / 544 reads)
    [!!] Query cache efficiency: 0.0% (0 cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)
    [!!] Temporary tables created on disk: 33% (2 on disk / 6 total)
    [!!] Thread cache hit rate: 37% (10 created / 16 connections)
    [OK] Table cache hit rate: 90% (65 open / 72 opened)
    [OK] Open file limit used: 0% (26/23K)
    [OK] Table locks acquired immediately: 100% (86 immediate / 86 locks)
    [OK] InnoDB buffer pool / data size: 128.0M/1.9M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_limit (> 128M, or use smaller result sets)
        tmp_table_size (> 128M)
        max_heap_table_size (> 64M)
        thread_cache_size (> 1024)
    
    - - - Updated - - -

    I hear ya, the only problem is the system is getting thrashed!

    top - 12:41:42 up 49 min, 1 user, load average: 25.45, 16.35, 9.46
    Tasks: 141 total, 28 running, 113 sleeping, 0 stopped, 0 zombie
    Cpu(s): 97.7%us, 2.0%sy, 0.3%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
    Mem: 2932184k total, 2859912k used, 72272k free, 11664k buffers
    Swap: 2097148k total, 71160k used, 2025988k free, 129376k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    8771 mysql 20 0 1366m 490m 7384 S 4.6 17.1 0:03.54 mysqld
    8167 silverto 20 0 280m 87m 8604 R 4.3 3.1 0:10.00 php
    8141 silverto 20 0 288m 92m 8880 R 4.0 3.2 0:13.96 php
    8193 silverto 20 0 280m 86m 8884 R 4.0 3.0 0:09.18 php
    8194 silverto 20 0 286m 90m 8880 R 4.0 3.2 0:09.15 php
    8266 silverto 20 0 269m 76m 8596 R 4.0 2.7 0:08.65 php
    8140 silverto 20 0 283m 88m 8880 R 3.6 3.1 0:13.99 php
    8151 silverto 20 0 282m 87m 8880 R 3.6 3.1 0:11.92 php
    8153 silverto 20 0 285m 89m 8908 R 3.6 3.1 0:11.84 php
    8155 silverto 20 0 283m 88m 8884 R 3.6 3.1 0:11.81 php
    8163 silverto 20 0 284m 88m 8884 R 3.6 3.1 0:10.36 php
    8165 silverto 20 0 284m 89m 8884 R 3.6 3.1 0:10.27 php
    8171 silverto 20 0 284m 89m 8880 R 3.6 3.1 0:09.87 php
    8201 silverto 20 0 283m 88m 8880 R 3.6 3.1 0:08.96 php
    8299 silverto 20 0 263m 71m 8608 R 3.6 2.5 0:08.22 php


    loads of php processes.
     
    #3 vivithemage, Oct 2, 2014
    Last edited: Oct 2, 2014
Loading...

Share This Page