…innodb_old_blocks_time now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… in cache, for example being washed away by other queries. MySQL Server does not provide any information of this type, Percona… / index_size, 2) fit_pct FROM (SELECT index_id, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb_sys_indexes ON id = index_id JOIN innodb_sys_tables ON table_id = innodb…
Post: Effect from innodb log block size 4096 bytes
January 3, 2011 By 19 Comments
… tpcc-mysql benchmark with 500W using Percona Server 5.5.8. Here is relevant part of config what I used innodb_buffer_pool_size=26G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb…
Post: Should MySQL update the default innodb_log_file_size?
November 21, 2011 By 37 Comments
… InnoDB is the default storage engine in MySQL, is it time to update the default configuration for the InnoDB log file size (innodb_log_file_size… installation. MySQL 5.5 increased the default buffer pool size to something more sane (128MB instead of 8MB), but the log file size remains at…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
…’t something you should expect to deliver huge wins. If MySQL is actually badly configured, you can hurt its performance significantly… ones I see are not configuring the InnoDB buffer pool size or log file size, and not using InnoDB. If your server really hasn’t been…, I sometimes see unlikely settings, such as making the sort buffer size much too large (a gigabyte comes to mind). As a…
Post: How Much memory do you use to run MySQL
November 2, 2012 By 8 Comments
… – these range from problems with large size Query Cache to bad drop table performance with large Innodb Buffer Pool size. As such I wonder how much memory do we really use to run MySQL Server these days…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
… MySQL 5.1 this is done by the master InnoDB thread. Since then, InnoDB has been moving towards the parallelized purge: in MySQL 5… settings were innodb_buffer_pool_size=16G innodb_log_file_size=1900M innodb_flush_log_at_trx_commit=2 innodb_doublewrite=1 innodb_flush_method=O_DIRECT innodb_max_dirty…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…) Here are the contents of the buffer pool in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index… | | INNODB_PAGES_READ | 191570 | | INNODB_ROWS_READ | 910844 | +—————————————+—————-+ 10 rows in set (0.01 sec) mysql> select sq.*, pages / ((@@innodb_buffer_pool_size / 16384)) * 100 pct_buffer_pool from…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool… MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5 MariaDB 5.5 w/ join_buffer_size=6M & mrr_buffer_size…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool… for Innodb_buffer_pool_read_ahead which shows that the access pattern was sequential and hence InnoDB decided to do read_ahead, while in MySQL…
