… search almost as efficient as a binary search can be performed. But InnoDB‘s adaptive hash indexes require that the prefix is… not have, then the only way to get good performance is to set innodb_flush_log_at_trx_commit=2, that is… denormalized database (for example, if ORDER and ORDERLINE tables are joined to form a single table), it might make sense for…
Comment: FusionIO 320GB MLC benchmarks
January 14, 2010 By 19 Comments
…’t have to be BCNF to do random I/O. InnoDB secondary indexes on a denormalized table can generate a lot… random I/O too. And while an SSD can improve join performance remember that all queries on MySQL are essentially single threaded… more performance. Also don’t forget that the optimizer may be forced to pick non-optimal join orders because of outer joins or…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… and good performance innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in… to use it in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics…thread is not runniing inside innodb kernel status could be “waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is …
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…5.5 and instead of expected performance improvement you see your performance being worse. What should you do … one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check …. You may consider disabling innodb_stats_on_metadata and increasing innodb-stats-sample-pages to get …
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index … which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…into the view query itself. This has significant performance advantages over TEMPTABLE views. A view which …, PRIMARY KEY (`c2`), KEY `c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2949071 DEFAULT CHARSET=latin1 1 … v2 as select c1, count(*) from v1 join params p on p.connection_id = connection_id…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…can see in this case the query self joins the table so we observe 2 table instances (…but add LOCK IN SHARE MODE so it performs locking reads to validate our theory: —TRANSACTION 12305,… never will run into problems with Innodb table level locks because innodb will only set intentional level locks…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io_threads = 16 join_buffer… next step, read detailed investigation what is the reason of performance drop in next post. And last one workload I tried…
Post: Looking for InnoDB/XtraDB hacker
October 25, 2011 By 3 Comments
… more performance fixes! If you are experienced software engineer and feel strong enough to hack InnoDB/XtraDB code we invite you to join our development team to work on the following (but not limited to) problems: Fix InnoDB…-biggest-innodb-problems/ Make some InnoDB tables non-transactional 
Make InnoDB page size setting per tables and per index Specify location of InnoDB tables…
