If you use a storage engine that supports transactions, you probably have faced or heard of deadlock's.

From MySQL Documentation:
“Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.”

At work, we had an important job that sometimes were failing due to dead lock. I wanted to enhance it, so it will do what the documentation says ( Retry the transaction ). In order to do that, I wanted to have a scenario where I was able to reproduce the deadlock and the victim transaction was the one from the job I was fixing.

Create a deadlock is simple, you just need to have 2 sessions that each one holds a lock that the other is waiting for. For example:
We have a table that has 4 entries on it (entry 1, entry 2, entry 3, entry 4) and we have 2 …

Percona invites you to attend a webinar Wednesday, January 27th, with CEO Peter Zaitsev: Compression In Open Source Databases. Register now!

Data growth has been tremendous in the last decade and shows no signs of stopping. To deal with this trend database technologies have implemented a number of approaches, and data compression is by far the most common and important. Compression in open source databases is complicated, and there are a lot of different approaches – each with their own implications.

In this talk we will perform a survey of compression in some of the most popular open source database engines including: Innodb, TokuDB, MongoDB, WiredTiger, RocksDB, and PostgreSQL.

Important information: …

The Percona Live Data Performance Conference 2016 is rapidly approaching, and we’re looking forward to providing an outstanding experience April 18-21 for all whom attend.

Percona Live is the premier event for the rich and diverse open source community and businesses that thrive in the MySQL and NoSQL marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, and CEOs representing organizations from industry giants such as Oracle to start-ups. Vendors increasingly rely on the conference as a major opportunity to connect with potential high-value customers from around the world.

Below are some highlights for the upcoming conference regarding the conference schedule, Tutorial sessions, Birds of a Feather talks, and Lightning talks.

Conference Schedule

Percona Live is packed with engaging sessions, helpful …

Data corruption! It can happen. Maybe because of a bug or storage problem that you didn’t expect, or MySQL crashes when a page checksum’s result is different from what it expected. Either way, corrupted data can and does occur. What do you do then?

Let’s look at the following example and see what can be done when you face this situation.

We have some valuable data:

> select * from t limit 4;
+---+--------+
| i | c      |
+---+--------+
| 1 | Miguel |
| 2 | Angel  |
| 3 | Miguel |
| 4 | Angel  |
+---+--------+
> select count(*) from t;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+

One day the query you usually run fails and your application stops working. Even worse, it causes the crash already mentioned:

> select * from t where i=2097151;
ERROR 2006 (HY000): MySQL server has gone away

Usually this is the point when panic starts. The error log shows: …

We work on data recoveries quite often. In many cases, we recover table structures from the .frm files because there was no backup available. There is already a great blog post by my colleague Miguel Ángel Nieto about how we can recover structures from .frm files using MySQL utilities.

This works pretty well and we prefer to run mysqlfrm with the “–server” option to get all possible information from a .frm file. However, this option expects that MySQL is up and running so that mysqlfrm can spawn a new MySQL instance, and run the structure recovery there.

Recently I came across a tool that makes this job easier. The name of tool is …

Percona is glad to announce the first release candidate of Percona Server 5.7.10-1 on December 14, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories.

This release contains all the bug fixes from latest Percona Server 5.6 release (currently Percona Server 5.6.27-76.0).

New …

MySQL introduces several improvements related to partitioning. Most importantly, work has been done to move InnoDB towards native partitioning support, which not only "paves the way for better overall partitioning" (source) in the future but already comes with measurable performance benefits.
This article focuses on memory usage in heavily partitioned InnoDB databases.
Background Over the last few weeks I've been (un)lucky enough to work with several MySQL databases suffering from stability issues due to extreme memory pressure. All databases belonged to the same DBA team, who tend to apply complex partitioning schemes even to very small tables. Discussion as to whether or not this approach is correct is beyond the scope of this article but the situation encouraged …

This Log Buffer Editions picks few blog posts from Oracle, SQL Server and MySQL.

Oracle:

• If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege.
• Lost SYSMAN password OEM CC 12gR5.
• How Terminal Emulation Assists Easy Data Management.
• Using EMCLI List Verb …

Amazon EBS volumes come with a very cool feature called "lazy loading". In a nutshell: if a volume is created from an existing snapshot, it can become available almost immediately without waiting for all data to be restored. This allows for extremely fast provisioning of large data sets as long as you don't explicitly require the entire data set to be present before you start using it.
When an EBS volume is restored from snapshot, its blocks are fetched from Amazon S3. It happens either lazily in the background or explicitly on demand (think of a pagefault-like mechanism) and of course, fetching pieces of data from Amazon S3 is going to be one-two orders of magnitude slower than reading blocks directly from a volume.
In this short article, I will try to give you an idea of how this may impact the crash recovery time of your MySQL databases. Why talk about this? Depending on the workload and data set layout, crash recovery of a MySQL …

It goes without saying that crash recovery of busy MySQL servers (and many other RDBMS for that matter) is not an extremely quick process. In MySQL context, one of the worst case scenarios is when the server is used for multi-tenant application hosting i.e. when the MySQL instance contains hundreds or thousands of schemas and (tens/hundreds of) thousands of tablespaces. In such scenario, the server may spend a considerable amount of time in the tablespace discovery phase, during which MySQL builds a mapping between tablespace IDs and names of actual tablespace files on disk.
MySQL 5.7 promises to put an end to tablespace discovery. The documentation lists the following improvements introduced in versions 5.7.5 and up:

• Elimination of file system scans prior to redo log application. The MLOG_FILE_NAME redo log …