… (64GB) with Innodb Buffer pool configured to be just a bit over 20GB. Innodb recovery was taking long hours with reasonably sized Innodb log files (256M… page. So I took a shot at restarting MySQL with much smaller buffer pool size and we got it recovered much faster. Interesting…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…/tmp | | TABLE_OPEN_CACHE | 400 | 2000 | | INNODB_VERSION | 5.5.30 | 1.2.10 | | INNODB_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE | 0… intensive workload will do a lot better on MySQL 5.6 with default configuration back_log Minor increase 50 to 80 does… behaviors are not enabled by default. innodb_old_blocks_time now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
….5.28-29.1 on it with 6GB allocated for innodb_buffer_pool which with other memory needs leaves roughly 1GB for OS… 30GB in size including indexes which is way more than buffer pool size and OS cache which is available. I run CHECK… different read_ahead configuration. Percona Server still allows me to enable Random Read_Ahead which is not available in MySQL 5.5…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…creation and without it with different buffer pool sizes. Results are pretty interesting. I…size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB… in this case is the default configuration, second is with expand_fast_…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
… that server configuration itself really isn’t something you should expect to deliver huge wins. If MySQL is actually badly configured, you can… are not configuring the InnoDB buffer pool size or log file size, and not using InnoDB. If your server really hasn’t been configured — that is…
Post: How InnoDB handles REDO logging
February 3, 2011 By 19 Comments
… double write buffer to ensure consistent page writes. Why log? Since InnoDB tries to keep the working set in memory (InnoDB Buffer Pool), therefore the… complete. Both the MySQL configuration and hardware will affect the speed at which InnoDB can log. You can request that InnoDB force its REDO…
Post: MySQL Wish for 2013 - Better Memory Accounting
December 31, 2012 By 6 Comments
…scratch my head with system configured to consume only few GBs in global buffers growing to consume …, we know how much memory was allocated for Innodb Buffer Pool or Query Cache but amount of memory … it which I’m sure hosting providers, MySQL as a Service vendors will especially appreciate …
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… 5.5.11-20.2 with former being configured with innodb_buffer_pool_instances=16 and 400GB buffer pool. Here are results: First it is worth…,sub_select,do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…buffer pool and the other secondary indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_buffer_pool_stats\G *************************** 1. row *************************** POOL_ID: 0 POOL…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… completely fits in the Innodb Buffer Pool (512MB). We also have larger table 4GB which does not fit in the buffer pool. We’re running…” user experience. Summary As we can see in default configuration MySQL has buffer pool which can be easily washed away by large table scans…
