myisam_sort_buffer_size vs sort_buffer_size

Question

I am MySQL on server with 6GB RAM. I need to know what is the difference between myisam_sort_buffer_size and sort_buffer_size?

I have following size set to them:

myisam_sort_buffer_size = 8M

sort_buffer_size = 256M

Please also mention if these values are fine or need adjustments?

Thanks

Answer 1

sort_buffer_size:

MySQL documentation:

Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.

Your sort_buffer_size value seems extremely high. The default is 2M. I'd recommend going no larger than that since there is a performance penalty for going higher. Some people recommend smaller values such as 256kB. One thing to remember is this is per-client-session, it's not a global value. Large values will add up fast.

myisam_sort_buffer_size:

MySQL documentation:

The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

Your myisam_sort_buffer_size seems fine. This won't be relevant unless you are rebuilding indexes using ALTER TABLE or REPAIR TABLE etc.