…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…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to …
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: buffer… … grep log_file # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M [root@tstlx1 mysql]#
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… Handler_read_rnd_next 136077 136094 136366 136163 136435 Innodb_buffer_pool_read_ahead 0 20920 23669 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads 120548 102948 76882 102672 76832 Innodb_data_read 1…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Innodb_buffer_pool_read_ahead 1152 23231 130919 23228 130731 131497 Innodb_buffer_pool_read_requests 12947073 10228611 7816154 10251697 9319281 9393396 Innodb_buffer_pool_reads 327963 332092 12889 335080 14067 13384 Innodb… Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool_reads are done, and less amount of data is read…
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 …
Comment: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 23, 2012 By 10 Comments
… it would be to use the same random dives trick Innodb uses – You can do 100 partial random dives in the… page in the buffer pool or you find one of the pages on your path is not in the buffer pool and so you stop just short of reading it. This would get you approximate…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description. Innodb Buffer Pool is… other 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…, or relay logs, Innodb Transactional Logs also like to be cached otherwise OS will need to do reads to serve writes to…
Post: XtraDB feature: save / restore buffer pool
January 20, 2010 By 19 Comments
…, the release-making feature is ability to save and restore InnoDB buffer pool. The idea is not new and was originally developed by… of buffer pool ? There are several reasons. First, it’s not rate on modern servers to have 32GB+ of RAM, with allocated InnoDB buffer_pool…_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/; it will create/read file ib_lru_dump from your database directory. You may…
Post: Innodb Recovery - Is large buffer pool always better ?
July 17, 2007 By 3 Comments
… (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…grow unrestricted without Innodb bothering to flush anything (I only could see reads while recovery was going with large buffer pool) plus large distinct…
