I talk with lot of people who are really interested in Percona XtraDB Cluster (PXC) and mostly they are interested in PXC as a high-availability solution. But, what they tend not to think too much about is if moving from async to synchronous replication is right for their application or not. Facts about Galera replication [...]
MySQL 5.6 – InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
A common practice to offload traffic from MySQL 5.6 is to use a caching layer to store expensive result sets or objects. Some typical use cases include: Complicated query result set (search results, recent users, recent posts, etc) Full page output (relatively static pages) Full objects (user or cart object built from several queries) Infrequently [...]
MySQL 5.6: Improvements in the Nutshell
January 27, 2013 By 16 Comments
Preparing for my talk for Percona MySQL University in Raleigh,NC, Tuesday 29th of January I have created the outline of improvements available in MySQL 5.6 which I thought was worth sharing to give a feel for how massive work have been done for this release in variety of areas. I’m sure the list is not [...]
Be productive with the MySQL command line
December 21, 2012 By 14 Comments
Even if you are using a GUI tool to connect to your MySQL servers, one day or another, you will have to deal with the command line. So it is nice to know a few tips that can really make your work easier. Note: The commands below are only available for Unix/Linux. Using pager Most [...]
Measuring the amount of writes in InnoDB redo logs
October 8, 2012 By 4 Comments
Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount of writes in the redo logs. You can find a detailed explanation in this post. To sum up, here are the main points: The redo logs should be large enough to store at most [...]
Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:
1 2 3 4 5 6 7 8 9 10 11 12 | DROP TABLE IF EXISTS `actor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; |
If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of [...]
Recovery deleted ibdata1
August 10, 2012 By 9 Comments
Recently I had a case when a customer deleted the InnoDB main table space – ibdata1 – and redo logs – ib_logfile*. MySQL keeps InnoDB files open all the time. The following recovery technique is based on this fact and it allowed to salvage the database. Actually, the files were deleted long time ago – [...]
Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
LVM snapshots is one powerful way of taking a consistent backup of your MySQL databases – but did you know that you can now restore directly from a snapshot (and binary logs for point in time recovery) in case of that ‘Oops’ moment? Let me show you quickly how. This howto assumes that you already [...]
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 [...]
Bug#12704861
November 20, 2011 By 1 Comment
As Mark pointed out, there isn’t a lot of detail in the release notes about what could potentially be a very serious problem that is fixed in MySQL 5.1.60. I’ll repeat here the full documentation from the release notes: “InnoDB Storage Engine: Data from BLOB columns could be lost if the server crashed at a precise [...]
