Why is MySQL nearly twice as fast on Linux?
JG
amd64list at jpgsworld.com
Sat May 22 07:41:15 PDT 2004
>
>Can you point to the results (same hardware, native linux vs
>libpthread vs linuxthreads)? There is too much volume on
>amd64 to go searching.
I'm working on a bunch of LOCAL benchmarks right now.
But I'll just repost here the benchmarks that have already been posted:
More benchmarks...
This time using super-smack.
(All of these are on the same hardware using identical hotswap drives for OS's)
--------------------------------------------------------------------
SERVER CONFIG:
** FreeBSD i386 **
amd32f# uname -a
FreeBSD 5.2-RELEASE FreeBSD 5.2-RELEASE #0: Sun Jan 11 04:21:45 GMT 2004
Using Generic release Kernel
MySQL: Ver 4.1.1-alpha for portbld-freebsd5.2 on i386 (FreeBSD port:
mysql-server-4.1.1_2)
MySQL Port options: BUILD_OPTIMIZED=yes
MySQL compiled with linuxthreads: liblthread.so.3 =>
/usr/local/lib/liblthread.so.3
---------------------------------------------------------------------
Each benchmark ran several times and highest was shown.
---------------------------------------------------------------------
Benchmark results:
LOCAL:
- Using default mysql config settings (empty/missing my.cnf)
- Using SCHED_4BSD kernel
amd32f# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=19ms min=12ms avg= 14ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 9 0 2589.28
update_index 300000 7 0 2589.28
- Using my-huge.cnf w/mysql.sock
- Using SCHED_4BSD kernel
amd32f# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=63ms min=13ms avg= 35ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 11 0 2369.88
update_index 300000 7 0 2369.88
REMOTE (Over 100mbit LAN dc0 to bge0):
- Using default mysql config settings (empty/missing my.cnf)
- Using SCHED_4BSD kernel
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=1431ms min=17ms avg= 731ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 10 0 2091.10
update_index 300000 4 0 2091.10
- Using my-huge.cnf w/mysql.sock lines in update-select.smack uncommented
- Using SCHED_4BSD kernel
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=817ms min=37ms avg= 326ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 2 0 3718.13
update_index 300000 9 0 3718.13
*Note1: ps -H only shows 1 mysql line during test.
*Note2: vmstat -w 1 ID's ~20 during this test.
--------------------------------------------------------------------
SERVER CONFIG:
** FreeBSD AMD64 **
amd64f# uname -a
FreeBSD 5.2-CURRENT FreeBSD 5.2-CURRENT #0: Wed May 19 03:37:58 PDT 2004
Without -CURRENT debugging in kernel.
MySQL: Ver 4.0.20 for portbld-freebsd5.2 on amd64 (FreeBSD port:
mysql-server-4.0.20)
With mysql-server40.diffs
With libpthreads
BUILD_OPTIMIZED=yes
BUILD_STATIC=yes
---------------------------------------------------------------------
Each benchmark ran several times and highest was shown.
---------------------------------------------------------------------
Benchmark results:
REMOTE (Over 100mbit LAN dc0 to bge0):
- Using default mysql config settings (empty/missing my.cnf) w/mysql.sock unc#
- Using SCHED_ULE
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=18ms min=1ms avg= 6ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 12 0 1672.22
update_index 300000 7 0 1672.22
*Note1: ps -H only shows 1 mysql line during test.
*Note2: vmstat -w 1 ID's ~50 during this test.
- Using default mysql config settings (empty/missing my.cnf) w/mysql.sock unc#
- Using SCHED_4BSD
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=28ms min=2ms avg= 16ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 7 0 2685.53
update_index 300000 5 0 2685.53
*Note1: ps -H only shows 1 mysql line during test.
*Note2: vmstat -w 1 ID's jump around between 20-50 during this test.
- Using my-huge.cnf w/mysql.sock lines in update-select.smack uncommented
- Using SCHED_ULE kernel
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=36ms min=1ms avg= 15ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 2 0 2813.09
update_index 300000 10 0 2813.09
*Note1: ps -H only shows 1 mysql line during test.
*Note2: vmstat -w 1 ID's ~12 during this test.
- Using my-huge.cnf w/mysql.sock lines in update-select.smack uncommented
- Using SCHED_4BSD kernel
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=34ms min=1ms avg= 16ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 4 0 3229.12
update_index 300000 8 0 3229.12
*Note1: ps -H only shows 1 mysql line during test.
*Note2: vmstat -w 1 ID's jump around between 20-50 during this test.
As you can see, so far, the best results on the FreeBSD testing of this AMD64
hardware came from a 32-bit i386 install using MySQL compiled with
Linuxthreads.
------------------------------------------------
Here is Linux:
Compare the last set of benchmarks to these new ones.
Using Linux Mandrake64 and super-smack:
--------------------------------------------------------------------
SERVER CONFIG:
** Mandrake64 64-bit**
SMP Mon Apr 19 10:48:13 CEST 2004 x86_64 unknown unknown GNU/Linux
2.6.3-9mdksmp
MySQL: Ver 4.0.18 for mandrake-linux-gnu on x86_64 (Source distribution)
[<http://lists.freebsd.org/mailman/listinfo/freebsd-amd64>root at amd64m]#
ldd /usr/sbin/mysqld
librt.so.1 => /lib64/tls/librt.so.1 (0x0000002a9566d000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000002a95786000)
libz.so.1 => /lib64/libz.so.1 (0x0000002a9588a000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x0000002a9599d000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000002a95ad0000)
libpthread.so.0 => /lib64/tls/libpthread.so.0
(0x0000002a95be8000) <-- eh?
libstdc++.so.5 => /usr/lib64/libstdc++.so.5 (0x0000002a95cfc000)
libm.so.6 => /lib64/tls/libm.so.6 (0x0000002a95eda000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000002a96033000)
libc.so.6 => /lib64/tls/libc.so.6 (0x0000002a9613e000)
/lib64/ld-linux-x86-64.so.2 => /lib64/ld-linux-x86-64.so.2
(0x0000002a95556000)
---------------------------------------------------------------------
Each benchmark ran 4 times and highest was shown.
---------------------------------------------------------------------
REMOTE (Over 100mbit LAN dc0 to bge0):
- Using "Stock" Mandrake64 Install, without my.cnf
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=65ms min=8ms avg= 20ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 4 0 4441.59
update_index 300000 1 0 4441.59
- Using "Stock" Mandrake64 Install, with my-huge.cnf
but it compiled about max_connections @ 512 and dropped them to 457...
040521 1:17:42 Warning: setrlimit couldn't increase number of open files
to more than 1024 (request: 1134)
040521 1:17:42 Warning: Changed limits: max_connections:
100 table_cache: 457
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=65ms min=0ms avg= 21ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 5 0 4296.86
update_index 300000 15 0 4296.86
So the results above were the best so far.
Strange that using the my-huge.cnf would produce worse results.
I guess that's a question for the Mandrake list though.
Anyway, the closest FreeBSD came so far was under 32-bit i386 (on AMD64
hardware) :-(
- Using my-huge.cnf
- Using SCHED_4BSD kernel
- Using mysql compiled with linuxthreads
- (see a couple posts back for complete details)
devbox# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=817ms min=37ms avg= 326ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 2 0 3718.13
update_index 300000 9 0 3718.13
More information about the freebsd-threads
mailing list