I saw something interesting today when helping out someone on the #mysql IRC channel. It was a cnf file that was designed to destroy a server. Before I get into the why-not, here are the goods:
...snip...
read_buffer = 128M
join_buffer = 128M
key_buffer = 512M
max_allowed_packet = 200M
thread_stack = 192K
thread_concurrency = 8
thread_cache_size = 64
query_cache_limit = 256M
query_cache_size = 256M
table_cache = 8192
query_cache_type = 1
sort_buffer = 128M
record_buffer = 128M
myisam_sort_buffer_size = 128M
thread_cache = 64
max_user_connections = 500
wait_timeout = 200
max_connections = 4096
tmp_table_size = 1000M
max_heap_table_size = 1000M
...snip...
Now, you may ask why these settings are bad. I will tell you. First, an equation for calculating per-thread buffer memory usage.
total per-thread buffer usage = (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections
So in this case we have the following memory usage in per-thread buffers. Any buffers or settings not specifically mentioned above take the MySQL defaults.
1537GB = ( 128M + 256K + 128M + 192K + 128M + 32K) * 4096
Yes, you read that correctly. This server is setup to allow 1537GB of ram usage on the per-thread buffer level. This server only has 4GB of ram. You tell me what would happen if 4096 connections were made and all of them, or even just a few of them, tried to max out their available buffer space. So folks, remember to always check your per-thread variables and make sure you’re not over allocating your memory resources.
Social tagging: buffers > mysql > tuning
Hi Matt.
Actually, you can overcommit memory with read_buffer_size and sort_buffer_size and usually have no problems. I have worked on large systems with physical RAM of 16 GB and formula = 64 GB just fine. And
those are MyISAM-specific.
Also, not sure if read_buffer and read_buffer_size are the same. I believe they are not.
Also see this blog post:
http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
James.
Yes, you *can* overcommit memory and have things be fine up until that memory is actually addressed and the machine starts to swap to disk. That’s when things get dirty. If you need to allocate that much ram to your buffers you’re better off installing 64GB of ram. Overcommitting memory is never a good idea.
If you need to commit that much memory to a buffer, do it in the query
That’s funny a funny cnf.