… remove performance_schema For the InnoDB tests, a 64GB buffer pool was used. O_DIRECT was used so, there was no caching of data…_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer…_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling symbolic-links is recommended to prevent…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… dataset to check server behavior when data fully fits buffer pool(size of buffer pool is 30GB). So basically this is a CPU-bound… benchmark: [mysqld] user=root port=3306 innodb_buffer_pool_size = 30G innodb_flush_method = O_DIRECT innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… speak for themselves. When the working set outstrips the available buffer pool memory but still fits into the cache device, vCache shines… innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method = O_DIRECT innodb_log_buffer_size…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M… To warmup server and load data and indices to the buffer pool we use following queries: select avg(id) from sbtest$i…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…:autoextend innodb_flush_method = O_DIRECT innodb_log_buffer_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_buffer_pool_size = 36G innodb_log… Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M…
Comment: Testing the Micron P320h
April 16, 2013 By 7 Comments
… with O_DIRECT the majority of the stuff what we expect file system to do is perform IO mapping from physical to direct… it. What I also find interesting is why at 25G buffer pool it provides the highest variance among different cards while on…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… needs for Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit… buffer pool than you specify for it – I just checked on our set of boxes which use 12GB buffer pool (with very small additional pool… Linux, FreeBSD, Solaris you need to set innodb_flush_method=O_DIRECT. On other Operating Systems you may be able to select…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… back to OS Wait. There is direct information about OS waits as well – you… i/o‘s: 0, sync i/o‘s: 0 Pending flushes (fsync) log: 0; buffer pool: 0…buffer pool for new data – when previous page content was not read to the buffer pool. Finally you can see buffer pool hit ratio which measures buffer pool…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… was performed with a buffer pool size of 1G, while benchmark on in-memory workload was performed with a buffer pool size of 6G… innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch… innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… optimization. I frequently see batch jobs killing server performance overtaking buffer pool. Though full table scan is only one of replacement policy… uses it’s own internal buffer pool but it’s in conflict with the Linux kernel’s buffer pool. In high load situations where…? HT: According to tests by Peter Zaitsev, O_DIRECT works well to remove double buffering. In distant future, I would like to have…
