Hello All,
I've run benchmarking suite that comes with mysql and I got a very
strange things. Most of the tests shows that the mysql on FreeBSD
system is about 2 times slower than on Linux boxes. And even
more, results of UPDATE and INSERT tests are VERY BAD in comparison to
results of those tests on Linux. I wonder what is the reason?
Maybe I should run benchmarks in single-user mode?
[root@p2x:/usr/local/sql-bench] myvars
+----------------------------+---------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------+
| back_log | 128 |
| connect_timeout | 5 |
| basedir | /usr/local/ |
| datadir | /usr/local/var/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 131072 |
| flush_time | 0 |
| key_buffer | 16773120 |
| language | /usr/local/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 256 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| net_buffer_length | 16384 |
| port | 3306 |
| protocol-version | 10 |
| record_buffer | 131072 |
| skip_locking | OFF |
| socket | /var/run/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 256 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /var/tmp/ |
| version | 3.22.20a |
| wait_timeout | 28800 |
+----------------------------+---------------------------------+
The result logs which where found and the options:
>> this is my server
>> 1 mysql-FreeBSD_3.1_STABLE_i386 : MySQL 3.22.20a
>> Pentium II 300 MHz x2, 256 M, UW SCSI AIC-7895 2x, IBM DDRS 8 GB
2 mysql-Linux_2.0.35_i686 : MySQL 3.22.13 beta
3 mysql-Linux_2.2.1_i686 : MySQL 3.22.18
pentiumpro 400mz x2, 256M, SCSI, gcc 2.9 compiled, key_buffer=16M
4 mysql-SunOS_5.7_sun4u : MySQL 3.22.18
UltraSPARC-II 2/CPU 400 MHz, 2G mem, key_buffer=16M
>> *******
==============================================================
Operation | 1| 2| 3| 4|
|mysql-F|mysql-L|mysql-L|mysql-S|
--------------------------------------------------------------
Results per test in seconds: |
--------------------------------------------------------------
ATIS | 174| 76| 66| 104|
alter-table | 1098| 681| 812| 1061|
big-tables | 136| 57| 49| 75|
connect | 204| 236| 151| 159|
create | 417| 20| 9| 231|
insert | 15340| 2593| 1558| 3468|
select | +2547| +1332| +1164| +1815|
wisconsin | 80| 25| 20| 33|
--------------------------------------------------------------
The results per operation: |
--------------------------------------------------------------
alter_table_add (992) | 650| 378| 453| 625|
alter_table_drop (496) | 429| 290| 343| 418|
connect (10000) | 25| 55| 31| 30|
connect+select (10000) | 35| 59| 35| 38|
count (100) | 123| 64| 62| 66|
count_on_key (50100) | +1821| +873| +719| +1290|
create+drop (1000) | 144| 3| 1| 69|
create_index (8) | 9| 7| 9| 9|
create_key+drop (1000) | 158| 3| 3| 67|
create_table (1031) | 69| 8| 4| 55|
delete_big () | | *363| *156| *279|
delete_key (500) | 6| 24| 26| 1|
drop_index () | | *6| *6| *8|
drop_table (1028) | 46| 1| 1| 41|
insert (350768) | 511| 206| 147| 287|
insert_duplicates (300000) | 48| 33| 26| 36|
insert_key (100000) | 9403| 736| 156| 1435|
insert_many_fields (2000) | 20| 14| 13| 18|
min_max (60) | 57| 42| 38| 43|
min_max_on_key (73000) | 330| 216| 198| 248|
multiple_value_inser (100000)| 20| 10| 9| 14|
order_by (10) | 282| 99| 72| 107|
order_by_key (10) | 184| 60| 47| 83|
select (20000) | 18| 12| 7| 16|
select_big (10080) | 274| 109| 91| 120|
select_distinct (800) | 36| 19| 17| 27|
select_group (3811) | 206| 124| 105| 144|
select_join (200) | 89| 32| 26| 44|
select_key (200000) | 374| 249| 212| 262|
select_key_prefix (200000) | 396| 275| 243| 282|
select_many_fields (2000) | 116| 43| 35| 56|
select_range (25420) | 497| 340| 331| 376|
select_range_prefix (25010) | 61| 42| 37| 48|
select_simple (20000) | 37| 61| 36| 40|
select_simple_join (500) | 7| 3| 3| 3|
update_key (500) | 4| 1| 3| 2|
update_key_big (501) | 77| 38| 37| 54|
update_of_key (256) | 1841| 108| 79| 192|
wisc_benchmark (114) | 18| 7| 7| 10|
--------------------------------------------------------------
TOTALS | +19995| +5013| +3824| +6943|
==============================================================
Best regards,
Ilya mailto:ilya@stripped
