… used: ## 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… see that the buffer pool is sized at 55G and I am using 4 buffer pool instances to reduce the contention caused by buffer pool mutexes. Another…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… to fill the B-tree pretty efficiently. With a 55GB buffer pool, we would expect the primary table to simply fit. But… RAM, Inno would hit a memory wall. Given that Memory sizes are perhaps 10 times bigger than they were 4 years…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
… Also make sure that innodb_buffer_pool_size is for performance reasons set to x4 of log file size. On my system I set… to be on the safe / perfect side. innodb_log_file_size is always a fixed parameter and it is quite difficult… (but e.g instead of 32M x 4 = 128M innodb_buffer_pool_size). According to my previous posts in this blog a dynamic…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
…: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full…: Restoring possible half-written data pages from the doublewrite InnoDB: buffer… 120502 11:51:16 InnoDB: Started; log sequence number 119…]# cat /etc/my.cnf | grep log_file # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M [root@tstlx1 mysql]#
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… 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… of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed up, so that it… values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool_reads are…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 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. Note that as mentioned at the start of the benchmark results section, the InnoDB dataset size is ~5G… of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed up, so that it…
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: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… block, which can use like this: ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 132183490560; in additional pool allocated 0 Internal hash tables (constant…) Threads 425080 (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 69 Comments
… = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… 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… is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough. You also… good estimates for this number. Eliminate Double Buffering – This is again very important for buffer pool size choice. You do not want OS to…
