mercredi 21 décembre 2011

Fine tuning read_rnd_buffer_size and read_buffer_size

Those variables are  easy to tune.

The evidence popup here between 128 and 256K for in memory workload.

My SQL SQL layer is optimistic and think that data will come from the storage engine at the speed of the memory bus , but in practice for IO bound workload and range scan in the table order it could not be the case , for MyISAM when reading from tables on disk, IOs are aligned on the read buffer so it may be of an intrest to increase it here.

In myisam.cc my_default_record_cache_size is set to  global_system_variables.read_buff_size;

To show IO size :
strace -p id_mysqld -e read -e write

If  monitoring smaller IO  request size from iostat  -xm, it may be that you are suffering fragmentation that does not help the FS cache to serve you efficiently. Optimize table ,Alter table order is a great help in MyISAM

In InnoDB the difference come with prefetch trigger when no fragmentation. It is the case when rows are inserted in increasing primary key order. So conservative read and rnd buffer values will speed up  the data move from the buffer_pool to the SQL thread with almost no impact on the disk speed rate .

Note that the read_rnd_buff will size the thread record cache when rr_sequential and rr_quick in case of full scan and some range scan

In record.cc

static int init_rr_cache(THD *thd, READ_RECORD *info)
 info->cache_records= (thd->variables.read_rnd_buff_size /
                        (info->reclength+info->struct_length));
 

Aucun commentaire:

Enregistrer un commentaire