MySQL Super Smack database benchmark results

June 16th, 2009 by Boštjan Škufca

After a couple of years away in academic environment, I got myself back into the business of system administration (or as I prefer to call it, system engineering), Linux/BSD system administration to be precise. Of course, all the old (and precious) notes and data were gone, so I had to start from scratch. I was given a task to put together a scalable system of web servers. At certain point I started to benchmark the systems and I have realised that I had almost no data to compare the results to. Therefore I decided to share these results with the world and in the same manner make it available for me in the future. They are intended as a reference point if you want to know how much performance can you squeeze out of certain hardware/software.

Today I am starting with MySQL, the database of choice for many web developers.

For benchmarking MySQL performance, I primarily use three tools:

  1. The MySQL benchmark suite,
  2. Super Smack and
  3. some custom applications for real world tests.

As it is commonly known, first two items from the list above are not what one might call “real world benchmark suites”. But they are very fine tools for discovering system bottlenecks and obvious configuration errors. Below follow benchmark commands and results with complete (relevant) setup description. Enjoy!

Results for select-key
Queries per second: 166541

cd /usr/share/smacks &&
super-smack select-key.smack 32 100000

Query Barrel Report for client smacker1
connect: max=34ms min=0ms avg= 14ms from 32 clients
Query_type num_queries max_time min_time q_per_s
select_index 6400000 0 0 166541.80

Results for update-key
Queries per second: 9225

cd /usr/share/smacks &&
super-smack update-select.smack 32 10000

Query Barrel Report for client smacker
connect: max=0ms min=0ms avg= 0ms from 32 clients
Query_type num_queries max_time min_time q_per_s
select_index 320000 2 0 9225.27
update_index 320000 2 0 9225.27

Hardware

  • HP DL380 g5
  • 2x Intel E5420 Xeon (4 cores @ 2.5GHz each)
  • 8GB ECC RAM
  • 2x 147 SAS 10k drives in hardware RAID-1 configuration
  • Write cache enabled for Smart Array P400i

Software

  • Slackware 12.2
  • Ext3 filesystem
  • Linux kernel version 2.4.29.1
  • Super Smack 1.3, compiled from source
  • MySQL version 5.0.77, compiled from source with configure line:

    ./configure –prefix=/usr/local/$PDIR \
    –with-charset=utf8 –with-collation=utf8_general_ci \
    –with-unix-socket-path=/var/mysql/run/mysql.sock \
    –with-mysqld-user=mysql \
    –with-openssl=/usr/local/openssl-0.9.8k

/etc/my.cnf

[client]
socket = /var/mysql/run/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
pid-file = /var/mysql/run/mysqld.pid
socket = /var/mysql/run/mysql.sock
datadir = /var/mysql/data
log-error = /var/mysql/log/mysql_error.log
log-slow-queries = /var/mysql/log/mysql_slow_query.log

ssl
ssl-cert = /etc/ssl/certs/_default.cert
ssl-key = /etc/ssl/certs/_default.key

key_buffer = 256M
max_allowed_packet = 32M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
net_buffer_length = 2K
thread_stack = 128K
thread_cache_size = 16K
thread_concurrency = 8
query_cache_size = 32M
max_join_size = 4294967295

innodb_data_home_dir = /var/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/mysql/data
innodb_log_arch_dir = /var/mysql/data
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

And also the results of sql-bench suite are here.

Feedback
If you have any questions, recommendations or benchmark results to compare, do not hesitate to leave a comment.

Tags: , , , , , ,


One Response to “MySQL Super Smack database benchmark results”

  1. […] Blog @ a2o.si Linux system administration « MySQL Super Smack database benchmark results […]

Leave a Reply

 

*