Recordings for webinars are now online

We’ve just published the recordings for our webinars; Migrating MyISAM to InnoDB and Introduction to Percona Server, XtraDB and Xtrabackup.  To listen, click on (Watch: Recorded Webinar) on each of these links.

We’re planning for future webinars next year.  If you have any suggestions, feel free to leave a comment.

Entry posted by Morgan Tocker |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Webinar: Introduction to Percona Server, XtraDB and Xtrabackup

We’ve had such a large number of signups to our Migrating MyISAM to InnoDB webinar, that we decided to hold one more before the year is out:

December, 8th 9AM PST: Introduction to Percona Server, XtraDB and Xtrabackup.

As the title suggests: this presentation is an introduction.  We will be running through the main aspects that change, and where you can expect to get the most benefit.

This will be a technical talk, but perhaps less technical than some of our other posts. I recommend inviting a few friends & colleagues that are already familiar with MySQL, but perhaps not regular readers of our blog.

(Time for Q&A will follow the main presentation).

Entry posted by Morgan Tocker |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Percona’s Commitments to MySQL Users

You probably saw the Twitter storm over Oracle’s pricing changes and InnoDB in the last few days. The fear about Oracle removing InnoDB from the free version of MySQL was baseless — it was just a misunderstanding. Still, in the years since MySQL has been acquired by Sun, and then by Oracle, many MySQL users have been uncertain about the future. We think it’s important for us to make our own position and plans clear and public, so that there is no fear or doubt about what Percona will do.

We want to begin by saying “thank you” to those who built and maintained MySQL over the years. Many of us at Percona came from the original MySQL team. We know that it took a decade-long group effort from Monty, David, Marten and many others to develop it into the world’s leading open-source database. Much of their work is never publicly recognized, but it is the foundation of our own work, and we’re indebted to it. MySQL development suffered some detours and slowdowns over the last few years. This era is over. MySQL has never progressed so rapidly towards better features, better performance, and better quality. We applaud the great work coming from Oracle.

Always business-focused

We know that statements such as these can be filled with corporate double-speak. Let there be no doubt: Percona is in business to make money, not to make flowers grow taller and the sun shine brighter. Likewise, our customers are in business to make money. We write software that contributes to our business goals, and to our customers’ goals. And we think the best way to do that is by adhering to open-source principles.

Always improving

Our flagship database server Percona Server with XtraDB powers critical portions of the U.S. economy, applications you rely on every day, utility services, and governments. Thousands of people use it. We take that very seriously.

Our roadmap is to continue what we are doing: diagnose and solve the root problems our customers experience, and build new features to overcome limitations in the server. We have a dedicated team of engineers making sustained and rapid progress on improving it. We plan to continue releasing new versions frequently.

Always free

Our software is 100% free. We are a pure-play open-source company. We do not practice dual-licensing. We hold nothing back. Our strategy is to be a services company, pure and simple. The value we provide to customers is our depth of knowledge about how to solve their problems. The software we produce is evidence of our abilities.

Always independent and objective

Percona Server with XtraDB is an enhanced drop-in replacement for MySQL, for those whose needs are not met by the official MySQL server from Oracle. It offers more performance, more features, and more diagnostic capabilities. If it meets your needs, we’re happy, but we make our money from services, not software. Our job is to tell our customers what we think is best for them. That’s partly why we are a pure-play open-source company. If we earned money directly from our software, that would compromise our core principle of objectivity, because we’d have a financial incentive to push customers towards our software instead of helping them make the right decisions for themselves.

Always open-source

We will always work to create and improve open-source software. When there’s a need, we respond to closed-source software by providing open-source alternatives. An example of this is Percona XtraBackup. We have also worked to create open-source tools for monitoring, query analysis, replication management, and other capabilities.

If you’re worried about Oracle making MySQL closed-source, stop worrying. It can’t be done. Oracle cannot take back what is already in the community’s hands; at worst, they can only stop updating it with new versions. And we can and will continue to provide an open-source database server that delivers improved performance and functionality to our customers.

Always fairly priced

Percona is the oldest independent support provider for MySQL. We opened in mid-2006. Since then we’ve grown to over 40 full-time staff and over 900 customers. We think this success is partially because we’re fair to our customers. Our pricing model has always been to match value for the customer with Percona’s costs as closely as possible. This is why our consulting is billed by the hour in a pay-as-you-go model: you pay only for what you use. Our support and maintenance offering covers unlimited servers and CPUs, because the cost of providing support is not related to how many servers you’re running. Per-unit pricing would also compromise our objectivity — we’d have an incentive to increase your server count, instead of helping you make your systems more efficient.


We’re dedicated to advancing the state of the art in MySQL performance and functionality, because our customers pay us to do it. You can count on us continuing to provide an outstanding open-source database server and other software tools, with accompanying world-class services and support. We salute everyone who’s helping advance MySQL and open-source software, including Oracle, and look forward to a bright and prosperous future for our businesses.

The Percona Executive Management Team:

Peter Zaitsev, Chief Executive Officer
Vadim Tkachenko, Chief Technology Officer
Baron Schwartz, Chief Performance Architect
Tom Basil, Chief Operating Officer
Bill Schuler, Vice President of Sales
Espen Braekken, Vice President of Consulting and Support

Entry posted by Baron Schwartz |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Percona at WebConf Riga 2010

WebConf LogoMy colleague Aleksandr Kuzminsky will be speaking at WebConf Riga 2010 next month on XtraBackup: Hot Backups and More and Recovery of Lost or Corrupted InnoDB Tables.

WebConf is the first big conference of its kind in the Baltic states (Estonia, Latvia, Lithuania) and we are very happy to be participating.

In addition to Aleksandr’s talks, we will also be offering our training courses. You can find out more from the conference website (they will be publishing more information in the next two days).

Entry posted by Morgan Tocker |
No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Percona talks at OpenSQL Camp this weekend

Four Perconians (perconites?) will be at OpenSQL Camp in Sankt Augustin, Germany this weekend presenting talks on:

  • Recovery of Lost or Corrupted InnoDB Tables
  • Keep your MySQL backend online no matter what
  • XtraDB — InnoDB on steroids
  • Xtrabackup for MySQL

If you would like to stop by and say hello, we are Aleksandr, Istvan, Morgan and Aurimas (pictures here).

If you can make the (approximate) location, but not the date, we also have training in Frankfurt in three weeks time.

Entry posted by Morgan Tocker |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Presentations Announcement: 2010 O’Reilly MySQL Conference & Expo

The Percona team participated at this year’s O’Reilly MySQL Conference & Expo held April 12-15, 2010 in Santa Clara, California. We gave a lot of talks on various topics and all of those presentations are now available. Here’s the list:

You can also read more about this conference on Percona site.

Entry posted by Michael Rikmas |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Community Events February-March

February and March as busy months for Community events.  There’s MySQL University, Fosdem, the Seattle MySQL Meetup & Here are the details:

  • February 4th – I’ll be presenting a talk via MySQL University on Optimizing Queries with Explain.  This talk will be about learning to read the output from MySQL’s EXPLAIN command, and optimizing some example queries from the IMDB dataset.  At 5.7G in InnoDB tables imported before any secondary key indexes IMDB is one of my new favorite example databases.  It’s about the right size on most desktop PCs that you can measure the difference between a query that’s optimized and one that’s not.  All material comes courtesy of a new chapter I’ve been writing for our Developer’s class on Schema & Indexing.
  • February 7th – My colleague Piotr will be presenting on Multi-Master Replication Manager for MySQL at the MySQL & Friends Dev Room.  Besides Piotr’s talk, there will be a number of MySQL enthusiasts in Brussels for FOSDEM.  I recommend reading Lenz’s announcement or the wiki page for more information.
  • February 16th – I’ll be at the Seattle MySQL User Group giving a talk on Quick Wins with third party patches for MySQL.  This is an old talk I’ve given previously, but I’ll be updating it to demonstrate some of the really cool things you can do with XtraDB R-9.
  • February 23rd – The Boston MySQL Meetup is organizing a social dinner.  More details to come.
  • March 8-12th – I’ll be at in Montréal presenting two training workshops, and talks on “I’m going to tell you what MySQL is bad at!” and “Diagnosing and Fixing MySQL Database Problems“.  Telling you what something is bad at is my provocative way of saying that there are some really awesome tools that compliment MySQL well.

Entry posted by Morgan Tocker |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


2010 Percona Training Schedule

After a nice long vacation, it’s time to unveil our destinations for public classes in 2010.  We are now offering a course for Developers as well as DBAs.  The dates are:

Do you run a meetup group in one of the cities listed? Get in touch! I would be happy to come along and give a presentation, or just answer your MySQL questions over a dinner or beer.  You can reach me via @percona or email.

Entry posted by Morgan Tocker |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Paul McCullagh answers your questions about PBXT

Following on from our earlier announcement, Paul McCullagh has responded with the answers to your questions – as well as a few I gathered from other Percona folks, and attendees of OpenSQL Camp. Thank you Paul!

What’s the “ideal” use case for the PBXT engine, and how does it compare in performance?  When would I use PBXT instead of a storage engine like MyISAM, InnoDB or XtraDB?

Unfortunately it is not possible to point to a specific category of applications and say, “PBXT will be better here, so try it”.  PBXT is a general purpose transactional storage engine, designed to perform well on a broad range of tasks, much like InnoDB.  However, PBXT’s log-based architecture makes performance characteristics different to both MyISAM and InnoDB/XtraDB. Tests show that PBXT’s performance is similar to InnoDB but, depending on your database designed and the application, it can be faster.

PBXT is a community project and, of course, we depend on users trying it out. In the long run, this will determine to what extent we are able to continue to develop and improve the engine.  So, despite this rather vague answer, we are hoping that more people try it out, and work with us to improve the engine as necessary.  My thanks to all who are already doing this! :)

I think I remember reports that PBXT (at an early stage) out performed InnoDB with INSERTS and UPDATES (but not SELECTS). That would make PBXT very interesting for non-SELECT-intensive applications (finance, production management etc.) in my opinion.  Is this the case, and do you have any recent benchmarks available?

This is no longer necessarily the case. For example a test ( by Mark Callaghan shows that PBXT can actually out perform InnoDB with SELECTs under circumstances.

The implementation of full-durability has changed the performance characteristics of PBXT from “MyISAM-like” to more InnoDB-like. Originally PBXT was conceived as an engine that would be somewhere between MyISAM and InnoDB in both performance and features. The early version of PBXT was not fully durable (equivalent to innodb_flush_log_at_trx_commit=2).

A major change was completed at the beginning of last year with the implementation of full-durability. In doing this it was important to keep the log-based architecture which was the reason for the high write performance of earlier versions.

Traditional transactional implementations suffer from the problem that a backlog of asynchronous writes accumulate until it swamps the engine. There has been a lot of work on both InnoDB and XtraDB to solve this problem. The key words here are fuzzy and adaptive checkpointing (the former, originally implementation by Heiki for InnoDB, and the latter, an excellent addition to XtraDB).

Both methods improve the management of the asynchronous writes. The idea behind the log-based solution, on the other hand, is to avoid the accumulating a backlog of asynchronous writes, but writing synchronously.

Although write performance is comparable with InnoDB, I am not entirely convinced that PBXT’s implementation of the log-based I/O is optimal at this stage. This is ongoing work for PBXT 1.5.

Morgan notes: As well as Adaptive Checkpointing, Oracle has also been working on Adaptive Flushing for the InnoDB Plugin. The engine being ‘swamped’ problem that Paul is referring to is best described visually – see this post for more info.

What were the hard decisions or trade-offs that you had to make when designing PBXT?

If you read the white paper from 2006 ( you will notice that the original design was uncompromisingly MVCC-based.  Some of this has been changed to make PBXT more InnoDB-like, but other principles have remained.

Pure-MVCC does not do any locking. Read locks are not required because each transaction effectively gets its own snapshot of the database. And write locks are not acquired when updating. Instead, the application can hit a “optimistic lock error” if a record is updated by another user.

Now PBXT does acquire locks for 2 reasons: to support SELECT FOR UPDATE, and to avoid optimistic locking errors. This makes PBXT’s behavior identical to InnoDB in REPEATABLE READ mode.

On the other hand, there are currently no plans to implement InnoDB style “gap locking”. Gap locking effectively involves locking rows that do not exist. This, in turn, means that PBXT transactions are not SERIALIZABLE.  A result of this is that statement-based replication is not supported by the engine.

Another hard decision was not to implement clustered indexes which I mentioned in more details later.

How does online backup work in PBXT, and is incremental backup possible?

A recent version of PBXT (1.0.09) supports the MySQL Backup API which was originally implemented in MySQL 6.0. This feature is now scheduled for an upcoming version of MySQL 5.4.

The Backup API makes it possible to pull a consistent snapshot of an entire database even when tables use different engine types. The API does not yet support incremental backup, but this is planned.

Internally this feature is implemented by PBXT using an MVCC-based consistent snapshot.

Does PBXT have a maintenance thread like InnoDB’s main thread?

PBXT has several system threads, that are responsible for various maintenance tasks. The most important of these are the “Writer”, the “Sweeper” and the “Checkpointer”;

  • The Writer transfers data from the transaction log to the database table files. This task runs constantly and is the main source of asynchronous I/O in the engine.
  • The Sweeper is a thread unique to the PBXT architecture. It’s job is to clean up after a transaction. Basically it recognizes which record versions are no longer needed and deletes them from the database.
  • The Checkpointer flushes the data written by the Writer to disk. It is also responsible for writing consistent snapshots of the index files to disk. The frequency of checkpoints, as with other engines like InnoDB, determines the recovery time.

Does PBXT support clustered indexes?

No, currently it does not. This is one of the original design decisions (as raised by a previous question).  Two things contributed to this decision:

  • Supporting clustered indexes would have made the implementation of PBXT more complicated than I would have liked. My goal was to make PBXT as simple as possible, so that it is easy to maintain the code and add features.
  • I believe clustered indexes are becoming less relevant with the rise of Solid State technology. As random read access times decrease clustering of data will become less and less important.

What is the page size in PBXT, and can it be tuned?

PBXT uses 16K pages for the index data and (approximately) 32K pages for the table data. Both sizes can be set using compile time switches. However, if the index page size is changed, then the indices need to be rebuilt, which can be done by REPAIR TABLE.  The table data page size does not require a rebuild because a page of records in the table is just a group of records (not an actual fixed length page).

Are there any differences in the PBXT implementation of MVCC that might surprise experienced InnoDB DBAs?  Also – In MVCC does it keep the versions in indexes, and can PBXT use MVCC for index scans?

If you are using InnoDB in REPEATABLE READ mode, then there is essentially no difference in the isolation paradigm between the two engines.

REPEATABLE READ is often preferred over SERIALIZABLE mode because it allows a greater degree of concurrency while still providing the necessary transaction isolation. So I do not consider the lack of serializability as a serious deficit in the engine. And, fortunately MySQL 5.1. supports row-based replication which makes it possible to do replication while using REPEATABLE READ.

PBXT does use MVCC to do index scans. Basically this means that all types of SELECTs can be done without locking.

Morgan notes: Indexes not using MVCC is one of the main differences in the Falcon storage engine.

PBXT supports row-level locking and foreign keys. Does this create any additional locking overhead that we should be aware of?

Firstly, PBXT does not acquire read locks. A normal SELECT does not lock at all. In addition, an UPDATE or DELETE only acquires a temporary row-lock. This lock is released when the row is updated or deleted because the MVCC system can detect that a row has been changed (and is therefore write locked).

This means that PBXT does not normally need to maintain long lists of row-level locks. This is also the case when a foreign key leads to cascading operations which can affect thousands of rows.

The only case you need to be aware of is SELECT FOR UPDATE. This operation acquires and holds a row-level lock for each row returned by the SELECT. These locks are all stored in RAM. The format is quite compact (especially when row IDs are consecutive) but this can become an issue if millions of rows are selected in this manner.

I should also mention that the consequence of this is that SELECT … LOCK IN SHARE MODE is currently not supported.

When I evaluate a storage engine my key acceptance criteria are things like backup, concurrency, ACID compliance and crash recovery. As a storage engine developer, what other criteria do you think I should be adding?

Yes, I think you have mentioned the most important criteria. What I can add to this list are 3 things that make developing a storage engine extremely demanding: performance, stability and data integrity.

Of course, as a DBA or database user these aspects are so basic that they are taken for granted.

But engine developers need to keep performance, stability and data integrity in mind constantly. The problem is, they compete with each other: increasing performance often causes instabilities that then have to be fixed. How to optimize the program without compromising data integrity is a constant question.

Relative to maintaining performance, stability and data integrity, adding features to an engine is easy. So I would say that these are the criteria that concern a developer the most.

MySQL supports a “pluggable storage engine API”, but it seems that not all the storage engine vendors are able to keep all their code at that layer (Infobright had to make major changes to MySQL itself).  What war stories can you report on in plugging into MySQL?

Unfortunately the “war” continues. I have already received several e-mails that PBXT does not compile with the recently released MySQL 5.1.41!

Any dot release can lead to this problem, and I think PBXT is fairly moderate with its integration into MySQL.

My main advantage: I have been able to avoid modifying any part of MySQL to make the engine work. This means that PBXT runs with the standard MySQL/MariaDB distribution.

But this has required quite a bit of creative work, in other words, hacks.

One of the main problems has been running into global locks when calling back into MySQL to do things like open a table, create a session structure (THD) or create a .frm file.

One extreme example of this is PBXT recovery. When MySQL calls the engine “init” method on startup it is holding the global LOCK_plugin lock. In init, the engine needs to do recovery. In PBXT’s case this means opening tables (reading a .frm file), which requires creating a THD. The code to create a THD in turn tries to acquire LOCK_plugin!

Unfortunately a thread hangs if it tries to acquire the same mutex twice, so this just does not work!

We went through quite a few iterations (MySQL code was also changing during the development of 5.1) before we came up with the current solution: create a background thread to do recovery asynchronously. So the thread can wait for the LOCK_plugin to be unlocked before it continues.

The affect is that the init function returns quickly, but the first queries that access PBXT tables may hang waiting for recovery to complete.

PBXT seems to have very few configuration parameters.  Was this an intentional design decision, and do you see it creating opportunities for you in organizations with less internal IT-expertise?

No, this is not by design.

While I try to only add tuning parameters that are absolutely necessary, PBXT is not specifically designed to be self-tuning, because I believe that is a very hard problem to solve in general.

Tuning parameters are often added to an engine in response to performance problems in particular configurations. This is not necessarily a bad thing because it provides DBA’s with the tools they need.

My goal for PBXT in this regard is twofold:

  1. I hope that most installations can get away with setting a minimum of tuning parameters, in particular, just the cache values.
  2. On the other hand, I aim to provide expert tuning parameters for installations that need to extract maximum performance from the hardware. This work is ongoing.

Morgan notes: There are more in InnoDB/XtraDB now than there were three years ago. This is probably something that emerges over time as we get to understand more about an engine.

Entry posted by Morgan Tocker |

Add to: delicious | digg | reddit | netscape | Google Bookmarks


Interviews for InfiniDB and TokuDB are next

I forwarded on a list of questions about PBXT to Paul McCullagh today.  While Paul’s busy answering them, I’d like to announce that Robert Dempsey (InfiniDB storage engine) and Bradley C. Kuszmaul (TokuDB storage engine) have also accepted an interview. If you have any questions about either storage engine, please post them here by Friday 20th November.

Entry posted by Morgan Tocker |
No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Powered by WordPress | Theme: Aeros 2.0 by