April 19, 2014

10 MySQL settings to tune after installation

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list […]

InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity

In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s responsible for performing flushing operations. Page_cleaner performs flushing of the dirty pages from the buffer pool based on two factors: – access pattern  -  the least recently used pages will be flushed by LRU flusher from LRU_list when buffer pool has no free pages anymore; […]

What’s required to tune MySQL?

I got a serendipitous call (thanks!) yesterday asking what would be needed to tune[1] a database for better performance. It is a question that I hear often, but I never thought about answering it in public. Here’s a consolidated version of what I explained during our conversation.

MySQL 5.5.8 – in search of stability

A couple of days ago, Dimitri published a blog post, Analyzing Percona’s TPCC-like Workload on MySQL 5.5, which was  a response to my post, MySQL 5.5.8 and Percona Server: being adaptive. I will refer to Dimitri’s article as article [1]. As always, Dimitri has provided a very detailed and thoughtful article, and I strongly recommend reading if […]

Why you should ignore MySQL’s key cache hit ratio

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 […]

How Percona does a MySQL Performance Audit

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 […]

Indexes in MySQL

MySQL does not always make a right decision about indexes usage. Condsider a simple table:

; 250001 (V1)

; 83036 (V2) (execution time = 110 ms) That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21% It is said (e.g. book “SQL Tuning”) if selectivity over 20% then a full table […]

Percona XtraDB Cluster 5.6.14-25.1 Beta is now available

Percona is glad to announce the first Beta release of Percona XtraDB Cluster 5.6 on November 21st, 2013. Binaries are available from downloads area or from our software repositories. Based on Percona Server 5.6.14-62.0 including all the bug fixes in it, Galera Replicator 3.1 and on Codership wsrep API 5.6.14-25.1 is now the first BETA […]

Percona Server 5.6.13-61.0 first GA release is now available

Percona is glad to announce the first GA (Generally Available) release of Percona Server 5.6.13-61.0 on October 7th, 2013 (Downloads are available here and from the Percona Software Repositories. Based on MySQL 5.6.13, including all the bug fixes in it, Percona Server 5.6.13-61.0 is the first GA release in the Percona Server 5.6 series. All […]

InnoDB performance optimization basics (redux)

I recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “Innodb Performance Optimization Basics.” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available […]