Firstly, I'd like to thank Jay Pipes for adding me to planetmysql. If you came to my blog through this website, then welcome. I promise I will try to continue to write good quality posts and maybe even use the spell checker from time to time.

So let me introduce myself. My name is Jonathan and I am currently employed by a company that owns a online hotel-reservation system. I have been living in Beijing, China for the last year and soon plan to move back to the UK. I am creative at finding solutions to problems and I use this blog to express my ideas. I started this blog to try to contribute back to the MySQL community by coming up with new ideas and hopefully implementing them as well.

So the purpose of this post is to highlight my previous posts (my greatest hits).

1. …

These days, DRBD is a popular way of achieving HA without having to pay for a SAN and it plays nicely with MySQL. DRBD uses a network connection to mirror two disk partitions on two servers and can use a lot of bandwidth so, usually, the servers are connected through a crossover cable. Recently, I did an engagement where we were setting DRBD for a 100 GB partition on two servers hosted in one of the largest hosting company in the US. For extra reliability, the DRBD private network was using a bond of two Ethernet adapters on each servers.

For a reason that I am not aware of, the hosting company chose to use a VLAN for the private DRBD network instead of a pair of crossover cables. When we started the first DRBD synchronization, loading the private DRBD network at 110 MB/s. At that point, something interesting happened… Here is the explanation of the network engineer of the hosting company. Because of the bonding and the bonding mode chosen …

The optimizer is the part of the mysql server whose responsibility is to discover the optimal plan (the execution oath) to solve an SQL query.
When dealing with joins there exist various execution paths depending on the number of tables involved. The number of possible plans to investigates grows exponentially with the number of tables. This is not a problem for queries with few tables (less than 10) but could be a serious problem when the number of tables climbs.
When dealing with dozens of tables the time spent to evaluate all possible plans to solve the query is longer than the execution of the query itself. Not good.
Starting from version 5.0.1 MySQL introduces a flexible method to avoid the possible bottleneck when you have lot of tables joined in a query.

Using the system variable optimizer_prune_level (possible values are 0 or 1) you can tell the optimizer to skip certain plans from being inspected based …

Following my successful linux.conf.au talk “Eat My Data: How Everybody Gets POSIX File I/O Wrong“, I started to feel the need to easily be able to have my data eaten.

Okay, not quite. However, when you’ve written your software properly, so it uses fsync() correctly, opening files with O_SYNC or whatever - tests take longer as you’re having to wait for things to hit the rust.

So….. LD_PRELOAD=libeatmydata.so to the rescue! With a POSIX compliant fsync() (that does nothing) and filtering on open(2), it can take your test run times down dramatically.

The only time you shouldn’t use it for your tests is when you end up crashing the machine to test durability (i.e. when the OS doesn’t have the opportunity to cleanly write out the data to disk).

See the …

There have been numerous occasions where I needed to make backups of individual tables from selected database. Usually I can achieve this by typing: mysqldump database_name table1 > table1.sql mysqldump database_name table2 > table2.sql

This could be very painful if you have 10?s or 100?s of tables. Until today, I never ran into a situation where I [...] …

Before I start this, I am going to begin by saying I know a number of the Subversion developers and they are good people who get it. I look at roadmap for Subversion and further see what is being done with SVK, and can see that Subversion is evolving to be a distributed revision control system. SVK would have pulled it kicking and screaming into that direction if the main developers hadn't decided to go that way, but they are smart and realize the need to evolve.

When I read blogs like Version Control and ?the 80%? I see though that they, being the Subversion community, are going have to go through an education process to get their own community on board. Points like:
No Windows support.
No GUIs
Too many commands.
Corporations are too centralized
No security.

It just goes to show that FUD is not only the domain of …

I’m returned from my 1-week vacation today and want to say - I’ve never been so productive as I was there Blue ocean, hot sun and white sand really helped me to finish my work on the first release of one really awesome project.

Today I’m proud to announce our first public release of the Data Recovery Toolkit for InnoDB - set of tools for checking InnoDB tablespaces and recovering data from damaged tablespaces or from dropped/truncated InnoDB tables.

This release already has a pretty decent set of features:

• Supports both REDUNDANT (pre mysql 5.0) and COMPACT (mysql 5.0+) versions of tablespaces
• Works with single tablespaces and file-per-table tablespaces
• Able to recover data even when processed InnoDB page has been reassigned …

Maatkit (formerly MySQL Toolkit) contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking slaves for data consistency, with emphasis on quality and scriptability.

This release fixes several minor bugs. It also renames all the tools to avoid trademark violation.

I have two ideas for hacking MySQL in a useful manner, in response to issues I've had using it:

InnoDB tablespace usage monitor

A really simple tool that would report the amount of innodb tablespace used by specific tables and, ideally, indexes too.

This is required as I'm fairly sure that there isn't a tool to do this at the moment.

MyISAM mmap() its index files

I'm having trouble tuning MyISAM's key_buffer for production use. We want to use delay_key_write to reduce the IO of a lot of inserts, but at the same time, I'd prefer it not to get too out of hand, as large flushes create a lot of I/O in one go.

Anyway perhaps that was a poor explanation of my motivation, however, the idea would be:

• 
  
• mmap() the whole of each index file when the table is opened.
  
• Leave the memory mapping in place …

As Peter mentioned in one of previous posts, we've done huge work developing robust strategies of InnoDB data recovery to provide our customers effective data recovery services and one of major parts of these strategies is our toolkit for InnoDB data recovery. Today I'm proud to announce its first public release which was used to help some of our customers to recover 95-100% of their deleted data.

This release already has a pretty decent set of features:

• Supports both REDUNDANT (pre mysql 5.0) and COMPACT (mysql 5.0+) versions of tablespaces
• Works with single tablespaces and file-per-table tablespaces
• Able to recover data even …