After my previous post about InnoDB Doublewrite Buffer impact I've received several feedbacks where people are claiming to see an important performance degradation when they enabling doublewrite... Discussing with them about their wokloads I've tried to reproduce a similar activity with dbSTRESS tool. And finally I was happy (or unhappy - all depends on the side :-))

• Happy because I was able to reproduce the problem :-)
• And unhappy because I've got 30% performance degradation by activating doublewrite buffer!

And now I'll tell you a story how to fix it :-))

Sorry, it'll be little bit long (but it was hard to make it shorter :-))
First of all let's get a look on the I/O activity and possible solutions to make it running more optimally...

I/O Requests
Generally we may …

If you're leaving the secure world of ASCII characters you're usually enter a dangerous land of confusion. It's actually a very complex topic and from a technical point of view there is no one to blame.

But if you don't care about the basics, don't care why or how, and just want a reliable and working system, simply follow these three advices to attain happiness and inner peace in your developer's life.

Advice 1

Define the character set for every column where you plan to store international character data:

CREATE TABLE t1 ( col1 VARCHAR(42) CHARACTER SET utf8 );

Or at least for every table:

CREATE TABLE t1 ( col1 VARCHAR(42) ) CHARACTER SET utf8;

The later example saves you some typing, but usually you don't need UTF-8 in every string column.

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 2

…

If you're leaving the secure world of ASCII characters you're usually enter a dangerous land of confusion. It's actually a very complex topic and from a technical point of view there is no one to blame.

But if you don't care about the basics, don't care why or how, and just want a reliable and working system, simply follow these three advices to attain happiness and inner peace in your developer's life.

Advice 1

Define the character set for every column where you plan to store international character data:

CREATE TABLE t1 ( col1 VARCHAR(42) CHARACTER SET utf8 );

Or at least for every table:

CREATE TABLE t1 ( col1 VARCHAR(42) ) CHARACTER SET utf8;

The later example saves you some typing, but usually you don't need UTF-8 in every string column.

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 2

…

I track security updates for the following vendors: Apple, Cisco, FreeBSD, Microsoft, Red Hat, and Sun Microsystems. I chose these vendors based on my own needs for the networks and systems I manage. I've also found that updates from these vendors tend to catch the major updates necessary for common software applications. If you have other vendors you would like me to provide updates for, send me a message.

If you have an interest in information security from both a technical and managerial level, check out Principles of Information Security. It does an excellent job of presenting material for both audiences.

Cisco Systems Inc.

• TCP State Manipulation Denial of Service …

Sun Microsystems, Inc. today announced an update to its MySQL Enterprise™ subscription, designed to assist corporate users improve the speed and uptime of their MySQL™ database applications. Thirty-day trial subscriptions of the new MySQL Enterprise Fall 2009 Release are now available free-of-charge from http://www.mysql.com/trials.

This is a list of most of the sources of disk IO in InnoDB. Most of the statements below are true for official MySQL 5.0 and the InnoDB plugin prior to 1.0.4. Versions 1.0.4+ of the InnoDB plugin and XtraDB have made InnoDB much better for IO bound workloads and eventually I will write more about them.

• synchronous page read - User threads read pages synchronously on buffer cache misses. They may also generate readahead (prefetch) requests handled by other threads. A page is taken from the free list to cache the contents of the disk page and the page is usually moved to the head of the LRU list.
• trx_purge - This is called by srv_master_thread to remove rows that have been marked as deleted when the rows are no longer visible to other transactions. The work uses synchronous IO. Dmitri has written about the benefits of using a …

MySQL Connector/Net 6.1.2, a new version of the all-managed .NET driver for MySQL has been released. This is our latest GA release and is suitable for use in all scenarios against servers ranging from version 4.1 to 5.4!

It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/6.1.html] and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.)

This release introduces a couple of exciting new features.

Website Configuration Dialog -- This is a new wizard that is activated by clicking a button on the toolbar at the top of the solution explorer. It is meant to work in conjunction with the ASP.Net administration pages.  It makes it easier to activate and set …

There is thorough documentation about the Partitioning feature in MySQL 5.1. There are also nice articles like this one by Robin. However, I thought it would be useful to have a quick "how-to" guide to partitioning by dates. I'll use the world schema to keep it easy.

Partitioning a table by a range of dates is quite popular. Unfortunately, the PARTITION BY RANGE only accepts an integer (or a function that evaluates to an integer) as the partition expression. That's fine if you want to partition by numeric types like this:

ALTER TABLE City PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (3000),
PARTITION p3 …

MySQL continues to be a tipping point for the IT industry.  In March of 2008 and January 2009 I talked about MySQL being a very important factor in the IT industry.  Today, MySQL  continues to play an important role in the direction of the IT industry.  Some facts about MySQL in a down economy:While technical conferences are being canceled or running at 50% attendance of

Sign up today for OpenSQL Camp 2009! The space is confirmed so go ahead and make your travel arrangements. The event is free and will be taking place in Portland, OR on November 14-15th, 2009. If you are interested in leading a session or presenting a talk, be sure to add it to the session ideas page. Also, we are still looking for sponsors! Please visit the sponsors page if you or your company/organization might be interested. All donations are tax deductible.

We should have representatives from many open source database (and database-related) projects. PostgreSQL, MySQL, Drizzle, memcached, and Gearman, just to name …