One question I often get is how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system but how do we know whenever there is any possibility to make this query [...]
MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]
Flexviews is a working scalable database transactional memory example
May 19, 2011 By 3 Comments
http://Flexvie.ws fully implements a method for creating materialized views for MySQL data sets. The tool is for MySQL, but the methods are database agnostic. A materialized view is an analogue of software transactional memory. You can think of this as database transactional memory, or as database state distributed over time, but in an easy way [...]
Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
Can Shard-Query scale to 20 nodes? Peter asked this question in comments to to my previous Shard-Query benchmark. Actually he asked if it could scale to 50, but testing 20 was all I could due to to EC2 and time limits. I think the results at 20 nodes are very useful to understand the performance: [...]
The two even more fundamental performance metrics
May 5, 2011 By 6 Comments
In a recent blog post, I wrote about four fundamental metrics for system performance analysis. These are throughput, residence time, “weighted time” (the sum of all residence times in the observation period — the terminology is mine for lack of a better name), and concurrency. I derived all of these metrics from two “even more [...]
Flexviews – part 3 – improving query performance using materialized views
April 4, 2011 By 20 Comments
Combating “data drift” In my first post in this series, I described materialized views (MVs). An MV is essentially a cached result set at one point in time. The contents of the MV will become incorrect (out of sync) when the underlying data changes. This loss of synchronization is sometimes called drift. This is conceptually [...]
Using Flexviews – part one, introduction to materialized views
March 23, 2011 By 3 Comments
If you know me, then you probably have heard of Flexviews. If not, then it might not be familiar to you. I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog about it before then. For those unfamiliar, Flexviews enables you to create and maintain incrementally refreshable materialized [...]
Testing MySQL column stores
August 16, 2010 By 15 Comments
Recently I had the opportunity to do some testing on a large data set against two MySQL column-store storage engines. I’d like to note that this effort was sponsored by Infobright, but this analysis reflects my independent testing from an objective viewpoint. I performed two different types of testing. The first focused on core functionality [...]
Color code your performance numbers
June 2, 2010 By 4 Comments
When analyzing how good or bad response time is it is not handy to look at the averages, min or max times – something what is easily computed using built in aggregate functions. We most likely would like to see some percentile numbers – 95 percentile or 99 percentile. The problem is computing these in [...]
Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
I’m not big fan on flow control functions like IF or CASE used in MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer. One way I find IF statement very useful is computing multiple aggregates over different set [...]
