Some people want to use a backup to prepare a new Percona XtraDB Cluster node. They want this to avoid State Snapshot Transfer that could slow down the donor (depending of the SST method you are using, the donor can be blocked. I will cover this in a future blog post). As backup are generally [...]
ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
Today I was looking at the ALTER TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table for these tests because original table as initially created only has index on column K which initially contains only zeros, which means index is very [...]
A case for MariaDB’s Hash Joins
May 31, 2012 By Leave a Comment
MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks [...]
Binary log file size matters (sometimes)
May 24, 2012 By 6 Comments
I used to think one should never look at max_binlog_size, however last year I had a couple of interesting cases which showed that sometimes it may be very important variable to tune properly. I meant to write about it earlier but never really had a chance to do it. I have it now!
Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5 [...]
Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
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 [...]
How to Monitor MySQL with Percona’s Nagios Plugins
February 22, 2012 By 11 Comments
In this post, I’ll cover the new MySQL monitoring plugins we created for Nagios, and explain their features and intended purpose. I want to add a little context. What problem were we trying to solve with these plugins? Why yet another set of MySQL monitoring plugins? The typical problem with Nagios monitoring (and indeed with [...]
The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 10 Comments
This is a time-honored topic, and there’s no shortage of articles on the topic on this blog. I wanted to write a post trying to condense and clarify those posts, as it has taken me a while to really understand this relationship. Some basic facts Most of us know that writing into Innodb updates buffer [...]
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 [...]
Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
It is no secret that bugs related to multithreading–deadlocks, data races, starvations etc–have a big impact on application’s stability and are at the same time hard to find due to their nondeterministic nature. Any tool that makes finding such bugs easier, preferably before anybody is aware of their existence, is very welcome.
