MySQL: How do I get my “Maximum possible memory usage” down?

Question

I've recently been having problems with thrashing as a result of running out of memory. (My VPS has 256M total)

I'm trying to tune MySQL using mysqltuner.pl, and get the following results:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 114M (Tables: 454)
[!!] Total fragmented tables: 34

-------- Performance Metrics -------------------------------------------------
[--] Up for: 40s (570 q [14.250 qps], 23 conn, TX: 154K, RX: 23K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 338.0M global + 2.7M per thread (20 max threads)
[!!] Maximum possible memory usage: 392.9M (153% of installed RAM)
[OK] Slow queries: 0% (5/570)
[OK] Highest usage of available connections: 15% (3/20)
[!!] Key buffer size / total MyISAM indexes: 8.0M/9.4M
[!!] Key buffer hit rate: 57.1% (7 cached / 3 reads)
[OK] Query cache efficiency: 21.9% (7 cached / 32 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 32 total)
[OK] Thread cache hit rate: 86% (3 created / 23 connections)
[OK] Table cache hit rate: 26% (128 open / 484 opened)
[OK] Open file limit used: 25% (259/1K)
[OK] Table locks acquired immediately: 100% (492 immediate / 492 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    key_buffer_size (> 9.4M)

But I'm a bit confused on how to get the maximum memory usage down? It seems to be based on key_buffer and max_connections, but there must be something else involved too?

my.cnf:

key_buffer              = 8M
max_allowed_packet      = 12M
thread_stack            = 128K
thread_cache_size       = 8
max_connections         = 20
table_cache             = 128
tmp_table_size          = 256M
max_heap_table_size     = 256M
join_buffer_size        = 256K
query_cache_limit       = 8M
query_cache_size        = 64M

I've been trying to read through MySQL tuning articles, but they seem geared toward people who already know what they're doing! Any help would be appreciated. Thanks!

Answer 1

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

Answer 2

I'm not a MySQL guru and I can't diagnose the problem with this information, but I tried searching for the formula in the source code. Here it is:

server_buffers + total_per_thread_buffers * max_connections

Where:

server_buffers = key_buffer_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size

and:

total_per_thread_buffers = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

Now you have to check each of these values and figure out which one is responsible for this huge number. And don't trust this script unreservedly - I tried running it on one of my DB servers and it calculated that the maximum memory is 140% of the total amount of physical memory, but the system has been running for years without any stability issues.

Good luck!

Answer 3

If I remember correctly, MySQL Tuner uses the following formula to estimate the maximum usage:

read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

Keep in mind that this is not 100% correct, and is in fact just an estimation, as certain settings in MySQL have no defined limit.

You can start toning down some of the settings in your config file and running the tuner again, but I'd advise getting an expert's help if you don't have time to waste changing my.cnf, restarting it and running the tuner.