Over the weekend, I experienced a strange issue (even though its not new) with the InnoDB tablespace (ibdata) corruption. When in general InnoDB crashes, it automatically recovers during the next start by rolling back/forward based on what was pending and un-flushed/un-committed changes at the time of crash.

But for some reason, one of the server; we ran out of disk space (yeah, no alerts) on data directory; where we store everything (tablespace, logs and data); and server was running for few hours in this mode (disk full); and it became un-available and not responding after a while. Only option left was to kill the server process and its PID along with cleaning the stuff to get the space back. After I (re)started the server; server failed to start with the following error..

      InnoDB: Error: trying to access page number 1098759810 in space 0,
InnoDB: space name /data/ibdata1,
InnoDB: which is outside …

The complaints and concerns over Oracle’s pending acquisition of Sun Microsystems and open source MySQL database grew this week to calls for the acquisition, or at least the relatively small MySQL part of it, to be blocked. The Open Rights Group calling for such blockage was joined by none other than the father of the free software movement, Richard Stallman. However, I have to once again question how free and open are these free and open source software advocates? Is the movement and FOSS open to all (except Microsoft, Oracle or anyone else the Open Rights Group, Richard Stallman or any …

Today I made a presentation to 100+ audience mostly comprising of DBAs and software consultants about MySQL Architecture and Solutions. You can download the presentation slides. This focuses on architecture overview of MySQL products and solutions.

Today I made a presentation to 100+ audience mostly comprising of DBAs and software consultants about MySQL Architecture and Solutions. You can download the presentation slides. This focuses on architecture overview of MySQL products and solutions.

Today I made a presentation to 100+ audience mostly comprising of DBAs and software consultants about MySQL Architecture and Solutions. You can download the presentation slides. This focuses on architecture overview of MySQL products and solutions.

In my old post there is a bug when run in MySQL 5.1.30 and old, because the status variable Queries was added in MySQL 5.1.31. So i change to choose between Queries and Questions status variables, and I think the Queries represent more accurate result.

http://forge.mysql.com/tools/tool.php?id=217

By the way:

# Queries The number of statements executed by the server. This variable includes

The linux.conf.au organisers have given all miniconfs an additional few weeks to spruik for more proposal submissions, huzzah!

So if you didn’t submit a proposal because you weren’t sure whether you’d be able to attend LCA2010, you now have until October 23 to convince your boss to send you and get your proposal in.

A couple of days ago I posted about scaling writes in mysql. I didn't say much about read performance in that post because a) it was irrelevant at the time, and b) there are thousands of articles all over the web that already cover read performance.

In this post I'm going to cover some of the things that I did to improve read performance for my own application. It may be relevant to others, however, you'd still need to read a whole bunch of other articles to understand MySQL read performance.

Looking at our access patterns, it turned out that there were two classes of read queries.

1. Reads to build the daily summaries
2. Reads from the summary tables in response to user actions

The former dealt with far more data at one go, but was only run once. Queries for this pattern were slow …

MySQL backup soon becomes an important matter when the database is used in production. The pain-point comes from the fact that while backuping the database is not available to respond to client requests anymore. With mysqldump - the standard tool for performing MySQL backups - and a large database the operation can go over many tenth of minutes if not hours. If I am running my business on line this is simply not acceptable.

The classical approach to workaround this problem is to take advantage of MySQL replication. I set up a master/slave configuration where the slave acts as copy of the master. Then, when needed, I run mysqldump on the slave without any service interruption on the master.

But ZFS snapshosts bring a new straightforward approach that avoids the pain and the complexity of a master/slave replication.

Snapshots are a key feature of ZFS that allows me to save a copy of …

We use MySQL on most of our projects. One of these projects has a an access pattern unlike any other I've worked on. Several million records a day need to be written to a table. These records are then read out once at the end of the day, summarised and then very rarely touched again. Each record is about 104 bytes long (thre's one VARCHAR column, everything else is fixed), and that's after squeezing out every byte possible. The average number of records that we write in a day is 40 million, but this could go up.

A little bit about the set up. We have fairly powerful boxes with large disks using RAID1/0 and 16GB RAM, however at the time they only had 4GB. For BCP, we have a multi-master set up in two colos with statement level replication. We used MySQL 5.1.

My initial tests with various parameters that affect writes showed that while MyISAM performed slightly better than InnoDB while the tables were small, it quickly …