View All Products
Compute
Storage
Databases
Networking
Developer Tools
Delivery
Security
Services
Industries
Pricing
Community
Engage With Us
Community ▶ Questions ▶ WordPress, 2Gb linode, OOM/MySQL woes
Log in to Ask a Question

WordPress, 2Gb linode, OOM/MySQL woes

performance

Hello folks. Since at least this summer, my server has been killing MySQL (or it's crashing, I can't quite tell) regularly. It might run for a week or two without a problem, or will crash every day or so. I have wordpress multisite on it. 7-8 sites, barely any traffic except one – http://climbtothestars.org -- which gets a few hundred visitors a day. It's an old blog, 15 years of writing, a few thousand posts, 10k comments, a bunch of plugins.

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

Post your mysqltuner output in a code block.

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>

@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!

See this thread: https://forum.linode.com/viewtopic.php?f=12&t=12522

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.

Standard advice pack:

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

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Log in to Reply
Community Code of Conduct