…multiple hard drives does not help if you have single query (or stream of queries…single query is executed for Innodb storage engine to be more specific. Lets look at update queries (from replication thread for example). When update…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
…MySQL Replication is single threaded, which means if you have any long running write query it clogs replication stream and small and fast updates…updates. Queries or transactions containing multiple update queries which add up to long time. I would keep the maximum query…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… MySQL 4.0 Query Cache was designed to work with single …multiple MySQL Query Cache instances hashed by query hash. It would help to reduce complexity dramatically. Fast Invalidation Right now when you update…MySQL Query Cache is working in many cases. This however can be improved and data about Query…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… rows in set (0.00 sec) node1 mysql> update autoinc set j=”node1″ where i = 1; Query OK, 1 row affected (0.00… rows in set (0.00 sec) node3 mysql> update autoinc set j=”node3″ where i=1; Query OK, 1 row affected (0.01… in most cases, but in principle the least likely it is for a single piece of data to be written on multiple nodes…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
…ones. Trying to handle all different cases in Optimizer – such as in …MySQL allows you using multiple storage engines in the query you need to test all kinds of combination such as running multi-table update… hoping nobody is touching “mysql” database any more. Single Storage Engine server would be…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… rows are the worst case for performance. Optimal … a single base table. Knowing this, the query … multiple levels of result set reduction. Finally, Shard-Query …but right now only MySQL storage nodes are supported…optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_airport…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
…for a way to improve update performance for the application and I was replacing single value UPDATE with multiple value REPLACE (though I also…&& trx->mysql_query_str) { /* Scan the MySQL query string; check if SELECT is the first word there */ ibool success; dict_accept(*trx->mysql_query_str, “…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…queries; really, the majority of the database was a single… first. So, MySQL checks our INSERT… the case. (root@localhost) [test]> select * from update_test; +————+———-+———+———————+ | id …multiple unique indexes such as this one would be equivalent to “UPDATE update…
Post: Making MySQL Replication Parallel
January 30, 2007 By 1 Comment
…queries if they are complex transactions. It should be however not as bad if we only look at single queries…multiple threads inside the server to share same transactional/lock context. In this case replication could accumulate number of queries…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… above is the simple case of adding a single new node. When you insert a subtree, you’re inserting multiple nodes, and you… TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL…
