Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

myisam_sort_buffer_size vs sort_buffer_size

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

like image 882
D3 K Avatar asked Oct 24 '11 03:10

D3 K


1 Answers

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.

like image 108
thomasrutter Avatar answered Oct 16 '22 05:10

thomasrutter