innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb
tables. Innodb tables are much more sensitive to buffer size compared to MyISAM.
MyISAM may work kind of OK with default key_buffer_size even with large data set but
it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both
data and index pages so you do not need to leave space for OS cache so values up to
70-80% of memory often make sense for Innodb only installations. Same rules as for
key_buffer apply – if you have small data set and it is not going to grow dramatically do
not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_additional_mem_pool_size This one does not really affect performance too
much, at least on OS with decent memory allocators. Still you might want to have it
20MB (sometimes larger) so you can see how much memory Innodb allocates for misc
needs.
innodb_log_file_size Very important for write intensive workloads especially for large
data sets. Larger sizes offer better performance but increase recovery times so be
careful. I normally use values 64M-512M depending on server size.
innodb_log_buffer_size Default for this one is kind of OK for many workloads with
medium write load and shorter transactions. If you have update activity spikes however
or work with blobs a lot you might want to increase it. Do not set it too high however
as it would be waste of memory – it is flushed every 1 sec anyway so you do not need
space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller
installations should use smaller values.
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than
MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each
update transaction commit (or each statement outside of transaction) will need to flush
log to the disk which is rather expensive, especially if you do not have Battery backed
up cache. Many applications, especially those moved from MyISAM tables are OK with
value 2 which means do not flush log to the disk but only flush it to OS cache. The log
is still flushed to the disk each second so you normally would not loose more than 1-
2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose
transactions even in case MySQL Server crashes. Value 2 only cause data loss with full
OS crash.
tables. Innodb tables are much more sensitive to buffer size compared to MyISAM.
MyISAM may work kind of OK with default key_buffer_size even with large data set but
it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both
data and index pages so you do not need to leave space for OS cache so values up to
70-80% of memory often make sense for Innodb only installations. Same rules as for
key_buffer apply – if you have small data set and it is not going to grow dramatically do
not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_additional_mem_pool_size This one does not really affect performance too
much, at least on OS with decent memory allocators. Still you might want to have it
20MB (sometimes larger) so you can see how much memory Innodb allocates for misc
needs.
innodb_log_file_size Very important for write intensive workloads especially for large
data sets. Larger sizes offer better performance but increase recovery times so be
careful. I normally use values 64M-512M depending on server size.
innodb_log_buffer_size Default for this one is kind of OK for many workloads with
medium write load and shorter transactions. If you have update activity spikes however
or work with blobs a lot you might want to increase it. Do not set it too high however
as it would be waste of memory – it is flushed every 1 sec anyway so you do not need
space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller
installations should use smaller values.
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than
MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each
update transaction commit (or each statement outside of transaction) will need to flush
log to the disk which is rather expensive, especially if you do not have Battery backed
up cache. Many applications, especially those moved from MyISAM tables are OK with
value 2 which means do not flush log to the disk but only flush it to OS cache. The log
is still flushed to the disk each second so you normally would not loose more than 1-
2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose
transactions even in case MySQL Server crashes. Value 2 only cause data loss with full
OS crash.
No comments:
Post a Comment