… other session modifications on that table. This makes reads consistent and therefore makes the replication between servers consistent. If you execute SELECT…. How to troubleshoot gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table…possible…
Post: Copying InnoDB tables between servers
July 31, 2009 By 10 Comments
… ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup… regular InnoDB ® file, and exp is file with special export information. Both files should be copied to remote server. Now on remote server…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
… as with MyISAM tables when you just copy table.frm, table.MYD, table.MYI files, but so nice here – just can copy InnoDB tables in fully non-blocking mode, you do not need to shutdown neither source nor destination server (if destination server runs…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… big spike in io-wait on the server during the time I ran the Innodb Table monitor (it was just for a minute) and he was very glad we waited until after hours. The server itself… for each table, it calls dict_update_statistics and appears to force table statistics to be regenerated for each table It’s possible I…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
…Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use… statement and possibly result in different data than on master. Locking rows in the source table while …(it has to be done outside of MySQL Server) as otherwise the script will fail second time…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
… more important “theoretically possible” maximum it provides have nothing to do with real memory consumptions. In fact typical server with 8GB of… have very large memory consumption Innodb Table Cache. Innodb has its own table cache in which meta data about each table accessed from the start…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… dedicated server is: set innodb_flush_method=O_DIRECT in my.cnf, to prevent double buffering. Configure as much memory as possible to InnoDB…. Q37: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are… for Heikki – it should be possible to implement online reorganize which locks and reorganizes small portions of table online. Q38: Seems that…
Post: Real-Life Use Case for "Barracuda" InnoDB File Format
April 23, 2008 By 11 Comments
… recent posts Vadim already gave some information about possible benefits from using new InnoDB file format but in this post I’d… table customer has on this server is one huge innodb table with a set of TEXT fields. We’ve been trying to optimize this server before by playing with various innodb parameters…
