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 …

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 …

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 …

I am happy to announce Yasufumi Kinoshita joins our team as Performance Engineer. Yasufumi is known as InnoDB hacking expert, and there is bunch of patches he made we include in our releases: innodb buffer pool scalability fix, innodb rw_lock fix, control InnoDB IO etc. Actually there is one more patch -
"adaptive flush" in InnoDB, which makes flushing process more uniform and predictable. Yasufumi will post about this patch soon.

Yasufumi also had talk on MySQL Conference & Expo 2007
InnoDB Performance Potential in High-end Environments

Yasufumi's primary tasks as you can guess will be InnoDB performance and scalability fixes, InnoDB improvements and related question, however we are not going to restrict his activity only to InnoDB area but for all MySQL related problems.

So expect more and better patches in near …

When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open transactions. This is in your reach with one simple command -- SHOW ENGINE InnoDB STATUS. On most occasions it works beautifully. The problems appear when you have a large spike in number of connections to MySQL, which often happens when several transactions kill the database performance resulting in very long execution times for even simplest queries, or a huge deadlock.

In such rare cases SHOW ENGINE InnoDB STATUS often fails to provide the necessary information. The reason is that its output is limited to 64000 bytes, so a long list of transactions or a large deadlock dump may easily exhaust the limit. MySQL in such situation truncates the output so it fits the required size and obviously this is not good since you may lose some valuable information from your sight. …

There is patch from Google to improve SMP performance , but for some workloads it showed for us reverse scalability.
E.g. update_key benchmark from sysbench. There are also results with Yasufumi's rw_locks (http://bugs.mysql.com/bug.php?id=26442)

SetupWe have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.ProblemWhen transporting an .idb file from one server to another, I ran into the following error:ERROR 1030 (HY000): Got

Do you have a master-slave MySQL set up?  Ever do DDL changes on the master?  You may be hit with a serious data integrity bug.  Read on. One of our clients does a regular rename tables on the master to keep the current table small and archive off old data.  We’d occasionally be hit by [...]

Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.All you need is an extra table and a couple triggers.The concept is fairly straight forward:Create a before insert trigger on the child table that validates the parent exists in the parent table.If there is no parent found, then insert a