I'm again returning to InnoDB scalability and related bug #15815 as it hurts many users and customers using multi-cpu servers.
Short intro into problem:
On 4-CPU box 1 thread executes full-table scan select query for 8 sec,
but with 4 threads - each thread executes query for 240 sec.
It is very strange as threads use only SELECT queries and ideally there should be no
any problem in concurrent enviroment, especially for CPU-bound workload.

I did the profiling which show the problem is with "buffer pool" mutex, which protects innodb_buffer_pool.
In details, for each scanned row InnoDB calls block_get / block_release functions
which aquire/release the block related to the current row. And the problem functions
block_get / block_release use mutex_lock(buffer_pool_mutex) / …

Just a quick reminder that the MySQL BoF is tonight from 8pm to 9pm in room D135. We'll have refreshments and you'll get to talk with the MySQL developers and have a good time. It probably won't be as crazy of a time as Monday's MySQL reception was... but nevertheless, come join us for a good chat.

trying to find places where people talk about using python, ruby, and php with mysql has been a bit of a challenge.

the problem on the php side is that php forum on forums.mysql.com is so filled with pre-beginner-level questions that it?s barely worth it for me to spend my time digging through it.

for python, the python forum on forums.mysql.com is nearly a ghost town. the forums for the mysql-python project seem slightly active, but the sourceforge forum interface is just bad. (not that any web-based forum isn?t starting from a bad place.) the db-sig mail archives also have some interesting discussions.

for ruby, the …

Here are the slides from my OSCON 2006 talk about the new features of MySQL Replication. Enjoy.

Lars Thalmann: Future of MySQL Replication

If you are at OSCON, don’t forget the MySQL BOF at 8pm tonight.

Here's a fun little performance tweak you can use to increase the throughput of your MySQL slave boxes.

MySQL slave replication is single threaded when it comes to running SQL. If you have a number of clients performing INSERTs at the same time on a master server these are serialized and replayed on your slaves. The master has an IO advantage in that the controller can batch operations (especially on SCSI and RAID controllers) and generally perform more intelligent IO which the slaves aren't capable of handling. Apparently this is slated to be fixed in MySQL 5.1 but I'm not currently aware of the details.

Rewriting your UPDATE/INSERTS to use ON DUPLICATE KEY UPDATE can allow you take individual statements and rewrite them. For example you can do:

INSERT INTO FOO (ID, BAR) VALUES(1,2),(3,4) ON DUPLICATE KEY UPDATE BAR=VALUES(BAR)

Instead of issuing two individual INSERT or UPDATE statements.

Here's the …

This is the first in a series of articles I’ll write on how to use innotop, the MySQL and InnoDB monitor I’m developing. This article explains how to install innotop. Note: this article is outdated now. Please see the innotop homepage for updated installation instructions and download options.

If you've ever created foreign keys on an InnoDB table, you'll see it automatically creates indexes, if none exists, on the referenced columns in the parent table, and also in the foreign key columns in the child table. This article explains why both are needed.

MySQL has a great feature called "Query Cache" which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know.

First let me clarify what MySQL Query Cache is - I've seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache - meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. This means it is much more efficient as query which required processing millions of rows now can be instantly summoned from query cache. It also means query has to be exactly the same and deterministic, so hit rate would generally be less. In any case it is completely different.

Query cache is great for certain applications, typically simple applications deployed on limited scale or applications dealing with small data sets. For example I'm using Query Cache on server which runs this …

Just a note to say that the first generally-available/production release of Connector/J 5.0 is now available for download.

This branch has had the synchronization fixed up quite a bit (which should help with those using multi-threaded pool managers like C3p0), adds support for Statement.cancel() and Statement.setQueryTimeout() and adds support for XA distributed transactions as well.

We‘ve also started publishing our builds to Maven‘s repository (we just put in the request for 5.0.3 to be uploaded, so it might take a couple of days). Let me know if there‘s more you‘d like to see around that.

These builds also contain the complete coverage report and unit test results (against MySQL-4.0, 4.1, 5.0 and 5.1 on Redhat EL4, Solaris, Mac OS/X, Ubuntu LTS and Windows), which is why the archives are a bit bigger than they used to be.

You can download your copy today from …

Just a note to say that the first generally-available/production release of Connector/J 5.0 is now available for download.

This branch has had the synchronization fixed up quite a bit (which should help with those using multi-threaded pool managers like C3p0), adds support for Statement.cancel() and Statement.setQueryTimeout() and adds support for XA distributed transactions as well.

We've also started publishing our builds to Maven's repository (we just put in the request for 5.0.3 to be uploaded, so it might take a couple of days). Let me know if there's more you'd like to see around that.

These builds also contain the complete coverage report and unit test results (against MySQL-4.0, 4.1, 5.0 and 5.1 on Redhat EL4, Solaris, Mac OS/X, Ubuntu LTS and Windows), which is why the archives are a bit bigger than they used to be.

You can download your copy today from …