MySQL buffer_size recommendations

To figure out the right settings for your database server, you really need a good MySQL DBA to take a look at your application's resource utilization and make decisions based on hard data.

Preliminary assessment for our current set up - after yet another crash:

My guess would be that the mysqld service crashed while writing to the table. Being as mysql is using 6G in global buffers alone and then trying to use 300 threads I bet it just ran out of RAM and crashed:

sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 128M


Configured Max Per-thread Buffers : 131 G
Configured Max Global Buffers : 6 G
Configured Max Memory Limit : 138 G
Physical Memory : 7.78 G

I would recommend setting the per thread values to something reasonable to avoid outages:

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size=2M

Also:

Current key_buffer_size = 6 G
Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52
See Bug#5731, Bug#29419, Bug#29446

This particular table has frequent inserts and as such is locking frequently. If possible, consider converting this table to InnoDB. This is not possible if you are using fulltext or advisable if you use the COUNT() function against it a lot.

This was on a dedicated database server with 8GB RAM and RAID5.

This server had already been upgraded to RHE 64-bit...