Setting the values for read_buffer_size, sort_buffer_size

MySQL & MariaDB Other MySQL® Questions
1

Hello,

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 30%-40% 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

And what are the definitions for read_buffer_size, sort_buffer_size. ( What do these variables represent ? )

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

2

Hi

I use this tool:
[URL]http://rackerhacker.com/mysqltuner/[/URL]

It will give you suggestions based on the mysql stats variables. I know very little about MySQL so I take those suggestions and search for the variables and then read what people are saying about them. That way you know if those changes will help in your situation.

As for always using up RAM. I wasn’t aware of this till recently, but apparently linux will use all the ram it can for caching. If you type “free -m” in SSH, it’ll show you the buffers/cache usage. This can be used by apps and such, so it isn’t like 100% used. But I suppose if it found a use for it, it wouldn’t hurt to let it keep using it =P