… A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. …innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log_buffer_size=32M innodb_buffer…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… optimization. Server Configuration: [mysqld] user=root port=3306 innodb_status_file=0 innodb_data_file_path=ibdata1:100M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…innodb options innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method = O_DIRECT innodb…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
… undo tablespace, 5.6-style buffer pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL… can spend a lot of time opening all the tablespaces. Optimization has been implemented and XtraBackup now avoids loading non-relevant…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
… undo tablespace, 5.6-style buffer pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL… can spend a lot of time opening all the tablespaces. Optimization has been implemented and XtraBackup now avoids loading non-relevant…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…: set innodb_flush_method=O_DIRECT in my.cnf, to prevent double buffering. Configure as much memory as possible to InnoDB‘s buffer pool… any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may last way too… innodb statusâ€. select * from table where id=5 show innodb status: Hash table size 10624987, used cells 1, node heap has 1 buffer…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a… needs for Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool… for other things and if you will not make Innodb to bypass OS buffering Innodb TableSpace IO will wipe out cache because it…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
…should be as good as it gets in terms of optimizations in this space. To keep test alligned to my … InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer… InnoDB: Doing…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… the join optimizations when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size… are the join optimizations when the workload is IO bound. For the purpose of benchmarking IO bound workload, the InnoDB buffer pool size… are 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…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 10 Comments
… (dirty) buffer pool pages are flushed down the to the tablespace. If Innodb stops (read: crashes) with dirty buffer pool pages, Innodb recovery must…, we have dirty buffers. These two numbers are relevant from the BUFFER POOL AND MEMORY section of SHOW ENGINE INNODB STATUS: Database pages… to optimize the Checkpoint age, it is flushing to try to optimize IO and to obey the LRU in the buffer pool. Since buffers…
