High Availability MySQL

Error injection tests for InnoDB would be nice

2012-01-26T20:27:00.000-08:00

I am trying to figure out why an InnoDB table was lost when a DDL statement failed. I think it was a RENAME TABLE statement. I have yet to find the root cause but I did find that InnoDB doesn't report some errors when RENAME fails so the user thinks that the table was renamed, the FRM file is renamed, and the ibd file is not renamed. This is only a problem for files not in the InnoDB system tablespace so --innodb_file_per_table=1 must be used. This is bug 64144.

As I wrote in a previous blog post, it is time to add error injection tests to InnoDB.

Who wants to write a storage engine?

2012-01-12T07:07:00.000-08:00

LevelDB is here. It might make an interesting storage engine for MySQL as it has many performance benefits at the cost of a few limitations: no multi-statement transactions and MyISAM-like concurrency. I doubt we will ever get a production quality LevelDB storage engine for MySQL because the storage engine API is hard, so hard that such projects require funding. This is unfortunate.

LevelDB might be a great fit for MongoDB. MongoDB doesn't need multi-statement transactions. Both are limited by 1-writer or N-reader concurrency, but writes to database files are much faster with LevelDB because it doesn't do update in place. So LevelDB doesn't lose as much performance for IO-bound workloads by doing 1-writer or N-readers and my guess is that this could make MongoDB much better at supporting IO-bound workloads.

Marketing a bug in 3 easy steps

2011-12-02T18:42:00.001-08:00

File a request for crash recovery tests and wait a few months
File a request for error injection tests during InnoDB DDL and wait a few months
Lose a table during alter table because untested error handling is incorrect and blog about it

Great work, bug #12704861 was fixed!

2011-11-18T14:15:00.001-08:00

MySQL Community Server 5.1.60 has been released and I am very happy because the release notes state that bug #12704861 has been fixed. I know this bug quite well. As my readers are very busy let me provide all of the details that have been made available to the community:

InnoDB Storage Engine: Data from BLOB columns could be lost if the server crashed at a precise moment when other columns were being updated in an InnoDB table. (Bug #12704861)

Unexplained jumps in Seconds_Behind_Master

2011-10-19T23:05:00.000-07:00

I am trying to understand why a server would go from 0 to 45 and then back to 0 seconds of replication lag as reported by the Seconds_Behind_Master column in SHOW SLAVE STATUS output. This occurs over a few seconds so there isn't a statement that runs for 45 seconds on the slave. I then compared consecutive SET TIMESTAMP values in the binlog and the absolute value of the differences is at most 2 seconds.

Has anyone else been confused by this? I filed bug 62839 and think there is a race condition in the code that computes the value for Seconds_Behind_Master. If I am correct about this then we need to fix the problem. Replication lag is a big problem for many of us and reporting a value that is much larger than the actual value is bad PR.

Multi-master, NoSQL and MySQL

2011-10-13T07:57:00.000-07:00

The MySQL family has been innovating rapidly. New features need names and sometimes those names are confusing. Describing something as multi-master or a NoSQL solution has confused me.

Multi-master requires one of conflict prevention, conflict resolution or faith. MySQL Cluster provides both conflict prevention and resolution as described in these great posts. Regular MySQL has minimal support for conflict prevention (auto-increment-offset can prevent insert conflicts) and thus requires faith that the application does the right thing. Regular MySQL gets conflict prevention via synchronous replication when used with Galera.

There has been talk of adding support to replicate from multiple masters into one slave. We have yet to agree on a name for this. It has been called fan-in, multi-source and multi-master. I hope multi-master isn't reused for this.

You can now use HandlerSocket and the memcache API to access MySQL. But taking away SQL doesn't make this a NoSQL solution. And in the case of MySQL Cluster it already has many of the properties of a NoSQL solution, getting the memcache API is just gravy. The new APIs will allow some workloads to get more read throughput from MySQL. They won't do much for write throughput because those bottlenecks are in InnoDB and independent of SQL/NoSQL. Note that the memcache plugin for InnoDB has tuning options that are good for benchmarks but might not be good for production. There are options that reduce the frequency at which transactions are committed and started. These options allow the transaction start and commit bottlenecks to be avoided at the cost of stale reads and async commits -- see daemon_memcached_r_batch_size and daemon_memcached_w_batch_size.

I don't think the lack of SQL access is what makes many of the NoSQL products compelling. The big thing for me is that they can reduce TCO because you will spend less time managing a DBMS that has these features:

no sharding required or sharding and resharding are easy
no failover via multi-master or failover is automated
much less downtime on schema change or no schema changes required

Eventual consistency is supported by some of the NoSQL products. Given their simpler data models I expect it to be supported by more. This feature is critical for a service with users distributed around the world as response time can be reduced when users access a local database.

Regardless of the API (SQL or NoSQL), regular MySQL doesn't have any of the features listed above. MySQL Cluster already has all of the features listed above. But there is still hope. Tungsten has automated failover, Galera continues to get better, official MySQL can get automated failover once global transaction IDs are supported. Semi-sync replication is supported and I think a few things can be done to make that much more useful for HA systems. There are tools to do online schema changes but I think that is better done in the MySQL server.

Read Amplification Factor

2011-08-11T08:53:00.000-07:00

Flash devices are described in part by their write amplification factor (or WAF). When the OS writes a page once the device might write it more than once and this multiple is the write amplification factor. The WAF isn't always described in marketing and even if it were the value you get in production is workload dependent.

Variations of the log-structured merge tree have been used by many new storage servers including HBase, Bigtable, Cassandra and leveldb. These servers append changes (delete, insert, update) to the end of a file rather than in place. To find one row by key value with an LSM the server might have to read from from multiple files or multiple locations within one file to fine one. I have been calling this the read penalty because a workload is very likely to do more disk reads when using an LSM than when using an update-in-place engine but I think that read amplification factor (or RAF) might be a better phrase. If a workload does 100 disk reads on InnoDB and 120 disk reads on an LSM then the RAF is 1.2. The RAF matters even for many write-optimized servers because an update intensive workload requires many random disk reads. Although an LSM can avoid some of the reads when the update is a replace or when the operation is commutative and doesn't require an immediate result. For example an update that increments a row can log +1 when the request doesn't need to return the old value.

Many LSM implementations use a bloom filter to reduce the RAF. The bloom filter prevents some reads from files known not to have data for a given key. A bloom filter only works for point lookups. It cannot be used for a range scan and the RAF for a workload will be at its worst when you map a relational schema directly to HBase (1 row in InnoDB --> 1 row in HBase). Fortunately many of the LSM implementations support schemas in which more data is consolidated into one row and in many cases something that requires a range scan in a SQL RDBMS will use a point lookup in HBase.

There are new products (TokuDB, Acunu, maybe RethinkDB) that claim to be better than an LSM in part because their RAF is much closer to one for both point lookups and range scans. By closer to one I mean that there is (almost) no read penalty. This should be easy to verify with a production workload.

While there are very interesting performance models described in the literature I use a very simple one when considering the read amplification factor. In my model all levels of a tree-structured index are in RAM except for the lowest level. In this model a point lookup with an update-in-place DBMS does at most one disk read from an index leaf page excluding access to external/overflow pages for LOB columns and other special cases. For something that claims to be better than an update-in-place DBMS I want to know how many index leaf pages are read in the worst and average cases.

This Percona patch saved me

2011-08-10T11:38:00.000-07:00

If you use InnoDB and have long-running select statements then the InnoDB undo space can grow large because purge can't advance beyond the longest open transaction. If this is blocked for too-long then pages to be purged might leave the InnoDB buffer cache and the purge thread will have to do many disk reads. As the InnoDB purge process is single-threaded it might not be able to catch up and a server can use too much disk space for a very long time.

I experienced this problem at work. I modified innochecksum to report the number of pages by type and it was easy to see that undo accounted for most of the ibdata1 file (a few hundred GB). As InnoDB doesn't shrink ibd files there is no way to get this space back on a running server but I would like to prevent the problem in the future.

The problem was solved by using the multi-threaded purge patch from XtraDB for MySQL 5.1. The patch isn't too much code so I felt comfortable testing it and was able to review it. Since deploying it I no longer have to worry about servers using too much space for undo because of purge lag.

Technical debt

2011-08-02T21:06:00.000-07:00

Last time I checked there was one test in the MySQL test suite (mtr) that covers one case for crash recovery. Perhaps there is a private test suite. Given that I modify InnoDB and replication code and that I frequently debug crashes at work I wish there were more tests. I added many tests in the Facebook patch for crash recovery to confirm that recovery works for the replication slave, replication master and InnoDB. While doing so I found at least one bug in rpl_transaction_enabled. While working on global transaction IDs Justin found a few bugs in official MySQL that prevented recovery after the slave crashed. These were fixed in official MySQL 5.1 so there is a lot of value in having tests like this. But there is a lot more that should be tested including:

crash recovery during DDL. There are windows where recovery is not possible given that many DDL commands are not atomic between InnoDB and the FRM file.
crash recovery during DDL on a partitioned table. I know from debugging crashes that a file named ddl.log is written.
crash recovery on a replication master. The master uses XA to keep the binlog and InnoDB in sync. But there are not tests to confirm that the right thing is done after a crash at each step of the two-phase commit process. The Facebook patch has such tests so I trust that the code in official MySQL but these tests should be in mtr.
crash recovery on a replication slave. Unfortunately, there are known race conditions in replication slave state updates that might be fixed in 5.6.3 so all of the possible failures cannot be tested. But updates to the replication state index files can be crash proof and I think they are now after the Google team reported the problems while testing global transaction IDs.
crash recovery for InnoDB. I don't think I have to write anything about this.

MySQL has extremely useful DEBUG macros for making the server crash at specific points in the code. This makes it very easy to add deterministic crash tests. The test suite was updated within the last year to allow the server to be crashed without failing a test. Unfortunately these features are rarely used in the official test suite.

I hope this changes. Tests with deterministic failures are just the start. The next step is a test with randomized failures. Unfortunately it won't be as easy to add this to mtr as such a test is much easier to write in Perl or Python. We have a test suite at work where a random workload is run against the master and the master is killed at random times. A slave replicates from that master and after each kill tables on the master and slave are compared.

And now some of the recovery bugs that I have encountered including problems that have been fixed:

And open bugs and feature requests

InnoDB crash recovery changes: make DDL in MySQL 'atomic'
assert fails in row_search_for_mysql on TRX_ISO_READ_UNCOMMITTED
Prevent relay-log.info from getting out of sync with transactions on slave crash or 26540
26489 will be the proxy for the binlog event checksums feature request. PITR isn't possible after a crash when binlog events are incorrect but you can't detect that.

Is this a new feature?

2011-04-29T17:08:00.000-07:00

Is this an amazing new feature or the next step after the change to bugs.mysql.com? I was about to file a bug report to improve the MySQL manual, but that won't happen now:

Go to page for MySQL Reference manual
Type text in "Search Manual" box on the left-hand side of the page
End up at login.oracle.com sign in page

O'Reilly MySQL & more 2011 was great

2011-04-17T17:14:00.000-07:00

I hope this is held again in 2012 and that Oracle sends more developers to the conference. I had useful conversations with the four developers and development managers who attended from Oracle. I also had great discussions with Percona, Monty Program, Continuent and many others. At home I sit and type, at the conference I stand and talk. It is a welcome change.

The MySQL team has been very productive recently with many bug fixes, many features implemented, a great 5.5 release and what appears to be an even better 5.6 release. The changes in 5.6 are a really big deal. I can't wait to stop porting rpl_transaction_enabled to get crash-proof slave state.

The value-added community continues to push the state of the art for those who can't wait for the great new features to be GA or for those with special problems. I am interested in trying parallel replication apply from Tungsten, working with Monty Program to improve monitoring in MariaDB and working with Percona to improve InnoDB quality-of-service for high-throughput OLTP.

I have begun to catch up on my reading to figure out what has changed for 5.6. I probably need another week to finish reading the many useful blogs and presentations. DimitriK published a 5-part performance report that I have yet to start (1, 2, 3, 4, 5). I am happy to find that few more MySQL developers at Oracle have begun blogging (Oystein, Didrik, Luis, Olav). How do I type the accented "O" in Oystein?

First the InnoDB changes:

Information schema system tables - InnoDB has moved a lot of data to IS tables. The ones listed here are not the most interesting to me but the migration in general makes life easier for me. I don't know if these are new in 5.6, but they are more interesting to me than the ones described in the InnoDB blog: INNODB_BUFFER_PAGE, INNODB_BUFFER_PAGE_LRU, and INNODB_BUFFER_POOL_STATS.
No more kernel_mutex - Wow! This is a huge deal for multi-core performance and will enable even more improvements in future releases. As part of the admission control feature we have been trying to reduce kernel_mutex contention and noticed that 5.5 already was much better for that.
Persistent index cardinality statistics - when a server is restarted all index cardinality stats are computed in MySQL 5.1 and without the Facebook patch, this is serialized because of LOCK_open. That can create too many stalls on restart. I assume this allows a DBA to populate the stats table manually which can be very useful for a deployment with many scale-out slaves to prevent query plans from changing between slaves. Other details are at Oystein's blog.
Multi-threaded purge - I want this right now. This and parallel replication apply make it possible to deploy large databases on slaves using disk setups that match what is available on a master. Otherwise you must read Yoshinori's slides to find the workaround.
Data dictionary LRU helps if you have a lot (or too many) tables.
Page cleaner thread - This is a good step but I wonder if it is enough and need to read code or wait for a Percona performance report. Has anything been done to prevent stalls when the async flush tries to flush too many pages in one call? The problem is that all dirty pages from an extent are flushed when at least one page with a too-old LSN is in the extent. Flushing neighbor pages can increase the number of pages flushed by up to 64X. I have seen benchmark servers stall for 60+ seconds while 200,000+ pages were flushed when the async limit was reached. This can be very painful in servers that are able to cache the database and fill with many dirty pages.
memcached API for InnoDB - this should enable HandlerSocket like performance while supporting an API that is already supported by most clients (PHP, Java, Python, ...). I think that non-SQL interfaces to InnoDB will be a big deal.
metrics table for InnoDB counters - I spent too much time adding counters to SHOW STATUS for InnoDB. Now you don't have to.

The InnoDB team has done a great job of making it easy to understand the changes. Getting docs for the replication changes required a bit more searching as they were not referenced from the main Oracle announcement. I added links to worklog entries and blogs by Mats and Luis. I am very interested in many of the replication changes that match and exceed what was in the Google patch.

parallel replication apply - I am confused. The labs page and worklog state that this is for RBR. I want parallel apply for SBR to overcome slave replication lag from IO bound slaves. Parallel apply provides parallel IO requests. Per the blog by Luis, I think this supports SBR.
system tables for slave state - I don't know if WL2775 describes what was implemented. Feedback from gmaxia makes me wish for more docs. Hopefully I can stop porting rpl_transaction_enabled and begin to use this instead. I need to read the blog by Mats.
replication checksums - I want binlog event checksums. I have not needed them for a long time since a certain bug was fixed but I would rather not worry about that problem again. Mats also wrote about this.
informational log events - I need the original SQL including its query comment in the binlog if I am to use RBR. With this feature I have one less excuse for not trying RBR.
remote binlog backup - We already have this in the 5.1 Facebook patch thanks to a backport by Harrison. It lets you archive the binlog almost as soon as it is written.
universal group identifiers - This might be the equivalent of global transaction IDs from the Google patch.
optimized RBR logging - This will be a big deal for tables with BLOB columns that get frequent updates to non-BLOB columns. I have a few of those.
Time delayed replication - I don't need this but many others will.

There also appear to be many useful changes in the Performance Schema. Alas, it isn't easy to figure out what has changed from reading the big announcement. I haven't been a fan of the P_S given my primary need is for aggregated workload stats (per-table, per-index, per-user) and that the user_stats patch from Facebook, Percona, MariaDB and Google has had that for many years in a form that is trivial to use (no setup) with very low overhead (it is always enabled in my benchmarks) and will always be enabled in production. It also is very easy to use -- select * from easy_to_remember_table_name. I hope the P_S can also provide that. Mark and Marc have begun to write more about this. I hope that continues.

Where have the bugs gone?

2011-02-20T16:18:00.000-08:00

The incoming bug rate over the past 7 days is much lower than the past 14 days. Maybe this is a blip. But commits to trunk have begun to use 8-digit bug numbers that do not reference entries in bugs.mysql.com. I think something has changed but nothing has been announced. I like bugs.mysql.com. We all benefit by sharing bug reports and I contributed a lot of content. It is also easy to search (use "site:bugs.mysql.com" on google searches).

Assuming there was a change maybe it doesn't matter to most community members.

This happens when you don't have a condition variable

2011-02-11T16:10:00.000-08:00

Did Windows NT not have condition variables? This might be an artifact of that. It shows what happens when you don't have a condition variable.

stop_ibuf_merges:

   mutex_enter(&fil_system->mutex);

   space = fil_space_get_by_id(id);

   if (space != NULL) {
   space->stop_ibuf_merges = TRUE;

   if (space->n_pending_ibuf_merges == 0) {
   mutex_exit(&fil_system->mutex);
   count = 0;
   goto try_again;
   } else {
   if (count > 5000) {
   ut_print_timestamp(stderr);
   fputs(" InnoDB: trying to delete tablespace ", stderr);
   ut_print_filename(stderr, space->name);
   }

   mutex_exit(&fil_system->mutex);

   os_thread_sleep(20000);
   count++;

   goto stop_ibuf_merges;
   }

So long kernel mutex

2011-02-04T06:22:00.000-08:00

If you are willing to look there are a few good changes in trunk for InnoDB. A frequent source of mutex contention in InnoDB, kernel_mutex, has been replaced with a rw-lock for the transaction system (see trx_sys_struct), two mutexes in srv_sys_struct and possibly other mutexes and rw-locks. The dulint struct has been replaced with a native 8-byte int. All of these changes should make InnoDB more efficient for workloads with a large number of concurrent transactions and help with bug 49169.

I wish there were a better to track changes in InnoDB. Right now my tools are luck and recursive diff.

Odd MySQL fact of the day

2011-01-18T14:50:00.000-08:00

The LogEvent class has four methods named read_log_event. This is the base class for binlog events. I might prefer different names. I was extremely confused by servers on which the value of Seconds_Behind_Master rapidly fluctuated between 0 and a large value. This behavior is documented, I just had not read that page recently. Fortunately, the db operations experts explained it to me. So I am adding a few more variables to make it easier to monitor replication including:

Counters for the number of binlog events written by the IO thread and run by the SQL thread.
Counters for the number of bytes written by the IO thread and run by the SQL thread.
A timer for the number of seconds that the SQL thread waits for the IO thread to provide more events.

And if you are curious, these are the read_log_event methods.

One:
  static Log_event* read_log_event(IO_CACHE* file,
   pthread_mutex_t* log_lock,
   const Format_description_log_event
   *description_event);

Two:

static int read_log_event(IO_CACHE* file, String* packet,

pthread_mutex_t* log_lock);

Three:

static Log_event* read_log_event(IO_CACHE* file,

const Format_description_log_event

*description_event);

Four:

static Log_event* read_log_event(const char* buf, uint event_len,

const char **error,

const Format_description_log_event

*description_event);

Performance monitoring in MySQL

2010-12-28T12:02:00.000-08:00

There are several types of performance monitoring. Some of them are made easier when workload metrics are summarized by database user, table and index. The Google patch added SHOW TABLE_STATISTICS, SHOW INDEX_STATISICS and SHOW USER_STATISTICS to MySQL several years ago. Since then they have been ported and improved in the Facebook patch, MariaDB and Percona Server. I think that Eric Bergen has also ported it. I guess you could say the community has spoken.

I like these features because they have been extremely useful to me. They are also simple to use and have little impact on performance. Most of what you need to know about them can be determined by listing the columns in the tables. But more than anything else they are easy to use.

The Facebook patch has these columns for user_statistics:
USER_NAME
BINLOG_BYTES_WRITTEN
BYTES_RECEIVED
BYTES_SENT
COMMANDS_DDL
COMMANDS_DELETE
COMMANDS_HANDLER
COMMANDS_INSERT
COMMANDS_OTHER
COMMANDS_SELECT
COMMANDS_TRANSACTION
COMMANDS_UPDATE
CONNECTIONS_CONCURRENT
CONNECTIONS_DENIED_MAX_GLOBAL
CONNECTIONS_DENIED_MAX_USER
CONNECTIONS_LOST
CONNECTIONS_TOTAL
DISK_READ_BYTES
DISK_READ_REQUESTS
DISK_READ_SVC_USECS
DISK_READ_WAIT_USECS
ERRORS_ACCESS_DENIED
ERRORS_TOTAL
MICROSECONDS_CPU
MICROSECONDS_WALL
QUERIES_EMPTY
ROWS_DELETED
ROWS_FETCHED
ROWS_INSERTED
ROWS_READ
ROWS_UPDATED
ROWS_INDEX_FIRST
ROWS_INDEX_NEXT
TRANSACTIONS_COMMIT
TRANSACTIONS_ROLLBACK

The Facebook patch has these columns for table_statistics:

TABLE_SCHEMA

TABLE_NAME

TABLE_ENGINE

ROWS_INSERTED

ROWS_UPDATED

ROWS_DELETED

ROWS_READ

ROWS_REQUESTED

ROWS_INDEX_FIRST

ROWS_INDEX_NEXT

IO_READ_BYTES

IO_READ_REQUESTS

IO_READ_SVC_USECS

IO_READ_SVC_USECS_MAX

IO_READ_WAIT_USECS

IO_READ_WAIT_USECS_MAX

IO_READ_OLD_IOS

IO_WRITE_BYTES

IO_WRITE_REQUESTS

IO_WRITE_SVC_USECS

IO_WRITE_SVC_USECS_MAX

IO_WRITE_WAIT_USECS

IO_WRITE_WAIT_USECS_MAX

IO_WRITE_OLD_IOS

IO_INDEX_INSERTS

The Facebook patch has these columns for index_statistics:

TABLE_SCHEMA

TABLE_NAME

INDEX_NAME

TABLE_ENGINE

ROWS_INSERTED

ROWS_UPDATED

ROWS_DELETED

ROWS_READ

ROWS_REQUESTED

ROWS_INDEX_FIRST

ROWS_INDEX_NEXT

IO_READ_BYTES

IO_READ_REQUESTS

IO_READ_SVC_USECS

IO_READ_SVC_USECS_MAX

IO_READ_WAIT_USECS

IO_READ_WAIT_USECS_MAX

IO_READ_OLD_IOS

How are index-only scans implemented in InnoDB?

2010-11-23T06:46:00.000-08:00

There have been interesting discussions in the PostgreSQL community about adding support for index only scans. On several occasions people were curious about how InnoDB supports this. A recent post by the InnoDB team is an excellent overview. A brief summary of that post and other material is:

records in the clustered (primary) index store hidden columns (DB_TRX_ID, DB_ROLL_PTR)
records in the non-clustered (secondary) index do not store hidden columns
records in clustered and non-clustered indexes have a delete-mark flag
records are not updated in the secondary index, they are delete-marked on delete, inserted on insert and delete-marked/inserted on update
delete-marked records are removed from indexes by the purge thread when it is safe to do so

When a secondary index page is read, if the max transaction ID on the page is less than the max transaction ID for which all transactions are visible to the reading transaction (low-water mark, up_limit_id), then the page can be used as is and the page read is index-only. If this condition is not true, then for any entry read from this page the record is read from the clustered index page to determine whether the index entry is visibile. In that case the secondary index read is not index only. Index only matters because when things are not index only there can be an additional random disk read to the clustered index for each entry read from the secondary index.

The max transaction ID for which all transactions are visible to the reading transaction is described as the low-water mark and assigned to the up_limit_field in the read view (read_view_struct). This is the max transaction ID for which there are no unresolved transactions when the reading transaction starts. If there is a long-open transaction when the reading transaction starts, then up_limit_id will be less than the transaction ID of the long-open transaction.

I began to read the code for this today as I want to add a counter for the number of secondary index page reads that are and are not index only. If you want to read the code too the function lock_sec_rec_cons_read_sees determines whether all entries on a secondary index page are definitely visible to a transaction (read view).

If you are interested in this topic, I recommend these books:

Monty style

2010-11-19T07:59:00.000-08:00

I make bad jokes about Monty-style code. I don't like to read or modify it. I love to run it in production. It never crashes. It never leaks memory. I exaggerate a little bit but not too much. It is remarkably stable.

This is an amazing accomplishment. Alas some of the senior developers who did that at MySQL have since left. Another senior developer left this week. I hope this trend does not continue.

Everyone can be replaced. Smart people can be found. But smart and productive people are not as widely available and it takes a while to figure out how things are done in MySQL. I know because I have made a lot of mistakes while trying to make things better.

Perhaps the Google response is appropriate. Everyone gets a 10% raise and a $1000 holiday bonus.

I did not submit this to http://planet.mysql.com

More about MySQL at Facebook

2010-10-26T11:04:00.000-07:00

The Facebook database teams will describe how MySQL is used at Facebook. Join us on Tuesday, November 2. The performance, operations and engineering teams will describe work in progress to keep MySQL happy. The event is from 7pm to 9pm (PDT or UTC -7).

The MySQL Plugins Development book is excellent

2010-09-30T18:37:00.000-07:00

Packt Publishing gave me a copy of MySQL 5.1 Plugins Development to review. The book is written by two people who are MySQL experts -- Sergei Golubchik and Andrew Hutchings. I know Sergei because he answers a lot of my questions on the MySQL Internals mail list. I wasn't in the best mood when I opened the e-book as I worked late last night. My mood was much better after reading a few pages. The book is amazing. The content, presentation and editing are excellent. The book is full of relevant examples that show you how to build plugins including source code and tips on compiling. The text carefully explains all of the steps required to build each of the plugins.

I wish I had this book several years ago. It would have saved me a lot of trouble. But from the quality of the book I suspect that they have been working on it for several years.

Thank you Sergei and Andrew. This is a great addition to the MySQL community.

The target audience for this book is someone who wants to write code that will run within the mysqld process. This includes user defined functions (UDF) to be called by SQL statements, daemon plugins that can run code using a dedicated thread in the server process, information schema plugins to expose data in INFORMATION_SCHEMA, full-text parser plugins and storage engine plugins. There are examples for three storage engines. The first engine is simple and only supports read-only tables. The second example supports read-write tables without indexes and uses HTML as the file format. The final example builds a storage engine using Tokyo Cabinet.

My team has put a lot of things into the mysqld process. Some of those changes were even useful. This book would have prevented us from making a few mistakes. It even has an example to export the output from getrusage via SHOW STATUS. I added a similar patch to MySQL, but I don't think my change was as nice.

Drizzle beta is here!

2010-09-29T16:41:00.000-07:00

Congratulations!

Can we please tone down the marketing? It is great to see a community project grow, especially in the MySQL family. But you are judged by successful deployments, not by the class libraries used by your project.

Drizzle is only more reliable than MySQL when it keeps the replication log and InnoDB in sync during crash recovery. But it does not do that today. Official MySQL supports this on a master via sync_binlog. MySQL slaves do this today via rpl_transaction_enabled which is available in the Facebook and Google patches. I think it is also available in Percona Server, MariaDB and XtraDB.

I have been told that Drizzle was designed for multi-core scalability. I am not sure what that means on real workloads or benchmarks. I know that there are some bottlenecks in InnoDB that have nothing to do with the code above it (MySQL, Drizzle). I also know that MySQL has made huge strides this year and MySQL 5.5 far exceeds my expectations. Alas I cannot validate whether "designed for scalability" results in performance that is better than MySQL 5.5 as I cannot build Drizzle on my servers due to the dependency on gcc 4.2 or greater.

Oh, no - MongoDB can be fast for key-value stores

2010-09-14T11:59:00.000-07:00

I have broken my promise to stop writing about this. Sorry, but I had to correct my mistake. I ran three micro-benchmarks: get by primary key, get by secondary key and update by primary key. MySQL had a higher peak QPS for all of them. Alas, the results for get by primary key were skewed because pymongo, the Python driver for MongoDB, uses more CPU than MySQLdb, the Python driver for MySQL. The client host was saturated during the test and this limited peak QPS to 80,000 for MongoDB versus 110,000 for MySQL.

I repeated one test using two 16-core client hosts with 40 processes per host. For that test the peak QPS on MongoDB improved to 155,000 while the peak for MySQL remained at 110,000. That is an impressive result. The results for get by secondary key and update by primary key are still valid as the server host saturated on those tests.

Now I must consider rewriting the test harness in Java, C or C++ or I could add MongoDB support to sysbench. I prefer Python. In addition to under-reporting MongoDB peak performance it also under-reports MySQL performance. I am able to get 180,000 peak QPS using sysbench on one 16-core client host and mysqld on another versus 110,000 using the Python equivalent.

MySQL versus MongoDB - update performance

2010-09-13T22:19:00.000-07:00

This is the end of my public performance comparison of MongoDB versus MySQL, at least for the next few weeks. For these tests I used a 16-core x86_64 client host and an 8-core x86_64 server host, MySQL 5.1.50 with the Facebook patch, MongoDB 1.7.0 and Python clients. Ping between the client and server hosts was ~200us.

I tested 5 configurations:

inno.5150fb.b0.s0 - MySQL 5.1.50, the Facebook patch, binlog disabled
inno.5150fb.b1.s0 - MySQL 5.1.50, the Facebook patch, binlog enabled, sync_binlog=0
inno.5150fb.b1.s1 - MySQL 5.1.50, the Facebook patch, binlog enabled, sync_binlog=1
mongo.170.safe - MongoDB 1.7.0 and safe updates
mongo.170.unsafe - MongoDB 1.7.0 and unsafe updates

Note that with unsafe updates the client does not wait for the server to respond. It sends requests as fast as it can or until the buffer between the client and server is full. When a sufficient number of concurrent clients are used and the clients run for enough time the buffer becomes full and unsafe updates do not improve performance.

InnoDB has much better throughput when the binlog is disabled. The clients update rows selected at random from 2M rows. InnoDB is able to handle more of that load concurrently than MongoDB as a reader-writer lock is used that prevents concurrency within the database. Note that while InnoDB allows more of the update to be done concurrently, it isn't perfect as there are several global mutexes in MySQL/InnoDB including LOCK_open, kernel_mutex and the InnoDB buffer pool mutex. When the binlog is enabled by sync_binlog is disabled even more global mutexes are used. Finally, when the binlog is enabled and sync_binlog=1 then group commit is not enabled and updates are rate limited by the performance of fsync. In this case fsync is fast as a HW RAID card with battery backed cache was used.

This is output from PMP that demonstrates one source of mutex contention in mongod. The pileup occurs at mongo::MongoMutex which apprently implements the reader-writer lock.

48 mongo::connThread,thread_proxy,start_thread,clone

37 pthread_cond_wait@@GLIBC_2.3.2,boost::condition_variable::wait,mongo::MongoMutex::lock,mongo::receivedUpdate,mongo::assembleResponse

10 recv,mongo::MessagingPort::recv,mongo::MessagingPort::recv

2

1 select,mongo::Listener::initAndListen,mongo::listen,mongo::_initAndListen,mongo::initAndListen,main,select

1 select,mongo::Listener::initAndListen

1 pthread_cond_wait@@GLIBC_2.3.2,mongo::FileAllocator::Runner::operator(),thread_proxy,start_thread,clone

1 nanosleep,mongo::DataFileSync::run,mongo::BackgroundJob::thr,thread_proxy,start_thread,clone

1 nanosleep,mongo::ClientCursorMonitor::run,mongo::BackgroundJob::thr,thread_proxy,start_thread,clone

1 nanosleep

1 mongo::webServerThread,thread_proxy,start_thread,clone

1 mongo::SnapshotThread::run,mongo::BackgroundJob::thr,thread_proxy,start_thread,clone

1 mongo::interruptThread,thread_proxy,start_thread,clone

1 mongo::BtreeBucket::findSingle,mongo::ModSetState::createNewFromMods,mongo::_updateObjects,mongo::updateObjects,mongo::receivedUpdate,mongo::assembleResponse

Source code for MongoDB queries is listed below. The code to setup MongoDB and MySQL is described in the previous posts.

def query_mongo(host, port, pipe_to_parent, requests_per, dbname, rows, check, testname, worst_n, id):
  conn = pymongo.Connection(host, port)
  db = conn[dbname]
  signal.signal(signal.SIGTERM, sigterm_handler)

  gets = 0
  stats = SummaryStats(worst_n)

  while True:
   for loop in xrange(0, requests_per):

   target = random.randrange(0, rows)

   s = time.time()
   try:
   r = db.c.update({'_id': target}, {'$inc': {'k': 1 }}, safe=True)
   assert r['updatedExisting'] == True
   assert r['ok'] == 1

   stats.update(s)

   gets += 1

   except:
   assert got_sigterm

The my.cnf settings for MySQL except for the values of log_bin and sync_binlog:

innodb_buffer_pool_size=2000M

innodb_log_file_size=100M

innodb_flush_log_at_trx_commit=2

innodb_doublewrite=1

innodb_flush_method=O_DIRECT

innodb_thread_concurrency=0

innodb_max_dirty_pages_pct=80

innodb_file_format=barracuda

innodb_file_per_table

innodb_deadlock_detect=0

max_connections=2000

table_cache=2000

key_buffer_size=2000M

innodb_doublewrite=0

MySQL versus MongoDB - fetch by secondary index

2010-09-13T11:28:00.000-07:00

This continues the silly benchmark series and compares performance from concurrent clients that fetch by secondary key. The previous post compared fetch by primary key. The test setup was the same as before. Clients were run on a 16-core x86 server. The servers (mongod, mysqld) were run alone on 8-core and 16-core x86 servers. The tests were run for servers that were 1ms and 200us apart according to ping. The database server saturates earlier when the client is only 200us away. That is to be expected.

InnoDB tables are clusters on the primary key and a query that fetches all columns by PK only has to read data from one leaf block of the PK index. When all columns are fetched by secondary key then the secondary index leaf node and PK index leaf node must be read. As all data was cached for this test that does not make a big difference. Were data not cached the extra IO used to read the PK index leaf node would be significant.

This displays throughput on the 16-core server. MongoDB saturates earlier on the 16-core server than on the 8-core server. From vmstat output this appears to be mutex contention on the server but I cannot provide more details on where that occurs as the mongod binary I downloaded has been stripped.

This displays throughput on the 8-core server. Peak QPS for MongoDB is much better than on the 16-core server.

This displays response time for the 16 core server.

The test was repeated using a hosts that were 200us apart according to ping. The database host was an 8-core server in this test. The peak QPS is similar to the previous tests but the servers saturate with fewer concurrent clients. The results are here and have been updated to include results for MongoDB 1.6.2 and 1.7.0.

Source code to setup the MySQL table:

def setup_mysql(host, db, user, password, engine, rows):
  filterwarnings( 'ignore', category = MySQLdb.Warning )
  conn = connect_mysql(host, db, user, password)
  conn.autocommit(True)
  cursor = conn.cursor()
  cursor.execute('drop table if exists bm')
  cursor.execute('create table bm (id int primary key, sid int, k int, c char(120), pad char(60), key sidx(sid)) engine=%s' % engine)

  vals = []
  for x in xrange(0, rows):
   sx = str(x)
   lsx = len(sx)
   row = '(%d, %d, %d, "%s", "%s")' % (x, x, x, sx+'x'*(120-lsx), sx+'y'*(60-lsx))
   vals.append(row)

   if len(vals) == 1000:
   r = cursor.execute('insert into bm values %s' % ','.join(vals))
   vals = []
   print '... row %d, result %s' % (x, r)

  if vals:
   r = cursor.execute('insert into bm values %s' % ','.join(vals))
   vals = []
   print '... row %d, result %s' % (x, r)

Source code to query the MySQL table:

def query_mysql(host, db, user, password, pipe_to_parent, requests_per, rows, check, testname, worst_n, id):
  conn = connect_mysql(host, db, user, password)
  conn.autocommit(True)
  cursor = conn.cursor()

  gets = 0
  stats = SummaryStats(worst_n)

  while True:
   for loop in xrange(0, requests_per):

   target = random.randrange(0, rows)

   s = time.time()
   cursor.execute('select id, k, c, pad from bm where sid = %d' % target)
   sel_rows = cursor.fetchall()
   stats.update(s)

   if len(sel_rows) != 1:
   print 'No rows for %d' % target
   assert False
   if sel_rows[0][0] != target:
   print 'id is %s and should be %s' % (sel_rows[0][0], target)
   assert False

   gets += 1

Source code to setup the MongoDB collection:

def setup_mongo(host, port, dbname, rows):
  conn = pymongo.Connection(host, port)
  conn.drop_database(dbname)
  db = conn[dbname]

  for x in xrange(0, rows):
   sx = str(x)
   lsx = len(sx)
   db.c.save({'_id':x, 'sid':x, 'k':x, 'c':sx+'x'*(120 - lsx), 'pad':sx+'y'*(60 - lsx)})
   if x % 1000 == 0:
   print '... row %d' % x

  db.c.create_index('sid')

Source code to query the MongoDB table:

def query_mongo(host, port, pipe_to_parent, requests_per, dbname, rows, check, testname, worst_n, id):

conn = pymongo.Connection(host, port)

db = conn[dbname]

gets = 0

stats = SummaryStats(worst_n)

while True:

for loop in xrange(0, requests_per):

target = random.randrange(0, rows)

s = time.time()

o = db.c.find_one({'sid': target})

stats.update(s)

assert o['_id'] == target

if check:

assert o['k'] == target

sx = str(o['_id'])

lsx = len(sx)

assert o['c'] == sx+'x'*(120-lsx)

assert o['pad'] == sx+'y'*(60-lsx)

gets += 1

MySQL versus MongoDB - yet another silly benchmark

2010-09-11T10:59:00.000-07:00

This is yet another silly benchmark because the results are likely to be misused. The results probably do not matter to you. I like MongoDB. It has many things in common with MySQL (ease of use, rapid iteration, give customers what they want). It would be more interesting to me were it to use embedded InnoDB as the backend.

Please read the update as I was able to get much better throughput from MongoDB once I realized that the client host was saturated on the CPU.

I want to evaluate the performance claims. Several of the MongoDB versus the world benchmarks were run on Mac laptops. I love my Mac laptop, but I don't use it to confirm database server throughput.

My first test uses concurrent clients to determine the load at which the server saturates. I run this test in two modes: cached and not cached. No disk IO should be done during the cached test. Most requests should require disk IO during the uncached test. I am not sure that I will run the uncached test for MongoDB as it memory maps the database file and there is no way to limit the amount of memory it will use so the MongoDB uncached test requires a database much larger than RAM. That will take a long time to setup and I don't know whether many MongoDB deployments run with databases much larger than RAM.

The test is interesting to me because it has found problems that limit throughput on workloads that I care about. Other tests should be done after understand the results from this test. I suspect that for many people peak QPS on highly-concurrent workloads is not a priority.

Each client does a sequence of calls (SELECT or HANDLER for MySQL, find_one for MongoDB) where each calls fetches one row by specifying the value of an indexed column. The column has a primary key index for MySQL and used the Collections create_index method for MongoDB. The id value to fetch is randomly selected.

The code for the benchmark is a work in progress, so I have yet to publish it. I used Python, the MySQLdb driver for MySQL and the pymongo driver for MongoDB. The clients are separate processes forked via the Python multiprocessing module to avoid the penalty from the Python GIL. Some of the code used for MongoDB is listed at the end of this post.

I only report peak QPS at this time. The results ignore average and worst-case response time. It is not good to ignore them which is another reason why this might be a silly benchmark. Eventually the code will be updated to measure that.

The clients and DBMS (mysqld, mongod) ran on separate 16-core servers. The test was run for 1, 2, 4, 8, 16, 24, ..., 152, 160 concurrent clients. Tests were run for MyISAM and InnoDB for MySQL. The tests used MySQL 5.1.50 unmodified (5150orig) and 5.1.50 with the Facebook patch (5150fb). There are a few changes in the Facebook patch that make a huge difference for peak QPS. I expect official MySQL to have these changes soon. Peak QPS was much higher for MySQL than for MongoDB. In order of peak QPS:

Peak QPS on MySQL 5.1.50 with the Facebook patch exceeds 100,000 and QPS increases to 128 concurrent clients.
Peak QPS on MySQL 5.1.50 unmodified is between 70,000 and 80,000 and QPS increases to 80 concurrent clients.
Peak QPS on MongoDB 1.7.0 is 40,000 and QPS increases to 64 concurrent clients.

MongoDB appears to saturate on mutex contention on the server at 64 concurrent clients. Alas, the binary is stripped and I cannot run PMP to determine where the problem occurs. My diagnosis is based on vmstat output.

This displays throughput MySQL 5.1.50 with the Facebook patch, MySQL 5.1.50 unmodified and MongoDB 1.7.0. All ran on a 16-core Nehalem server -- 16 cores with hyperthreading enabled.

This displays average and 98th percentile response time for MySQL 5.1.50 with the Facebook patch and MongoDB 1.7.0. Both ran on a 16-core Nehalem server -- 16 cores with hyperthreading enabled. Results are from the same test as used for the previous graph.

I repeated the tests using an 8-core Nehalem server. It is the same as the previous server except that hyperthreading was disabled. The graphs below display throughput. MySQL is able to handle more concurrent clients before saturating on the server.

This is the response time graph for the 8-core server.

The servers for the tests above were 1ms apart according to ping. I repeated the test for servers that have a 200us ping time but I don't want to inline another graph. It is linked in the next sentence. The QPS for get by primary key with an interesting and unexplained spike in QPS for MongoDB near 152 concurrent clients. Despite having a similar QPS at that point, MongoDB average response time was about 1.5X that for MySQL. This includes results for MongoDB versions 1.6.2 and 1.7.0.

The code to setup the collection for MongoDB is:

def setup_db(host, port, dbname, rows):

conn = pymongo.Connection(host, port)

conn.drop_database(dbname)

db = conn[dbname]

for x in xrange(0, rows):

sx = str(x)

lsx = len(sx)

db.c.save({'_id':x, 'k':x, 'c':sx+'x'*(120 - lsx), 'pad':sx+'y'*(120 - lsx)})

if x % 1000 == 0:

print '... row %d' % x

And the code to query MongoDB:

def query_process(host, port, pipe_to_parent, requests_per, dbname, rows, check, id):

conn = pymongo.Connection(host, port)

db = conn[dbname]

gets = 0

while True:

for loop in xrange(0, requests_per):

target = random.randrange(0, rows)

o = db.c.find_one({'_id': target})

assert o['_id'] == target

if check:

assert o['k'] == target

sx = str(o['id'])

lsx = len(sx)

assert o['c'] == sx+'x'*(120-lsx)

assert o['pad'] == sx+'y'*(120-lsx)

gets += 1

if pipe_to_parent.poll():

msg = pipe_to_parent.recv()

print 'Received: %s' % msg

pipe_to_parent.send({'gets' : gets})

pipe_to_parent.close()

return

The my.cnf settings for MySQL 5.1:

plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

innodb_buffer_pool_size=2000M

innodb_log_file_size=100M

innodb_flush_log_at_trx_commit=2

innodb_doublewrite=1

innodb_flush_method=O_DIRECT

innodb_thread_concurrency=0

innodb_file_format=barracuda

innodb_file_per_table

max_connections=2000

table_cache=2000

key_buffer_size=2000M

SBR and innodb_autoinc_lock_mode

2010-09-08T16:32:00.000-07:00

The 5.1 manual states:

Therefore, if you are using statement-based replication, you must either avoid INSERT ... ON DUPLICATE KEY UPDATE or use innodb_autoinc_lock_mode = 0

And earlier in the same page is the description of innodb_autoinc_lock_mode=0

This lock mode is provided only for backward compatibility and performance testing. There is little reason to use this lock mode unless you use “mixed-mode inserts” and care about the important difference in semantics described later.

I don't think these statements agree. I am confused even more because upgrading a master-slave pair of servers to 5.1 with innodb_autoinc_lock_mode=1 and statement-based replication fixed a logical corruption problem that occurs when the wrong value was written to the binlog for SET INSERT_ID=... as part of a transaction that does INSERT ... ON DUPLICATE KEY UPDATE.There are a few details on that at bug 50413.

Speaking at MySQL Sunday

2010-09-02T07:31:00.000-07:00

I am speaking at MySQL Sunday. The title for my talk is Success with MySQL and I will focus on things that operations and users can do to make a MySQL deployment succeed. There are many interesting talks scheduled for Sunday, including several at the same time as mine. I hope to see you there.

Why don't you use X?

2010-07-25T12:18:00.000-07:00

Sometimes I am asked why don't I use X instead of official MySQL. The answer is simple. I like to use it because I have been using it, the MySQL development team (including InnoDB) has done great work this year and because change is expensive. The cost of change includes the cost of evaluating the alternatives and the cost of deploying them. The cost of change also includes the features I won't work on because I am doing an evaluation. I also use it because the quality of new 5.1 releases has been very high this year. I know because I test them and some of the alternatives.

My initial evaluation criteria are simple. I don't like compiler or valgrind warnings. The alternative should not introduce new ones. I like regression tests. The alternative should not disable or fail existing tests. If the existing test is somewhat bogus, then it should be fixed. I love buildbot as done by MariaDB, fixes in official MySQL to reduce compiler warnings and all of the work done by Drizzle to not tolerate compiler warnings. When the alternative adds new features it must add new regression tests (hooray for status_user.test in MariaDB).

I spent a lot of time debugging valgrind warnings that occur in MySQL 5.1.47. All of them were bogus and I think future versions of MySQL will prevent these. That is good news as I prefer to not repeat that effort the next time I upgrade to a new MySQL release.

Percona and MariaDB confront the same issues and more when considering code to incorporate. In addition to what I wrote about above, they must review patches to make sure the code is reasonable. There is a lot of duplicate effort done by groups that patch or fork MySQL. I wish this weren't the case. If the external fork & patch effort is consolidated around MariaDB, then we can reduce some of this.

Building MariaDB with the InnoDB plugin

2010-07-23T10:55:00.000-07:00

This post was inspired by a couple events. But I won't explain them other than to say I think there have been too many subjective comments (or FUD) about quality. This is an attempt to quantify whether the grass is greener on the other side.

Today I tried to build MariaDB with the InnoDB plugin. I was told his is now supported. The last time I checked XtraDB replaced the InnoDB plugin in MariaDB. MPAB had a reasonable reason for doing this as they don't want to test both the InnoDB plugin and XtraDB. But I prefer choice, despite the many great features in XtraDB.

First I tried using the 5.3 release. That failed fast. I prefer fast failures over obscure ones:

./configure --enable-thread-safe-client --with-plugins=partition,csv,blackhole,myisam,heap,innodb_plugin --without-plugin-innobase --with-fast-mutexes --with-extra-charsets=all --with-debug C_EXTRA_FLAGS="-fno-omit-frame-pointer -fno-strict-aliasing -Wall"

...

configure: error: unknown plugin: innodb_plugin

Then I tried the 5.2 MariaDB release. This time the configure command worked. After running make, both XtraDB and the InnoDB were compiled. Time to try a test. There were no failures! Alas, all tests were skipped.

./mysql-test-run.pl --suite=innodb_plugin

...

innodb_plugin.innodb [ skipped ] No innodb support

innodb_plugin.innodb-analyze [ skipped ] No innodb support

innodb_plugin.innodb-autoinc [ skipped ] No innodb support

innodb_plugin.innodb-autoinc-44030 [ skipped ] No innodb support

innodb_plugin.innodb-consistent [ skipped ] No innodb support

innodb_plugin.innodb-index [ skipped ] No innodb support

innodb_plugin.innodb-index_ucs2 [ skipped ] No innodb support

innodb_plugin.innodb-lock [ skipped ] No innodb support

innodb_plugin.innodb-replace [ skipped ] No innodb support

innodb_plugin.innodb-semi-consistent [ skipped ] No innodb support

innodb_plugin.innodb-timeout [ skipped ] No innodb support

innodb_plugin.innodb-use-sys-malloc [ skipped ] No innodb support

innodb_plugin.innodb-zip [ skipped ] No innodb support

innodb_plugin.innodb_bug21704 [ skipped ] No innodb support

innodb_plugin.innodb_bug34053 [ skipped ] No innodb support

innodb_plugin.innodb_bug34300 [ skipped ] No innodb support

innodb_plugin.innodb_bug35220 [ skipped ] No innodb support

innodb_plugin.innodb_bug36169 [ skipped ] No innodb support

innodb_plugin.innodb_bug36172 [ skipped ] No innodb support

innodb_plugin.innodb_bug38231 [ skipped ] No innodb support

innodb_plugin.innodb_bug39438 [ skipped ] No innodb support

innodb_plugin.innodb_bug40360 [ skipped ] No innodb support

innodb_plugin.innodb_bug40565 [ skipped ] No innodb support

innodb_plugin.innodb_bug41904 [ skipped ] No innodb support

innodb_plugin.innodb_bug42101 [ skipped ] No innodb support

innodb_plugin.innodb_bug42101-nonzero [ skipped ] No innodb support

innodb_plugin.innodb_bug44032 [ skipped ] No innodb support

innodb_plugin.innodb_bug44369 [ skipped ] No innodb support

innodb_plugin.innodb_bug44571 [ skipped ] No innodb support

innodb_plugin.innodb_bug45357 [ skipped ] No innodb support

innodb_plugin.innodb_bug46000 [ skipped ] No innodb support

innodb_plugin.innodb_bug46676 [ skipped ] No innodb support

innodb_plugin.innodb_bug47167 [ skipped ] No innodb support

innodb_plugin.innodb_bug47621 [ skipped ] No innodb support

innodb_plugin.innodb_bug47622 [ skipped ] No innodb support

innodb_plugin.innodb_bug47777 [ skipped ] No innodb support

innodb_plugin.innodb_bug51378 [ skipped ] No innodb support

innodb_plugin.innodb_bug51920 [ skipped ] No innodb support

innodb_plugin.innodb_bug52663 [ skipped ] No innodb support

innodb_plugin.innodb_bug52745 [ skipped ] No innodb support

innodb_plugin.innodb_file_format [ skipped ] No innodb support

innodb_plugin.innodb_information_schema [ skipped ] No innodb support

innodb_plugin.innodb_trx_weight [ skipped ] No innodb support

Then I checked for compiler warnings. I really dislike compiler warnings. MySQL has recently done a lot of work to remove them from 5.1 (thanks Davi). I think all of this work was considered one bug, but it was a lot of work and will make MySQL better. They have also begun to do some builds with Werror. See bug 53445 for all of the details.

After compiling storage engines with -Wall, there are no warnings for the official InnoDB plugin (storage/innodb_plugin). There are warnings for XtraDB (storage/xtradb):

btr/btr0btr.c:2871: warning: null argument where non-null required (argument 1)

btr/btr0cur.c:1841: warning: null argument where non-null required (argument 2)

btr/btr0cur.c:1860: warning: null argument where non-null required (argument 1)

btr/btr0cur.c:1967: warning: null argument where non-null required (argument 1)

fil/fil0fil.c:3106: warning: pointer targets in passing argument 2 of ‘dict_table_get_index_on_name’ differ in signedness

ibuf/ibuf0ibuf.c:775: warning: null argument where non-null required (argument 1)

ibuf/ibuf0ibuf.c:950: warning: null argument where non-null required (argument 1)

os/os0file.c:4194: warning: pointer targets in assignment differ in signedness

I want to compare the results from running all regression tests with valgrind. But that might take some time. I am able to run all InnoDB tests without valgrind warnings using the Facebook-patched MySQL 5.1.47. That required a few small changes that are likely in the recent 5.1.49 release.

Have all regression tests been run under valgrind for MariaDB using either the InnoDB or XtraDB plugin?

Stay tuned for part 2.

Conversation starters for OSCON

2010-07-21T10:01:00.000-07:00

I will be at OSCON in a few hours. Mohan and I have a talk on FlashCache on Thursday. The talk will have lots of details on the FlashCache implementation. I expect to be quiet except for a few slides on performance. Mohan and Paul did an amazing job getting FlashCache running on our servers. This is an opportunity to learn from Mohan.

As Percona has been doing a lot of work with it, I hope they will be at OSCON to discuss their experience with it.

There are other interesting things to talk about if your area is data management:

MySQL is doing great for their customers this year. I began using MySQL in 2005. This has been the best year for me. The 5.1 release is excellent and the 5.5 beta looks great. The MySQL development team has been fixing bugs fast. Merging InnoDB and MySQL into one company means that their development teams work better, fix bugs faster and talk more about new features.
Your SQL, not MySQL, is frequently the problem. Sometimes we make MySQL deployments better by hacking on MySQL. More often improvement comes from changing application SQL. While some of the application changes compensate for less than perfect behavior in MySQL, more are done to fix things that would be a problem for any database. The biggest thing that MySQL needs to fix in this area is monitoring. It must make it easier to identify performance problems. Until then tcpdump, Poor Mans Profiler and mk-query-digest are excellent options.
Where did Java go wrong? MySQL has a wonderful JDBC driver. I don't blame the implementation. But Java clients continue to cause too many database problems for me. I recently logged all SQL on a server and noticed that the JDBC client connected, ran 13 statements to prepare the connection (including 5 set autocommit statements) and then ran 1 query. That is an amazing amount of bloat. I have seen many other cases where preparing/returning a connection from/to the pool required 5 to 10 statements. Given that number of round trips between the client and server it isn't likely that the connection pool saves any overhead on the database. I am currently dealing with Java applications that set tx_isolation to read-committed for InnoDB. With MySQL 5.1 all binlog events written for such a connection must use row-based replication. For now I will assume that most of the Java apps use read-committed because they want to rather than because they need to.
Just because your database is sharded doesn't mean you lose joins. You lose the ability to do joins or enforce foreign keys across all of your data. But lots of interesting queries can be run within one shard. I prefer that long running queries using something other than MySQL as they will run much faster elsewhere.

A new book on MySQL replication and HA

2010-07-13T09:21:00.000-07:00

There is a new book on MySQL replication and HA from Charles, Mats and Lars. I read it as a reviewer and learned more than a few things. It has many details on internals that are not described elsewhere unless you are willing to read the source code. It also describes how to deploy MySQL replication for many use cases. I think the book can save people from some failures that are inevitable when a distributed system is deployed for enough time on enough servers.

Best practices

2010-05-05T10:35:00.000-07:00

Back in the day we wrote C without support for type checking. Many bugs were missed because of this. Someone wrote lint and many bugs were prevented. Not everyone takes advantage of tools that prevent easily fixed errors. MySQL builds are done without using the -Wall option in gcc to generate more warnings. Nor do they use the -Werror option to fail on warnings. This allows for some silly things in production releases like bug 51289 (return NULL for a function that is declared to return double):

double Item_cache_decimal::val_real()
{
  DBUG_ASSERT(fixed);
  double res;
  if (!value_cached && !cache_value())
    return NULL;

Not using -Wall and -Werror allows for more serious problems to be missed. Bug 42733 is an example of one such problem. It also has allowed me to miss problems in code that I change.

I filed bug 53445 for this. MySQL has been very good at fixing things lately. This should also get fixed. Visit the bug and subscribe to it or update it.

Searching for "site:bugs.mysql.com compiler warning" finds a lot of entries.

Consistency across a WAN

2010-04-25T11:05:00.000-07:00

There are three solutions for providing consistency in a data service that operates across a wide area network (WAN). None of them are free. What are you willing to pay and where are you willing to add complexity? Depending on what you choose your system can be more complex for external users, internal application developers or operations. The choices are multi-master with conflict resolution (eventual consistency), multi-master with conflict prevention (strong consistency) and single master with downtime on failover.

If you choose eventual consistency (EC) then internal application developers must write logic to resolve conflicts and external users will occasionally encounter inconsistent data. This might be a small price to pay for a system that provides higher availability and transparent failover. I am not aware of support for secondary indexes in the popular EC systems. I wonder if the same logic that does eventual consistency across a WAN might be reused to keep secondary indexes eventually consistent within a datacenter. That would impose an additional cost on internal application developers in return for expanding the workloads that EC can support.

If you choose strong consistency then external users experience more latency on writes as the transaction commit requires one or two round trips across a WAN. This might be a small price to pay for a system that provides higher availability and transparent failover. Galera is doing interesting work in this area for MySQL and they have already begun to publish results. I need to read more about that.

If you choose single master then you will spend more money to make that master less likely to fail. You will also experience more downtime and higher support costs while doing manual failover as quickly as possible. Solutions include RAID 10, battery backed write cache, highly-available SAN/NFS, DRBD and pagers for your operations team.

I don't know if people choose single master in the MySQL community. There are not many choices. It supports multi-master replication but without conflict resolution. It supports strong consistency with Galera but that is new on the MySQL market. Galera might be the killer application for MariaDB. Tungsten is another product that can reduce the complexity of master-slave replication.

Unless you are using Tungsten, it very hard to automate master failover for MySQL when there is more than one slave per master. But many deployments need a master and slave in one datacenter and another slave in a remote datacenter.

CAP for MySQL

2010-04-20T08:34:00.000-07:00

Stonebraker wrote a post on CAP. It is interesting because he highlights all of the causes for failures in a database service and then provides his estimates on the frequency of those causes. Many posts on CAP ignore failures caused by how the service is used and operated.

Choose CA - You can get CA (consistency, availability) for a database service running within a datacenter. Use DRBD with MySQL. Then use asynchronous replication to a remote datacenter to survive the loss of a datacenter. You don't get CA between datacenters in this setup. Others have written about the ability to get CA within a datacenter.
Repeatable DBMS errors - I call these queries of death. I notice about one per year. MySQL is remarkably stable if you are careful about the features that you use. New SQL must never run first on a master.
Unrepeatable DBMS errors - These are also infrequent with MySQL but much more frequent than repeatable errors. These are usually impossible to distinguish from intermittent errors caused by hardware and other system software.
P of the CAP theorem is a rare event - I don't have numbers but I don't agree with this for services running across a WAN. Additionally, there are other reasons to sacrifice P. Many applications cannot afford the latency required for strong consistency.

Can we afford strong consistency?

I am certainly not an expert on Paxos, but it is the way to get strong consistency for a database service running across a WAN. This costs one or two round trips depending on whether the commit coordinator migrates between servers. In theory we should be able to afford the overhead of 100 milliseconds to 200 milliseconds for latency sensitive services. We get a lot in return.

But applications and existing database servers can make this difficult to achieve. Many applications are conversational (request, think, request, think, commit -- substitute network latency for 'think'). Many database servers have one resource, the database log, for which Paxos must be run. This guarantees that the commit coordinator will frequently migrate and commit will require two round trips. Even when the log isn't a problem, there will be performance problems for rows that are frequently updated from all locations.

This will change in the future. Servers that are optimized for OLTP (no conversational transactions) will be designed. VoltDB is doing this today (disclaimer, a family member works there). Servers that are optimized for strong consistency across a WAN are also getting built and will become more popular over time.

I think that MySQL can take part in that future (strong consistency across a WAN), but that requires a new storage engine.

How MySQL can placate the communtiy

2010-04-18T21:49:00.000-07:00

Announce
Release

This works as long as the gap between Announce and Release is not unreasonably large. The Announce step was done last week. We can all help with the Release step by using a 5.5 beta, reporting bugs and reading the excellent documentation for MySQL 5.5 and InnoDB 1.1.

CAP theorem

2010-04-18T09:22:00.000-07:00

Do you want to reference the CAP theorem at your next tech conference? I certainly do. Consistency, availability and partition tolerance are wonderful. You only get two and sometimes you only get one. But what are they? In the CAP context availability and partition tolerance are labels for behavior defined by the CAP presentation and paper and this is the source of much confusion. Start with the original work before reading summaries by others.

Eric Brewer presented CAP in a keynote at the PODC. The best slides are on page 4. With XA you forfeit partitions and get CA (consistency + availability). With majority protocols you forfeit availability and get CP (consistency + partition tolerance). XA == CA is easy for me to remember and then I know that majority protocols provide CP.

CAP was proved in a paper by Gilbert and Lynch. This paper defines availability and partition tolerance as used by CAP.

availability - every request received by a non-failing node must result in a response
partition tolerance - The network will be allowed to lose arbitrarily many messages sent from one node to another. Every node receiving a request from a client must respond, even though arbitrary messages that are sent may be lost.

CAP was explained in a great post by Jeff Darcy on Availability and Partition Tolerance. Read it. He explains why XA == CA.

What does this have to do with MySQL? I think we will be talking about CAP much more in the future. I found an interesting presentation that explained current MySQL solutions (DRBD, master-slave, master-master, NDB/Cluster) in terms of CAP, but it didn't have enough notes to explain the content.

Fast reads or fast scans?

2010-03-28T16:39:00.000-07:00

MyISAM is frequently described and marketed as providing fast reads when it really provides fast index and table scans. This is a more narrow use case as fast reads implies great performance for most queries while fast scans implies great performance for single-table queries that are index only or do a full table scan.

MyISAM caches index blocks but not data blocks. There can be a lot of overhead from re-reading data blocks from the OS buffer cache assuming mmap is not used. InnoDB and PBXT are 20X faster than MyISAM for some of my tests. However, I suspect that mutex contention on the key cache is also a factor in the performance differences.

While there are many claims about the great performance of MyISAM. There are not as many examples that explain when it is fast. Alas, the same marketing technique is being repeated with NoSQL to the disadvantage of MySQL.

Tests were run on a server that reports 16 CPU cores. The full test configuration is described elsewhere. For this test I modified the sysbench oltp test to do a self-join query. I will publish the code soon. The schema for the test is:

CREATE TABLE sbtest (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT '0',
c char(120) NOT NULL DEFAULT '',
pad char(60) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY k (k)
) ENGINE=InnoDB;

The self-join query uses a range predicate that selects a fixed number (1, 10, 100, 1000 or 10000) of rows. This is an example that selects 1000 rows.

SELECT t1.c, t2.c FROM sbtest t1, sbtest t2
WHERE t1.id between 245793 and 246792 and t2.id = 2000000 - t1.id

Tests were run using MySQL 5.1.45 for MyISAM, InnoDB plugin 1.0.6 and PBXT 1.1. Results are in queries per second for 1, 2, 4, 8, 16, 32, 64, 128, 256, 512 and 1024 concurrent clients. I do not report results for 512 and 1024 clients to avoid long lines in this post.

The performance of MyISAM is much worse compared to InnoDB and PBXT as the number of rows selected grows from 1 to 10,000.

Queries per second when the between predicate selects 1 row:
6843 13157 24552 46822 62588 57023 46568 30582 18745 innodb
6164 13627 25671 48705 63741 59217 48300 30964 18866 pbxt
6354 12061 23373 44284 50778 49546 44412 30444 18827 myisam

Queries per second when the between predicate selects 10 rows:
4240   8466 16387 33221 53902 39599 36214 28026 18084 innodb
4802   8835 17688 35917 57461 47691 41578 29087 18558 pbxt
3890   7129 12512 16450 12272 12304 12441 12448 11304 myisam

Queries per second when the between predicate selects 100 rows:
1842   3455   7249 14842 20206 13875 13471 12942 12344 innodb
2113   3522   7893 13411 18597 18905 18694 18123 12301 pbxt
1608   2260   2263   1899   1371   1399   1451   1468   1442 myisam

Queries per second when the between predicate selects 1000 rows:
   380    654   1222   2023   2487   1866   1791   1794   1942 innodb
   303    641   1149   1699   2044   2069   2072   2063   2056 pbxt
   232    248    227    189    141    143    149    148    148 myisam

Queries per second when the between predicate selects 10000 rows:
    43     70    130    213    254    199    194    196    199 innodb
    49     69    123    182    213    216    216    216    216 pbxt
    24     24     23     19     14     14     15     15     15 myisam

MyISAM is at a disadvantage because it does not cache data blocks, so I changed the query to be index only and it is listed below. This did not make MyISAM faster. I think the bottleneck is contention on the key cache mutex.

SELECT t1.id, t2.id FROM sbtest t1, sbtest t2
WHERE t1.id between 245793 and 246792 and t2.id = 2000000 - t1.id

Queries per second for range 1000 using the index only query:
   457    706   1354   2146   2596   2044   1918   1887   1953 innodb
   576    837   1386   1681   2058   2094   2103   2095   2087 pbxt
   353    244    223    190    140    142    147    146    146 myisam

Results for MySQL 5.0.84 are similar to 5.1.45 for the range 1000 query:
   390    642   1241   2045   2547   1891   1825   1813   1930 innodb
   303    239    225    189    140    141    147    146    146 myisam

The query plan for the basic query:

explain SELECT t1.c, t2.c
from sbtest t1, sbtest t2
where t1.id between 245793 and 246792 and t2.id = 2000000 - t1.id

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: t1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1072
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: t2
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.01 sec)

The query plan for the index only join:

explain SELECT t1.id, t2.id
from sbtest t1, sbtest t2
where t1.id between 1916457 and 1917456 and t2.id = 2000000 - t1.id

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: t1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 978
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.00 sec)

Do we still need innodb_thread_concurrency?

2010-03-28T08:44:00.000-07:00

Baron wrote this in a comment to a recent blog post.

I consider innodb_thread_concurrency a vestigial tail of the “built-in InnoDB” that ships by default with MySQL 5.0 or 5.1, and should generally be set to 0 with recent versions of XtraDB or the InnoDB Plugin.

Can this be? I cannot wait for innodb_thread_concurrency to be made obsolete. I run a lot of CPU-bound benchmarks on 8 and 16 core servers and I always set it to 0 in my benchmark framework. A few times I have repeated tests with it set to a non-zero value to understand whether that helps and it has never helped. Alas, this will also make my FLIFO patch obsolete.

I agree with Baron that it should be set to 0 with the InnoDB plugin and XtraDB. This is a big deal that has not received enough attention. InnoDB and XtraDB have gotten much better at supporting highly-concurrent workloads on many-core servers. For me highly-concurrent means 100 to 1000 concurrent transactions and many-core means 8 and 16 core servers.

This is not an easy workload to support. MySQL is getting much better at it. A lot of work remains to be done. MySQL 5.5 has even more improvements and several problems have yet to be fixed in InnoDB. But this is a huge deal. Maybe we can have a going away party for innodb_thread_concurrency at the conference?

Pay now or pay later

2010-03-25T21:17:00.000-07:00

I think I have rpl_transaction_enabled working for MySQL 5.1 and will publish the patch after more testing. I hope to never port this again but that depends on whether the distribution I use provides an equivalent feature. Apparently people in operations enjoy not having to restore slaves after hardware and software crashes.

Some features require payment up front. They either cost a lot for developers to implement or for users to deploy. Others avoid the up front costs but require payment down the road by users who encounter many problems. I think that MySQL replication has been on the wrong side of this trade off for too long. But things are changing as the replication team has been done a lot of good things for the past few years. I am sure if we follow Mats around at the User conference we can find out what is coming.

MySQL has to improve to remain competitive as PostgreSQL and others have compelling features pending or available now.

Durable, not durable and really not durable

2010-03-24T07:58:00.000-07:00

A lot of interesting work is being done today on SQL and NoSQL servers. This generates a lot of interesting discussions about CAP, ACID and BASE. Be careful what you read.

The D in ACID stands for durability. A DBMS (SQL or NoSQL) is either durable or it is not. But there are several ways to be not durable. Two popular ways are lose-last-N-transactions (not durable) and lose-unspecified-amount-of-data (really not durable). One of these is much better than the other. Alas this distinction is frequently ignored when describing really-not-durable servers.

lose-last-N-transactions - not durable servers provide a configuration that enables better performance by allowing the last N transactions to be lost during a crash. InnoDB does this when innodb_flush_log_at_trx_commit=2. I am not a MySQL Cluster expert but I think that global checkpoints provide the same property. Cassandra can run in this mode. I think that HBase must run in this mode. Some filesystems provide a similar option. The key point is that the system will quickly recover to a consistent point in time after a crash and the time to which it recovers will be not too far in the past.
lose-unspecified-amount-of-data - really not durable servers can be great for batch and read-only workloads. I am skeptical about using them for OLTP. A server specific version of fsck must be run after a software or hardware crash. The really not durable servers that I am familiar with do not document this behavior prominently (the amount of data that might be lost after crash recovery and the amount of time required to run the recovery tool on a large database file). I suspect some of their users are not aware of the problems that await them.

Why do NoSQL systems use XML for configuration files?

2010-03-20T20:36:00.000-07:00

MySQL uses SQL for data and name-value pairs for configuration files. Cassandra uses XML for configuration files and something closer to name-value pairs for data (or name-value-value-... pairs). Why does it use a stronger data model for configuration than for data?

While I am writing this in jest I think this is an interesting question.

Can a protocol be GPL?

2010-03-16T09:27:00.000-07:00

I am trying to understand the behavior of MYSQL_OPT_READ_TIMEOUT which can be used to set a client-side read timeout for connections to a MySQL server. This determines how long a client will wait for a response to a request. I was uncertain based on the documentation.

The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary, so the total effective timeout value is three times the option value.

I read the code. The documentation is correct. The code attempts to read from the socket three times. I prefer to not have to multiply by three to know the real timeout. But it is too late to change this. Maybe they could add a new option -- MYSQL_OPT_READ_TIMEOUT_DO_NOT_MULTIPLY_BY_THREE.

I encountered this interesting claim while reading the source in sql/net_serv.c. We recently discussed this elsewhere. I wasn't aware that the claim is still in the source code.

This file is the net layer API for the MySQL client/server protocol, which is a tightly coupled, proprietary protocol owned by MySQL AB.

@note
Any re-implementations of this protocol must also be under GPL, unless one has got an license from MySQL AB stating otherwise.

UPDATE

The read timeout is enforced by my_real_read in sql/net_serv.c. This code is hard to read. Output from the preprocessor is slightly better. I think it is an accident that the read is retried 3 times for client side code.

I am pretty sure that Drizzle removed this code. Good for them.

The outermost loop should be ignored as all retries occur in the first iteration of it. The comment that the first read is done with non blocking mode is wrong when this is used in the client library. That may explain why one of the retries is done:

    for (i=0 ; i < 2 ; i++)
    {
      while (remain > 0)
      {
        /* First read is done with non blocking mode */
        if ((long) (length= vio_read(net->vio, pos, remain)) <= 0L)
        {

After that fails on the first read attempt this block of code runs and then continue is called to jump to the start of the while loop. This comment is again wrong as the code within this block changes the socket to use blocking mode. The comment also contradicts the previous comment mentioned above.

          /*
            We got an error that there was no data on the socket. We now set up
            an alarm to not 'read forever', change the socket to non blocking
            mode and try again
          */
          if ((interrupted || length == 0) && !thr_alarm_in_use(&alarmed))
          {
            if (!thr_alarm(&alarmed,net->read_timeout,&alarm_buff)) /* Don't wait too long */
            {

The block above is not executed after the second read call fails because thr_alarm_in_use is true. In this case alarms aren't really used, the alarmed variable is an int set to 0 at function entry and set to 1 after the first read fails. This block of code is executed after the second read call fails and it executes a continue statement to branch to the start of the while loop and retry the read call for a third time. It increments retry_count to 1 before doing so.

          if (thr_alarm_in_use(&alarmed) && !thr_got_alarm(&alarmed) &&
              interrupted)
          {                                     /* Probably in MIT threads */
            if (retry_count++ < net->retry_count)
              continue;
#ifdef EXTRA_DEBUG
            fprintf(stderr, "%s: read looped with error %d, aborting thread\n",
                    my_progname,vio_errno(net->vio));
#endif /* EXTRA_DEBUG */
          }

The previous block of code is also executed after the read fails for the second time. However, retry_count was previously incremented to 1 and net->retry_count equals 1. So the continue statement is not called after the third read failure and my_real_read returns.

Thoughts on Drizzle

2010-03-14T11:16:00.000-07:00

I wish the case for Drizzle could be made without bashing MySQL. Sometimes it is, but too often it isn't. I guess this is karma.

This isn't a rant against Drizzle. This is a rant against pulling up Drizzle by pushing down MySQL. I occasionally have negative things to say about MySQL, but I usually say them to get the problems fixed. We have lots of complaints about MySQL because we use it in production.

What have I learned about the Drizzle vision?

Drizzle will re-think everything.

Alas, I have problems to solve today. While I am passionate about doing things correctly, I am also aware that compromises must be made to get things done. Some of those compromises turn out to be mistakes. It isn't always possible to know which compromises will turn out to be a mistake. Nor is it always possible to identify the right thing.

You hate MySQL replication? You now love Drizzle.

I love what Drizzle might do for replication. I love what MySQL is doing with replication. I can't compare the two until Drizzle replication is running in production.

MySQL has a data type called a 3-byte integer. Think about that for a moment. On today’s server hardware that does not make a whole lot of sense.

I thought about it. Does this mean that some of my tables will grow from 3G to 4G on disk? I won't be happy if that is the result.

No triggers or stored procedures. That stuff is bloat as done in MySQL, and Drizzle has other ways to deal with these needs. These capabilities can be added in later as needed such that they are done right.

I need stored procedures. They are required for high-performance OLTP as they minimize transaction duration for multi-statement transactions. Alas, I have yet to use them in MySQL.

MyISAM is gone. Long live the Queen!

Alas, I need MyISAM. Long-running insert, update and delete statements consume too many resources in InnoDB. Such statements are used for reporting jobs on slaves and in that case I want to use InnoDB for production tables and MyISAM for transient tables.

Ever tried to compile MySQL from source. Hah! Yeah, drizzle builds like butter.

I have no problems building MySQL from source. I have had more problems building Drizzle because it has a few more dependencies (google protobufs, libdrizzle). But both are easy to build and nobody cares too much in either case with one exception. Does Drizzle build on Windows?

Index only

2010-03-12T18:29:00.000-08:00

A problem with SQL is SQL. It is easy to write queries that require random IO in the worst case. It is usually easy to find queries that do too much random IO on a NoSQL system as you must code the extra data fetches manually.

Digg has begun to write about their reasons for migrating from MySQL to Cassandra. They provide an excellent summary and then describe a performance problem fixed by the migration. I think Cassandra and a few other members of the NoSQL family are amazing technology but I don't think a migration was needed to fix this performance problem. A better index on the Diggs table would have done that. Others have said the same thing. Maybe I don't have all of the details. I can only go on what was written in the blog.

You can learn more about the power of indexes at the MySQL conference.

The Diggs table was the source of the problem:

CREATE TABLE Diggs (
id INT(11),
itemid INT(11),
userid INT(11),
digdate DATETIME,
PRIMARY KEY (id),
KEY user (userid),
KEY item (itemid)
) ENGINE=InnoDB;

It supported an important query that was too slow. A simple form of this query is:

SELECT digdate, id
FROM Diggs
WHERE userid in (10, 20, 30) AND itemid = 50
ORDER BY digdate DESC, id DESC LIMIT 4;

This query requires too much random IO because it isn't index only. The query can use either the index on itemid or the index on userid. In both cases it will scan more entries than it needs to from the secondary index and then lookup the remaining columns from the primary index. Each lookup on the primary index can do one disk seek. On my test server the plan for this query is:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Diggs ref user,item item 5 const 8960 Using where; Using filesort

After running the query I ran SHOW SESSION STATUS LIKE "Handler_read%" and the result from that is below. The query scanned 5000 entries from the secondary index and would have done more than 5000 disk seeks in the worst case to lookup columns from the primary key index.

Variable_name    Value
Handler_read_first    0
Handler_read_key    3
Handler_read_next    5000
Handler_read_prev    0
Handler_read_rnd    0
Handler_read_rnd_next    0

The query is much faster for a table with different indexes

CREATE TABLE DiggsFast (
id INT(11),
itemid INT(11),
userid INT(11),
digdate DATETIME,
PRIMARY KEY (itemid,userid,digdate,id),
UNIQUE KEY (id)
) ENGINE=InnoDB;

The query has a better plan:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE DiggsFast range PRIMARY PRIMARY 8 NULL 149 Using where; Using index; Using filesort

It also is much better in reality. The output from SHOW SESSION STATUS LIKE "Handler_read%" is listed below. With a better index the query scans 150 entries from 5 range scans of the index. It should do about 5 disk seeks in the worst case. It is also index only so it doesn't have to lookup other columns after the index scan. Although that doesn't matter much in this case because the query uses the primary key index which has all columns for an InnoDB table. This query will be much faster than the previous one (5 disk seeks versus 5000).

Note that this query uses the first two columns in the primary index for the predicates on itemid and userid. InnoDB stores all columns in the primary key index entries so any query that uses the PK index is index only.

Variable_name    Value
Handler_read_first    0
Handler_read_key    5
Handler_read_next    150
Handler_read_prev    0
Handler_read_rnd    0
Handler_read_rnd_next    0

UPDATE

I created another variant of the Diggs table that uses a secondary index for the query. InnoDB includes all columns from a PK index in the secondary index to serve as the pointer to the row. Note there is a difference between being 'in the index' and being indexed.

CREATE TABLE DiggsFast2 (
id INT(11),
itemid INT(11),
userid INT(11),
digdate DATETIME,
KEY itemuserdig (itemid,userid,digdate),
PRIMARY KEY (id)
) ENGINE=InnoDB;

From the query plan:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    DiggsFast2    range    itemuserdig    itemuserdig    10    NULL    150    Using where; Using index; Using filesort
digdate    id

And SHOW SESSION LIKE "Handler_read%"

Variable_name    Value
Handler_read_first    0
Handler_read_key    5
Handler_read_next    150
Handler_read_prev    0
Handler_read_rnd    0
Handler_read_rnd_next    0

Plays well with others

2010-03-01T08:40:00.000-08:00

A few years ago MySQL+memcached and PostgreSQL+memcached were the only choices for high-scale applications. That has changed with the arrival of NoSQL. Change is good. Open-source monopolies are not much better than closed-source ones from the perspective of an end user. I expect MySQL to focus much more on the needs of high-scale applications to remain relevant. I also expect it to play better with others as it is no longer the only persistent data store for high-scale applications.

I think that MySQL+memcached is still the default choice and I don't think it is going away in the high-scale market. But some high-scale applications either don't need all of the features of a SQL RDBMS or are willing to go without those features to scale. This isn't a blanket endorsement of NoSQL as the definition of NoSQL is weak. I am referring to the NoSQL systems that support high-scale.

I don't believe all of the bad press that MySQL receives from high-scale applications. I know that some problems with MySQL are self-inflicted (seriously, I know this). It is hard to diagnose many problems for which the primary symptom is a slow MySQL server so it is also hard to identify self-inflicted problems. I also don't think that some NoSQL systems will provide a different scale-out experience than MySQL given that some NoSQL systems scale-out by sharding (just like MySQL) and that I can deploy MySQL like NoSQL (disallow joins and secondary indexes, use HANDLER statements)

I also wonder whether affordable SSD/Flash reduces the need to migrate from MySQL to NoSQL. Many MySQL deployments that were IO bound when it was difficult to get more than a few thousand IOPs on a commodity server can now get 10,000 to 100,000 IOPs in that server at commodity prices.

MySQL and NoSQL are also at significantly different stages. MySQL is mature and maturity has its benefits. MySQL has amazing support and documentation. There are client libraries for almost every language that you should use. There are even bindings for languages you shouldn't use. The MySQL C API is easy to use. The JDBC driver is awesome, even if support for JDBC makes it much more complex than needed. There is a lot of MySQL expertise that can be hired or rented (MySQL, Monty Program, Percona, Open Query, Pythian, FromDual) and there is some innovation (not enough companies, but they are doing amazing things) from third-parties such as InfiniDB, InfoBright and TokuDB.

What happened?

NoSQL systems are improving faster than MySQL, MySQL has focused on features for the enterprise RDBMS market in the past two releases and the changes we need from MySQL are hard to implement. Change is hard because MySQL is a complex server that supports many features. Change is also much harder than it should be because of the MySQL coding style. Parts of it are not modular and features are entangled. Some of the difficulty could be overcome were there interest from external contributors. There are external contributors willing and able to improve server code but they are working on other projects like NoSQL. The MySQL effort is also split (or diluted) between official MySQL, Drizzle and MariaDB.

What really happened?

I don't know. It may have been better for the business of MySQL to focus on the enterprise market. I can describe some of the problems that need to be fixed in MySQL to make things easier for me. I think other high-scale applications share these problems:

Multi-master - high-scale applications have users around the world. Latency is reduced by distributing databases and application servers around the world. Databases are rarely sharded by location so the data store must support multi-master deployments with conflict resolution and eventual consistency for some database tables. There is no support for conflict resolution in MySQL. It might be possible to do something with the output of row-based replication.
SQL - this is a problem that MySQL cannot fix. SQL makes it easy to make mistakes. Mistakes include insert, update and delete statements that lock all rows in a table. Alas, the EXPLAIN statement in MySQL does not support insert, update and delete statements. Another serious mistake is a query that has a lousy response time when the database buffer cache is cold because it does many random disk reads. The EXPLAIN statement in MySQL does not provide an estimate for the worst-case number of random disk IOs and many people who write SQL don't know how to interpret it to get an estimate. Worst-case performance is critical for queries run during web requests.
Write-optimization - several NoSQL systems are write-optimized including Cassandra, HBase and Bigtable. A write-optimized system makes it possible to use more indexes than an update-in-place data store. With more indexes it is more likely that there can be an index defined for every popular query and the index reduces the number of disk reads that must be done to evaluate the query. This improves worst-case query response time and reduces the need to use memcached or a huge database buffer cache. Write-optimization has finally arrived for MySQL with the availability of TokuDB. I hope that RethinkDB provides a GA version in the future.
Monitoring - without good monitoring you will either spend too much time fixing performance problems or never find them and buy too much hardware. I suspect that monitoring in MySQL is much better than anything in a NoSQL system but MySQL is missing features that make it easy to understand current and new sources of workload. I need to aggregate the overhead (CPU time, disk operations, rows read, ...) by database user, table and statement. It is extremely hard or not possible to do this by database user and table. It became possible in MySQL 5.1 to do this by statement for short periods of time by using the slow query log in MySQL 5.1. Prior to MySQL 5.1, the slow query log was limited to queries that ran for at least two seconds. The alternative is to use tcpdump with mk-query-digest. Despite all of the work that has gone into the performance schema, MySQL has yet to support anything like my favorite feature -- user and table monitoring.
Crash proof slaves - replication slaves are not crash proof. The slave commits transactions to a storage engine and then updates a state file to maintain the replication offset. There is nothing to keep the state file and storage engine in sync. Until recently there wasn't even an option to force the state file to disk after it was updated. Unplanned hardware reboots are frequent when there are hundreds or thousands of slaves. If you are clever and use the right version of InnoDB it is possible in some cases to figure out the correct offset for the slave after a crash and repair it manually. This isn't a good use of DBA time. Otherwise DBAs must waste their time and network bandwidth to restore the slaves. The Google patch published two different fixes for this: rpl_transaction_enabled and global transaction IDs. MySQL is working on a fix.
Automated failover - for MySQL deployments that have many slaves connected to one master it isn't possible to automate failover when a master crashes. Tungsten and DRBD might make this better.
Resharding - sharding is an excellent way to scale MySQL. Sharding usually requires resharding. Resharding is hard and must be done with minimal downtime. It might be possible to build a tool that uses row-based replication output to reshard a database in the background with little downtime. No such tool exists today.
Replication lag - a slave with replication lag is useless for OLTP scaleout. The replication thread is single-threaded. MySQL is working on support for parallel execution on a slave. Until then we need to improve mk-slave-prefetch (Domas can you hear me).
Schema change - these are frequently needed for growing high-scale applications. Long running schema changes in MySQL require downtime unless first done on a slave (assuming you have a spare slave and that slave can become the master after the change). Users don't like downtime. I think it is possible to do many of these on a master with minimal downtime using the output from row-based replication. Alas, there is no tool for that today.

What is NoSQL?

Do your homework when evaluating a NoSQL system as they differ greatly from each other:

Crash safety - most NoSQL systems are crash safe but a few are not. I would limit the use of systems that are not crash safe to supporting batch workload. Unplanned server reboots are frequent for high-scale applications when a large number of servers is used. At least two prominent members of the NoSQL family are not crash safe. That should be documented in bold text on their project pages. It is not.
Sharding - some NoSQL systems do sharding. BigTable and others do not. With sharding it is possible to support transactions and multiple-indexes on a table within the scope of a shard. That then requires support for resharding. It also requires that queries on secondary indexes to be run on all shards while queries on primary indexes can be limited to run on one shard which may limit the ability to use secondary indexes.
Index types - Many NoSQL systems are limited to hash indexes. You can't do range scans on hash indexes. I wonder whether this leads to data redundancy when every query must be resolved by one index lookup.
Secondary indexes - NoSQL systems like BigTable not only do not support transactions, they also do not support secondary indexes. You can explicitly maintain a secondary index but there is no support to make multiple-changes atomic and there can be a failure between the primary and secondary index updates which results in data drift. It is also difficult to do consistent reads between the two.
Consistent reads - consistency is usually the responsibility of the client and done by using per-row timestamps.
Single-node performance - I know that performance != scale-out but scale-out is not a substitute for lousy single-node performance in the high-scale application market. It might be acceptable to use 5X as many nodes because your data store is slow when you end up using 40 nodes. This becomes a show-stopper when you end up using thousands of nodes. One NoSQL system has accepted this compromise. While I know it has many other use cases I think that will limit the use of it for high-scale applications.
Network efficiency - MySQL reduces use of the network because all query evaluation is done at the server. All NoSQL systems evaluate predicates implied by the index access. Only some NoSQL systems evaluate non-indexed predicates. This can result in more data returned to the client.
Technology or solution - MySQL is more mature than the NoSQL systems. A lot of work remains to grow NoSQL from technology into solution with support for audit, backup, monitoring and all of the other things required to scale in a large company.

Time to use -Werror

2010-02-20T14:12:00.000-08:00

This is in 5.1.44. It is easy to make mistakes like this in a large and rapidly changing code base. Why not compile with -Werror to catch the problem?

double Item_cache_decimal::val_real()
{
DBUG_ASSERT(fixed);
double res;
if (!value_cached && !cache_value())
return NULL;
my_decimal2double(E_DEC_FATAL_ERROR, &decimal_value, &res);
return res;
}

Save MyISAM

2010-02-15T09:02:00.000-08:00

What is the future for MyISAM? MySQL has invested a lot in storage engines over the past few years (Falcon, Maria) and it isn't clear that anything will come from those efforts. A lot of effort has been put into InnoDB and much will come from that. There has not been a significant effort to improve MyISAM (other than hot backup). What could be done with it?

Support undo. The manual claims that MyISAM supports atomic operations. They must use a different meaning for atomic. When a long-running insert, update, delete or replace statement is killed it remains half-done for MyISAM. This could be fixed by supporting undo for MyISAM. I will guess that MySQL can reuse some of the code they added for hot backup to implement undo.
Reduce mutex contention. MyISAM could use multiple key caches per table as MPAB has proposed.

MyISAM performance matters even when you don't explicitly create MyISAM tables. MyISAM is used for some implicit temp tables created to evaluate ORDER BY and GROUP BY clauses.

Dude, where is my link?

2010-02-03T16:03:00.000-08:00

What happened to the obvious link from mysql.com to dev.mysql.com? Did it move here or here?

Save MySQL, save the world

2009-12-29T16:08:00.000-08:00

Things are getting interesting. MPAB continues to drive away potential supporters with the tone of their messages, the inclusion of pointless assertions, and the complete lack of references.

For example, Oracle could buy some companies developing PostgreSQL and target the core developers. Without the core developers working actively on PostgreSQL, the PostgreSQL project will be weakened tremendously and it could even die as ar result.

Or another company could hire all of the core developers from one area of MySQL. I am glad that people have the opportunity to work elsewhere.

MySQL is the database with the highest number of installed units in all markets (except in the high enterprise market where it has only a medium size unit share).

All markets or non-embedded markets? SQLite claims at least 500M deployments. Oracle claims there are 200M deployments of Berkeley DB.

MySQL is causing Oracle sales losses around 1 billion usd/year (in lost sales to MySQL and because of having to do heavy discounting when competing with MySQL).

Where does this number come from?

Oracle did not provide any remedies to the EC and the public promises they have published are just empty promises.

How do we know what Oracle has or has not promised? I am sure that Oracle can contact the EC without involving MPAB. Besides, I thought the hearings at the EC were private. I don't trust summaries of the hearings from anyone on either side of the issue.

The open source software it has acquired, like InnoDB, has after being acquired, been developed secretly and slowly which is against how things are done in the open source environment.

Compared to what? From my perspective neither Oracle/InnoDB nor Sun/MySQL have been great in this area. But so what? Both continue to improve their software and most people don't care whether or not the development process is open.

MariaDB is an enhanced (faster, more features and less bugs) drop-in replacement of MySQL that is only available under GPL.

The GA release of MariaDB has no bugs because there is no GA release.

The fork can't be used with other products that are using MySQL as a building block for their closed source applications.

Yes, it can (thanks Sheeri)

The fork has to work in an environment where no one has to pay for it.

I will speculate that most of the money earned by MySQL is from customers who don't have to pay. People buy support contracts because the support product is excellent, not because they must.

As long as the products are recognized to be competing, any solution that the EC would accept has to ensure that there is as much competition in the database field before the merger as after the merger.

Is it that simple? Is competition a binary decision?

If MySQL were licensed under a permissive license, like BSD, then the users would benefit as they now can securely continue to use MySQL in all context. Monty Program Ab would also switch to only produce code under BSD for the MariaDB server, to ensure that also MariaDB can be used in all context. Monty Program Ab would benefit very little from of this; We cannot take money from selling BSD; We can only hope that there is a market demand for our skilled engineers.

I would love for it to be BSD. Then I can form a company to build a custom version of it make people pay for that version. Your blog post cites EnterpriseDB for doing this. Why can't MPAB do the same?

The companies that would benefit the most from BSD are the companies that enhance MySQL (storage engine vendors and companies providing extensions to MySQL) and companies that embed MySQL in their products, like Adobe or Cisco.

How does saving storage engine vendors, Adobe and Cisco save the internet? This has nothing to do with the MySQL users you claim have something at risk. Storage engine vendors don't have thousands of customers. I assume app vendors who embed MySQL have more customers, but even in that case I fail to see how the internet is at risk.

MySQL 5.5 and semi-sync replication are here

2009-12-15T07:34:00.000-08:00

MySQL 5.5 is here with several new features including semi-sync replication. The MySQL team has been getting a lot done lately. I know because I get many bug and feature request status updates. They did a lot of work on semi-sync because their implementation was a rewrite rather than a port of the Google patch. It had to be done that way to maintain code quality. Those of us who maintain large patches against MySQL frequently do things the convenient way rather than the right way to simplify patch maintenance.

Read the MySQL manual to understand what semi-sync does. It is not synchronous replication. With semi-sync each connection to a master can lose at most one transaction when the master crashes. This reduces but does not eliminate the problem of transaction loss on a master crash. Semi-sync also limits a busy connection so that it cannot commit faster than a slave can receive its transactions.

Additional references for this include:

notes from the MySQL forge page
design docs from the Google patch

Oracle RDBMS != MySQL RDBMS

2009-12-02T22:22:00.000-08:00

The Oracle and MySQL RDBMS are very different products. This makes me happy. I used to work on the Oracle RDBMS. It has a lot of features that do amazing things. Unfortunately, this also makes it extremely hard to modify. MySQL doesn't have as many features. This makes it easier to modify. This also means there are a lot of things to fix in it when you care about high-performance and high-availability OLTP workloads.

But now we have a new story emerging from an independent source of news on the Oracle-Sun merger.

One more week won’t change the fact that MySQL competes fiercely with Oracle’s database products including its flagship ‘11g’ across all major market segments.

What does this mean besides a few more months of uncertainty for people at Sun/MySQL? Do they compete for customers? Or do they compete based on technology? We can only guess as the report is not public. I am sure it is a great document, at least that is what I have been told.

Can we get this done and return our focus to the roadmap for 5.4, 6.0 and the MySQL User Conference? I would much rather bicker about who doesn't get to present at the conference, the rate at which community patches are accepted and my inability to republish an edited version of MySQL docs. MySQL would otherwise be on a roll right now with the progress they have made on 5.1 (it is a great release) and with work in progress for future releases.

Update

Wow, maybe the GPL means something. Eben Moglen finds factual errors in the still-secret statement of objections.

Time to get it done

2009-11-10T09:51:00.000-08:00

I am neither for nor against the Oracle-Sun merger. I am against the damage done by extending the uncertainty on the outcome of the deal. MySQL as an organization is in great shape. The 5.1 release turned out better than some expected. The InnoDB plugin is excellent. What is the roadmap? MySQL is limited in what they can say about their future. That hurts all users and customers.

A lot of nonsense has been written about this. As MySQL employees cannot write about it, the discussion has been one sided, full of speculation and justified by quotes from random people. I almost provided a few quotes myself when contacted by someone I thought was a potential MySQL customer.

I am neither a lawyer nor an economist, so I don't understand their notion of competition as applied to this issue. I wish that were clear. I don't think that the 8-year old E-Week benchmark implies anything about whether MySQL and Oracle compete. Nor do I think that a few slides from a project at Sun that failed to migrate Oracle customers to MySQL is evidence of that. Marten's letter set a high standard for the discussion. I hope others follow it.

Clearly competition isn't defined by revenue as that is something between $100M and $300M per year. The database market is much larger than that. For better or worse, MySQL has not done a good job of monetizing their users. Maybe they have not tried to do that as their value seems to be independent of their revenue. But someone has to fund the development of MySQL.

I have worked on source code for the Oracle and MySQL database servers. I have used MySQL in production. Some parts of MySQL are amazing (InnoDB, JDBC, support, docs, bug database, server uptime, ease of use, NDB) but in no way do they compete on a feature basis. I hope that never changes as MySQL would be ruined were it to become as complex as Oracle. I am sure they compete for some customers who don't need all of the features provided by Oracle. But that competition includes Sybase, Microsoft and IBM.

InnoDB plugin gets better again

2009-11-05T09:25:00.000-08:00

Forgive me for being a shill, but InnoDB appears to have added a feature for the next release of the InnoDB plugin that prevents the buffer pool from getting wiped out by a full table scan. Many people have requested this. The documentation is excellent. I have tested it and not only did it work as advertised, but it didn't degrade performance on OLTP workloads. This fixes bug 45015 and is a nice feature to have when you occasionally use mysqldump to copy a table from a busy OLTP server. Now is a good time to evaluate MySQL 5.1 with the InnoDB plugin.

Managed MySQL -- Amazon RDS

2009-10-27T10:19:00.000-07:00

Managed MySQL is here. Amazon RDS allows you to run MySQL on their hardware. It isn't perfect, but I think this is a great first release. I expect this will support PostgreSQL soon given that the command-line tools are not MySQL specific.

Note:

This uses MySQL 5.1.38
I did not see an option to enable SSH connections to MySQL. I think that is required for this to be a great way to run MySQL.
This supports MyISAM and InnoDB. They don't give you command line access to the machines, so you cannot run myisamchk to recover corrupt MyISAM tables, nor can you run myisampack to compress them. I think it is a good idea to stick with InnoDB and then ask Amazon to upgrade to the InnoDB 1.0.4+ plugin.
This appears to use network attached storage for most data. For example, innodb_data_home_dir=/rdsdbdata/db/innodb. I am not sure whether this buffers data in the OS buffer cache and if it does not, that will hurt MyISAM performance as it does not buffer table data.
Replication is disabled. That makes it much easier to run many instances of MySQL in the environment. Replication state is not crash proof and Amazon probably does not want to spend their days recovering/replacing/rebuilding slaves. But that also limits the use of this for read scale out. Maybe Amazon and RightScale have something in progress to change that without introducing manageability overhead.
The master user does not have SHUTDOWN, SUPER or replication privileges.
Binlogs are enabled, but the master user does not have privileges to run SHOW MASTER STATUS. The documents state that databases can be recovered up to the last 5 minutes. I assume this means that any writes done are guaranteed to be archived somewhere after 5 minutes. If there were an option to archive the binlogs, then that would provide an extra degree of safety.

Be careful with FLUSH TABLES WITH READ LOCK

2009-10-16T11:57:00.000-07:00

Be careful when using FLUSH TABLES WITH READ LOCK (aka FTWRL). I have written about potential problems that may occur when using FTWRL. Anyone who runs ibbackup or xtrabackup on a server that writes a binlog needs FTWRL to run as fast as possible with as few problems as possible, but that is not always the case. In its current form, you must monitor FTWRL and either kill it or long-running queries when FTWRL takes too long.

MySQL does three things when processing FTWRL. First it sets the global read lock. Then it closes open tables. Finally it sets a flag to block commits. You will have problems in production when FTWRL doesn't return quickly. It doesn't return quickly when there are long running queries as it waits for the current queries to finish. The problem is that insert, update, delete and replace statements are blocked after the first step. When FTWRL lingers in the second step (close open tables), then your server will stop accepting writes. An additional problem is that for deployments with many open tables, it is a lot of work to close and then re-open them. I need to confirm whether re-open is done serially because a mutex is held and whether InnoDB re-samples all indexes on all reopened tables to get optimizer statistics.

I blame MyISAM for the current problems. As I am not a MyISAM expert, this is an educated guess and I welcome your feedback. The problem with FTWRL is FT (flush tables) and MyISAM is the reason that tables must be flushed. The --delay-key-write option and possibly other features in MyISAM allow open tables to buffer committed changes. The buffered changes are written to MyISAM data files when the open table is closed.

INSERT DELAYED might also cause problems, but anybody who needs a hot backup shouldn't be using that option.

I think we can make this better and my solution is DFTBGRL (don't flush tables but get read lock). Maybe it needs a better name. DFTBGRL skips the second step of FTWRL -- it sets the global read lock and then it sets a flag to block commits. This should be much safer to use in production.

Update:
After I wrote this, Harrison and Konstantin from MySQL/Sun gave me advice on a better way to fix this. I have implemented their advice and it appears to work, but I need to test it. The result will be much better than FTWRL for InnoDB.

OpenSQL Camp

2009-09-12T12:58:00.000-07:00

My travel is booked for OpenSQL camp in Portland on November 14 and 15. It should be a great event in my favorite city. It is also an opportunity to speak with technical people about something other than MySQL. The current sessions are skewed towards MySQL, but Portland has an active PostgreSQL community and is home to Len Shapiro who contributed a lot to the development of high-performance hash joins. I hope there is some kind of PostgreSQL-MySQL exchange. I have yet to propose a topic, but am considering MySQL GIS, MUMPS, embedded InnoDB or the InnoDB plugin.

Explaining subqueries in the FROM clause

2009-09-05T10:55:00.000-07:00

I was debugging the performance of a DELETE statement that contained a subquery in the FROM clause. As there is no EXPLAIN for DELETE, I converted it to a SELECT statement (and hoped the same optimizations were done). But I still had to wait for EXPLAIN to complete. EXPLAIN evaluates subqueries in the FROM clause for MySQL. This can make EXPLAIN take a long time and create load on a server. Recent versions of MySQL have had many improvements for subquery optimization, but the documentation for all versions states that this is still done. A feature request is open to change this. Feature requests are also open to get EXPLAIN for UPDATE, INSERT and DELETE.

Do other RDBMS products support EXPLAIN for subqueries in a FROM clause without evaluating the subquery?

Blame it on MyISAM

2009-09-01T18:03:00.000-07:00

I reviewed most of the changes from the v4 Google patch today. My head hurts now. During this review I checked whether bugs fixed in the patch have also been fixed in recent releases of official MySQL. I am happy that most of them have been fixed. But some changes will never be accepted, such as the one that added support for INF for FLOAT/DOUBLE columns.

The default value of sql_mode is the empty string. You probably want to change that before your applications come to depend on it. When it is the empty string, invalid values are coerced to valid values on INSERT and UPDATE and a warning is returned. Applications usually ignore the warnings. The coercion includes:

INT values that are too big are set to the maximum value of an INT. The same is done for BIGINT
INF is changed to MAX_DOUBLE or MAX_FLOAT for a DOUBLE/FLOAT column
varchar and LOB columns are truncated to not exceed the maximum length
invalid DATE values are accepted

What needs this behavior? MyISAM. For a storage engine that doesn't do rollback, one way to handle invalid data during an INSERT or UPDATE statement is to coerce it to valid values and proceed with the statement. I am not fond of this approach. An alternative for data warehouse workloads is to use an exception table to log rows with invalid data and avoid corrupting non-exception tables.

MyISAM has also made replication semantics and internals much more complex. For example, what is written to the binlog in this case, and has this behavior changed between releases?

begin;
insert into Innodb_table values (1);
insert into Myisam_table values (1);
rollback;

I think that MyISAM has its place. It does fast table scans, but InnoDB is much faster on just about everything else. I am just not thrilled with the impact it has had on MySQL. It can be used for tasks where a table or partition is loaded once and then made readonly after the insert. This is a good fit for data warehouse tasks. Although it would be better were multi-core performance improved and the key cache expanded to include data blocks. MyISAM can also be used for scratch tables on a slave.

Drizzle avoided these problems by limiting MyISAM to temporary tables.

Fun with user variables

2009-09-01T13:37:00.000-07:00

What type is used for the expression returned by this SELECT statement?

set @x = 1e300; select @x

It depends:

MySQL 4.0, 5.1 - double
MySQL 4.1, 5.0 - string

Note that this table uses double in all releases:

set @x = 1e300; create table tt as select @x

MySQL on windows is irrelevant (to me)

2009-08-27T08:32:00.000-07:00

Henrik mentioned the problem of patches for MariaDB that ignore the Windows platform. I am guilty of this as MySQL on Windows is irrelevant to me. I am not alone. What contributions do we get from the MySQL on Windows community in patches, performance tests or bug reports? I have never seen a benchmark result for MySQL on Windows.

There is nothing wrong with this. The relationship between users and vendors for MySQL on Windows is different. This is an opportunity for vendors (Sun/MySQL, Monty Program, Innobase/Oracle, Percona, Pythian, Open Query) to add value. I suspect that Sun/MySQL and Innobase/Oracle already care a lot about MySQL on Windows. But maybe they should offer a discount for MySQL on Linux users to offset our contributions (insert smiley face).

As an example of the value that can be added, I filed bug 46957 because InnoDB does not appear to support concurrent IO requests per file on Windows. That is, there can be either 1 pending read or 1 pending write to a file. That might hurt performance. I wrote about this in March and while there were assertions that this couldn't be the case I am not aware of any progress. The basis for my claim is from reading the source. Maybe someone who builds and/or runs MySQL on Windows can confirm or deny this.

If you want to learn more about systems programming on Windows, this is the book to buy. I have an earlier edition of it. One day I will read it. I tried to learn more about WriteFile, ReadFile and why win32/win64 don't support the equivalent of pread/pwrite by searching online for 'WriteFile API' and 'WriteFile API site:mysql.com'. The results were disappointing. Am I expected to buy a copy of the API docs?

A reason to use 5.1

2009-08-12T07:39:00.000-07:00

We now have 2 great storage engines for 5.1 -- InnoDB 1.0.4 and XtraDB. We need more performance results to understand InnoDB 1.0.4, but it looks excellent from the code I have reviewed. This describes some of the changes based on a brief review. All of this make my work easier as I can reduce the size of the patch I need to maintain extreme performance with MySQL.

Kudos to InnoDB for delivering these features in 5.1 and to Percona and Google for contributing patches.

support for more background IO threads - InnoDB and XtraDB support a configurable number of background IO threads for prefetch reads and dirty page writes. The my.cnf parameters are innodb_read_io_threads and innodb_write_io_threads. Prefetch read requests are generated during queries and when insert buffer entries must be merged. For InnoDB, IO requests are hashed by extent number (64 16kb pages per extent) to the per thread request queues although when a request queue is full, then a request will use any queue. Each queue can hold 256 pending requests. I assume the code in XtraDB is the same. The Google patch uses one queue for all read or write threads which should provide better throughput when there are hot extents, but also requires many more changes to the current source.
support for group commit - not only does this fix an old regression, I think that it also fixes bug 46459 which degrades performance when autocommit insert statements are used on tables with an auto increment column.
adaptive flushing - one of the things that makes InnoDB is the use of adaptive algorithms to keep the server balanced. Many of these are not documented because they work and we don't need to know about them. They may have added a new one with support for adaptive flushing. I hope to see performance results from Percona for this, but I think this is another thing in InnoDB we can soon forget as it will work without problems.
readahead - prior to 1.0.4, InnoDB could generate read prefetch requests when it detected sequential or random access to most pages in an extent. For 1.0.4, the use of readahead for random access to pages within an extent appears to have been removed. The use of readahead for sequential access to pages within an extent has been changed to use a new my.cnf parameter, innodb_read_ahead_threshold, that sets the number of pages that must be accessed sequentially within an extent before all of the pages in the physically adjacent extent will be prefetched. I am still not fond of this feature because:

I am not aware of any performance counters that report on the success of readahead (#fetched versus #fetched_and_used). But you can disable readahead now and measure the impact on your application.
Prefetch requests for the pages in the next extent are generated late. For example, if innodb_read_ahead_threshold=56, then requests are generated when the 56th (out of 64) page in the current extent is used.
If request merging is done for all of the pages in the next extent, then a 1MB read will be used and none of the pages can be accessed until the read completes.

Fast count(*) for InnoDB

2009-08-04T11:20:00.000-07:00

Why must SELECT COUNT(*) FROM FOO run fast? It is much more valuable to make that query fast when it has a WHERE clause. When there isn't a where clause, MyISAM executes SELECT COUNT(*) FROM FOO fast. When there is a WHERE clause, MySQL has limited support for combining index scans but nothing like bitmap indexes.

If you must, the following will make SELECT COUNT(*) FROM FOO fast for InnoDB:

Install INSERT and DELETE triggers to maintain the row count for the InnoDB table in a metadata table. This will kill concurrency on the table, just like MyISAM.
Modify the parser to accept SELECT ESTIMATED_COUNT(*) and evaluate this internally to use the row count estimate provided by the storage engine. Is the estimate good enough? If an exact answer is needed and the table is not locked after the exact value is computed, then the exact value will quickly become incorrect.

Who knew Ingres had it in them?

2009-07-29T09:26:00.000-07:00

VectorWise has emerged from stealth mode. Even more interesting is that Ingres is collaborating with them on Ingres VectorWise. And most interesting is the video made to promote the work. In the interest of disclosure, I have friends who work on this at Ingres and VectorWise.

I have read many of the papers on X100 and MonetDB and believe the performance claims they make. Within the MySQL community, this is most similar to Kickfire in terms of the query workloads it can support. The difference is that VectorWise writes their software to run at full speed on a modern CPU and maintain a high IPC rate while Kickfire uses custom hardware (and a lot of really good software that they probably cannot describe without an NDA). Anyone attempting to build a VectorWise storage engine would have to do all of the fun things that KickFire has done to circumvent much of the MySQL optimizer and execution code, so I doubt that will be repeated.

It is alive -- Monty Program AB

2009-07-24T08:47:00.000-07:00

I have been curious about Monty Program AB as their mailing list has been relatively quiet. They have been too busy to write much as they are setting up the company, talking to customers and doing real work including debugging the problems with MRR/BKA/ICP and InnoDB (thanks Igor and Sergey).

I will let them reveal their plans for growing the MariaDB community but I hope it includes my agenda of improving MySQL replication. Drizzle has a very clean interface for providing different replication implementations. I think the same is possible with MariaDB. It won't be as clean as the API in Drizzle because it has not diverged from MySQL source, but it should allow us to provide interesting alternatives (sync replication, conflict resolution with multi-master) in a reasonable amount of time. With such an interface and with row-based replication we can make it much easier and less expensive to manage a large MySQL deployment.

Relevance in the datacenter

2009-07-23T11:02:00.000-07:00

Do you know SQL or do you NoSQL? MySQL has been very popular for internet-scale deployments. But times have changed and there are alternatives. The alternatives either out-scale or out-avail MySQL and this is more important than providing the features of an RDBMS for many applications. My prediction is that there will be much less usage of MySQL for internet-scale applications in the future if we do not make big changes.

What are the problems and what can we do to fix them? From my perspective there are two problems:

MySQL is not efficient on modern hardware (multicore, many disk IOPs)
Replication is very expensive to manage

We are in the process of fixing the first problem for InnoDB and Percona has binaries you can use in production today that make things much better. However many problems remain that limit throughput on servers with 8+ cores and there is little visible work in progress to fix them (MyISAM, query cache, LOCK_table, ...). This is a serious issue as 8 cores is or will soon be the new common box in the datacenter and price/performance comparisons will get much worse for MySQL.

Replication requires much more work. I want more automation and more flexibility.

The lack of automation is apparent when you consider the replication related errors that require manual intervention. These errors are frequent or constant when you run a large number of MySQL servers. It is very expensive to support MySQL in this environment. Actions that must be automated include:

the promotion of a slave to a master after the failure of the master
failover of slaves to the new master

I also want the flexibility to extend replication. I have participated in the development of many replication enhancements (semi-sync, mirror binlog, global group IDs) and that effort has been incredibly difficult. I am still amazed at what Wei and Justin were able to accomplish. I doubt that anyone would ever volunteer for such a project (I was paid). The code is not fun to modify.

I have more ideas to improve replication but it isn't clear to me that I can afford the cost to modify the replication code in official MySQL. But then I looked at the code for Drizzle. Wow! The code is clean, easy to read and easy to modify. So I still have hope for MySQL-related technology in the datacenter, but in the form of Drizzle.

No docs for you

2009-07-22T13:53:00.000-07:00

I use http://dev.mysql.com every day. But not today as the site is down. There is a mirror of the docs at http://docs.sun.com/source/mysql-refman-5.0/index.html. Although the docs license is one of my favorite topics, I am not a lawyer. Does the license not allow others to serve a copy of the docs?

Crashing bugs

2009-07-19T09:38:00.000-07:00

What does it mean to release a server with no known crashing bugs? I don't know. A lot of this depends on your perspective. Perhaps old releases of MySQL were done with no known crashing bugs, but this means that testing for current releases is much more rigorous. If you don't believe me then read some of the bug updates from Shane and others to understand how good they are at testing it.

I judge quality by whether I can depend on the code in production. Both 4.0 and 5.0 have been stable in production for me and both have had crashing bugs. I am sure that MySQL 5.0 releases have had more known bugs than 4.0 releases as 5.0 has more features, a larger community testing it and is run on much larger hardware (more RAM, more disks, multicore, more load). Note that my perception of 5.0 is based on using it with many features disabled including query cache, subqueries, stored procedures, triggers and views.

The quality metric that I am interested in is the number of major releases for a feature to become stable in production and feature complete. I don't mind when this number if more than 1 as long as I can disable the feature. But this number should never exceed 2.

Listen to Monty

2009-07-16T06:08:00.000-07:00

I don't always agree with Monty but this time he is right. Now is the time to provide feedback on the merger. I wish there were more commentary on plans for MySQL after the merger.

My concern is the documentation license. If we want to improve MySQL at a faster rate than the docs copyright holder, then we must create separate documentation as we cannot publish a modified version of the official documentation. Several people have written about this before including me, Baron, Sheeri, Arjen and Justin.

Despite comments on stage between Karen and Kaj at the User Conference, there have been no changes to the docs license. The lead for the MySQL docs team explained to us that there will be no change and he deserves kudos for his bravery. I am very disappointed that neither Karen nor Kaj have said anything on this topic since then.

What else can you do? Find a different community. Check out the activity on the Drizzle mailing lists. I don't participate in Drizzle but I lurk there and am extremely impressed by the activity on the mailing lists and the changes they make to the code. MariaDB has yet to become active for external developers but it is quickly gaining momentum.

Hello Facebook!

2009-06-22T08:00:00.000-07:00

Today is my first day at Facebook. I am thrilled to be here. This is a great opportunity for me to work on hard problems with talented people. I expect to continue making MySQL faster.

You can friend me on Facebook. I have started a Facebook group on which MySQL can be discussed. I will start posting there soon. But this is a group so you are welcome to join and post.

I will be at the Velocity Conference on Tuesday evening. Will anyone else who deploys MySQL be in town?

What could possibly go wrong?

2009-06-16T09:11:00.000-07:00

The default table type was changed from MyISAM to InnoDB on a few production servers. There were good reasons for doing this. Besides, what could possibly go wrong? Sometimes the status quo has value even when you can't elaborate it.

MyISAM tables are created in several cases:

CREATE TABLE (...) engine=MyISAM
CREATE TABLE (...) ; // when the default storage engine is MyISAM
implicit temporary tables created for ORDER BY and GROUP BY processing that are too large are converted from HEAP to MyISAM

What was the problem?

Users began reporting ER_LOCK_TABLE_FULL errors during long running CREATE TABLE AS SELECT statements. These statements did not specify the storage engine type and began using InnoDB. From reading the code, this error is raised when buf_LRU_buf_pool_running_out returns TRUE during an insert statement. It returns true when less than 25% of the memory allocated for innodb_buffer_pool_size is available for the buffer pool. Memory from this allocation can be used elsewhere including for lock structures.

I need to investigate this to determine whether the locks were used for the created table or the selected table. I don't think the locks have to be used in either case. Locks on rows in the created table might not be needed because the table is not visible to other sessions until the CTAS statement completes (right?). In this case it was CREATE TEMPORARY TABLE ... SELECT, so the table will never be visible to others. Share locks on rows in the selected table might have been taken to guarantee deterministic replay in replication. But this statement was run on a slave, so that should not be needed. I have dealt with this particular problem in the past.

When innodb_locks_unsafe_for_binlog is set in my.cnf, then share locks are not obtained on rows read from the selected table. InnoDB really should be clever enough to do this when the binlog is not open.

Lock structs appear are not allocated for the inserted table regardless of the value of innodb_locks_unsafe_for_binlog, so I have yet to determine the source of memory allocations from the buffer pool. The binary we use has behavior similar to innodb_locks_unsafe_for_binlog but based on whether or not the binlog is open.

Still a mystery

Memory is allocated from the buffer pool for:

Buffer pool frames
Row locks (trx->lock_heap) - but row locks should not be allocated for this statement given the fix or if innodb_locks_unsafe_for_binlog were used.
Recovery (recv_sys->heap) - this looks like code that is only run during crash recovery when the server is started.
Adaptive hash index (btr_search_sys->hash_index) - this should use no more than a fixed amount of memory as the number of pages to be indexed is fixed.

So, given the above, I don't know what is using memory from the buffer pool. But I learned a bit more about InnoDB, so all is not lost.

Notes from 5.0.77

A call path to buf_LRU_get_free_block is:
mem_heap_create_block -> buf_frame_alloc -> buf_block_alloc

And mem_heap_create_block is called by (search for MEM_HEAP_BUFFER)

mem_heap_create_func
mem_heap_add_block

So the callers are:

mem_heap_create_in_buffer

used for trx->lock_heap, recv_sys->heap, ha_create

mem_heap_create_in_btr_search

used for ha_create

And ha_create is used for btr_search_sys->hash_index

Alternate page sizes in InnoDB

2009-06-15T20:14:00.000-07:00

InnoDB supports multiple page sizes. The default page size is 16kb and compiled into the binary/plugin. The valid page sizes are 8kb, 16kb, 32kb and 64kb. However, there is a known bug for page sizes > 16kb with row_format=COMPACT. Also, there are few deployments with a page size other than 16kb, so the amazing reliability of InnoDB may degrade for other page sizes until the bugs are found and fixed.

To use an alternate page size, edit innobase/include/univ.i to change these:

/* The universal page size of the database */
#define UNIV_PAGE_SIZE (2 * 8192)
/* The 2-logarithm of UNIV_PAGE_SIZE: */
#define UNIV_PAGE_SIZE_SHIFT 14

Does InnoDB support 4kb pages? Maybe. If you edit the fields listed above for a 4kb page size and run MySQL, you will quickly get a core dump. The problem is that there are other per-page objects that are too big for a 4kb page. The first problem I encountered is fixed by editing innobase/include/trx0rseg.h to reduce the number of rollback segment slots per page. The default value is:

#define TRX_RSEG_N_SLOTS 1024

To be really safe, I set it to 256 and the segfault was fixed. I haven't done testing beyond this so your mileage may vary.

InnoDB had a 900 byte per-page overhead when I last measured this. 900 bytes per 4kb page is much worse than per 16kb page. I am not sure when this will get fixed.

InnoDB, IO-bound OLTP, 2-disk server

2009-06-08T08:43:00.000-07:00

I have run many tests on mid-size servers to show how patches make InnoDB faster for IO bound workloads. The servers could do more than 1000 IOPs and the patches make a big difference. But do the patches help on small servers? I ran tpcc-mysql on my home server with 2 SATA disks, 2 CPU cores and 2GB RAM. There were two interesting results:

The v4 Google patch makes things faster.
Only the v4 Google patch enforced innodb_max_dirty_pages_pct=20. InnoDB is not good at enforcing this limit on write-intensive workloads. This problem has not received much press. If you are running a critical OLTP server and want it to recover quickly after a crash then this limit must be enforced. Code has been added to the v4 Google patch to delay user sessions by making them flush dirty pages prior to making more pages dirty if the limit has been exceeded. This is enabled by the my.cnf parameter innodb_check_max_dirty_foreground. Nobody has reviewed this code (hint, hint).

I tested the v4 Google patch (5.0.37), Percona highperf b13 (5.0.77) and unmodified MySQL 5.0.77 using tpcc-mysql. The server uses 2 disks with SW RAID 0, 1 MB RAID stripe, XFS and the SATA write cache disabled.

tpcc-mysql was run with 20 warehouses and 8 users with a 600 second warmup and 3600 second measurement period.

Results

The 5077 binaries were faster than the v4 Google patch at innodb_max_dirty_pages_pct=20 because they did not enforce that limit. I have included a result for the v4 Google patch at innodb_max_dirty_pages_pct=50 to provide an additional reference point.

Binary	TpmC	innodb_max_dirty_pages_pct	Avg %dirty pages
v4-5037	1294	20	25.1
percona-5077	2112	20	60.0
mysql-5077	2836	20	72.1
-
v4-5037	3024	50	52.8
-
v4-5037	3854	80	72.0
percona-5077	2930	80	73.7
mysql-5077	3373	80	82.8

Throughput over time for v4-5037:

Configuration

Notes:

tests were run twice for each binary with innodb_max_dirty_pages_pct set to 20 and 80
the Percona highperf b13 5.0.77 binary uses more memory for the same value of innodb_buffer_pool_size, so I had to reduce that value to 1G

The my.cnf settings for the v4 Google patch:

innodb_buffer_pool_size=1200M
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=80 or 20
innodb_ibuf_max_pct_of_buffer=10
skip_innodb_ibuf_reads_sync
innodb_doublewrite=0
innodb_file_per_table
allow_view_trigger_sp_subquery
skip_innodb_readahead_random
skip_innodb_readahead_sequential
innodb_flush_method=O_DIRECT
innodb_check_max_dirty_foreground

The my.cnf settings for Percona highperf b13 5.0.77:

innodb_log_file_size=1900M
innodb_buffer_pool_size=1000m
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=80
innodb_ibuf_max_size=120M
innodb_ibuf_active_contract=1
innodb_ibuf_accel_rate=200
innodb_doublewrite=0
innodb_read_ahead=0
innodb_adaptive_checkpoint=1

The my.cnf settings for 5.0.77:

innodb_buffer_pool_size=1200M
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=80
innodb_doublewrite=0
innodb_file_per_table
innodb_flush_method=O_DIRECT

Buffered versus direct IO for InnoDB

2009-06-05T11:03:00.000-07:00

I learn a lot about InnoDB when people ask me questions. In this case, someone asked whether fsync was used for writes done when InnoDB is running with innodb_flush_method=O_DIRECT. It is still used. It does not need to be used and I am not sure whether this has a measurable impact on performance.

Buffered IO

You can configure InnoDB to use direct IO for data files or for transaction log files but not for both at the same time. I added a new value for innodb_flush_method, allsync, to change that. When innodb_flush_method=allsync is used, the behavior for O_DIRECT and O_DSYNC (described below) are implied. I have yet to find a significant performance benefit from that change. I may not have the right hardware.

Data files are opened with O_DIRECT when innodb_flush_method is set to O_DIRECT. fsync is still used in this case, but it doesn't need to be.

Transaction log files are opened with O_SYNC when innodb_flush_method is set to O_DSYNC. fsync is not used in this case. Writes to the transaction log are done in multiples of OS_FILE_LOG_BLOCK_SIZE (set to 512 in os0file.h). The filesystem block size is likely to be much larger than 512 and each 512 byte (or N*512 byte) write done by InnoDB requires a file system block write to the disk. According to the man page and actual behavior on Linux, buffering can be done so that a sequence of 512 byte writes with a 4kb file system block size does not require a disk read on every write.

It might be good to use a larger value for OS_FILE_LOG_BLOCK_SIZE (1024?) and a smaller file system block size for the file system that stores the transaction log (but not for the file system that stores the data files). This is more likely to be useful on SSD and dependent on your workload.

Update

I used sysbench fileio to determine whether there is a performance impact from calling fsync after writes on a file opened with O_DIRECT. There is an impact, but it remains to be seen whether that translates to a performance impact for InnoDB. To test this, I used a server with:

2 CPU cores
2 disks, SATA, 7200 RPM
2 disk SW RAID 0, 1MB RAID stripe, XFS file system
SATA write cache enabled

I used this sysbench command line with fsf set to (0, 1) and nt to (1,2,4,8,16):

sysbench --test=fileio --file-num=1 --file-total-size=4G --file-test-mode=rndwr --file-extra-flags=direct --file-fsync-freq=$fsf --num-threads=$nt --max-requests=0 --max-time=60 run

Writes per second was always higher when fsync was not done and the SATA write cache enabled:

503 vs 532 for --num-threads=1
501 vs 438 for --num-threads=2
510 vs 447 for --num-threads=4
495 vs 451 for --num-threads=8
502 vs 464 for --num-threads=16

They were also higher when fsync was not done and the SATA write cache disabled:

109 vs 49 for --num-threads=1
160 vs 58 for --num-threads=2
189 vs 70 for --num-threads=4
209 vs 77 for --num-threads=8

Update2

I get ~5% speedup on tpcc-mysql (1294 vs 1237 transactions per second) when fsync calls are disabled with innodb_flush_method set to O_DIRECT. The server for this test is described in the previous section. The SATA write cache was disabled for the test. These my.cnf parameters were used:

innodb_buffer_pool_size=1200M
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=20
innodb_ibuf_max_pct_of_buffer=10
skip_innodb_ibuf_reads_sync
innodb_doublewrite=0
innodb_file_per_table
allow_view_trigger_sp_subquery
skip_innodb_readahead_random
skip_innodb_readahead_sequential
innodb_flush_method=O_DIRECT
innodb_check_max_dirty_foreground

Current events in the MySQL community

2009-06-03T09:26:00.000-07:00

Jeremy has an article in LinuxMag. This is worth reading.

On synchronous replication

2009-06-02T09:36:00.000-07:00

Is synchronous replication possible in MySQL? Yes. Is it possible without major surgery to the existing code? Probably (or hopefully). Notes on an approach are at code.google.com.

The MySQL replication team may be working on this now for MySQL 6.0. They have spent a lot of time recently making replication flexible to support semi-sync and other new features. I assume they plan to support sync replication as well.

Performance impact of prefetching in InnoDB

2009-06-01T15:16:00.000-07:00

InnoDB prefetches blocks when it detects multiple accesses to blocks within an extent. Unfortunately, there are no metrics in the server to determine whether it is effective. There are also weak metrics in the server to determine how frequently it is done -- counters incremented each time the readahead code prefetches one or more blocks rather than once per prefetch request.

There are cases where prefetch improves performance. A query that does a full table scan was run with prefetch enabled and disabled. It was 35% slower with prefetch disabled.

Percona and Matt have written about potential performance problems from this feature. There isn't much data to indicate when this feature should be enabled. I have published data for a few IO-bound benchmarks. On these tests, the prefetching done by InnoDB reduces performance. The tests run were:

insert benchmark
tpcc-mysql with data cached by InnoDB
tpcc-mysql with too much data to cach

XtraDB has an option to disable readahead. The v4 Google patch changes the SHOW STATUS counters for readahead to display the number of prefetch requests. An upcoming v4 Google patch will also have options to disable readahead.

Update -- my peers just reminded me to add support for per-session (dynamic) usage of the new my.cnf parameters (innodb_readahead_random, innodb_readahead_sequential).

JavaOne and the Open HA Cluster Summit

2009-05-30T08:44:00.000-07:00

Are you going to JavaOne? I am not, but I will be at the Open HA Cluster Summit tomorrow. I will be part of a panel session on HA. I guess they needed someone with the HA light perspective -- that is how do you get a highly available service when you don't get to use HA components like MySQL Cluster. A lot of interesting work remains to be done to make this possible with regular MySQL. Projects like MMM, Tungsten and the Google patch with global transaction IDs are pieces that might eventually provide a complete solution. There is work underway at MySQL/Sun in the replication team as well. They may even be buiding the integrated solution for MySQL Enterprise.

InnoDB performance TODO list

2009-05-28T11:33:00.000-07:00

These are my plans for making InnoDB faster on SMP and high-IOPs servers. I think we can double throughput at high levels of concurrency.

Future work:

Reduce the size of mutex and rw-lock structures
Reduce contention on the sync array mutex
Reduce contention on kernel_mutex
Reduce contention on commit_prepare_mutex
Reduce the number of mutex lock/unlock calls used when a thread is put on the sync array
Name all events, rw-locks and mutexes in InnoDB to make contention statistics output useful
Add optional support to time all operations that may block
Introduce dulint to native 64-bit integer types
Make BUF_READ_AHEAD_AREA a compile-time constant
Prevent full table scans from wiping out the InnoDB buffer cache
Make prefetching smarter
Get feedback from Dimitri, Domas, Mikael and Percona
Use prefetch with MRR/BKA to get parallel IO in InnoDB
Investigate larger doublewrite buffer to allow for more concurrent IOs
Make Innodb work with a 4kb page size
Make trx_purge() faster when called by the main background thread
Use crc32 for Innodb page checksums with hardware support or otherwise make checksum faster.
Reduce the per-page overhead for sync objects
Repeat

Current work:

Add my.cnf options to disable InnoDB prefetch reads
Put more output in SHOW INNODB STATUS and SHOW STATUS
Reduce the overhead from buf_flush_free_margin()
Change background IO threads to use available IO capacity
Use more IO to merge insert buffer records when the insert buffer is full

Non-InnoDB work:

Fix mutex contention for the HEAP engine
Fix mutex contention for the MyISAM engine
Fix mutex contention for the query cache
Give priority (CPU, disk) to the replication SQL thread to minimize replication delay.
Push changes for --oltp-secondary-index to public sysbench branch
Add support to sysbench fileio for transaction log and doublewrite buffer IO patterns

InnoDB checksum performance

2009-05-28T10:17:00.000-07:00

Once again Domas is unhappy with some aspect of Innodb performance and doing crazy things with gdb to tune it. I made it faster by changing the checksum code to process one 32-bit word at a time rather than one byte at a time. This will be in a future Google patch and is enabled with the parameter innodb_fast_checksum. This is not compatible with the old checksum so you must dump and reload the database to use it.

I measured the benefit using the insert benchmark from Tokutek on a server that can do a lot of IO. CPU overheads are measured using oprofile. The data below lists the percentage of time for the top 4 functions in mysqld. The checksum is computed in buf_calc_page_new_checksum. By using the fast checksum, the checksum overhead drops from 33.6% to 22.1% for gcc -O2 and from 31.6% to 17.3% for gcc -O3.

Overhead for gcc -O2

Using the original checksum code:

33.6% - buf_calc_page_new_checksum
10.4% - memcpy
4.4% - os_aio_simulated_handle
4.3% - rec_get_offsets_func

Using the fast checksum code:

22.1% - buf_calc_page_new_checksum
12.1% - memcpy
5.1% - rec_get_offsets_func
4.9% - os_aio_simulated_handle

Overhead for gcc -O3

Using the original checksum code:

31.6% - buf_calc_page_new_checksum
12.6% - memcpy
5.8% - rec_get_offsets_func
2.6 - os_aio_simulated_handle

Using the fast checksum code:

17.3% - buf_calc_page_new_checksum
13.6% - memcpy
6.8% - rec_get_offsets_func
2.0% - os_aio_simulated_handle

InnoDB IO performance and the v4 Google patch

2009-05-26T17:16:00.000-07:00

The v4 patch has been published. A description of the changes and performance results are here. I am still analyzing the results to make sure that I can explain performance differences and the lack of performance differences.

A good reason to use inodb_file_per_table -- per-table IO statistics

2009-05-22T22:51:00.000-07:00

I added support for per-tablespace IO statistics to InnoDB. This also provides per-table IO statistics when you innodb_file_per_table is used. The stats are listed in SHOW INNODB STATUS and the text below is output when tpcc-mysql is run -- pardon the formatting. The code should appear at code.google.com real soon now.

File IO statistics
./test/warehouse.ibd 10 -- read: 4 requests, 4 pages, 0.00 secs, 0.72 msecs/r, write: 3 requests, 3 pages, 0.00 secs, 1.43 msecs/r
./ibdata1 0 -- read: 30 requests, 203 pages, 0.03 secs, 0.99 msecs/r, write: 124 requests, 3020 pages, 0.74 secs, 5.93 msecs/r
./test/orders.ibd 29 -- read: 8490 requests, 10033 pages, 8.48 secs, 1.00 msecs/r, write: 6754 requests, 12728 pages, 34.27 secs, 5
.07 msecs/r
./test/customer.ibd 28 -- read: 33901 requests, 34226 pages, 32.05 secs, 0.95 msecs/r, write: 11224 requests, 11850 pages, 43.17 se
cs, 3.85 msecs/r
./test/stock.ibd 27 -- read: 151957 requests, 176913 pages, 256.89 secs, 1.69 msecs/r, write: 41475 requests, 52199 pages, 220.43 s
ecs, 5.31 msecs/r
./test/order_line.ibd 25 -- read: 14239 requests, 14876 pages, 13.10 secs, 0.92 msecs/r, write: 11610 requests, 38413 pages, 45.01
secs, 3.88 msecs/r
./test/new_orders.ibd 22 -- read: 2023 requests, 2316 pages, 1.80 secs, 0.89 msecs/r, write: 1213 requests, 7004 pages, 7.58 secs,
6.25 msecs/r
./test/history.ibd 21 -- read: 5740 requests, 7711 pages, 5.64 secs, 0.98 msecs/r, write: 4938 requests, 22754 pages, 27.97 secs, 5
.66 msecs/r
./test/district.ibd 18 -- read: 15 requests, 15 pages, 0.01 secs, 0.78 msecs/r, write: 8 requests, 31 pages, 0.02 secs, 3.02 msecs/
r
./test/item.ibd 16 -- read: 757 requests, 904 pages, 0.67 secs, 0.89 msecs/r, write: 0 requests, 0 pages, 0.00 secs, 0.00 msecs/r
./ib_logfile0 4294967280 -- read: 6 requests, 9 pages, 0.00 secs, 0.02 msecs/r, write: 25630 requests, 25877 pages, 0.56 secs, 0.02
msecs/r

Patch for global transaction IDs, binlog event checksums and crash-safe replication state

2009-05-12T12:44:00.000-07:00

Justin just added a patch for global transaction IDs, binlog event checksums and crash-safe replication state. It is at code.google.com. This patch is based on MySQL 5.0.68, so Justin did a bit of work to port code forward from the version we use (5.0.37).

Well, I assume that this includes support for crash-safe replication state. This replaces transactional replication. But it works for all storage engines.

Percona has ported a few of the replication features from previous Google patches. Hopefully, they are interested in these changes. MySQL has semi-sync replication in 6.0 with a promise to backport to 5.4. Perhaps these changes will end up there too.

Vendor lock in and MySQL documentation

2009-04-30T08:26:00.000-07:00

Part of the sales pitch for MySQL is that there is less risk of vendor lock in. This is repeated frequently on their marketing here, here, here and here. The explanation is that the source code for MySQL is available with a GPL license and if you are unhappy with MySQL the company you can continue using MySQL the product and get support elsewhere.

Documentatation does not have a similar license. You can decide whether this creates the risk of vendor lock in. Details are here.

We cannot edit it.
We have limited rights to publish it.

Isn't it in the best interests of Sun/MySQL to address this issue and reassure potential customers?

Arjen, Sheeri, Baron and the lead for the MySQL docs team have also written about this.

InnoDB on IO bound workloads

2009-04-28T13:39:00.000-07:00

I ran the iibench test using a server with 2 CPU cores, 2 disks in SW RAID 0 and 1 MB stripe, 2G RAM and XFS. If you just want a summary, it is that software changes can make InnoDB run much faster on the same hardware. There is a lot of opportunity -- but certainly not enough to catch TokuDB.

The binaries tested are:

XtraDB 1.0.3-5
InnoDB with MySQL 5.0.77
v3 Google patch dated April 12

There were two tests:

Time to insert 50M rows into an empty table
Time to insert several million rows into a table with 50M rows

I disabled the innodb doublewrite buffer for all tests as I want to compare the results to a server that doesn't use that level of safety.

The my.cnf parameters for 5.0.77 are:

innodb_buffer_pool_size=1G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=20
innodb_doublewrite=0

The my.cnf parameters for the v3 Google patch are:

innodb_buffer_pool_size=1G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=20
innodb_ibuf_max_pct_of_buffer=10
innodb_ibuf_reads_sync=1
innodb_doublewrite=0

The my.cnf parameters for XtraDB are:

innodb_log_file_size=1900M
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=250
innodb_use_sys_malloc=0
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=20
innodb_ibuf_max_size=100M
innodb_ibuf_active_contract=1
innodb_ibuf_accel_rate=200
innodb_doublewrite=0

All performance results are anonymous for binaries X, Y and Z. Maybe I can monetize my performance testing effort by doing this. I probably need a new disk soon.

The first result is the time to insert 50m rows into an empty table measured in seconds. The difference is not that signficant. However, the results can be misleading. 5.0.77 has much more pending work at test end (dirty pages and insert buffer entries). That also made 5.0.77 much slower near the end of the test, but I will save the graphs for the next set of results. The test is run using the run_ib found in the link for iibench at the start of this page. The command line is:

bash run.sh 1 $path no root pw test no innodb 50000000 innodb yes yes $binary

And the results are:

24478 seconds -- binary X
21016 seconds -- binary Y
37146 seconds -- binary Z

The second result is the time to insert 3,380,000 rows into a table that starts with 50m rows on a cold server (no entries in the insert buffer, no dirty pages, server restarted). Queries are continuously run by 4 threads concurrent with the inserts. The test is run using run_ib from the iibench link at the top of this page. The command line is:

bash run.sh 1 $path no root pw test no innodb 10000000 innodb no no $binary

And the results are:

38673 seconds -- binary X
11143 seconds -- binary Y
21018 seconds -- binary Z

Finally, the graph for row insert rate over time. Note that the graphs for binaries Y and Z don't extend to the right because they inserted the 3.3M rows much faster.

Hack on Drizzle, get paid!

2009-04-27T15:15:00.000-07:00

Did you know that Rackspace has a cloud offering? I didn't. The name is Mosso.

Someone from Rackspace/Mosso on the drizzle-discuss mailing list offered to hire a person full time to work on Drizzle. Curious? Find the post on the mailing list.

Drizzle has a lot of potential for making it easier to run a DBMS server on the cloud. There are a few things that need to be done differently from traditional MySQL replication. Drizzle has started over and has removed the code inherited from MySQL. Their focus is a clean API (right Jay). There should be a lot of interesting work that can be done.

Why must you insult us Matt?

2009-04-27T12:20:00.000-07:00

Surely there must be a better way to get your point across. Well, I assume this is an insult because you don't appear to be too hippie-ish. But if you really are the expert in open-source business that you claim to be, you probably can do better than to describe part of the MySQL community as an open-source hippie commune that displays hippie-esque tendencies, unless they self-identify as that. Although that will be a fair description once we start holding the user conferences at Burning Man and Country Fair.

I haven't linked the blog in question because I don't want to promote it.

Slides for MySQL User Conference 2009 Talks

2009-04-26T06:10:00.000-07:00

Slides for my talks. Code described here is in the v3 Google patch.

What is next?

2009-04-25T13:37:00.000-07:00

In his keynote Baron reminded us that we need to focus on what we can do to improve community MySQL rather than wait for things to get done by the corporate owners. What will you do?

Many of us will continue to add high-end features to MySQL. It will great if those features make it into an official MySQL release. It will be a great business opportunity for the community if they do not.

In the short term, I have some things to do:

run IO bound tests (iibench) for PBXT and provide the results to the PBXT team with a comparison to the v3 Google patch
run IO bound tests (iibench) for XtraDB and provide the results to Percona with a comparison to the v3 Google patch
publish more documentation for features in the v3 Google patch (support for roles, more changes to improve IO performance, more details on row-change logging)
read the docs and evaluate embedded InnoDB

Other people on my team also plan to share more details and code:

Ben is working on a backport of the pool-of-threads code to MySQL 5.0. While the backport itself to 5.0.37 might not help those using 5.1 or recent 5.0 versions, we have also fixed the SMP performance problems in the pool-of-threads code and that change is isolated to a single file. Others will be able to use it (but only on Linux as it uses epoll directly).
Justin may publish a patch for a recent version of 5.0 that only includes the changes for global group IDs, binlog event checksums and crash safe replication that works for all storage engines.

Much of the big patch is not easy to consume. But there is an answer to that. If you really want the feature then you can hire someone to port it to a recent 5.0 or 5.1 release. Rumor has it that Percona has done just that with several features. This makes me happy and proud.

InnoDB has continued to add valuable features to their releases. The most recent is embedded InnoDB. It can be used to do some very interesting things. But first I must read the docs. They have also added a lot of new functionality to the 5.1 branch via the InnoDB plugin. This includes fast index creation, compression and SMP performance improvements. This is a big deal as the standard response and practice from MySQL is that new features cannot go into a production branch.

New storage engines for MySQL -- rocket science or great engineering?

2009-04-24T09:08:00.000-07:00

There were several new storage engine vendors at the MySQL Conference. I spoke with people from Virident, Tokutek and Schooner at length about their technology. Their products are impressive and I look forward to more details on performance from them including read-intensive and write-intensive workloads. Tokutek has already published performance results on an insert intensive workload and then worked with me to improve the InnoDB results and improve the test code so others can run it. The code and results are here.

One test I want all of them to run is to run a write-intensive workload so that InnoDB accumulates many dirty pages in the buffer pool and many entries in the insert buffer, kill mysqld and then determine how long it takes the server to perform crash recovery. This should be compared between InnoDB on commodity hardware, InnoDB on Virident and Schooner hardware and TokuDB on commodity hardware. I suspect that the results will be impressive for the new storage engines.

I use the term rocket science because a lot of vendors will have you believe that they have something special. In this case, I believe that each of the vendors really do have something special. But of course, more results will help us understand what they can do. Each of them have also chosen a path that doesn't require a huge investment on their part to build a product as they have limited their software and hardware investments to areas where they have a lot of value to add and that makes it more likely that they can deliver on their promises.

TokuDB is software-only. But this is really clever software. They have implemented a new algorithm that significantly reduces random IO for write-intensive workloads. There have been algorithms that do this. For example, Log-Structured Merge Trees. I have even published a paper on this at VLDB. But TokuDB may be much better than previously known approaches.
most of the hardware expense for Virident is isolated to one component that implements industry standard interfaces and can plug into commodity servers. Their software investment is focused on improving pieces of MySQL/InnoDB to leverage their hardware. They have been able to improve on the work of others in the InnoDB developer community.
Schooner uses mostly commodity hardware with value-added in the integration of that hardware. Their software investment in MySQL also appears to be focused on improving pieces of it rather than replacing it and they have been able to improve on the work of others.

TokuDB allows a small server to handle a much larger workload. This has many benefits including reduced power consumption and less need to shard or add shards to a large MySQL deployment. I think they also use much less disk space than InnoDB. Their technical staff explained the math behind the algorithms that justify their performance. Math is hard so this took some time, but eventually I kind of understood and I believe in their results. Their approach will enable many more optimizations in the future.

The servers from Virident and Schooner are optimized for InnoDB, so it should be easy for existing users to try them out. I expect ridculously high throughput results from both of them. Schooner hardware is easier to understand as they provide much better IO performance (and many other benefits). They also appear to have designed a balanced system so that peak and actual performance won't be too far away. Oracle has done this with the Exadata machine and it is very nice to see a similar effort from Schooner.

Virident uses NOR Flash to provide fast access with byte-level accessing (as opposed to reading a disk page at a time). This takes more time to understand. It is almost as if they have reduced the InnoDB page size to the size of a row, so much less data is transferred when reading rows randomly. MySQL loves to access rows randomly and reading less data means less effort is wasted and there should be less contention on shared resources with many-core and multi-core servers.

Cool things you can almost do with replication

2009-04-23T07:42:00.000-07:00

We added support for row-change logging to MySQL 5.0. The logged data is similar to row-based replication with changes to the output that make it much easier to parse. Gene Pang describes this work at 2pm at the conference.

What might be done with this data?

replicate row changes to a data store that is not MySQL (Teradata, HBase/Hypertable, memcached)
materialized view maintenance
change notification

And I talk at the Percona Performance Conference at 10:50am today on the InnoDB IO architecture.

Where is the Calpont code?

2009-04-22T11:22:00.000-07:00

Calpont has a talk on their MPP column-store storage engine for MySQL at 2PM today. The talk title is Open Source Columnar Storage Engine. It sounds interesting, especially if the source will be available as many people can try it out. But the source isn't available today. Where is the source?

Note, Calpont doesn't mind that I am asking about this in public.

Other questions I have include:

Does it implement the condition pushdown interface?
Will it implement the batch key access interface?

Really cool features in the Google patch

2009-04-22T09:34:00.000-07:00

Justin and Ben talk today at 4:25pm on features for SMP performance and high availability.

Ben is an expert on InnoDB internals related to SMP performance. He designed and implemented the faster rw-mutex changes that are now in the 1.0.3 InnoDB plugin and MySQL 5.4 and have made MySQL much faster on SMP servers. More recently he changed InnoDB to significantly reduce mutex contention on the transaction log and buffer pool mutexes. This makes InnoDB 20% faster on sysbench and other read-write workloads. Right now he is finishing the backport of the pool-of-threads code from MySQL 6 to 5.0 and making it scale on SMP.

Justin is a replication expert. He added support for global transaction IDs to automate slave failover, made replication slaves crash-safe, added checksums for binlog events and fixed many bugs in replication.

They both have done very interesting work. And we don't just build these features, we also run them in production soon after adding them.

InnoDB on high IOPs servers (SSD)

2009-04-22T07:15:00.000-07:00

How does InnoDB do on high IOPs servers? Thanks to SSD, many of us will soon have such servers. I will provide more details in my talks:

The summary is that InnoDB is very effective at using the read capacity of a high IOPs server. It has problems at using enough of the write capacity. Alas, this is InnoDB and the problem is easy to fix. Many of the fixes are in the v3 Google patch. Many are also in the Percona patches and builds. Although there may be one fix in the v3 Google patch that Percona has yet to implement.

Note that I may mention the P word a few times in my talk today and tomorrow.

Hack MySQL at MySQL Camp on Monday

2009-04-19T17:10:00.000-07:00

I host the MySQL Hackfest at MySQL Camp on Monday. Bring your laptop setup to build MySQL. I will have a few CDs with MySQL 5.0.37, the v3 Google patch for MySQL 5.0.37 and a few other releases of MySQL 5. Possible projects that can be started (but maybe not finished) include:

Parse the output from the row-change log and convert it to protocol buffers.
Apply the output from the row-change log to another data store (RDBMS, HBase, Hypertable)
Add a new SHOW command
Add a new SQL function

Everything I know about running MySQL on Amazon EC2...

2009-04-16T11:49:00.000-07:00

... I learned from excellent documentation written by Eric Hammond. For someone just starting out with EC2 (me), the documentation is great. It has made it very easy for me to try things out and repeat many of the performance tests I run for the v2 Google patch.

I needed to setup RAID on the local storage -- not a problem.

I needed to setup RAID over several EBS volumes -- not a problem. Just be sure to follow the instructions and use XFS rather than ext2 or ext3 (which don't allow concurrent writes to a file).

Clouds like EC2, whether internal or external, represent an interesting environment in which to deploy MySQL. Many of the requirements for a stable MySQL environment in the past might not be true anymore. For example, it isn't so easy to guarantee that the server is always shutdown cleanly (yes, MyISAM and replication state files, I am looking at you). But once a few of the problems are solved, then it becomes much easier for a few people to manage a large number of servers.

Talks at the (free) MySQL Camp

2009-04-14T21:15:00.000-07:00

David Lutz will talk about Predicting Performance with Queueing Models at the (free) MySQL Camp on Thursday at 2PM. I will be there. Good performance testing includes tests and an explanation of the results. The test results are frequently much better when the explanation includes a performance model. I usually skip the model (math is hard). My published results would be more useful were I to include the models. I have a few books by Neil Gunther gathering dust at home that I really need to read.

The v3 Google patch now includes support for row-change logging. The code has a few bugs that we are fixing ASAP but it is ready for testing. There is a talk on the this at the regular conference. We can try it out at the MySQL Hackfest at the (free) MySQL Camp on Monday morning. Row-change logging generates one text line per changed row that describes the change. It is easy to parse and can be used to:

Maintain a copy of the MySQL table in another RDBMS or in a scalable data structure such as HBase or Hypertable.
Implement a change notification service.
Maintain materialized views.

Battle of the hot boxes

2009-04-13T17:03:00.000-07:00

Two companies are in the process of launching MySQL appliances: Schooner Infotech and Virident. Both incorporate flash in some form and it will be interesting to find out what value they add. I also want to know how they overcome some of the performance limits in InnoDB for multi-core and high-IOPs servers. The problems on multi-core servers are well known. The problems on high-IOPs servers are slowly becoming understood and there are fixes in the v3 Google patch and in Percona binaries.

Do the appliances use official versions of MySQL? The schooner web site states that InnoDB 1.0.3 is used, which goes a long way towards improving performance on servers with 8+ cores.

I will describe some of the IO problems and improvements during my talks at the Percona Performance Conference and the MySQL Conference.

MySQL has a new storage engine for DB2?

2009-04-10T08:17:00.000-07:00

MySQL has a new storage engine for DB2 on IBM System i. System i used to be known as iSeries which used to be known as AS/400. With a name change rate of 1 per decade, it has at least 3 more decades as a viable system (2020 -- renamed to the i, 2030 -- renamed to i, 2040 -- renamed to '').

I would describe this as a hybrid engine as it provides an interface to an existing DBMS and is tightly integrated with the existing DBMS. By comparison, the Federated storage engine also provides an interface to an existing DBMS, but is not tightly integrated. The integration should greatly increase usability and performance. Performance can be further improved by adding support for condition pushdown for MySQL 5.1 and by implementing the batch key access interface when the engine is available in MySQL 6.

A few more comments:

My favorite line from the wikipedia page is on the System i everything is an object. Profound.
Where is the storage engine independent test suite that makes it easy to test new engine? The current suite is hardwired to use MyISAM and InnoDB for most tests.
Where is the virtual machine image that will allow us to try out System i with MySQL and the new engine using our Mac/Win/Linux boxes?

An obscure bug

2009-04-09T13:19:00.000-07:00

We have begun running a tool that determines whether tables on slaves and masters match. This tool is similar to mk-table-checksum with a few optimizations from the Google patch:

It uses the aggregate function LAST_VALUE to determine where to start a scan when a table is scanned using multiple (fast) queries rather than one slow query.
It uses the aggregate functions UNORDERED_CHECKSUM and ORDERED_CHECKSUM to compute checksums.

We soon found an InnoDB table with rows in the secondary index that were missing from the PRIMARY index. How can this be? My guess is that one or more disk writes to the PRIMARY index were lost. InnoDB stores an LSN and checksum on each page. That page for which writes were lost had a valid old version on disk. When that page is read, the checksum is still valid and there is no way to determine that the LSN is correct unless the last write for that page is recorded in the current transaction log or the doublewrite buffer.

ZFS would detect this error as it stores a page checksum separate from the disk page. RAID 10 might help. If writes for one of the copies was lost, then as long as your are lucky enough to read the page from the good side of the mirror, you will get the good data. But that might not be comforting. What else would help to detect and or correct this?

Master-master replication and crash recovery

2009-04-06T11:44:00.000-07:00

The slave SQL thread executes binlog events from the relay log to keep a slave in sync with the master. Prior to row-based replication, binlog events were SQL statements. The slave SQL thread records its state in the relay-log.info file. The state includes the file offset of the next binlog event to execute, so it is important that this state be correct to avoid skipping a transaction or running a transaction multiple times on the slave. The slave SQL thread does the following in a loop:

replay all binlog events from a transaction
commit the transaction to the storage engines that participated
write new state to the relay-log.info file

Unfortunately, if the mysqld server crashes after step 2 and before step 3 then it will run the last transaction twice (before the crash, after the crash). This may fail with a duplicate key error or it may not and leave the slave inconsistent with the master with little evidence left behind for the DBA to notice the problem.

This is bug 26540 if you want to express interest in a fix. This is also fixed in some cases for InnoDB by transactional replication which has made it into Percona. By some cases, I mean that it does not protect transactions that update MyISAM tables, at least not in the Google patch. I have not reviewed the Percona code.

This problem gets more interesting for master-master replication. In that case a server writes a binlog and a relay log and the update sequence is:

replay all binlog events from a transaction
XA prepare for the binlog
XA prepare for InnoDB (assuming InnoDB is used)
write the XID to the binlog (commit)
commit the transaction to the storage engines that participated
write new state to the relay-log.info file

In this configuration, the server uses internal XA to coordinate the update of the binlog and commit to the storage engines. There are three interesting crashes that can occur:

before step 4 - This is not a problem. The prepared InnoDB transaction is rolled back during crash recovery and then run when the slave SQL thread starts.
between step 4 and step 5 - This is a problem. The prepared InnoDB transaction is committed during crash recovery but relay-log.info is not updated. Note that transactional replication does not correct the mismatch so the last transaction will be run again when the slave SQL thread starts. Running the same transaction multiple times may cause replication to halt or may corrupt your database.
between step 5 and step 6 - This problem is fixed by transactional replication.

So there is a new problem that we need to fix for servers that will soon run with --log-slave-updates (not because I will use master-master replication). Some of the changes described in my previous post can fix this new problem, at least they do in our stress test framework.