One of the well known and much written about complaint regarding InnoDB recovery is that it does not scale well on high-end systems. Well, not any more. In InnoDB plugin 1.0.7 (which is GA) and plugin 1.1 (which is part of MySQL 5.5.4) this issue has been addressed. Two major improvements, apart from some other minor tweaks, have been made to the recovery code. In this post I’ll explain these issues and the our solution for these. First issue reported here is about available memory check eating up too much CPU. During recovery, the first phase, called redo scan phase, is where we read the redo logs from the disk and store them in a hash table. In the second phase, the redo application phase, these redo log entries are applied to the data pages. The hash table that stores the redo log entries grows in the buffer pool i.e.: memory for the entries is allocated in 16K blocks from the …

Performance Schema Support in InnoDB

With the plugin 1.1 release, InnoDB will have full support of Performance Schema, a new feature of MySQL 5.5 release. This allows a user to peak into some critical server synchronization events and obtain their usage statistics. On the other hand, in order to make a lot of sense of the instrumented result, you might need some understanding of InnoDB internals, especially in the area of synchronization with mutexes and rwlocks.

With this effort, the following four modules have been performance schema instrumented.

1. Mutex
2. RWLOCKs
3. File I/O
4. Thread

Almost all mutexes (42), rwlocks (10) and 6 types of threads are instrumented. Most mutex/rwlock instrumentations are turned on by default, a few of them are under special define. For File I/O, their statistics are categorized into Data, Log and Temp file I/O.

This blog is to give you a quick …

Background

The original motivation behind this patch was the infamous Bug#26590 – MySQL does not allow more than 1023 open transactions. Actually the 1024 limit has to do with the number of concurrent update transactions that can run within InnoDB. Where does this magic number come from ? 1024 is the total number of UNDO log list slots on one rollback segment header page. And in the past InnoDB created just one rollback segment header page during database creation. This rollback segment header page is anchored in the system header page, there is space there for 128 rollback segments but only one was being created and used resulting in the 1024 limit. Each slot in the rollback segment header array comprises of {space_id, page_no}, where both space_id and page_no are of type uint32_t . Currently the space id is “unused” and always …

Just a quick note that the InfiniDB team is at the MySQL show, so make sure you stop by our booth in the Exhibition Hall to say hi. Tonight is the Expo Hall reception, so grab some food and drink, and come on by to see a pretty cool demo we've got with lots of data that will show you what InfiniDB is capable of.  Hope to see you there!

It's been a busy year for MySQL. Perhaps you've heard. Here are some recent improvements to the speed, scalability, and user-friendliness of the MySQL database and the InnoDB storage engine that we think deserve their own headlines. Now is a great time to beta test the 5.5 release and give feedback to the MySQL engineering team.

An increasing number of large MySQL applications, e.g. social networking and SaaS back-ends, use a distributed MySQL architecture. MySQL data is distributed logically or heuristically on multiple, and in some cases thousands of, real or virtual servers. Backing up such large and dynamic environments presents its own complexities.

In this blog, we will use the cluster terminology - but we do not imply that NDB Cluster storage engine is being used for MySQL. Most implementations use InnoDB for data and MyISAM for dictionary. Typical architecture for such applications uses Database Sharding - i.e. shared-nothing partitioning of data across similarly configured nodes.

In most sharded environments, high availability is built-in - i.e. the cluster can continue to answer the queries and commit the transactions of all users in face of a node failure. This is typically accomplished either by database level replication or …

Quick note to let our users know that there was an XML tag closure error on the “alert_22″ subroutine in the “bin/kontroll-reporter-cli.pl” script. This does not affect the webapp portion of Kontrollbase – only reports generated via the command line reporter script. It is not a fatal error but will cause the XML file to […]

I have a brief keynote at the MySQL conference tomorrow at 10am. I will talk about the use of MySQL at Facebook. I promise that the talk will include interesting details.

I also have a short presentation at Ignite MySQL on Wednesday at 7pm. There are many interesting sessions on the Ignite schedule. Bradley Kuszmaul from Tokutek will be there. Unfortunately they do not have a talk about TokuDB at the MySQL conference, so this is our chance to try and remember everything from that algorithms class we had so many years ago.

Two reminders for upcoming appearances: This Saturday I’ll be presenting a 24-hour art collaboration with Evan Roth at Seven on Seven at the New Museum. On Monday I’ll be in Durham, North Carolina to speak at Duke University. If you’re interested in the latter, leave a comment and I’ll make sure you get the details. We don’t have a ton of room but I’d like ma.tt readers to be able to attend. After that I’ll be in Houston briefly if anyone wants to do a meetup.

Other day we had a small discussion about data stores and hardware; and which one drives the other when it comes to data storage solution, rather it is a hard discussion as both on its own are bigger entities; and one can not easily conclude as it depends on use cases and actually speaking data [...]