tweaking mysql and php for one site

vivithemage

October 02, 2014 11:05

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:

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

mysqltuner:


 >>  MySQLTuner 1.3.0 - Major Hayden 
 >>  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)

Comments

2 comments

cPanelMichael

October 02, 2014 16:16
Hello :) Please let MySQL run for at least 24 hours before running the tuner to ensure more accurate results. Thank you.
0
vivithemage

October 02, 2014 16:22
I played around with some settings that the tuner advised, but still no difference:
[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

>> MySQLTuner 1.3.0 - Major Hayden >> 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)
[COLOR="silver">- - - 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.
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?