Here are the timings: 64-bit 5.0 Single Thread 1:00:12.17 total (~76% slower) 64-bit 4.1 Single Thread 41:38.07 total (~20% slower) 64-bit 4.0 Single Thread 34:50.23 total
I have been trying to get a stable configuration for a 64-bit mysql on ubuntu for the past 6-8 months, and have developed a number of stress tests to try to isolate problems. I've been able to successfully discover several bugs in MySQL (or libc) running in 64-bit mode. I've recently started running these tests against 4.1 and 5.0 versions of MySQL, but I have discovered very very different performance characteristics compared to MySQL 4.0. For this test, it imports one of our customer databases, records the import time, drops the database, and restarts. I've run this test against the 4.0, 4.1 and 5.0 binaries. I've found a *dramatic* performance drop on the 4.1 and 5.0 lines against the 4.0 line of MySQL when executing this test. The tests and configurations are identical. This test may be lopsided (insert + alter table enable keys), however it is something that we actually do daily in our production environment. If we were to consider upgrading our production environment from 4.0 to 5.0, a 75% drop in performance would not be acceptable for these types of operations. I ran these tests for a minimum of 24 hours each (although there was very little variance between the 1st and the last run). Each installation was a fresh mysql binary installation, with newly built grant tables (EG: blank). The machine is a 2-cpu 2.4GHz Opteron 4GB Memory with a 4 disc RAID 0+1 array, running Ubuntu Breezy. I ran these tests against the 32-bit version for each of those versions, and saw similar timings, except on average they were 11% slower than their corresponding 64-bit version. I also ran these tests with two threads, (operating on separate databases), and found similar performance hits with MySQL 4.1 and 5.0 and an overall 17% drop in performance against single threaded operation. All my.cnf files were identical between the versions, except that I changed the datadir and language settings (to point to the appropriate directory). I have attached the my.cnf file. I am using MyISAM tables for all tests. The test harness is very simple: #!/bin/zsh while true; do echo "create database $1" | /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/bin/mysql -u root time zcat TEST_DATABASE.mysql.gz | /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/bin/mysql -u root $1 echo "drop database $1" | /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/bin/mysql -u root date done I thought perhaps MySQL 5.0 may be doing a better job determining index cardinalities than 4.0, and as a result there would be no need to do an optimize-db after an insert. I modified the test to insert the database, then optimize each of the tables. I discovered the same test execution time (MySQL 5.0 ~75% slower) and MySQL 4.0 was able to optimize the tables 20% faster. Although I cannot provide our customer data, I can show you an example of what the import file looks like: CREATE TABLE ABCState ( userID int(11) NOT NULL default '0', roleID int(11) NOT NULL default '0', aiFilter int(11) default NULL, PRIMARY KEY (userID,roleID) ) TYPE=MyISAM; -- -- Dumping data for table `ABCState` -- /*!40000 ALTER TABLE ABCState DISABLE KEYS */; LOCK TABLES ABCState WRITE; INSERT INTO ABCState VALUES (15,4,8),(20,4,8),(21,4,8),(22,4,8),(19,5,8),(40,4,8),(42,4,8),(38,4,8),(39,4,8),(43,4,8),(33,4,8),(27, 4,8),(28,4,8),(26,4,8),(25,4,8),(34,4,8),(32,4,8),(35,4,1),(31,4,8),(24,4,8),(36,4,8),(37,4,8),(30,4,8),(29,4,8),(47,6,8),(66,6,8),(64,6,8),(46, 6,8),(67,10,8),(60,6,8),(68,10,8),(69,10,8),(44,5,8),(70,10,8),(71,10,8),(72,5,8),(73,10,8),(77,6,8),(76,6,8),(75,4,8),(74,4,8),(78,4,8),(76,4,8 ),(79,4,8),(77,4,8),(18,5,8),(82,4,8),(11,5,8),(81,5,8),(84,4,8),(85,4,8),(106,4,8),(107,4,8),(31,6,8),(108,4,8),(108,6,8),(110,6,8),(109,6,8),( 111,4,8),(6,5,8),(35,6,8),(36,6,8),(112,6,8),(113,6,8),(114,6,8),(115,4,8),(116,4,8),(120,6,8),(16,5,8),(121,6,8),(118,4,8),(119,4,8),(117,4,8), (126,4,8),(127,4,8),(128,4,8),(129,4,8),(130,4,8),(123,4,8),(126,6,8),(133,4,8),(132,4,8),(132,6,8),(134,4,8),(135,10,8),(38,6,8),(136,10,8),(11 0,4,8),(145,4,8),(137,4,8),(144,4,8),(141,4,8),(140,4,8),(146,4,8),(142,4,8),(148,4,8),(147,4,8),(138,4,8),(149,4,8),(141,6,8),(146,6,8),(142,6, 8),(144,6,8),(147,10,8),(75,10,8),(150,4,8),(137,10,8),(156,10,8),(157,4,8),(158,4,8),(159,4,8),(160,4,8),(113,4,8),(165,4,8); UNLOCK TABLES; /*!40000 ALTER TABLE ABCState ENABLE KEYS */; the dump was generated from a mysqldump -l --add-locks --extended-insert --quick --all --disable-keys. There are millions of records, the compressed GZ file is 500 megs. There is a good array of varchar, char, blob fields across 100+ tables. Does anybody have any idea why 5.0 is performing so poorly? Since I am not using any of the new 4.1 or 5.0 features, I would have expected my performance to be similar between versions. I thought originally that performance may have suffered starting in 4.1 because of the localization overhead, but 5.0 has a much more dramatic performance drop. Are there any settings I should tweak? We do not have any plans to use any of the new features in 4.1 or 5.0. Thanks, -Joe
# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "/var/lib/mysql/my.cnf" to set server-specific options or # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... [client] #password = my_password port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 # Both location gets rotated by the cronjob. log-error = /var/log/mysql/mysql.err #log = /var/log/mysql.log #log = /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/data tmpdir = /var/lib/mysql/tmp language = /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/share/mysql/english skip-external-locking # # The skip-networking option will no longer be set via debconf menu. # You have to manually change it if you want networking i.e. the server # listening on port 3306. The default is "disable" - for security reasons. #skip-networking key_buffer = 512M tmp_table_size = 1024M max_connections = 2000 max_connect_errors = 999999999 table_cache = 1024 myisam_max_sort_file_size=2048M myisam_sort_buffer_size =512M join_buffer_size =512M sort_buffer =512M #low-priority-updates =TRUE max_allowed_packet = 16M thread_stack = 128K # # Query Cache Configuration # query_cache_limit = 1M query_cache_size = 256M query_cache_type = 1 # # Here you can see queries with especially long duration log-slow-queries = /var/log/mysql/mysql-slow.log # # The following can be used as easy to replay backup logs or for replication #server-id = 1 #log-bin = /var/log/mysql/mysql-bin.log #binlog-do-db = include_database_name #binlog-ignore-db = include_database_name # # Read the manual if you want to enable InnoDB! skip-innodb # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # If you want to enable SSL support (recommended) read the manual or my # HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]