While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not [...]
Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
Percona is glad to announce the release of Percona Server 5.1.66-14.2 on January 15th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.1.66, including all the bug fixes in it, Percona Server 5.1.66-14.2 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.28-29.3
January 8, 2013 By 2 Comments
Percona is glad to announce the release of Percona Server 5.5.28-29.3 on January 8th, 2012 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.28, including all the bug fixes in it, Percona Server 5.5.28-29.3 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 [...]
Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
I prefer to run MySQL with innodb_flush_method=O_DIRECT in most cases – it makes sure there is no overhead of double buffering and I can save the limited amount of file system cache I would normally have on database server for those things which need to be cached — system files, binary log, FRM files, MySQL [...]
How to STOP SLAVE on Amazon RDS read replica
December 20, 2012 By 4 Comments
We are doing a migration from Amazon RDS to EC2 with a customer. This, unfortunately, involves some downtime – if you are an RDS user, you probably know you can’t replicate an RDS instance to an external server (or even EC2). While it is annoying, this post isn’t going to be a rant on how [...]
The Optimization That (Often) Isn’t: Index Merge Intersection
December 14, 2012 By 6 Comments
Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that didn’t understand this limitation would often have tables with lots of single-column indexes on columns which commonly appeared in their WHERE clauses, and they’d wonder why the EXPLAIN plan for a given SELECT would [...]
Replication of the NOW() function (also, time travel)
November 28, 2012 By 6 Comments
Notice the result of the NOW() function in the following query. The query was run on a real database server and I didn’t change the clock of the server or change anything in the database configuration settings.
1 2 3 4 5 6 7 | mysql> SELECT NOW(),SYSDATE(); +---------------------+---------------------+ | NOW() | SYSDATE() | +---------------------+---------------------+ | 1999-01-01 00:00:00 | 2012-11-29 05:50:03 | +---------------------+---------------------+ 1 row in set (0.00 sec) |
You may proceed to party like it is 1999. How can the NOW() function return a value [...]
Percona XtraDB Cluster – installation and setup webinar follow up Q&A
October 28, 2012 By 4 Comments
Thanks for all, who attended my webinar, I got many questions and I wanted to take this opportunity to answer them. Q: Even ntp has a delay of 0.3-0.4 between servers does that mean a 0.25 as from logs can be an issue ? A: My demo vms were running for a few hours before [...]
Edge-case behavior of INSERT…ODKU
October 27, 2012 By 6 Comments
A few weeks back, I was working on a customer issue wherein they were observing database performance that dropped through the floor (to the point of an outage) roughly every 4 weeks or so. Nothing special about the environment, the hardware, or the queries; really, the majority of the database was a single table with [...]
Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
Did you just run an UPDATE against your 10 million row users table without a WHERE clause? Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement? This is possible if you are running in binlog_format=ROW ! Imagine this scenario:
1 2 3 4 5 6 | CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB; INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina'); |
We run an accidental UPDATE statement that [...]
