“The least expensive query is the query you never run.” Data access is expensive for your application. It often requires CPU, network and disk access, all of which can take a lot of time. Using less computing resources, particularly in the cloud, results in decreased overall operational costs, so caches provide real value by avoiding [...]
Using Flexviews – part two, change data capture
March 25, 2011 By 26 Comments
In my previous post I introduced materialized view concepts. This post begins with an introduction to change data capture technology and describes some of the ways in which it can be leveraged for your benefit. This is followed by a description of FlexCDC, the change data capture tool included with Flexviews. It continues with an [...]
How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
Among the many things that can cause a “server stall” is a long-running transaction. If a transaction remains open for a very long time without committing, and has modified data, then other transactions could block and fail with a lock wait timeout. The problem is, it can be very difficult to find the offending code [...]
How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not – it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide [...]
Replication of MEMORY (HEAP) Tables
October 15, 2010 By 7 Comments
Some Applications need to store some transient data which is frequently regenerated and MEMORY table look like a very good match for this sort of tasks. Unfortunately this will bite when you will be looking to add Replication to your environment as MEMORY tables do not play well with replication.
Data mart or data warehouse?
July 15, 2010 By 13 Comments
This is part two in my six part series on business intelligence, with a focus on OLAP analysis. Part 1 – Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL which a ROLAP tool must generate to answer those queries. [...]
Intro to OLAP
July 12, 2010 By 10 Comments
This is the first of a series of posts about business intelligence tools, particularly OLAP (or online analytical processing) tools using MySQL and other free open source software. OLAP tools are a part of the larger topic of business intelligence, a topic that has not had a lot of coverage on MPB. Because of this, [...]
Active Cache for MySQL
January 10, 2010 By 11 Comments
One of the problems I have with Memcache is this cache is passive, this means it only stores cached data. This means application using Memcache has to has to special logic to handle misses from the cache, being careful updating the cache – you may have multiple data modifications happening at the same time. Finally [...]
What do we optimize with mk-query-digest ?
December 15, 2009 By 2 Comments
When we’re looking at mk-query-digest report we typically look at the Queries causing the most impact (sum of the query execution times) as well as queries having some longest samples. Why are we looking at these ? Queries with highest Impact are important because looking at these queries and optimizing them typically helps to improve [...]
Watch out for your CRON jobs
October 14, 2009 By 19 Comments
Resolving extreme database overload for the customer recently I have found about 80 copies of same cron job running hammering the database. This number is rather extreme typically the affect is noticed and fixed well before that but the problem with run away cron jobs is way to frequent. If slow down happens on the [...]
