…In previous versions of MySQL, you can create two indexes with the same columns (in the same… # ######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), …
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 1
May 21, 2013 By 8 Comments
…’s create a simple replication configuration with one master and two slaves, all running MySQL 5.6 with GTIDs disabled. First… OFF, Error_code: 1593 These simple tests answer the first two questions: replication works only if all servers have the same…> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES FROM test on both slaves shows that…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… need to know what fields form the primary key. The matter is regardless at what position primary key fields are defined in CREATE TABLE…_TRX_ID and DB_ROLL_PTR . These two fields always reside between the primary key fields and the rest of the fields. SYS…, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB; In the end there are two notes: The generated structure differs from…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… SSD: Kingston HyperX 3K 240 GB. Workload: I will use two different schemas. The first schema is from sysbench, and the…, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite…, $ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… brief context recap. There are two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode… transactions become obvious. In the following test sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each thread… use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… generating md5 digest over tuples in a table with no primary key was not deterministic which could lead to a node failure… a temporary table. Bug fixed #1084702 (Seppo Jaakola). In case two nodes in a 3-node cluster had to abort due…-uniform foreign key reference could cause a slave crash. Fixed by using primary key of the child table when appending exclusive key for cascading…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… async master, for example like the one described on these two bugs. Galera use writesets to replicate cluster events to the… planning to write on multiple nodes. Have PRIMARY KEYS If you still have tables without PRIMARY KEYs, then its time to make one for…, you can still hit issues like when automatic creation of primary keys for use during certification becomes non-deterministic. Although the previous…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…’s ad hoc query leads to scanning the entire last two partitions, performance is not bad. (The last 2-3 partitions… the DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1) find partition… in all partitions. * A ‘range’ delete would mostly follow above two points. * Secondary indexes are also BTrees, so they need updating…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… approximately the worst packet round trip time (RTT) between any two nodes in your cluster. Transaction apply on slave nodes is… of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a… doing that for Galera is approximately one RTT between the two nodes in the cluster that are furthest apart (regardless of…
Post: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
… a small change can make a big difference. There are two ways to know how many records an InnoDB page stores… organized in an unidirectional list. The list is sorted by primary key and starts with the internal record infinum and ends with… the infinum and end up with the supremum record then two conclusions can be make: The page structure is not corrupt…
