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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
