Benchmarking/optimization of MySQL
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) 614 create_index (8) 1 2 drop_index (8)2 3 alter_table_drop (91) 613 Total time 1533 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 (1) 12 72 select_group_when_MANY_tables (1) 7 7 drop_table_when_MANY_tables (1) 3 3 create+drop (1)13 59 create_key+drop (1) 14 54 Total time 49195 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 times were 1336(ONE) vs. 1084(TWO). But it had one most of iritating results: select_column+column (10) 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]
Re: Benchmarking/optimization of MySQL
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) 614 create_index (8) 1 2 drop_index (8)2 3 alter_table_drop (91) 613 Total time 1533 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 (1) 12 72 select_group_when_MANY_tables (1) 7 7 drop_table_when_MANY_tables (1) 3 3 create+drop (1)13 59 create_key+drop (1) 14 54 Total time 49195 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 times were 1336(ONE) vs. 1084(TWO). But it had one most of iritating results: select_column+column (10) 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
Re: Benchmarking/optimization of MySQL
Bostjan Skufca (at) domenca.com wrote: 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. Machine one has a faster memory bus - that is a factor, although not a factor of 2-fold performance difference - I would expect the difference to be no more than 5%. A gracious my.cnf often does harm. Try using the same one in both configurations. And, newer version is not necessarily faster in every way - it just has more features, and is usually faster on some queries that have been an serious issue in the past version. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimization of MySQL
Hi there Is there anything similar to this that will run on a Windows platform? Regards WARREN ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ]On Behalf Of Jeremy Zawodny Sent: 02 May 2001 07:30 To: Bryan Coon Cc: '[EMAIL PROTECTED]' Subject: Re: Optimization of MySQL On Tue, May 01, 2001 at 04:46:39PM -0700, Bryan Coon wrote: Our database is large, and getting larger, with several tables approaching the 1gig mark. In addition, the database will be moving to a dedicated node on a beowulf cluster. Cool... :-) For our users, we are not particulary interested in squeezing every last drop of performance out of MySQL, but I would be interested to know if there are obvious things that I should do to optimize our performace. For example, compiling my own mysql with certain flags, or perhaps startup options, etc. I searched the usenet and mysql site, and found some information but would like to hear some experienced advice before I jump in with both feet. Well, just wait a few weeks for my article in the next issue of Linux Magazine. :-) But before that happens, here are some ideas... There are two approaches to optimization, and you should use both. First is optimizing your application. This is generally just making sure your queries are fast (well indexed), you're only retrieving the data you need, you aren't indexing columns which will never benefit from indexes, you're caching data in your app which can be cached, etc. Second is server tuning. You can look at increasing the size if the key_buffer, record_buffer, and so on in your /etc/my.cnf (or similar) file. Try to get an idea how efficient things are currently. I often use mytop (http://public.yahoo.com/~jzawodn/mytop/) to gather some info about my system before, during, and after tuning. It doesn't give you everything you'll need (yet!), but it's a decent start. You'll probably want to look closely at the output of SHOW VARIABLES and SHOW STATUS and learn more about what some of them mean. And, of course, we're glad to field specific questions on this list. (This reminds me... I'm thinking of another patch to the MySQL manual which explains some more of this stuff. Just need to find the time to do it. Things are getting more, uh... interesting as the number of table handlers expand. With ISAM, MyISAM, InnoDB, BDB, Gemini, and HEAP, there is more room for both improvement and error.) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 118 days, processed 734,376,106 queries (71/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Optimization of MySQL
Hi, I am running a MySQL server on a nice machine (dual pIII 1gHz/1g ram), with RedHat 7.0. Currently, I am using the rpm version of mysql, which is 3.23.37. Our database is large, and getting larger, with several tables approaching the 1gig mark. In addition, the database will be moving to a dedicated node on a beowulf cluster. This node is compareable to the machine described above. For our users, we are not particulary interested in squeezing every last drop of performance out of MySQL, but I would be interested to know if there are obvious things that I should do to optimize our performace. For example, compiling my own mysql with certain flags, or perhaps startup options, etc. I searched the usenet and mysql site, and found some information but would like to hear some experienced advice before I jump in with both feet. Thanks! Bryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimization of MySQL
On Tue, May 01, 2001 at 04:46:39PM -0700, Bryan Coon wrote: Our database is large, and getting larger, with several tables approaching the 1gig mark. In addition, the database will be moving to a dedicated node on a beowulf cluster. Cool... :-) For our users, we are not particulary interested in squeezing every last drop of performance out of MySQL, but I would be interested to know if there are obvious things that I should do to optimize our performace. For example, compiling my own mysql with certain flags, or perhaps startup options, etc. I searched the usenet and mysql site, and found some information but would like to hear some experienced advice before I jump in with both feet. Well, just wait a few weeks for my article in the next issue of Linux Magazine. :-) But before that happens, here are some ideas... There are two approaches to optimization, and you should use both. First is optimizing your application. This is generally just making sure your queries are fast (well indexed), you're only retrieving the data you need, you aren't indexing columns which will never benefit from indexes, you're caching data in your app which can be cached, etc. Second is server tuning. You can look at increasing the size if the key_buffer, record_buffer, and so on in your /etc/my.cnf (or similar) file. Try to get an idea how efficient things are currently. I often use mytop (http://public.yahoo.com/~jzawodn/mytop/) to gather some info about my system before, during, and after tuning. It doesn't give you everything you'll need (yet!), but it's a decent start. You'll probably want to look closely at the output of SHOW VARIABLES and SHOW STATUS and learn more about what some of them mean. And, of course, we're glad to field specific questions on this list. (This reminds me... I'm thinking of another patch to the MySQL manual which explains some more of this stuff. Just need to find the time to do it. Things are getting more, uh... interesting as the number of table handlers expand. With ISAM, MyISAM, InnoDB, BDB, Gemini, and HEAP, there is more room for both improvement and error.) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 118 days, processed 734,376,106 queries (71/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
