I ran into pretty interesting behavior today. We needed to dump and reload large database and we had pretty good IO subsystem so we started number of mysqldump processes in parallel. Unlike in other case when we did load in parallel, dump in parallel did not increase IO rate significantly and we could still see just about 1.5 average outstanding IO requests to the disk.

Lets look at SHOW INNODB STATUS:

——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 256, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
112084404 OS file reads, 29836003 OS file writes, 2038246 OS …

I have just released the first version of the BLOB Streaming engine for MySQL (MyBS). You can download the source code of the engine from http://www.blobstreaming.org/download. Pluggable binaries for MySQL 5.1.19 (32-bit Linux and Mac OS X) are also available.

To install the plug-in copy libmybs.so to the /usr/local/mysql/lib/mysql directory, connect to your server using mysql, and enter:

mysql> install plugin MyBS soname "libmybs.so";

This version allows you to download BLOBs that are already stored in the database using HTTP. The URL is specified as follows:

http://mysql-host-name:8080/database/table/blob-column/condition

Where condition has the form: column1=value1&column2=value2&...

I gave an example of this in my previous blog: …

Earlier this month, Lenz Grimmer and I approached the top Forum posters on our Forums (http://forums.mysql.com), thanking them for their contributions and soliciting them for feedback on how we could improve the Forums to make them more usable and fun to use. Especially, we asked for suggestions in these areas:

• for easier navigation / threading / search
• for ranking / highlighting / voting on entries
• for showing user profiles / avatars / case studies of contributors

Of the 24 people we approached, fifteen replied. Out of these, thirteen came back with suggestions, some of which were very detailed proposals.

Here is a condensed summary of the most popular requests/suggestions:

1. Re-enabling the display of new posts per forum on …

If you are as curious as I am about technology, you would have been intrigued when the MySQL proxy entered the radar. So I followed this promising jewel from its first inception as source only (and for internal distribution) until its release with a full series of binaries.

The recent announcement has made it official. MySQL Proxy is a full fledged supported product, and it's ready to use.
You just have to get the binaries and use it.
If you feel like experimenting (and there is no reason to get the proxy if you aren't) you can have a look at the sample lua scripts available on the Forge …

It couldn't happen to a better set of people. As BusinessWeek reports, MySQL is well on its way to an IPO. The interesting thing is what it's doing to get there:

...[C]an MySQL keep up the growth without adding hefty sales and marketing costs?and getting squeezed by competitors? The company employs just 30 field sales staff out of a head count of 360 and strives to close deals more quickly than rivals. Most employees work from home. "Managing the cost of sales and marketing in an open-source company is the key to profitability," says Mickos, sitting in a small, spartan office adjacent to a sea of cubes in the company's Silicon Valley digs. "We're not just innovating in software, we're innovating in sales."

Open source, leaving the cave to …

Mysql 5 introduces some nice features, like triggers and stored procedures, which can be very usefull if the clients are not written in the same language.
However, as soon as you get some read load on the mysqld or need to connect from different locations, like USA, France, Germany, replication gets important.

So I´ve tried to setup a simple replication. 1 Master and multiple Slaves. This is more than 8 month ago.
But everytime another bug makes it impossible.
User defined variables aren´t replicated a long time. If one of your procedures/ triggers depend on them, replication breaks.
INSERT ... ON DUPLICATE KEY UPDATE is also still broken in the most recent enterprise-version. With every release, another problem occurs.
"after update"-trigger got not executed, the query failed if the update-clause was "modifying" a unique key to the same value or innodb assigned another auto_increment value on the …

Jan Kneschke has released a new tool called "MySQL Proxy".
This proxy is between the mysqld and the client and can handle load balancing, fail over, query rewriting and analysis.
It´s also possible to extend the proxy with LUA scripting which opens nice possibilities.
I´m thinking about adding Memcache-Support. But perhaps its better up to the application to decide, if the cache should be used or not.
At least it should be possible to implement load balancing/ failover in an easy way for a slave-farm.

BusinessWeek asks: How much is MySQL worth? MySQL, a fast-growing maker of database software used by some of the Internet's most recognized brands, is preparing to file for an initial public offering, perhaps as soon as late 2007. The offering could value the company at between $600 million and $1 billion, according to sources, and inject some pep into a tech IPO market that's seen only a handful of successful offerings in the past year.... READ MORE

Version 1.2 of Zmanda Recovery Manager (ZRM) for MySQL, a robust and intelligent solution for backup and recovery of MySQL databases is available at Zmanda downloads page.

Changes in this release:

• Code restructured into modules
• Snapshot plugin interface. LVM snapshots implemented as a plugin.
• ZRM should be run as user belonging to mysql group.
• Numerous bug fixes

Documentation is available at ZRM for MySQL wiki. Ask questions and provide feedback in Zmanda forums.

One may think one may use MySQL Slow query log to log all slow queries to catch problematic queries or for audit purposes. In fact however not all the queries are logged. I already mentioned mysql slave queries are not logged to slow query log and it looks like I was wrong connecting it just with replication.

In reality it is not the fact the thread is replication thread causes queries to be omitted from slow query log but the fact thread uses SET TIMESTAMP functionality. If you do this within normal connection result would be the same.

Why is this happening ? I guess it happens because of the way the code is structured. During query start current timestamp is stored to special value which will be used for the query execution by all NOW() calls internal timestamp assignments etc. At certain point …