On Tue, 2004-03-02 at 19:00, Bostjan Skufca (at) domenca.com wrote: Bostjan,
At first I shall mention you have set up your experiment the hardest way for comments. You have different hardware, different MySQL versions and different MySQL settings. Normally you would like to change only one of them at the time to be able to analyze the difference better. Side Load also does not really benefits results accuracy, however if it is really light making a run several times and taking the best results might be reasonable. Most of your results can be explained by fsync() for .frm creation added in MySQL 4.0.18 which slows down CREATE/ALTER commands a bit, which is however not critical for production. I would also recommend you to get updated sql-bench version from public "mysql-bench" BitKeeper tree it has more benchmarks available. I have however no good explanations for you select col+col query. What is about results with same MySQL options (notes you need to supply them as some defaults were changed in MySQL 4.0, run "show variables" to find out these) > Hello, > > for the last few days I've been running benchmarks from sql-bench directory > and tunning server parameters and I have few questions. > > Firstly I would like to note that benchmarks were run on two different but > similar machines: > > Machine ONE: > Dual Xeon 2.4 533MHz FSB > 4GB RAM > SCSI raid 10 (controller from Adaptec) > Reiserfs > Linux 2.4.25-grsec > MySQL 3.23.58 > /etc/my.cnf is almost empty, server mostly uses defaults for given version > This one is running Apache also but was tested when very lightly loaded > (<5req/s, <5queries/s) > > Machine TWO: > Dual Xeon 2.4 400MHz FSB > 2GB RAM > SCSI raid 1 (controller from Adaptec) > Reiserfs > Linux 2.4.25-grsec > MySQL 4.0.18 > /etc/my.cnf is gracious, giving server enough resources - i guess > This one is actually a mail server but is running MySQL for testing and > comparison purposes. > > Both machines return similar results when doing hdparm on MySQLs' datadir > disks (+/-2Mb for disk reads): > Timing buffer-cache reads: 128 MB in 0.24 seconds =533.33 MB/sec > Timing buffered disk reads: 64 MB in 1.37 seconds = 46.72 MB/sec > (Does somebody also think this is not enogh?) > > Running bonnie++ on machines also resulted in very similar results (results > not included in this message). > > Load on machines was not noticeable at the time of benchmarking but machine > ONE is generally considered "more loaded" than machine TWO. > > > My questions have arisen from observations that in some results the older > version of MySQL on "more loaded" machine was quite faster that the newer > one. > > Running: > ./test-alter-table --host=localhost --user=test --password=test > --database=test --socket=/tmp/mysql.sock --server=MySQL --random --threads=10 > > gave following results: > Test name ONE TWO > ------------------------------------------------------------- > insert (1000) 0 1 > alter_table_add (100) 6 14 > create_index (8) 1 2 > drop_index (8) 2 3 > alter_table_drop (91) 6 13 > Total time 15 33 > > After repeting tests for some time I believed these values are "for real". So > - is there any explanation why newer version alters table slower than older > one? > > > > Running: > ./test-create --host=localhost --user=test --password=test --database=test > --socket=/tmp/mysql.sock --server=MySQL --random --threads=10 > > gave following results: > Test name ONE TWO > ------------------------------------------------------------- > create_MANY_tables (10000) 12 72 > select_group_when_MANY_tables (10000) 7 7 > drop_table_when_MANY_tables (10000) 3 3 > create+drop (10000) 13 59 > create_key+drop (10000) 14 54 > Total time 49 195 > > Now these are what I call drastical difference. > > > > There were also some differences in "test-insert" set of tests but there > machine TWO compensated some of it's loss with it's query cache so "Total > time"s were 1336(ONE) vs. 1084(TWO). But it had one most of iritating > results: > select_column+column (100000) 12 20 > Why is older version that faster in such a simple query? > > Also note that when I installed MySQL 3.23.58 to machine TWO with exactly same > options as it is installed on machine ONE the results were almost identical - > meaning hardware has no noticable impact whatsoever. > > > Does anyone know where these (and other) differences come from? > > > PS: I would be very pleased if I could see hardware description / my.cnf / > sql-bench results from you to see if I am on the right way and how much > headroom do I still have. (Currently my "run-all-tests" script finishes with > just above 1500 seconds on server TWO. Details I will post tomorrow as this > message is already way too long and it is 4o'clock here and I can already see > my bed in front of me although it is still 15 km away:). > > > > Best regards, > > Bostjan Skufca > system administrator > > Domenca d.o.o. > Phone: +386 4 5835444 > Fax: +386 4 5831999 > http://www.domenca.com -- Peter Zaitsev, Senior Support Engineer MySQL AB, www.mysql.com Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
