[alexander]

So recently, i've been toying a bit with MySQL, and it is amazing how a proper setup, and a proper server configuration can affect the performance of MySQL. So to give some ideas about how i am going about and setting this up and how it affected throughput, here is a brief tear-down.

Server: Every time its a VM, 4 physical cores (no ht atm), 8GB ram, 80GB hard drive capacity.
Setup 1 (one i'm trying to show is not very optimal):
RHEL 5.6 stock
MySQL 5.0 (latest release, whatever it is, 92 i think) installed from rpm
originally stock config

Setup 2 (one i'm working with):
CentOS 5.6
3 partitions, boot (ext3), / (ext3) and /var/lib/mysql (XFS)
/var/lib/mysql is mounted with noexec,noatime,nodiratime,nobarrier
vm.swappiness = 0
vm.dirty* = 0
MySQL 5.5 (rpm, same deal)
google perf tools
tcmalloc_minimal preloaded pre mysqld start
not-so-stock config includes:

# InnoDB options
#Do not extend files too frequently
innodb_autoextend_increment = 20
# Use one file per table
innodb_file_per_table
# Buffer pool size
innodb_buffer_pool_size           = 5G
innodb_additional_mem_pool_size   = 32M
# Transaction log location and sizes
innodb_log_files_in_group         = 4
innodb_log_file_size             = 256M 
innodb_log_buffer_size            = 4M
# Percentage of unwritten dirty pages not to exceed
innodb_max_dirty_pages_pct        = 80
# Transaction commit policy
innodb_flush_log_at_trx_commit    = 2
# Timeout to wait for a lock before rolling back a transaction
innodb_lock_wait_timeout          = 50
# Flush method
innodb_flush_method               = O_DIRECT
# Number of concurrent threads to run
innodb_thread_concurrency         = 8
# Autoinc lock mode
innodb_autoinc_lock_mode = 1
# Enable fast innodb shutdown
innodb_fast_shutdown              = 0
# Dont delay insert, update and delete operations when purge
# operations are lagging
innodb_max_purge_lag              = 0

So, what did i try.
I used mysqlslap to test mysql io performance, so i ran it with a concurrency of 1, for 100000 queries write-centric, the results of the first run were vastly different. It took over 40 sec on the stock setup, so i fixed that by changing the setup to resemble what i am using in 5.5, here are the results:
Example of command line: mysqlslap --concurrency=1 --iterations=1 --engine=innodb --auto-generate-sql --auto-generate-sql-load-type=write --number-of-queries=100000 -u [user] -h [ip] --password=[password]
stock:

Quote

Running for engine innodb
Average number of seconds to run all queries: 26.058 seconds
Minimum number of seconds to run all queries: 26.058 seconds
Maximum number of seconds to run all queries: 26.058 seconds
Number of clients running queries: 1
Average number of queries per client: 100000

mine:

Quote

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 24.931 seconds
Minimum number of seconds to run all queries: 24.931 seconds
Maximum number of seconds to run all queries: 24.931 seconds
Number of clients running queries: 1
Average number of queries per client: 100000

So i increased concurency to see how they bare:
stock:

Quote

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 9.176 seconds
Minimum number of seconds to run all queries: 9.176 seconds
Maximum number of seconds to run all queries: 9.176 seconds
Number of clients running queries: 10
Average number of queries per client: 10000

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 9.449 seconds
Minimum number of seconds to run all queries: 9.449 seconds
Maximum number of seconds to run all queries: 9.449 seconds
Number of clients running queries: 100
Average number of queries per client: 1000

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 91.794 seconds
Minimum number of seconds to run all queries: 91.794 seconds
Maximum number of seconds to run all queries: 91.794 seconds
Number of clients running queries: 1000
Average number of queries per client: 100

mysqlslap: Cannot drop database 'mysqlslap' ERROR : MySQL server has gone away

it doesn't crash, but queries start timing out, so you get a "server unresponsive thing here" the mysql server doesn't actually crash

not-so-stock:

Quote

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.613 seconds
Minimum number of seconds to run all queries: 5.613 seconds
Maximum number of seconds to run all queries: 5.613 seconds
Number of clients running queries: 10
Average number of queries per client: 10000

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.250 seconds
Minimum number of seconds to run all queries: 5.250 seconds
Maximum number of seconds to run all queries: 5.250 seconds
Number of clients running queries: 100
Average number of queries per client: 1000

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 6.030 seconds
Minimum number of seconds to run all queries: 6.030 seconds
Maximum number of seconds to run all queries: 6.030 seconds
Number of clients running queries: 1000
Average number of queries per client: 100

no conclusions yet, though you can see, that server setup and configuration does matter and can gain significant performance on the same hardware. Now, i'm going to write a script with a battery of tests and attempt to brake both setups, and we'll see how both of these servers bare the weight, write though, as you can see, there is a significant difference in performance already I also want to monitor io, threads, and stuff like that while its all running...

For now, this was the first part of a teaser...

[Robinsoneion]

MySQL Community Server is a freely downloadable version of the database's most popular open source which is supported by an active community of developers and enthusiasts free....