… the way, a good practice to avoid this is always naming your keys with a consistent pattern. This new behavior was introduced… creation of the duplicate index. Does it mean that tools like pt-duplicate-key-checker will not be necessary for MySQL 5.6? Let… includes mysqlindexcheck, similar to pt-duplicate-key-checker, but it does not detect all cases. For example: mysql> alter table test add index…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…:3307/slow | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –dispatcher-plugin… statements that match no rows or insert statements that have duplicate key errors may be faster than an actual database write. The…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add index key_for_first_name(name); ERROR 1061 (42000): Duplicate key name ‘key_for_first_name…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key idx_i(i); ERROR 1061 (42000): Duplicate key name ‘idx_i’ Adding indexes without name specified is very common reason why systems tend to get duplicate indexes. BTW make…
Post: How Does Semisynchronous MySQL Replication Work?
January 19, 2012 By 24 Comments
…thing to understand is that despite the name, semi-synchronous replication is still asynchronous. … replicas have also committed successfully. In MySQL‘s semi-synchronous replication, the commit …can apply and commit it successfully themselves (duplicate key error, anyone?). If any of these problems…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…duplicate index ? This is when table has multiple indexes defined on the same columns. Sometimes it is indexes with different names… wrong and hurts MySQL Performance. It is enough to create PRIMARY KEY and it will …delete. Duplicate keys are bad so once you find them get rid of them. Note: Duplicate …
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…KEY. The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY…KEY is always going to be considered as being defined first. So, MySQL…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…, like this: +———————————–+———————-+———————-+ | Variable_name | Value | Value | +———————————–+———————-+———————-+ | Aborted_clients | 205174… in the mysql.* tables, running mk-duplicate-key-checker …
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… new value to the column. Prior to MySQL 5.1.22 InnoDB used a method … a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on the auto_increment column… NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…affected (0.57 sec) Records: 2 Duplicates: 0 Warnings: 0 session1 > select *…> show status like ‘Open%tables’; +—————+——-+ | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 0…null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the…
