Added some new code to Kontrollbase to allow you to view the cnf file on each host, as well as all of the global variables and global status information that was collected from the most recent polling period. Here are some screencaps.
Archive for category CNF
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.
MySQL and DNS woes?
Nov 4
A quick solution to a common problem: your MySQL server has thousands of connections, some or most are not persistent. Unless you say otherwise, MySQL will check connections hostnames against the system DNS server. While this is generally not a problem with low traffic, when you are dealing with many concurrent connections you are not only wasting cycles with DNS name resolve, you may also overwhelm or alert your DNS server/provider. The solution? Set skip-name-resolve in the my.cnf file and MySQL will run the connections off of their IP address instead of the DNS name that it resolves to.
