Received a support email from my server support and they made some adjustments. I'm a little concerned about the max connecctions going from 500 down to 64. Is this reasonable? Seems a bit drastic
“A member of our monitoring team found your server in an unresponsive state today, and rebooted it. The following actions were taken to prevent this from happening again:
-Decreased mysql max_connections from 500 to 64;
-Added mysql max_heap_table_size of 64M;
-Added mysql tmp_table_size of 64M;
-Added mysql interactive_timeout of 300.”
Thanks for any insight.
Effectively the 65th (and subsequent) concurrent connection to the MySQL database will fail. If you have 64 or more concurrent connections - which can be made by server processes and user interaction, you are going to run into problems. If you send out a mass mail or your site(s) get featured on a popular website which in turns sends a lot of traffic in a short space of time to your site, you may find users will receive errors because a connection could not be made to the database. If I remember correctly the default is somewhere around 151. Lowering the max_connections value will lower the memory consumption, but 64 is too low imo.
Thank you. I had them set it to 256.
If you chanage the 'max_connections' value then other MySQL settings have to be changed/optimized as well. Here is an example with 'max_connections=150':
max_connections=150
key_buffer_size=240M
join_buffer_size=512K
query_cache_type=1
query_cache_size=96M
query_cache_limit=90M
query_cache_min_res_unit=4K
is that config right?
key_buffer_size = 256M
join_buffer_size = 128M
read_buffer_size = 128M
sort_buffer_size = 128M
myisam_sort_buffer_size = 256M
read_rnd_buffer_size = 16M
max_tmp_tables = 128
tmp_table_size = 800M
max_connections = 300
thread_stack = 128K
thread_cache_size = 8
thread_concurrency = 4
query_cache_limit = 256M
query_cache_size = 512M
net_read_timeout = 600
net_write_timeout = 600