Here are some numbers I got from running mysqlbench on our own-compiled versions of MySQL 5.0.51b which is used internally here.
We are using gcc 4.1.2 for our Solaris x86 machines. For the tests, the following compilation options have been used:
{isam,inno}32: -m32
{isam,inno}32k8: -m32 -march=k8 -O3
{isam,inno}64: -m64
{isam,inno}64k8: -m64 -march=k8 -O3
mysqlbench was used, consisting of nine tests: connect, create, alter table, big-tables, ATIS, insert, select, transactions and wisconsin.
K8-Optimization was used because we are using Sun X4200 servers here with AMD Opteron dual core processors.
Please note that for the unoptimized 64 InnoDB benchmark, only 3 values have been taken, for all other measurements, 4 values have been taken.
We are using gcc 4.1.2 for our Solaris x86 machines. For the tests, the following compilation options have been used:
{isam,inno}32: -m32
{isam,inno}32k8: -m32 -march=k8 -O3
{isam,inno}64: -m64
{isam,inno}64k8: -m64 -march=k8 -O3
mysqlbench was used, consisting of nine tests: connect, create, alter table, big-tables, ATIS, insert, select, transactions and wisconsin.
K8-Optimization was used because we are using Sun X4200 servers here with AMD Opteron dual core processors.
Please note that for the unoptimized 64 InnoDB benchmark, only 3 values have been taken, for all other measurements, 4 values have been taken.
1. connect
Connect times do not differ very much. 64bit is slightly faster than 32bit, and k8-optimization is slightly faster than unoptimized gcc code.
2. create
Creating databases and tables does not relate to optimization. I assume that this is purely disk i/o so the CPU does not have a significant part here. InnoDB tables take way longer to be created than MyISAM ones.
3. ATIS
The ATIS benchmark is the first where differences are visible. Again, 64bit is faster than 32bit and k8-optimized is faster than unoptimized code, especially on InnoDB tables! 64bit k8 optimized is 50% faster than 32bit unoptimized code.
4. big-tables
Again the same results: t(32bit) > t(64bit) > t(32k8) > t(64k8).
5. alter
InnoDB tables take much more time to be altered, and - 64bit code is slower than 32bit code. It may be a special behaviour of gcc 4.1.2 created code. One point is certain: Using unoptimized gcc code is not a really good idea.
6. insert
64bit k8 code gives a 10% performance plus over a 32bit k8 MySQL DB executable (mysqld).
Again, unoptimized code is - especially if using InnoDB - very unpleasant.
7. select
Look at the 64bit performance plus in the MyISAM case.
8. transactions
As MyISAM does not offer transactions, there are no benchmark values here.
9. wisconsin
Last but not least - the wisconsin test.
Relevant parameters in my.cnf:
Connect times do not differ very much. 64bit is slightly faster than 32bit, and k8-optimization is slightly faster than unoptimized gcc code.
2. create
Creating databases and tables does not relate to optimization. I assume that this is purely disk i/o so the CPU does not have a significant part here. InnoDB tables take way longer to be created than MyISAM ones.
3. ATIS
The ATIS benchmark is the first where differences are visible. Again, 64bit is faster than 32bit and k8-optimized is faster than unoptimized code, especially on InnoDB tables! 64bit k8 optimized is 50% faster than 32bit unoptimized code.
4. big-tables
Again the same results: t(32bit) > t(64bit) > t(32k8) > t(64k8).
5. alter
InnoDB tables take much more time to be altered, and - 64bit code is slower than 32bit code. It may be a special behaviour of gcc 4.1.2 created code. One point is certain: Using unoptimized gcc code is not a really good idea.
6. insert
64bit k8 code gives a 10% performance plus over a 32bit k8 MySQL DB executable (mysqld).
Again, unoptimized code is - especially if using InnoDB - very unpleasant.
7. select
Look at the 64bit performance plus in the MyISAM case.
8. transactions
As MyISAM does not offer transactions, there are no benchmark values here.
9. wisconsin
Last but not least - the wisconsin test.
Relevant parameters in my.cnf:
key_buffer = 16M
max_allowed_packet = 2M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections= 1000
character_set_server = utf8
collation_server = utf8_general_ci
innodb_data_file_path = ibdata1:400M:autoextend
innodb_buffer_pool_size = 400M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_doublewrite=0
innodb_flush_log_at_trx_commit=0
Leave a comment