An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB [...]
Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the divide and conquer approach that Hive takes in combination with Hadoop. Shard-Query applies a clever approach to parallelism which allows it to [...]
MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
Amazon’s Relational Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance limitations on standard EC2 servers with EBS volumes (see SSD versus EBS death match), and one of them wanted to evaluate RDS as a replacement. It is built on the same technologies, but the hardware and networking are supposed [...]
Percona Server 5.1.49-rel12.0
September 15, 2010 By 6 Comments
Dear Community, Starting with this release, we introduce a new release model for Percona Server. From now on, we will have both Stable and Release Candidate releases. Release Candidates will introduce new features not yet available in Stable releases. Along with new features, our new 5.1.49-12.0 RC contains a couple of patches from the Facebook-MySQL [...]
Estimating Replication Capacity
July 20, 2010 By 4 Comments
It is easy for MySQL replication to become bottleneck when Master server is not seriously loaded and the more cores and hard drives the get the larger the difference becomes, as long as replication remains single thread process. At the same time it is a lot easier to optimize your system when your replication runs [...]
Joining on range? Wrong!
May 17, 2010 By 10 Comments
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 [...]
Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
It is very common for me to look at a customer’s database and notice a lot of overhead from checking whether a database connection is active before sending a query to it. This comes from the following design pattern, written in pseudo-code:
1 2 3 4 5 6 | function query_database(connection, sql) if !connection.is_alive() and !connection.reconnect() then throw exception end return connection.execute(sql) end |
Many of the popular development platforms do something similar to this. Two [...]
Why you should ignore MySQL’s key cache hit ratio
February 28, 2010 By 28 Comments
I have not caused a fist fight in a while, so it’s time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL’s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and [...]
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 [...]
