One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other [...]
Configuring MySQL For High Number of Connections per Second
January 6, 2012 By 23 Comments
One thing I noticed during the observation was that there were roughly 2,000 new connections to MySQL per second during peak times. This is a high number by any account. When a new connection to MySQL is made, it can go into the back_log, which effectively serves as a queue for new connections on operating [...]
Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
Overview Profiling, analyzing and then fixing queries is likely the most oft-repeated part of a job of a DBA and one that keeps evolving, as new features are added to the application new queries pop up that need to be analyzed and fixed. And there are not too many tools out there that can make [...]
Percona Server 5.5.18-23.0: Now with Group Commit!
December 19, 2011 By 3 Comments
Percona is glad to announce the release of Percona Server 5.5.18-23.0 on December 19th, 2011 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.18, including all the bug fixes in it, Percona Server 5.5.18-23.0 is now the current stable release in the 5.5 series. All of Percona ‘s software is open-source and free, all the details of the release can [...]
Setting up XFS on Hardware RAID — the simple edition
December 16, 2011 By 9 Comments
There are about a gazillion FAQs and HOWTOs out there that talk about XFS configuration, RAID IO alignment, and mount point options. I wanted to try to put some of that information together in a condensed and simplified format that will work for the majority of use cases. This is not meant to cover every [...]
Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
In the first part of this article I have showed how I align IO, now I want to share results of the benchmark that I have been running to see how much benefit can we get from a proper IO alignment on a 4-disk RAID1+0 with 64k stripe element. I haven’t been running any benchmarks [...]
Shard-Query EC2 images available
May 11, 2011 By 2 Comments
Infobright and InnoDB AMI images are now available There are now demonstration AMI images for Shard-Query. Each image comes pre-loaded with the data used in the previous Shard-Query blog post. The data in the each image is split into 20 “shards”. This blog post will refer to an EC2 instances as a node from here [...]
Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
Over last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention problems as well as stalls and due to coarse invalidation is not as efficient as it could be. These are however mostly due to neglect Query Cache received over almost 10 years, with very [...]
MySQL caching methods and tips
April 4, 2011 By 5 Comments
“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 [...]
More on dangers of the caches
September 23, 2010 By 5 Comments
I wrote couple of weeks ago on dangers of bad cache design. Today I’ve been troubleshooting the production down case which had fair amount of issues related to how cache was used. The deal was as following. The update to the codebase was performed and it caused performance issues, so it was rolled back but [...]
