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...

Limitations of MySQL on 32-bit OS -- RedHat Linux Enterprise

Most developers will never run into this situation. But if you're running a Linux server RHE 32-bit OS and have a large MySQL database, watch out!

It took a few days of crashing this database with a table that has over 14GB of data.

The index on that table had grown over 2GB and was causing MySQL server to page the data (writing temp data to the hard drive instead of RAM). Disk I/O is thousands of times slower than RAM, and being over the MySQL limit on the 32-bit OS was just a recipe for disaster.

Oh, and also, at first the techs determined that our MySQL temp directory was full... so that was another major crash.

The solution included:
- upgrade the OS to 64-bit RHE
- change the storage engine to InnoDB
- get bigger drives
- set up the drives in a RAID 10 configuration
- optimize the various indexes that are used in JOIN statements...
- make sure the temp directory partition will have plenty of room

Of course that's just the beginning -- in the right direction.

-j