Looking for a bottleneck in MySQL server

I have two Ubuntu servers with very similar, if not identical, configuration: both have 16 cores (4 E5540 Xeons), 16GB RAM, HP Smart Array P212 controllers with 2 HDDs in RAID1. I/O scheduler used is deadline. Both run the same versions of MySQL (5.1.26, later upgraded to 5.1.47/Percona Server 10.2). The databases are similar, but not exactly the same. MySQL configuration is also very similar. But the performance is incomparable:

Server1:

mysql> alter table documents add column test int(10) unsigned not null default '0';
Query OK, 2496892 rows affected (7 min 18.41 sec)
Records: 2496892  Duplicates: 0  Warnings: 0

Server2:

mysql> alter table documents add column test int(10) unsigned not null default '0';
Query OK, 2497414 rows affected (1 min 59.15 sec)
Records: 2497414  Duplicates: 0  Warnings: 0

Server2 is 3.7 times as fast.

MySQL benchmark (sql-bench) only confirms that Server2 is much faster:

Server1:

alter-table: Total time:  7 wallclock secs 
ATIS: Total time:  3 wallclock secs 
big-tables: Total time:  0 wallclock secs
connect: Total time:  1 wallclock secs 
create: Total time:  7 wallclock secs 
insert: Total time:  3 wallclock secs
select: Total time:  2 wallclock secs 
wisconsin: Total time:  4 wallclock secs 

Server2:

alter-table: Total time:  1 wallclock secs 
ATIS: Total time:  1 wallclock secs 
big-tables: Total time:  0 wallclock secs 
connect: Total time:  0 wallclock secs 
create: Total time:  0 wallclock secs 
insert: Total time:  2 wallclock secs 
select: Total time:  2 wallclock secs
wisconsin: Total time:  2 wallclock secs 

The only difference between the servers is the OS version. Server1 is Ubuntu 9.10 with 2.6.31-20-server #58-Ubuntu SMP kernel, while Server2 is Ubuntu 10.05 with 2.6.32-22-server #33-Ubuntu SMP.

I had serious suspicions about I/O on Server1. So, iostat -dkx gives significantly higher %util values on Server1, about 25-30% vs 1% on Server2.

I ran I/O benchmarks (iozone3), but to my surprise, Server1 produced slightly better results. RAM test with mbw was also in favor of Server1.

What should I test next? What could be the problem?

Thank you.

From serverfault minaev

• I'm not an Ubuntu guy, but a 10 second search shows 9 uses ext3 and 10 uses ext4 by default. Post the output of 'tune2fs -l' on your data partitions to contrast/compare.

  pauska : Very good suggestion, was about to post it myself. EXT3 is -horrible-, switch to XFS ASAP.

  minaev : Good idea, indeed, but it is ext4 on both machines, sorry for not mentioning.

  minaev : tune2fs output is also very similar. Interesting differences are in the `features` list: Server1: has_journal ext_attr resize_inode dir_index filetype needs_recovery extent flex_bg sparse_super large_file huge_file uninit_bg dir_nlink extra_isize Server2: has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file I'm afraid, that doesn't say much to me...

  minaev : UPDATE: Sorry, I was wrong, but it's getting even curiouser... Server1 uses ext4, while Server2 has ext3.

  troyengel : there is a release note for 10 LTS that states performance for ext4 will be worse in some cases. Even though server1 is only 1 kernel revision behind that could be a big deal for ext4 performance, not to mention any special patches Ubuntu adds in packaging (if they're anything like Red Hat). I suspect your performance problem is related to the ext4 on Server1.

  minaev : How should I interpret these, then: Server1 read: http://img535.imageshack.us/img535/7262/readps.png Server1 write: http://img25.imageshack.us/img25/2710/writeps.png Server1 random read: http://img443.imageshack.us/img443/6010/randreadps.png Server1 random write: http://img38.imageshack.us/img38/8579/randwriteps.png Server2 read: http://img441.imageshack.us/img441/7262/readps.png Server2 write: http://img694.imageshack.us/img694/2710/writeps.png Server2 random read: http://img408.imageshack.us/img408/6010/randreadps.png Server2 random write: http://img9.imageshack.us/img9/8579/randwriteps.png

  minaev : I am mostly interested in write and random write tests...
  From troyengel