…InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
…! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: …etc/my.cnf | grep log_file # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M [root@tstlx1 mysql…
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
… out my insert still be blocked. The SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207…, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929,…LOCK TO BE GRANTED: RECORD LOCKS space id 57 page no 3 n bits 72 index `GEN_CLUST_INDEX`…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already….2G 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read… values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size ~95G… 76832 Innodb_data_read 1.84G 1.89G 1.53G 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 77213 Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections … Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages 23419 …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock… locks rec but not gap RECORD LOCKS space id 19 page no 4 n bits 80 index `age` of table `test… need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except…
Post: InnoDB page sizes: plans and ideas
April 21, 2011 By 8 Comments
It is well known fact that InnoDB standard page size is 16K or 16384 bytes. Sometime ago we added feature to Percona Server to change that to 4K or 8K (innodb_page_size… area for improvements: support 32K and 64K pages. Why this is needed ? For InnoDB compression. Right now if you use 16K and…
Post: InnoDB page size
June 4, 2006 By 3 Comments
As maybe you know InnoDB uses hard page size 16Kb for datafiles and for buffer pool. However this size can be changed if you… of UNIV_PAGE_SIZE: */ #define UNIV_PAGE_SIZE_SHIFT 14 UNIV_PAGE_SIZE is page size (as you see – default value 16Kb). Possible values for UNIV_PAGE_SIZE is 8K… UNIV_PAGE_SIZE_SHIFT (according comment it must be 2-logarithm of UNIV_PAGE_SIZE). For pagesize 8K – UNIV_PAGE_SIZE_SHIFT=13, for 32K – UNIV_PAGE_SIZE…
