WordPress, 2Gb linode, OOM/MySQL woes
Here's where I'm at:
Apache logs have shown slight traffic spikes in the past before crashs (1-2 requests per second, possibly script kiddies -- same IP but different user-agent)
mysql tuner suggests a whole lot of improvements I imperfectly understand. I've tried some things and am waiting to see if it helps: innodbbufferpool_size=256M
tmptablesize = 64M
maxheaptable_size = 64M
table_cache=128M
querycachesize = 64M
databse is 100Mb, biggest table is now 15Mb (posts table, makes sense given how many articles I've written over the years)
I've reduced MaxClients to 10 and turned KeepAlive off in apache
I've added Cloudflare
Anybody else have this kind of issue? Ideas, hypothesis? Does anything I did strike you as useless?
Logs and stuff available, I'm just not sure what's useful.
6 Replies
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2M (Tables: 11)
[--] Data in InnoDB tables: 77M (Tables: 140)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 140
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9h 23m 3s (839K q [24.863 qps], 6K conn, TX: 4B, RX: 240M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 432.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 837.8M (42% of installed RAM)
[OK] Slow queries: 0% (0/839K)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 32.0M/4.2M
[OK] Key buffer hit rate: 99.9% (5M cached / 4K reads)
[OK] Query cache efficiency: 61.2% (439K cached / 717K selects)
[!!] Query cache prunes per day: 102993
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30K sorts)
[!!] Temporary tables created on disk: 47% (38K on disk / 81K total)
[OK] Thread cache hit rate: 99% (15 created / 6K connections)
[OK] Table cache hit rate: 63% (206 open / 325 opened)
[OK] Open file limit used: 7% (72/1K)
[OK] Table locks acquired immediately: 100% (341K immediate / 341K locks)
[OK] InnoDB data size / buffer pool: 77.1M/256.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
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_size (> 64M)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)</major@mhtx.net>
initial mysqltuner output (before I made any changes):
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.43-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2M (Tables: 11)
[--] Data in InnoDB tables: 75M (Tables: 140)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 140
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 14h 20m 2s (7M q [51.195 qps], 55K conn, TX: 37B, RX: 1B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 208.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 613.8M (30% of installed RAM)
[OK] Slow queries: 0% (0/7M)
[OK] Highest usage of available connections: 13% (20/151)
[OK] Key buffer size / total MyISAM indexes: 32.0M/4.2M
[OK] Key buffer hit rate: 99.9% (52M cached / 40K reads)
[OK] Query cache efficiency: 58.2% (3M cached / 6M selects)
[!!] Query cache prunes per day: 404687
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 271K sorts)
[!!] Temporary tables created on disk: 47% (323K on disk / 682K total)
[OK] Thread cache hit rate: 99% (48 created / 55K connections)
[!!] Table cache hit rate: 1% (64 open / 3K opened)
[OK] Open file limit used: 0% (9/1K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 75.8M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 64)</major@mhtx.net>
@stephtara:
current mysql tuner output:
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.46-0ubuntu0.12.04.2 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 2M (Tables: 11) [--] Data in InnoDB tables: 77M (Tables: 140) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 140 -------- Performance Metrics ------------------------------------------------- [--] Up for: 9h 23m 3s (839K q [24.863 qps], 6K conn, TX: 4B, RX: 240M) [--] Reads / Writes: 81% / 19% [--] Total buffers: 432.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 837.8M (42% of installed RAM) [OK] Slow queries: 0% (0/839K) [OK] Highest usage of available connections: 7% (11/151) [OK] Key buffer size / total MyISAM indexes: 32.0M/4.2M [OK] Key buffer hit rate: 99.9% (5M cached / 4K reads) [OK] Query cache efficiency: 61.2% (439K cached / 717K selects) [!!] Query cache prunes per day: 102993 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30K sorts) [!!] Temporary tables created on disk: 47% (38K on disk / 81K total) [OK] Thread cache hit rate: 99% (15 created / 6K connections) [OK] Table cache hit rate: 63% (206 open / 325 opened) [OK] Open file limit used: 7% (72/1K) [OK] Table locks acquired immediately: 100% (341K immediate / 341K locks) [OK] InnoDB data size / buffer pool: 77.1M/256.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries 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_size (> 64M) tmp_table_size (> 64M) max_heap_table_size (> 64M)</major@mhtx.net>initial mysqltuner output (before I made any changes):
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.43-0ubuntu0.12.04.1 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 2M (Tables: 11) [--] Data in InnoDB tables: 75M (Tables: 140) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 140 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 14h 20m 2s (7M q [51.195 qps], 55K conn, TX: 37B, RX: 1B) [--] Reads / Writes: 87% / 13% [--] Total buffers: 208.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 613.8M (30% of installed RAM) [OK] Slow queries: 0% (0/7M) [OK] Highest usage of available connections: 13% (20/151) [OK] Key buffer size / total MyISAM indexes: 32.0M/4.2M [OK] Key buffer hit rate: 99.9% (52M cached / 40K reads) [OK] Query cache efficiency: 58.2% (3M cached / 6M selects) [!!] Query cache prunes per day: 404687 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 271K sorts) [!!] Temporary tables created on disk: 47% (323K on disk / 682K total) [OK] Thread cache hit rate: 99% (48 created / 55K connections) [!!] Table cache hit rate: 1% (64 open / 3K opened) [OK] Open file limit used: 0% (9/1K) [OK] Table locks acquired immediately: 100% (3M immediate / 3M locks) [OK] InnoDB data size / buffer pool: 75.8M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 16M) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_cache (> 64)</major@mhtx.net>
I would try the following changes:
set max_connections to a much smaller number. The highest used from both reports is 20. I use 32.
reduce the size of innodbbufferpool_size. It was at 128 but the latest report had 256. Your total database size is currently 76MB.
set table_cache = 512
double the size of querycachesize
DS
@dasand:
I would try the following changes:
set max_connections to a much smaller number. The highest used from both reports is 20. I use 32.
reduce the size of innodbbufferpool_size. It was at 128 but the latest report had 256. Your total database size is currently 76MB.
set table_cache = 512
double the size of querycachesize
OK, done all that.
Database size in PhpMyAdmin is listed as 97.7MB.
Thanks, will let it run a bit like that and see how it goes!
If MySQL keeps getting OOM killed despite having reasonable settings, it is probably an innocent victim of greedy Apache gobbling up all the memory. The default settings for Apache with mod_php are horrendously inadequate for a 2GB Linode.
1) Calculate memory allocation expectation for MySQL and Apache
2) Constrain both
3) Switch to PHP-FPM and event MPM
4) Ensure opcache is enabled