…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 6 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…any long running updates. Queries or transactions containing multiple update queries which add up to long time. I would keep the maximum query length at …
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…of original implementation which appeared in MySQL 4.0 Query Cache was designed to work with single core systems and relatively small memory…have multiple MySQL Query Cache instances hashed by query hash. It would help to reduce complexity dramatically. Fast Invalidation Right now when you update the…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…on only a single base table. Knowing this, the query result …to the multiple levels of result set reduction. Finally, Shard-Query can …SQL, but right now only MySQL storage nodes are supported. … merge optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_airport_id…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
One of the big “Selling Points” of MySQL is support for Multiple Storage engines, and from the glance view it is indeed… MySQL allows you using multiple storage engines in the query you need to test all kinds of combination such as running multi-table update… TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more. Single Storage Engine server would be also a lot easier…
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 7 Comments
… simple case of adding a single new node. When you insert a subtree, you’re inserting multiple nodes, and you want to… 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…
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
… common cache methods for MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL examines each query to see if the… the MySQL query cache, since the rate of invalidation can not be controlled, and multiple cache entries may be invalidated by a single table… each time (like a memcache miss) or they must be updated. Updating the summary tables efficiently is a hard problem to solve…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 30899, query id 100627 localhost root Updating update…in “Sleep” stage – if it is multiple statement transaction. Innodb also will print…be flushed by checkpointing process and single page – independent page writes. We can…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… set multiple times. mysql> load data infile ‘/tmp/words’ into table words (chars); Query OK, … other important optimization combines results from multiple queries together. This query is single threaded, and thus this …BY 1 ASC ON DUPLICATE KEY UPDATE `word`=VALUES(`word`), `md5(word)`=VALUES(`md5…
