faster InnoDB writes?

Question

I'm about to replace an old server with completely new hardware, but something strange happens.

• The old machine is an AMD Athlon 64 X2 5600+ @ 1GHz with 2 GB RAM running Debian 4.0 32bit and 2x380 GB (or so) HDD in RAID1
• The new machine is an Intel i7-3770 @ 3.4GHz with 32 GB RAM running Debian 6.0 64 bit with 2x3TB HDD in RAID1 (6Gbps SATA, 7200rpm)

When loading a SQL dump (see below) the new server is busy for about 32 minutes! Being freshly installed, the server has nothing else to do. On the other hand, the old server takes 32% less time even though it is busy with other things. How can that be?

Both machines have been installed using Debian minimal images and then packages have been installed as needed.

The my.cnf settings of the old server and the new server both use innodb_file_per_table and innodb_buffer_pool_sizeis much larger (16 GB vs 0.5 GB) on the new server.

Data about the SQL dump:

• loading a completely new database (CREATE DATABASE)
• 16 tables
• a single large table (about 1,2M rows / 240MB SQL data) that is slow
• InnoDB with foreign keys
• dump uses LOCK TABLES "xxx" WRITE; ALTER TABLE "xxx" DISABLE KEYS; (generated by mysqldump)

Any ideas how to speed up that thing?

Update

I noticed that the old machine uses a dedicated 50GB ext3 partition on /var while the new one has a 1TB ext4 root partition (including /var) and a 2TB ext4 on /home. MySQL data is at /var/lib/mysql/ in both cases.

Update 2

Apparently it's the HDD that is handling small blocks slowly:

# dd if=/dev/zero of=test bs=1024 count=100 oflag=direct,sync
102400 bytes (102 kB) copied, 2.49824 s, 41.0 kB/s

The old server hits 1.1 MB/s with the same test.

Larger blocks can be handled find by the new server:

# dd if=/dev/zero of=test bs=1024k count=100 oflag=direct,sync
104857600 bytes (105 MB) copied, 7.11175 s, 14.7 MB/s

hdparm says that the write cache is on on both drives. A partition table alignment problem? See my partition table.

Answer 1

Ok, I found the reason for this and hopefully this will help others.

ext4 had "barriers" enabled on that system causing small writes to become very slow. Such writes are normal for database files.

Disabling barriers boosts performance and the same SQL dump is loaded in ~8 minutes (that's 4 times faster).

mount -o remount,barrier=0 /

Disabling barriers comes with fs integrity downsides, so think about that first...