Open source the way it ought to be. Today, Percona announced a replacement for InnoDB that improves performance and fixes bugs. The new engine is called XtraDB.

According to Vadim at Percona:

It's 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

The release is pure GPL (v2) and commercial support is available from Percona. If percona keeps this up, they just might become the new MySQL.

The source is available from Launchpad and from …

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

PLAIN TEXT SQL:

1. mysql> SHOW TABLES;
…

How much space would empty MyISAM table take ? Probably 8K for .frm file, 1KB for .MYI file and 0 for MYD file. .MYI file can be larger if you have many indexes.

How much space will Innodb take:

PLAIN TEXT SQL:

1. mysql> CREATE TABLE test_innodb(a int, b int) engine=innodb;
2. Query OK, 0 rows affected (0.30 sec)

Check out files (using Innodb File Per Table)

-rw-rw---- 1 mysql mysql 8578 Dec 16 20:33 test_innodb.frm
-rw-rw---- 1 mysql mysql 98304 Dec 16 20:33 test_innodb.ibd

So we get about 100K and so about 10 times more for MyISAM. This is ignored space which needs to be allocated in main tablespace for Innodb data dictionary. But that one is pretty small.

This is the good reason to avoid having very small Innodb tables - they will …

Back in October last year a corporate accountability group called As You Sow attempted to persuade Oracle to detail its commitment to open source by publishing an Open Source Social Responsibility Report.

Oracle resisted the proposal but did promise to share more details on its use of open source in the next version of its Oracle’s Commitment social responsibility report. I just noticed that the renamed Oracle Corporate Citizenship Report (Pdf) was recently published (in late November as far as I can make out) and does indeed include a section on Oracle’s commitment to open source.

In the section “Open Source and Accessibility” Oracle notes that …

IBM picks Ubuntu for virtual desktop. Nokia acquires Symbian and prepares for open source. SourceForge gets a new CEO. Microsoft finds some old TCO numbers down the back of the couch. And more.

Official announcements
IBM and Business Partners Introduce a Linux-Based, Virtual Desktop IBM

Novell Reports Financial Results for Fourth Fiscal Quarter and Full Fiscal Year 2008 Novell

SourceForge Appoints Scott L. Kauffman President and Chief Executive Officer SourceForge

…

We've recently done benchmarks comparing different MySQL versions in terms of their CPU efficiently in TPC-C like Workload. We did it couple of weeks ago so MySQL 5.0.67, MySQL 5.1.29 and Innodb Plugin 1.0.1 were used which are not very recent, though we do not think results will differ a lot with today versions.

Results are as follows:

The system was 2* Quad Core Xeon E5310, CentOS 5, Data stored on ramfs. We controlled number of cores used with /sys/devices/system/cpu/cpuX/online Maximum performance for each number of cores was taken though it was reached with number of sessions matching number of cores. Just 1 "Data warehourse" was used to keep data small.

As you can see there is some gain for MySQL from read-write lock split patch (found in Percona Builds) though it is not very significant for this workload. To isolate effect of this patch …

Peter wrote a post a while ago about choosing a good InnoDB log file size.  Not to pick on Peter, but the post actually kind of talks about a lot of things and then doesn't tell you how to choose a good log file size!  So I thought I'd clarify it a little.

The basic point is that your log file needs to be big enough to let InnoDB optimize its I/O, but not so big that recovery takes a long time.  That much Peter covered really well.  But how do you choose that size? I'll show you a rule of thumb that works pretty well.

In most cases, when people give you a formula for choosing a configuration setting, you should look at it with skepticism.  But in this case you can calculate a reasonable value, believe it or not.  Run these queries at your server's peak usage time:

…

Do you know that there are two limits about dirty (modified but not flushed to disk) blocks of InnoDB buffer pool? One is the limit of "amount". The other is the limit of "age".

-- limit of "amount" --

As you know, buffer pool of InnoDB works as write-back cache of its datafiles. If the buffer pool is filled by dirty blocks, InnoDB cannot allocate new blocks without flushing the dirty blocks and the performance would get worse. This is the limit of dirty block "amount". We can avoid this limit by setting 'innodb_max_dirty_pages_pct' smaller or setting the larger buffer pool size. We might be never at a loss about the limit.

The another limit we should understand is limit of dirty block "age".

-- limit of "age" --

As you know again, because InnoDB write the modifies of datafile to transaction log file synchronously, InnoDB is allowed to treat its buffer pool as write-back …

In case anyone is interested in a copy of my slides for the two lightning talks I gave at the Open Source Days 2008 conference, I have made them available here:

• "Optimizing Large Databases Using InnoDB Clustered Indexes:" HTML and PDF.
• "Profiling with OProfile and Intel Core 2 performance counters:" HTML and PDF.

I waqs quite pleased with the benchmark that I prepared for the InnoDB mini-talk, where I measure the performance …

Introduction to the Innodb IO subsystem

Basics When a client connects to MySQL, it creates a thread to handle it. This thread executes SQL queries and interacts with the storage engine (for simplicity, lets call them user threads). Innodb uses a four (4) additional threads to implement asynchronous io1. Although Innodb has an option innodb_file_io_threads to control the number of IO handler threads, it has no effect on how many IO handler threads are actually created. These IO handler threads wait and process events in a loop. Each IO handler thread processes different kinds of events. (Insert buffer writes, log writes, datafile writes, and read-ahead or prefetch) Let us now see how different types of IO are handled by Innodb.

Reads Using a simple sysbench read-only test, we notice that all user threads issue reads (pread(2)) to the data files. We can verify …