…the best performance. So I decided to look at yet another similar variable – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does not have to keep open file…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background… when you are using innodb_file_per_table. Implementation Before going into showing how Lazy Drop Table and the new DROP Table in MySQL >= 5.5… The new DROP TABLE related fixes in MySQL >= 5.5.23 have certainly reduced the performance hit, and the performance dips are not…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer. innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace…
Post: Impact of logging on MySQL’s performance
February 10, 2009 By 8 Comments
…immediately a question arises how much logging impacts on performance. When we do performance audit often we log every query to find…max_connections=3000 innodb_log_file_size=128M innodb_flush_log_at_trx_commit=1 innodb_file_per_table table_cache=2000 Depending on workload different InnoDB buffer was…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
… innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb… next step, read detailed investigation what is the reason of performance drop in next post. And last one workload I tried…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from… free space (or specified max size for ibdata1 file). “Good” thing is performance typically starts to suffer terribly well before that and…
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 20 Comments
… scan performance even if table is not logically fragmented and Innodb is reading data in large blocks – this can happen in case Innodb table file is… the same – OPTIMIZE TABLE tbl – this command recreates the table by writing the new .ibd file (if you’re using innodb_file_per_table=1) which normally…
Post: Effect from innodb log block size 4096 bytes
January 3, 2011 By 19 Comments
… used innodb-log-block-size=4096 in Percona Server to get better throughput, but later Dimitri in his article MySQL Performance: Analyzing…:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=4G innodb_adaptive…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | INNODB_PURGE_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE….6 innodb_file_per_table is now ON by default. This is very big change and is good one. We have been running innodb_file_per_table=1…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
….24 Configuration innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size=512M innodb_log_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method… Join would perform better than Hash Join when you are joining two tables on a PK column such that both tables are read…
