… used different values for innodb_buffer_pool_size, like 13GB, 52GB, and 144GB, for testing the tpcc-mysql database with size 100G. This… SHOW STATUS variable innodb_buffer_pool_pages_LRU_flushed, which shows only pages flushed by the LRU list. So, let’s see what flushing we have using…
Post: Innodb Recovery - Is large buffer pool always better ?
July 17, 2007 By 3 Comments
… finding place to insert the page. So I took a shot at restarting MySQL with much smaller buffer pool size and we got it… bothering to flush anything (I only could see reads while recovery was going with large buffer pool) plus large distinct amount of pages modified so a lot of pages had to be added to the flush list. I…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… anything. On Linux, FreeBSD, Solaris you need to set innodb_flush_method=O_DIRECT. On other Operating Systems you may be… adjustments. You may want to make MySQL to use Large Pages for allocating Innodb Buffer Pool and few other buffers, which may have other performance… if some of buffer pool is swapped out, I would have one IO to fetch page if I would have small buffer pool and now…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 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…Run “FLUSH …Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… innodb_flush_method=O_DIRECT Test Setup: [root@localhost msb_ps_5_5_15]# sysbench –test=oltp –db-driver=mysql –mysql-host=localhost –mysql… ? To understand it we should take a look at the buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema…_sys_indexes as i using(table_id) inner join innodb_buffer_pool_pages_index as p using(index_id) where t.schema=’test…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… not need much introduction, especially to the MySQL server developers. The Valgrind Memcheck tool,…pool->stat are unprotected. These are buffer pool statistics counters: number of read, written, evicted, etc. pages….c:1472) ==9090== by 0x6B0E0E9: buf_flush_write_block_low (buf0flu.c:999) ==9090…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 9 Comments
…, enabling log_slave_updates, setting innodb_flush_log_at_trx_commit=1. Also …” change as increasing buffer pool size could cause performance problems. MySQL Restart MySQL Restarts may include …pages per second which is 1.6MB/sec which may take 24+hours to feel 200GB buffer pool. Automatic Buffer Pool…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
…_buffer_pool_size=16G innodb_log_file_size=1900M innodb_flush_log_at_trx_commit=2 innodb_doublewrite=1 innodb_flush_method=O_DIRECT innodb_max_dirty_pages_… to penalize further TPS. MySQL 5.6.2 Results Now let’s test MySQL 5.6.2. It …
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… pages age towards the tail of the LRU. The other good thing is that in Oracle MySQL‘s implementation the buffer pool mutex and flush list mutex are released after every 1024 pages being evicted. Both these…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
…setting innodb_flush_neighbor_pages=0 , to disable flushing of neighborhood pages (not available in MySQL 5.5.8). The problem with flushing neighborhood pages is … MySQL performance changes for the last workload if we set innodb_buffer_pool_instances=8 or 16. As you see, having several buffer pools…
