The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may [...]
Introducing percona-patches for 5.1
February 9, 2010 By 21 Comments
Our patches for 5.0 have attracted significant interest. You can read about SecondLife’s experience here, as well as what Flickr had to say on their blog. The main improvements come in both performance gains and improvements to diagnostics (such as the improvements to the slow log output, and INDEX_STATISTICS). Despite having many requests to port [...]
InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
One problem made me puzzled for couple hours, but it was really interesting to figure out what’s going on. So let me introduce problem at first. The table is
1 2 3 4 5 6 7 8 | CREATE TABLE `c` ( `tracker_id` int(10) unsigned NOT NULL, `username` char(20) character set latin1 collate latin1_bin NOT NULL, `time_id` date NOT NULL, `block_id` int(10) unsigned default NULL, PRIMARY KEY (`tracker_id`,`username`,`time_id`), KEY `block_id` (`block_id`) ) ENGINE=InnoDB |
Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump, the query that scans data [...]
Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
Accidentally me and Baron played with InfoBright (see http://www.mysqlperformanceblog.com/2009/09/29/quick-comparison-of-myisam-infobright-and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB. Reading comments to Baron’s post I tied to load the same data to LucidDB, but I was not successful in this. I tried to analyze a bigger dataset and I took public [...]
Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE=’CA’ they would create 2 separate indexes on AGE and STATE [...]
Just do the math!
July 6, 2009 By 4 Comments
One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve. Let me start with example to make it clear. Lets say [...]
How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This [...]
ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb. But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking [...]
Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.
MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), [...]
