… compromise, either of performance, or of accuracy of randomness. The ORDER BY RAND() solution is known to have poor performance, but… in a compound index, then the order would matter, and it would have to match the order of columns in the index. Example: WHERE… scan the `title` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN seem like…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… versions of MySQL, you can create two indexes with the same columns (in the same order) and the server will not complain… DROP INDEX `redundant`, ADD INDEX `redundant` (`col2`); # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 9 # Total Duplicate Indexes 1 # Total Indexes 2…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates…
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… loaded in PK order. What am I doing wrong with my hand-wavy estimates? 234M * (200 + 20) for PK index + 234M * (12… * PK index pages are 15/16 full as they are loaded in order * 7 bytes per-row metadata overhead for secondary index entries…/3 full So my guess is: * 50G for PK index * 6G for secondary index on base table * 11G for sbtest_r10 * much…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… want to find out The highest number of follow and order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM…_fans GROUP BY follow order by NUM desc limit 5 Follow was index。 the explain: type:index，Extra:Using index; Using temporary; Using filesort…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single… and not just a quorum? Well, it turns out transaction ordering really, really matters (really!). By enforcing replication to all nodes, we can (simultaneously) establish global ordering for the transaction, so by the time the original node…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… traffic except for replication. The buffer pool and adaptive hash index on the standby server will not have recently accessed pages… on the standby server. You will need Percona Server in order to use innodb_fake_changes. Thread pool Percona Playback added… logarithmic, so the difference between the IO usage is 2 orders of magnitude. All graphs looked like this (we did 39…
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
… of threads started by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel processing of individual tables… index. Percona XtraBackup has implemented Encrypted Backups. This feature can be used to encrypt/decrypt both local and streamed backups in order…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… of ensuring transactions are copied to all nodes and global ordering is established, but apply and commit is asynchronous on all… translates into number of unique key lookups into the certification index, which is a hash table. A small fc_limit does however keep the certification index smaller in memory. It keeps the certification interval small, which…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… explicitly named foreign key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and… upstream MySQL 5.6 the fix for unnecessary log_flush_order_mutex acquisition. Bug fixed #1163262 (Alexey Kopytov). When mysqldump was…
