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]
