From: tcobb Date: April 4 1999 11:15am Subject: RE: VERY STRANGE benchmark results List-Archive: http://lists.mysql.com/mysql/1400 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Ilya, The problem is related to SMP in FreeBSD. Because a database server is I/O bound, and FreeBSD's SMP implementation does a giant i/o lock around the kernel for each processor, it actually is SLOWER to run MySQL on a dual processor FreeBSD machine than on a single processor. I ran into the same problem -- had just bought a dual pII 350, which then became my workstation machine when MySQL ran slowly on it. The GOOD NEWS is that, on a single processor 450MHZ machine, I found FreeBSD MySQL to run significantly FASTER than the benchmarks for any Linux box, including dual PIIs. The other good news is that the FreeBSD SMP implementation is improving rapidly but NOT RECOMMENDED for MySQL right now. -Troy Cobb Circle Net, Inc. http://www.circle.net > -----Original Message----- > From: Ilya Obshadko [mailto:ilya@stripped] > Sent: Sunday, April 04, 1999 6:53 AM > To: mysql@stripped > Subject: VERY STRANGE benchmark results > > > 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 > > > > ------------------------------------------------------------ > --------- > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail mysql-thread1399@stripped To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail mysql-unsubscribe@stripped instead.