…original implementation which appeared in MySQL 4.0 Query Cache was designed to work with single core systems and relatively …multiple MySQL Query Cache instances hashed by query hash. It would help to reduce complexity dramatically. Fast Invalidation Right now when you update the table all 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… 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‘s…
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
… the MySQL query cache, since the rate of invalidation can not be controlled, and multiple cache entries may be invalidated by a single table change… time (like a memcache miss) or they must be updated. Updating the summary tables efficiently is a hard problem to solve. My latest…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… a single base table. Knowing this, the query result …UPDATE is the same. All of the fastest incremental materialized view optimizations can be applied. Shard-Query works only on sets Shard-Query… multiple levels of result set reduction. Finally, Shard-Query …but right now only MySQL storage nodes are …
Post: Data mart or data warehouse?
July 15, 2010 By 13 Comments
…with and without aggregation over multiple MySQL storage engines at various … category information into a single dimension, while also maintaining…the dimension tables in a data mart may be updated. In …, bitmap indexes, table partitioning, parallel query execution, clustered tables and materialized …
Post: Managing Slave Lag with MySQL Replication
October 12, 2007 By 11 Comments
…multiple reasons why application falls behind and why we see those lag spikes: Slave Server Load – MySQL Replication goes in single…query chopping making sure all your update queries are short. If you need to update 1…LOG=0 before you run ALTER TABLE query. Replication Overload Many tutorials out …
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… as MySQL allows you using multiple storage engines in the query you need to test all kinds of combination such as running multi-table update… hot way without nasty “FLUSH TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more. Single Storage Engine server would be…
Post: Making MySQL Replication Parallel
January 30, 2007 By 1 Comment
…MySQL Replication is single threaded so in reality it is only able to use only single CPU and single…single queries or transactions which do not have any overlap in terms of tables. For some users commit order for independent queries…tables. There is other possible solution it is to allow multiple…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…tables only to optimize bulk inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single…Query Cache also could use this feature. low_priority_updates If enabled selects will get higher priority than updates and waiting 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…updates. Queries or transactions containing multiple update queries which add up to long time. I would keep the maximum query…
