…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_adaptive_flushing_method = estimate 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. On my system… recommend you to be on the safe / perfect side. innodb_log_file_size is always a fixed parameter and it is quite… e.g instead of 32M x 4 = 128M innodb_buffer_pool_size). According to my previous posts in this blog a dynamic innodb log file RECREATE…
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… 120502….rpmnew [root@tstlx1 mysql]# 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
…MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5 MariaDB 5.5 w/ join_buffer_size=6M & mrr_buffer_size…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 of …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so… for Innodb_buffer_pool_read_ahead which shows that the access pattern was sequential and hence InnoDB decided to do read_ahead, while in MySQL…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL…(406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size…adjustments. You may want to make MySQL to use Large Pages for allocating Innodb Buffer Pool and few other buffers, which may have other …
Post: InnoDB memory allocation, ulimit, and OpenSUSE
August 23, 2010 By 7 Comments
… allocating another chunk that is in proportion to the setting of innodb_buffer_pool_size for the blocks array. The exact proportions will probably vary… the proper innodb_buffer_pool_size estimations when the system has a lot of RAM and you want to have the largest possible innodb_buffer_pool_size. Do not…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
… by other queries. MySQL Server does not provide any information of this type, Percona Server however adds number of tables to Information Schema… / index_size, 2) fit_pct FROM (SELECT index_id, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages…
