…InnoDB Fulltext, Memcached integration, a more complete performance schema, online DDL and several …######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the IDs for… solution for randomly choosing rows involves some compromise, either of performance, or of accuracy of randomness. The ORDER BY RAND() solution… table, and making lookups to the `kind_types` table by primary key, the result was a query that took half the time…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, $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… how both InnoDB and TokuDB performs if PK is (`id`,`hid`,`mid`). This also will affect select performance, so we will need…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… on MySQL performance can be found in this blogpost. This release of Percona XtraDB Cluster has fixed number of foreign key and… generating md5 digest over tuples in a table with no primary key was not deterministic which could lead to a node failure…-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: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside… transactions become obvious. In the following test sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each thread…_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and load data and…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… 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…), table without PK imposes an additional overhead, and because cluster performance is somewhat dependent on the slowest node – this overhead can…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a… does NOT set an lower bound on performance, only a upper bound. The best performance you can expect is modifying a given… update much faster. I see this tuning very frequently for “performance” reasons when data durability isn’t as crucial. This is…
Comment: Solving INFORMATION_SCHEMA slowness
May 6, 2013 By 25 Comments
Dave005, If your performance issue is caused by bug #56178 then you must run … to discover the structure of a table, its columns and primary key, etc. Using I_S was too slow, so instead I… at the time, and it didn’t suffer from the performance issues.
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… hoc query leads to scanning the entire last two partitions, performance is not bad. (The last 2-3 partitions tend to… the DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1) find partition…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…fewer tables (for the same database) you join the better performance you will get. As for any rules there are …*************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: Using index *************************** 3…
