Forum Moderators: open
I know that the most important formula to respect when tunning a MySQL server is:
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = total_memory
I know total_memory and that key_buffer_size should be 1/4 - 1/3 of the total_memory
But how about read_buffer_size, sort_buffer_size and max_connections, how do I actually know what size to set them. Is there a diagnostic tool that can tell me the peaks, medium, etc values that my server is actually using for each of them? Or where to start, what parameters to take into consideration when determining the sizes of these variables
This is my current configuration for a 8G of RAM server and I keep running out of RAM and I have no idea where the problem is
set-variable = max_connections=3096
set-variable = max_allowed_packet=15M
key_buffer_size = 1024M
table_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 3M
thread_cache = 32
thread_concurrency = 16
open-files-limit= 261424
set-variable = thread_stack=512k
set-variable = query_cache_size=128M
set-variable = wait_timeout=120
set-variable = interactive_timeout=60
set-variable = max_connect_errors=999999
thanks
Have a flip through [ibm.com...]
It goes over most of the common settings, and how to determine the correct value.
BTW, you are on a 64 bit machine, right? When you say you're "running out of ram", how do you know this?
Sean
The total size of my indexes is 63M. So, should I increase key_buffer from 16M to 64M?
To my shame I have no idea if my machine is a 64 bit one. And I say I'm running out of RAM because I get the following error a few times a day ( and when it happens my server blocks for up to an hour ):
"Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug"
