Linux Database Server CPU Comparison

Benchmarks MySQL 4.0.18: Intel versus AMD

A Linux database server report would not be complete without the open source database MySQL. Many of our readers requested that we test with both MyISAM (default storage engine in MySQL 3.x) and InnoDB (default storage engine in MySQL 4.x), so we performed many more tests than last time.

It must be said that the MySQL results had a large margin of error (3% - 4%) compared to DB2, especially at high levels of concurrency.

Here is our MySQL configuration:

           Read_buffer=2GB
           Port=3306
           socket = /var/lib/mysql/mysql.sock
           skip-locking
           set-variable = max_user_connections= 2000
           set-variable = max_connections= 2000
           key_buffer=2G
           Read_buffer=2G
           table_cache=1024
           tmp_table=128M
           max_heap_table=256M
           read_rnd_buffer = 64M
           thread_cache=16
           net_buffer_length=16k

The " query cache" was off, as we wanted to test worst case performance. In some cases, the query cache was able to push a single Xeon to 1000 queries per second, and the CPU was still capable of doing more, as the CPU load was at 50% - 70%. At 1000 queries/s and more, other bottlenecks started to kick in, such as the latency of the network driver, the operating system and so on.

All numbers are expressed in queries per second. All concurrency tests below 5 are not reliable enough to make any firm conclusion as the margin of error is much higher.

Concurrency	Dual Xeon (Gallatin) with L3 cache	Single Xeon (Gallatin) with L3 cache	Dual Xeon (Nocona) with HT	Single Xeon (Nocona) with HT	Dual Xeon (Irwindale) 3.6GHz with HT	Dual Core Intel 3.2GHz	Dual Opteron 250 2.4Ghz	Single Opteron 250 2.4GHz	Single Opteron 252 2.6 GHz
1	243	248	280	277	286	233	290	298	319
2	357	317	423	338	450	344	438	370	399
5	466	356	473	358	497	442	543	435	470
10	505	361	521	375	517	487	629	465	502
20	496	350	531	371	545	507	670	455	498
35	508	355	555	371	506	490	665	470	507
50	497	348	526	368	495	502	669	472	508
AVG	494	354	521	368	512	486	635	460	497
MAX	508	361	555	375	545	507	670	472	508

Those were the raw numbers. Let us now analyse this...

Concurrency	Dual versus Single Xeon Galatin	Dual versus Single Xeon Nocona/ Irwindale	Dual Opteron 250 vs Single
1	-2%	1%	-3%
2	12%	25%	18%
5	31%	32%	25%
10	40%	39%	35%
20	42%	43%	47%
35	43%	50%	41%
50	43%	43%	42%
AVG	40%	41%	38%

MySQL ISAM is an incredibly fast database engine in our benchmark situation: it handles the same workload about twice as fast as DB2. I have to emphasize "our benchmark situation" because we cannot forget that our workload is mainly about reading the database and not writing. And of course, it must be said that the MySQL ISAM engine does less work on each query than DB2; it does not support transaction-safe (ACID compliant) commit, rollback, and crash recovery capabilities.

MySQL, as we have also noticed 6 months ago, doesn't seem to scale as well as DB2. At best, you get a 40% - 45% performance increase when the concurrency level is high enough. When we move to quad CPUs, we only get a 20% - 30% increase while DB2 still offers a 70% increase. The better scaling of DB2 means that with enough CPUs, it runs almost as fast as the MySQL ISAM engine, and offers all the transaction-safe capabilities as a bonus.

Let us check if the architectural differences between the CPUs make a difference . Again, don't pay too much attention to the results of the lower concurrency levels.

Concurrency	Dual Xeon Irwindale versus Nocona (3,6 GHz)	Xeon Nocona (3,6 GHz) vs Galatin (3,06)	Opteron 2.6 vs Nocona 3.6	Opteron 2.6 vs Pentium-D	Xeon Nocona 3,6 GHz vs Pentium-D
1	2%	12%	15%	37%	19%
2	6%	7%	18%	16%	-2%
5	5%	1%	31%	6%	-19%
10	-1%	4%	34%	3%	-23%
20	3%	6%	34%	-2%	-27%
35	-9%	5%	37%	4%	-24%
50	-6%	6%	38%	1%	-27%
AVG	-2%	4%	35%	2%	-24%
MAX	-2%	4%	36%	0%	-26%

The bigger L2-cache of the Xeon Irwindale did nothing more than compensate for the slightly higher latency of the L2-cache. The Xeon Irwindale and Nocona perform alike.

MySQL, unless you get the special Intel Compiler optimized version, remains the stronghold of the Opteron. The fastest (single core) Opteron outperforms the best Intel CPU by a 35% margin. We didn't use the Intel compiler version as we have reason to believe that this version is not used a lot in the real world. We might try it out in a future article.

The relatively limited scaling also means that high clocked single CPUs can be an interesting option. This is illustrated by the Opteron 252 2.6 GHz, which outperforms the dual core Pentium-D 3.2 GHz by a small margin.