… instance type with highest memory available, and memory is what really really matters. High-Memory Quadruple Extra Large …is the configuration that I 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…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
… edit: /etc/mysql/my.cnf Also make sure that innodb_buffer_pool_size is for performance reasons set to x4 of log file size… sure what the MySQLDB performance cracks like Baron recommend you to be on the safe / perfect side. innodb_log_file_size is always… 32M x 4 = 128M innodb_buffer_pool_size). According to my previous posts in this blog a dynamic innodb log file RECREATE did NOT…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
… problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch… user experience will suffer. So what you can do to prevent this problem from happening ? The answer is easy. Throttle the side… to where it does not overload the system. Unless it is really time critical process I would not use more than…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…its features might be helpful to you. So what‘s new and great in MariaDB as compared…the same or slightly different forms, so there is some subtlety to this list. For example, Percona…, improvements to partitioning, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5.5 might be…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… optimization that is used in conjunction with BKA and that is MRR Key-ordered Scan. Let’s see what this optimization actually is. Key…, 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… the workload is IO bound. For the purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
….5 Now let’s take a look at what this optimization actually is and what benefits it brings. Multi Range Read With traditional…, 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… the workload is IO bound. For the purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP… Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at. Index Condition Pushdown Traditional B…, where index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block, which can use like this: ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 132183490560; in additional pool… in this space allowing us to see directly for what purpose memory is allocated and so detect all kinds of memory usage…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…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 … Buffering – This is again very important for buffer pool size choice. You do not want OS to cache what Innodb is caching already. Innodb cache is more …
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
… 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… tune buffer pool invalidation strategy, for example play with innodb_old_blocks_pct and innodb_old_blocks_time actually observing data stored in buffer pool rather… these stats during warmup to see what is really getting warmed up first as well as how buffer pool is affected by batch jobs, alter…
