According to a recent blog post by Google engineer Mark Callaghan, Google is a big user and fan of MySQL. Callaghan introduces some of the changes and enhancements Google made to the MySQL code base to make the DBMS more scaleable and manageable. The changes were contributed back to the community under the GPL license.
On the day MySQL announced that the company backing the popular open-source DBMS has reached $50M in revenue, and is planning an IPO, it is appropriate to note that large enterprises increasingly rely on MySQL for mission-critical projects. Google is among those large companies, and a recent blog post by Google engineer Mark Callaghan describes some of the contributions Google has made to the MySQL code base:
We think MySQL is a fantastic data storage solution, and as our projects push the requirements for the database in certain areas, we've made changes to enhance MySQL itself, mainly in the areas of high availability and manageability...
The high availability features include support for semi-synchronous replication, mirroring the binlog from a master to a slave, quickly promoting a slave to a master during failover, and keeping InnoDB and replication state on a slave consistent during crash recovery.
The manageability features include new SQL statements for monitoring resource usage by table and account. This includes the ability to count the number of rows fetched or changed per account or per table. It also includes the number of seconds of database time an account uses to execute SQL commands.
Wiki entries on each sub-project's Google Code pages describe the enhancements in greater detail:
The MySQL replication protocol is asynchronous. The master does not know when or whether a slave gets replication events. It is also efficient. A slave requests all replication events from an offset in a file. The master pushes events to the slave when they are ready...
Each replication event sent to a semi-sync slave has two extra bytes at the start that indicate whether the event requires acknowledgement. The bytes are stripped by the slave IO thread and the rest of the event is processed as normal. When acknowledgement is requested, the slave IO thread responds using the existing connection to the master. Acknowledgement is requested for events that indicate the end of a transaction, such as commit or an insert with autocommit enabled.
We have modified the slave IO thread to maintain a copy of the master's binlog as it writes the relay log. By copy, [means] that the file has the same name and same contents. When this is done, slave can transparently failover between replication proxy slaves as long as the proxies all mirror the binlog.
Replication state on the slave is stored in two files: relay-log.info and master.info. The slave SQL thread commits transactions to a storage engine and then updates these files to indicate the next event from the relay log to be executed. When the slave mysqld process is stopped between the commit and the file update, replication state is inconsistent and the slave SQL thread will duplicate the last transaction when the slave mysqld process is restarted...
This feature prevents that for the InnoDB storage engine by storing replication state in the InnoDB transaction log. On restart, this state is used to make the replication state files consistent with InnoDB.
Activity monitoring per table, account and index
We have added code to measure database activity and aggregate the results per account, table and index. We have also added SQL statements to display these values.
Asynchronous IO in InnoDB
InnoDB supports asynchronous IO for Windows. For Linux, it uses 4 threads to perform background IO tasks and each thread uses synchronous IO... InnoDB issues prefetch requests when it detects locality in random IO and when it detects a sequential scan. However, it only uses one thread to execute these requests. Multi-disk servers are best utilized when more IO requests can be issued concurrently.
[For] servers with many GB of RAM are used, it is frequently better to use direct IO... We have changed InnoDB to support a configurable number of background IO threads for read and write requests.
Fast Master Promotion
These commands allow fast promotion of a slave to a master. It is fast because it can be done without restarting the slave. Storage engines with dirty pages, such as InnoDB, can take a long time (more than a minute) to shutdown.
What features do you believe MySQL would really need in order for developers to use the open-source DMBS in demanding enterprise projects?