I was curious to check how Percona XtraDB Cluster behaves when it comes to MySQL replication latency — or better yet, call it data propagation latency. It was interesting to see whenever I can get stale data reads from other cluster nodes after write performed to some specific node. To test it I wrote quite a [...]
Logging Deadlock errors
September 19, 2012 By 4 Comments
The principal source of information for InnoDB diagnostics is the output of SHOW ENGINE INNODB STATUS but there are some sections that are not very useful. For example, LATEST DETECTED DEADLOCK only shows, as the name implies, the latest error detected. If you have 100 deadlocks per minute you will be able to see only [...]
Announcing Percona Server 5.1.65-14.0
September 4, 2012 By Leave a Comment
Percona is glad to announce the release of Percona Server 5.1.65-14.0 on September 4th, 2012 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.1.65, including all the bug fixes in it, Percona Server 5.1.65-14.0 is now the current stable release in the 5.1 series. All of Percona‘s software is open-source and free, all the details of the release can [...]
Announcing Percona Server 5.5.27-28.0
August 23, 2012 By 5 Comments
Percona is glad to announce the release of Percona Server 5.5.27-28.0 on August 23rd, 2012 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.27, including all the bug fixes in it, Percona Server 5.5.27-28.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 [...]
Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication. For those who don’t know, it gives you: Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node Automatic cluster synchronization, both incremental and full restores The ability to read [...]
Innodb Table Locks
July 31, 2012 By 4 Comments
Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:
1 2 3 4 5 | ---TRANSACTION 12303, ACTIVE 26 sec mysql tables in use 2, locked 0 MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data select count(*) from sbtest,sbtest x Trx read view will not see trx with id >= 12304, sees < 12301 |
As you can [...]
Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and [...]
InnoDB’s gap locks
March 27, 2012 By 15 Comments
One of the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but needs additional precautions to avoid phantom reads and to get a consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks. What is a Phantom Read A [...]
Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This documentation bug is already submitted. Firstly, we will start with a simple question. Why do we have [...]
Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables, or look for content in the Percona documentation or forums. So I present to you what is now my newest Firefox toolbar bookmark: an option and [...]
