… error code to tell you why operation could not be performed (read error, out of file descriptors etc). In fact you… until you run perror: [pz@sl1 ~]$ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed What is also interesting is… to our foreign keys example it is not only you need to run perror to find out this error is foreign key related but…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… from the fact how MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases… KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…your transactions affecting the concurrency and the performance you can disable them in two different … Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important …1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default …
Post: Clustrix benchmarks under tpcc-mysql workload
October 26, 2011 By 9 Comments
… Clustrix product, and this is the report on performance characteristics of Clustrix under tpcc-mysql workload. I tested tpcc 5000W (~500GB of… multi-statements transactions and foreign key relations. For a workload with a small number of threads, Clustrix does not perform as well as…
Post: Announcing Percona XtraDB Cluster 5.5.27-23.6
September 7, 2012 By Leave a Comment
… can now be adjusted for better performance. Percona XtraDB Cluster has implemented ability to have a MySQL Utility user who has system… user schema. This feature is especially useful to those operating MySQL As A Service. New Expanded Program Option Modifiers have been… under high rate SQL write load. Bug fixed #1026181. Cascading foreign key constraint could cause a slave crash. Bug fixed #1013978. Fixed…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… you’re scaling out you can get better performance by using couple of MySQL Servers on single node. You can make them… a foreign key not reference an entire primary key or unique constraint (something that is mandatory in every other RDBMS that suppors foreign key constraints)? Any concrete examples are appreciated. HT: Yes, it is sufficient that a foreign key and the referenced key appear as…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…have performed a number of simple benchmarks. Let’s start with a table containing 4 million rows and one secondary key: mysql> …dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…internal Innodb performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance …log entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… but not so much in 2010. MySQL surprisingly supports declarative integrity constraints. But wait being MySQL there must be something about it… performance by using one of these foreign key index with low cardinality and somewhat even distribution first instead of a primary or unique key…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used…. Setting innodb_concurrency_tickets too high can have startlingly negative performance implications. On the other hand, if we determine that 99…
