… ## 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_… the buffer pool is sized at 55G and I am using 4 buffer pool instances to reduce the contention caused by buffer pool mutexes…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
… to edit: /etc/mysql/my.cnf Also make sure that innodb_buffer_pool_size is for performance reasons set to x4 of log… be on the safe / perfect side. innodb_log_file_size is always a fixed parameter and it is quite difficult to extend… 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
… find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system…, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many cases… Many developers will test script with multiple level of concurrency and find out doing work from 32 processes is faster than…
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 loaded in the buffer pool… 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 done, and less amount…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, 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… purpose 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….6 and MariaDB 5.5 are reporting high numbers for Innodb_buffer_pool_read_ahead which shows that the access pattern was sequential and hence InnoDB…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at. Index Condition Pushdown Traditional B…`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity = 1 and l_quantity
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… 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 allocated… 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…
Comment: Performance problem with Innodb and DROP TABLE
March 15, 2012 By 23 Comments
… quesitons also have a pool english ^_^. according to your view, when you set innodb_table_per_file=1 and then you drop the…. so , why the pages associate with that tablespace in the buffer pool must be discarded too? in my opinion, when the table… that tablespace,then the useless pages in the buffer pool can be discared when buffer pool is full or other situation, not at the…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…/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_at_trx_commit = 1 innodb… (15 min 54.29 sec) Please help to improve performance and suggest if any config changes are to be made. Thanks
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 in memory fit for each index and…
