… of ensuring transactions are copied to all nodes and global ordering is established, but apply and commit is asynchronous on all… large transaction briefly stalling an applier thread. However, it is possible to halt queue applying on any node by simply by running “FLUSH TABLES… up a large replication queue without flow control if they are blocked by the underlying SST method, i.e., by FLUSH TABLES WITH READ…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…ordering really, really matters (really!). By enforcing replication to all nodes, we can (simultaneously) establish global ordering for the transaction, so by…. This really will open a large window where rows won’t … in separate tables and there also exists a users_groups table to define the …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
….5.30-30.2 and a followup by Dimitri Kravchuk, we have received a large number of questions on why the benchmark…-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order…-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=9 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order…
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
… queries, making it ideal for companies with large data sets and mission-critical applications that …encrypt/decrypt both local and streamed backups in order to add another layer of protection to the…assume the table has been dropped if the tablespace was renamed after it was scanned by XtraBackup …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…) you’re going to use in ORDER BY in other table. Here is example when ORDER BY is done by second table which requires filesort: mysql> explain… for ORDER BY without LIMIT as well but there are some differences. I should write another article about ORDER BY without limit and large tables soon…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…scan/range scan speed dramatically. The rows referenced by indexes also could be located sequentially or …values first and then accessing rows in sorted order can be a lot of help for big … or less full table scan may be faster. Avoid joins to large tables Joining of large data sets using nested…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: ref possible… and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: ref possible… can be skewed) using filesort is better as otherwise very large portion of index may need to be scanned to find…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…cnt,c from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select_type | table | type | possible_keys | key… write caching is not effective for relatively large area (which still fits in memory) which…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…’ve tried to order results by states. 1) Results for ENUM: select SQL_NO_CACHE city from cities_enum ORDER BY state limit 10000….id = c.state_id) ORDER BY s.name limit 10000,5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: ALL possible… we can see that performance hit may not be as large as you expect. Though again a lot depends on your…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…for large indexes which would get inserts in very random order,… mysql> select * from a order by id limit 10; +—-+——————————————+ | id | c | +—-+——————————————+ | 1 | …this trick for ALTER TABLE which requires table rebuild. Dropping all …
