MySql performance optimization is an interesting task, I like to focus on it on my free time.
Please note that these are house rules. There is no guarantee that your performance will improve. Use with precautions!
MySQL key_buffer_size
An important variable setting is key_buffer_size; a good value is the 20% of total RAM.
Here it is a sample:
key_buffer_size = 820M
MySQL max_connections recommended value
Also, the max_connections variable could be safely lowered from its default value. Take a look at your Max used connections status variable; you can safely set the max_connections to Max used connections x 5, with a minimum of 40.
Sample:
max_connections = 85
MySQL tmp_table_size and max_heap_table_size
Finally, it’s important to take a look to tmp_table_size and max_heap_table_size. You can get a good value for them by dividing the available RAM by the max_connections variable.
Sample:
# Total RAM: 4GB; Mysql supposed available RAM: 3GB; Max_connections: 85;
# Good Value = 3GB / 80 = 35MB
tmp_table_size = 35M
max_heap_table_size = 35M
> OK, now I see, nothing to do with this bug. You simply ask too much. The > approximate formula is table_cache*2 max_connections. So 1536*2 500 > 2048, > for 500 connections maximal table_cache is 700 something. Ok in “Mysql Administrator” under the “Connection health” tab. The MAX my connections have ever gone to is just over 100 users. At vbulletin.com under their server tune forum they recommended a value of 600 max_connections as they said it will set a better table_cache value for better performance. My question: Will setting my max_connections to 200 or 300 (resulting in larger table_cache) improve performance? Or should I leave it at 500 max connections? Thanks, you’ve been a big help!
Hi Melisa,
lowering max_connections won’t improve performance itself, it just lets you increase the memory available for each connection (“tmp_table_size” and “max_heap_table_size” values, also “table_cache” can be tuned), without worrying about running out of RAM. Cheers!