vivithemage

Member
Jul 2, 2009
12
0
51
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 <[email protected]>
 >>  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)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,228
463
Hello :)

Please let MySQL run for at least 24 hours before running the tuner to ensure more accurate results.

Thank you.
 

vivithemage

Member
Jul 2, 2009
12
0
51
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 <[email protected]>
 >>  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 - - -

Hello :)

Please let MySQL run for at least 24 hours before running the tuner to ensure more accurate results.

Thank you.
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.
 
Last edited: