Today’s blog post diving into the waters of the MySQL buffer pool is a cross-post from Groupon’s engineering blog, and is Part 1 of 2. Thank you to Kyle Oppenheim at Groupon for contributing to this project and post. We’ll be posting Part 2 on Thursday. I’ll be at the Percona Live MySQL Conference and Expo next week in Santa Clara, California so look for me there – I’d love to connect and talk more about MySQL buffer pools or anything else that’s on your mind!

There

The Percona Live MySQL Conference and Expo begins next Monday and runs April 22-25, 2013. Attendees will see great keynotes from leaders in the industry including representatives from Oracle, Amazon Web Services, HP, Continuent, and Percona. They can also participate in thought provoking Birds of a Feather sessions on Tuesday night and the Wednesday night Community Networking Reception will be fun and entertaining with the presentation of the Community Awards and the Lightning Talks.

If you

In preparing for this month’s Percona Live MySQL Conference and Expo, I’ve been reminiscing about the annual MySQL User Conference’s history – the 9 times it previously took place in its various reincarnations – and there are a lot of good things, fun things to remember.

2003 was the year that marked the first MySQL user conference independently organized by MySQL AB. It was called the “MySQL Users Conference” and took place at the Double Tree

5.6 has redefined MySQL performance and usability. Some great talks at the Percona Live MySQL Conference and Expo will provide insight into the new features and benefits of this major release. The conference is April 22-25, 2013 at the Santa Clara Convention Center and Hyatt Santa Clara.

Monday evening features the conference Welcome Reception where attendees can relax over food and beverages in the exhibition area following the day’s tutorials. After the Welcome Reception, at 6:30 pm, Oracle is hosting a reception to celebrate MySQL 5.6 going GA which is open to the public (but space

Percona CEO Peter Zaitsev leads a track at Percona MySQL University in Raleigh, N.C. on Jan. 29, 2013.

Percona MySQL University, Toronto is taking place this Friday and I’m very excited about this event because it is a special opportunity to fit a phenomenal number of specific and focused MySQL technical talks all into one day, for free.

Over the course of the day we will cover some of the hottest topics in the MySQL space. There will be talks covering topics like MySQL 5.6, MySQL in the Cloud and High Availability for MySQL, as well as

Facebook is a major user of MySQL and has pushed the performance limits of the technology. Their MySQL experts have deep, hands on knowledge of the technology. I’m pleased to welcome Mark Callaghan, Software Engineer for Database Infrastructure at Facebook, back again this year to the Percona Live MySQL Conference & Expo to share his expertise. Mark was a keynote speaker at last

Catch the webinar: “Learn How MySQL 5.6 Makes Query Optimization Easier” for more tips on the 5.6 optimizer

While preparing the webinar I will deliver this Friday, I ran into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10.

This turned into a really nice exercise for checking the performance and inner workings of one of the nicest features of the newer MySQL

As part of Percona Remote DBA for MySQL service we recognize that reliable backups are one of the most important things we can bring to the table. In my experience handling emergencies, the single worst thing that can happen is finding out you don’t have backups available when some sort of data loss or catastrophic event occurs.

With our Remote DBA service we can take care of backups for you, what follows are some of the internals of our implementation.

What kind of outages can happen?

• Someone runs UPDATE or DELETE and forgets the

I’m pleased to announce that Oracle is sending some of their top technical people to speak at the Percona Live MySQL Conference and Expo. The conference takes place April 22-25, 2013 at the Santa Clara Convention Center and Hyatt Santa Clara.

Tomas Ulin, VP, MySQL Engineering for Oracle, will present an invited keynote talk on “Driving MySQL Innovation” during the Tuesday morning opening keynotes. With the recent release of MySQL 5.6, conference attendees will hear about the latest developments of this

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things will happen; either the results returned from a MyISAM FTS query will be exactly identical to the same query when performed against InnoDB data, OR that the results returned by InnoDB FTS will somehow be

MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server

Like Ovais said in Implications of Metadata Locking Changes in MySQL 5.5, the hot topic these days is MySQL 5.6, but there was an important metadata locking change in MySQL 5.5.  As I began to dig into the Percona Toolkit bug he reported concerning this change apropos 

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many customers for whom the prudent move would be a migration to InnoDB, but due to their use of MyISAM FTS, the idea of a complete or partial migration was, for one reason or another, an impractical solution. So, when FTS for InnoDB was first announced, I thought this might end up being the magic bullet that would help these sorts of customers realize all of the benefits that have been engineered into InnoDB over the past few years and still keep their FTS capability without having to make any significant code

There are many reasons for wanting a small MySQL database server:

• You’re a uni student who wants to learn the SQL language better and needs a mini-testbox
• You’re a Windows user who wants to play around with Percona Server on Linux
• You’re a corporate application developer who wants a small SQL development & test box
• You’re a Internet startup that just needs a tiny startup database server without all the added costs

So, how about if you could setup a small Arch Linux ARMv6-based hardware device which runs Percona Server for MySQL, in a space not much bigger than your mouse, with the power consumption of only a smartphone charger, fully networked, all for under $80?

Introducing the Raspberry Pi with Percona Server:

MySQL 5.6 was made generally available as a production-ready solution earlier this month. This release comes about 2 years after MySQL 5.5 was released, but MySQL 5.6 contains improvements started long before that – for example, work on the Innodb Full Text Search project was started over 6 years ago, in addition with many optimizer and replication features. We’re happy to congratulate MySQL development team at Oracle with making this release happen.

In this blog post, I will not go into a features overview of MySQL 5.6. You can check out Release Notes for a good overview as well as many blog posts and articles written on this matter. Instead, I will tell you what is

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 been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata  [Read more...]

At Percona we offer a number of services. One of them, Custom MySQL Server Development, is commonly the most misunderstood and undervalued. There are a lot of ways Percona custom MySQL server development can help your business be more successful with MySQL. Here are some ways:

Bugs – There are Bugs in MySQL, Percona Server, and other products. By far the best and most cost efficient way to deal with Bugs is to have a Percona MySQL Support subscription which includes all you can eat bug fixes. If you need just one bug fixed this may be a way to go. Many bugs we’re tasked to fix are actually bugs in MySQL which the Oracle MySQL development team has not fixed. This does not only apply to “community reported

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 complete so If I miss something significant please let me know through the comment and I’ll update the page

Scalability
- Scalable Read Only Transactions
- Concurrent Innodb data file extension
- Non-Recursive Deadlock Detection
- Faster Locking Primitives
- Improved Innodb Thread Concurrency
- Multiple background Purge Threads
- Improved Purge lag control (now works)
- Split of “Kernel Mutex”
-

Today I gave a presentation on “Read/Write Splitting with PHP” for Percona Webinars.  If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as I could during the session, but here are all the questions with my complete answers:

Q: I wasn’t able to start up the webinar until it was 20 minutes in progress. Is it possible to get a recording once it is over?

A: Yes, we will email a link to all webinar attendees with a recording of the webinar and a link to the slides within 24 hours.  Folks who did not attend the webinar can also visit the webinar

I’ll be presenting a webinar next Wednesday, January 23 at 10 a.m. (Pacific Time), about issues application developers should think about for scaling out read-query traffic using multiple MySQL instances in a replication pair.

Specifically, about the care we have to take because replication is asynchronous.  This means the slave  may not have current data at all times, so an application must choose to query the slave or the master dynamically.  As much as possible, we’d like to automate this choice so that application developers can be the most productive.

Please join me for this webinar by registering here: http://www.percona.com/webinars/readwrite-splitting-mysql-and-php

The post

One of the most common causes of a poor Sphinx search performance I find our customers face is misuse of search filters. In this article I will cover how Sphinx attributes (which are normally used for filtering) work, when they are a good idea to use and what to do when they are not, but you still want to take advantage of otherwise superb Sphinx performance.

The Problem

While Sphinx is great for full text search, you can certainly go beyond full text search, but before you go there, it is a good idea to make sure you’re doing it the right way.

In Sphinx, columns are basically one of two kinds:

a) full text
b)

There are times where you need to know exactly how much memory the mysqld server (or any other program) is using, where (i.e. for what function) it was allocated, how it got there (a backtrace, please!), and at what point in time the allocation happened.

For example; you may have noticed a sharp memory increase after executing a particular query. Or, maybe mysqld is seemingly using too much memory overall. Or again, maybe you noticed mysqld’s memory profile slowly growing overtime, indicating a possible memory bug.

Whatever the reason, there is a simple but powerful way to profile MySQL memory usage; the Massif tool from Valgrind. An excerpt from the Massif manual page (Heap memory being simply the allotted pool of memory for use by

With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically Memory Usage.

I can’t count how many time I had to scratch my head with system configured to consume only few GBs in global buffers growing to consume much more for some unknown needs leaving me puzzled whenever it is user variables, complex stored procedures temporary tables or something else. Not only such connection related allocations are invisible but many global allocations are poorly visible too. Sure, we know how

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 show N possible index choices but only one index actually used.

To some extent, MySQL 5.0 and later changed this situation with the introduction of the “index merge” optimizer plan. The basic idea behind index merge is that for certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y”

Earlier this week, Cédric blogged about how easy we can get confused between a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according to the documentation the 2nd worst possible execution plan after a full table scan.
If it is obvious that a full table scan is not good for performance, how much can we expect if we can switch to a full index scan? In other terms, is a full table scan always the worst possible execution and should it be avoided at all costs?

Let’s take the employees database, and slightly modify the employees tables:

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 an auto-incrementing integer PK and a secondary UNIQUE KEY.

The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding to the columns in the secondary index, and they were coming in at a rate of approximately 1500 to 2000 per second, sustained, 24h per day. The mathematically-astute among you may already be able to see where this is going.

For purposes of discussion, we can use the following

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:

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 changes a row:

UPDATE `t1` SET `c2` = 'tom' WHERE `c1` = 2;

If we examine this UPDATE using the Binary Logging format of STATEMENT the entry would look like:

# at 464
#121019 16:10:42 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677442/*!*/;
BEGIN
/*!*/;
# at 532

I presented a webinar this week to give an overview of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording.

I’d like to invite folks who are interested in tools for query optimization to attend the new Percona online virtual training Analyzing SQL Queries with Percona Toolkit, October 29 – November 2, 2012. See http://store.percona.com/catalog.php?category=17 for details on this training class and how to register to attend.

During my webinar, I got a number of good questions. Here are the questions (paraphrased), with my answers.

Q: Can

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 the latest one and that is not very helpful when you are debugging your application code. Continuing my series of blog posts about Percona Toolkit, in this case I’m going to talk about pt-deadlock-logger.

pt-deadlock-logger

The tool is very simple and useful. It monitors the output of SHOW ENGINE INNODB STATUS and log the new deadlocks to a file or to a table that we can later review. Let’s

Ever ran into a situation where you saw “some important variable you really needed to know about=<optimized out>” while debugging? Let’s look at an example:

[Roel@qaserver master-data]$ gdb /percona-server/Percona-Server-5.5.25a-rel27.1-285.Linux.x86_64/bin/mysqld ./core.3200 
[...]
(gdb) bt
[...]
#20 handle_select (thd=0x33acd30, lex=0x33ae900, result=0x7f3e840058d0, setup_tables_done_option=1073741824)
    at /percona-server/5.5/Percona-Server-5.5.25a-rel27.1/sql/sql_select.cc:312
#21 mysql_execute_command (thd=thd@entry=0x33acd30)
    at /percona-server/5.5/Percona-Server-5.5.25a-rel27.1/sql/sql_parse.cc:3138
#22 mysql_parse (thd=thd@entry=0x33acd30, rawbuf=<optimized out>, length=72, parser_state=parser_state@entry=0x7f3ed013f810)
    at

Inspiration for this post is courtesy of a friend and former colleague of mine, Greg Youngblood, who pinged me last week with an interesting MySQL puzzle. He was running Percona Server 5.5.21 with a table structure that looks something like this:

CREATE TABLE foo (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 uid INT UNSIGNED NOT NULL,
 update_time DATETIME NOT NULL,
 ....
 INDEX `uid` (uid, update_time),
 INDEX `bar` (some_other_columns)
 ....
) ENGINE=InnoDB;

When he ran this query:

SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time  '0000-00-00 00:00:00';

The result came back as 2012-06-22 10:28:16. However, when he ran a slightly different query:

SELECT MIN(t.update_time) FROM (SELECT uid, MIN(update_time) AS "update_time" FROM foo WHERE update_time IS