Last year, when I was speaking about MySQL performance at Devconf in Moscow, I expected my audience will be very experienced as this always happen at all PHPClub conferences. So I had to choose: either make full-day seminar and explain people every basic of performance, or rely on their knowledge and make some one and half hours seminar. I prefer short speeches, so I considered latter.

But even with such a mature audience you don't always know if they knew some or another basic thing. Like somebody can be good analyzing EXPLAIN output and other is in reading InnoDB Monitor printout. Also, native language of the audience is not English and it would be always good to have short reference to simple things, described in their native language. In this case Russian. This is why I created …

The MySQL & Cloud Solutions Day will take place at the Hyatt Regency Santa Clara, immediately following Percona Live: MySQL User Conference and Expo 2013

read more

We continue to improve Percona XtraBackup, and today I would like to give a preview for one feature which comes in next Percona XtraBackup 2.1 release.

This feature is “Compact backups”, and let me explain what it does.
As you may know InnoDB PK (Primary Key) contains all data, and all secondary indexes are only subset of columns of Primary Key. So in theory we can store only PK, and re-build secondary indexes as we need. Well, now it is possible not only in theory.

To create a compact backup you should use
innobackupex --compact
and it will create a backup where all InnoDB tables contain only Primary Keys and not secondary.
It allows to safe some space on a backup storage. How much? Well, it depends on how many indexes you have.
…

In MySQL 5.6, it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: NULL
…

UPDATE: Here’s the recording, enjoy!

Hello everybody!

#DBHangOps coming at you this week, Wednesday 1/30/12 at 12:00pm PST. Be sure to check out this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

This week’s topics were:
* bug fixes in recent versions
* Plugins
** authorization plugins (e.g. LDAP)
** audit plugin API — http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html
*** state transitions of records in MySQL with a plugin?
* InnoDB Status variables from twitter — https://github.com/twitter/mysql/wiki/InnoDB-Status-Variables
* Table alters/schema changes
** Twitter patch for non-blocking alter table (throws a different error than “lock_wait_timeout”) — …

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
, movie_title …

Just a silly article about a couple of instances recently where my colleague and I have both been duped by scheduled jobs. The feint and obvious lesson here is to be aware of your surroundings. I’m guilty of berating my 3 year old for spilling every glass of water she is handed because she’s unaware...

UPDATE: Here's the recording, enjoy!

Hello everybody!

#DBHangOps coming at you this week, Wednesday 1/30/12 at 12:00pm PST. Be sure to check out this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

This week's topics were: * bug fixes in recent versions * Plugins ** authorization plugins (e.g. LDAP) ** audit plugin API -- http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html *** state transitions of records in MySQL with a plugin? * InnoDB Status variables from twitter -- https://github.com/twitter/mysql/wiki/InnoDB-Status-Variables * Table alters/schema changes ** Twitter patch for non-blocking alter table (throws a different error than "lock_wait_timeout") -- https://github.com/twitter/mysql/wiki/Change-History#wiki-5.5.28.t8 ** Schemanator from Etsy -- …

This blog entry is on a step that many DBAs either skip, ignore, or do not know about. My last entry was on some install tricks and this time I want to cover where to put the various pieces of a MySQL server if you are looking to maximize performance. George Trujillo covered this for Oracle DBAs transitioning to MySQL but generally you do not see a lot of best practices information on this subject.

1. Pick a spot on a drive for the server software. It does not matter if it is /opt/mysql, /usr/local/mysql, or some variant.
2. Pick a spot on another drive that is on another disk controller for the log files.
3. Pick a spot on yet another drive that is on yet another disk for the data. Or get different spots for the various schemas you have to support — accounting, manufacturing, shipping all on different …

I wrote pt-archiver for jobs like deleting or archiving rows from a big table in small chunks. These days, that’s the kind of task I like doing inside the database, and Shlomi’s magical common_schema feels a lot more suited for this than an external Perl script.

When I say it’s magical, it really does feel magical. It’s amazing how he’s created an entire expressive scripting language that runs in MySQL and feels just right for the job.

Right now I’m watching this kind of stuff scroll by in my terminal:

+---------------------+
| rows_deleted_so_far |
+---------------------+
|             2871119 |
+---------------------+
1 row in set (7 min 42.67 sec)

+---------------------+
| rows_deleted_so_far |
+---------------------+
|             2872119 |
+---------------------+
1 row in set (7 min 42.75 sec)

Notice that the execution …