Bottom-Up Database Hardware Benchmarking
Greg Smith
2ndQuadrant US
04/13/2011
Greg Smith Bottom-Up Database Hardware Benchmarking
About this presentation
I The master source for these slides is:
http://projects.2ndquadrant.com
I Source code to automate testing available there too
I Slides are released under the Creative Commons Attribution
3.0 United States License:
http://creativecommons.org/licenses/by/3.0/us
Greg Smith Bottom-Up Database Hardware Benchmarking
Why should you always benchmark your hardware?
I Many useful tests will only run when the server isn’t being
used yet
I Software stacks are complicated
I Spending money on upgrades only helps if you upgrade the
right thing usefully
I Vendors lie
Greg Smith Bottom-Up Database Hardware Benchmarking
Systematic Benchmarking
I Memory
I CPU
I Disk
I Database server
I Application
Greg Smith Bottom-Up Database Hardware Benchmarking
Databases and the CPU
I MySQL and PostgreSQL use only a single CPU per query
I Queries executing against cached data will bottleneck on CPU
I Both CPU and memory need to be fast for individual queries
to be fast
Greg Smith Bottom-Up Database Hardware Benchmarking
STREAM Benchmarking
http://www.advancedclustering.com/company-blog/
Greg Smith Bottom-Up Database Hardware Benchmarking
Oracle Calling Center OLTP Benchmark
http://it.anandtech.com/IT/showdoc.aspx?i=3769&p=4
Greg Smith Bottom-Up Database Hardware Benchmarking
stream-scaling memory read test
git clone git://github.com/gregs1104/stream-scaling.git
cd stream-scaling
./stream-scaling
Greg Smith Bottom-Up Database Hardware Benchmarking
Memory Speeds - DDR2 Era
Greg Smith Bottom-Up Database Hardware Benchmarking
Memory Speeds - DDR3 Era
Greg Smith Bottom-Up Database Hardware Benchmarking
Sources for slow memory results
I Single channel RAM/slot mistakes
I Incorrect SPD/timing/voltage
I Bad RAM/CPU multiplier combination
I Poor quality RAM
I BIOS setup error
Greg Smith Bottom-Up Database Hardware Benchmarking
CPU Tests
I Synthetic CPU benchmarks don’t work anymore
I Use an in-memory, CPU intensive database test instead
I Heavy trivial SELECT statements work well
Greg Smith Bottom-Up Database Hardware Benchmarking
Sources for slow CPU results
I Slow memory
I Power management throttling
I Linux: /proc/cpuinfo shows 1000MHz suggests you need to
adjust the CPUFreq Governor to “performance”
Greg Smith Bottom-Up Database Hardware Benchmarking
Disk Tests
I Sequential write: INSERT, Bulk loading (when not CPU
limited)
I Sequential read: SELECT * FROM and similar table
sequential scans
I Seeks: SELECT using index, UPDATE
I Commit fsync rate: INSERT, UPDATE
Greg Smith Bottom-Up Database Hardware Benchmarking
dd test
I Compute 2X the size of your RAM in 8KB blocks
I blocks = 250,000 * gigabytes of RAM
time sh -c "dd if=/dev/zero of=bigfile bs=8k count=X &&
sync"
time dd if=bigfile of=/dev/null bs=8k
I Watch vmstat and/or iostat during disk tests
I vmstat’s bi and bo will match current read/write rate
I Note the CPU percentage required to reach the peak rate
Greg Smith Bottom-Up Database Hardware Benchmarking
bonnie++
./bonnie++
bon csv2html
I Ignore the per-character and create results, look at the block
output/input ones
I Random Seeks:
I The test runs SeekProcCount processes (default 3) in parallel,
doing a total of 8000 random seek reads to locations in the
file. In 10% of cases, the block read is changed and written
back.
Greg Smith Bottom-Up Database Hardware Benchmarking
bonnie++ ZCAV
./zcav -f/dev/sda > t500
I Must get a recent version of bonnie++ for ZCAV to scale
properly for TB drives (1.03e works)
I ZCAV on experimental branch (1.96) gave useless results for
me
I Download somewhat broken gnuplot script sample and typical
results from:
http://www.coker.com.au/bonnie++/zcav/results.html
Greg Smith Bottom-Up Database Hardware Benchmarking
Improved bonnie++ ZCAV gnuplot script
unset autoscale x
set autoscale xmax
unset autoscale y
set autoscale ymax
set xlabel "Position GB"
set ylabel "MB/s"
set key right bottom
set terminal png
set output "zcav.png"
plot "raid0" title "7200RPM RAID 0 3 Spindles",
"single" title "7200RPM Single Drive"
Greg Smith Bottom-Up Database Hardware Benchmarking
bonnie++ ZCAV: Laptop 7200RPM Disk
Greg Smith Bottom-Up Database Hardware Benchmarking
Scaling of 3-Disk RAID0 with 7200RPM SATA Disks
Greg Smith Bottom-Up Database Hardware Benchmarking
3-Disk Short-Stroked RAID0, larger 7200RPM SATA
Greg Smith Bottom-Up Database Hardware Benchmarking
Read seeks/second - sysbench
THREADS=1
GB=10
MODE=rndrd
OPTIONS="--test=fileio --num-threads=$THREADS
--file-block-size=8K --file-test-mode=$MODE
--file-num=$GB --file-total-size=${GB}G
--file-fsync-freq=0 --file-fsync-end=no"
sysbench prepare $OPTIONS
sysbench run --max-time=60 $OPTIONS
sysbench cleanup $OPTIONS
Greg Smith Bottom-Up Database Hardware Benchmarking
Sample sysbench random read results
Read 78.125Mb Written 0b
Total transferred 78.125Mb (1.0059Mb/sec)
128.75 Requests/sec executed
I That’s 128.75 seeks/second over 10GB, resulting in a net
throughput of 128.75 * 8KB/s = 1.01MB/s
I Consider both the size of the disk used and the number of
clients doing seeks
Greg Smith Bottom-Up Database Hardware Benchmarking
More customizable seek tests
I bonnie++ experimental (currently at 1.96)
I iozone
I fio
I Windows: HD Tune does everything but commit rate
Greg Smith Bottom-Up Database Hardware Benchmarking
Sources for slow disk results
I Poor mapping to underlying hardware
I Buggy driver
I Insufficient bandwidth to storage
I Bottlenecking at CPU/memory limits
I Bad performing filesystem or filesystem misaligned with stripe
sizes
I Writes faster than reads? Probably low read-ahead settings
somewhere.
I Vibration: don’t shout at your JBODs! They don’t like it!
http://it.toolbox.com/blogs/database-soup/the-problem-with-iscsi-30602
http://blog.endpoint.com/2008/09/filesystem-io-what-we-presented.html
http://www.youtube.com/watch?v=tDacjrSCeq4
Greg Smith Bottom-Up Database Hardware Benchmarking
fsync tests
sysbench --test=fileio --file-fsync-freq=1 --file-num=1
--file-total-size=16384 --file-test-mode=rndwr run
| grep "Requests/sec"
I Database insert-only tests
Greg Smith Bottom-Up Database Hardware Benchmarking
Solid State Drives
I Writes are batched to block size by caching small ones
I There must be a write cache for good speed and to reduce
wear
I Look for the battery, capacitor, or super-capacitor to allow
flushing writes when power is lost
I Ask what happens when someone trips over the power cord
I Manufacturer doesn’t say? Assume your data is toast.
Greg Smith Bottom-Up Database Hardware Benchmarking
Good and bad drives
I Bad: Intel X25-M, X25-E, and most cheap consumer drives
I Good: OCZ Vertex 2 Pro, Intel 320 series
I Enterprise SSD models usually get this right, sometimes with
weird downsides
I Run diskchecker.pl and pull the plug yourself:
http://brad.livejournal.com/2116715.html
I Pull the plug on write-heavy database tests, too
Greg Smith Bottom-Up Database Hardware Benchmarking
Sample laptop disk specification
I ST9320423AS Momentus 7200.4 320GB
I 7200 RPM
I 16MB Cache
I Average seek: 11ms read/13ms write
I Average rotational latency: 4.17ms
Greg Smith Bottom-Up Database Hardware Benchmarking
Computed parameters
I Rotational latency = 1 / RPM / 60 / 2
I IOPS=1/(latency+seek)
I IOPS = 1/(((1/(RPM/60))/2) + S)
I IOPS = 1/(4.17ms + 11ms) = 65.9 IOPS
Greg Smith Bottom-Up Database Hardware Benchmarking
IOPS Calculators and Info
http://www.wmarow.com/strcalc/
http://www.dbasupport.com/oracle/ora10g/disk_IO_02.shtml
http://storageadvisors.adaptec.com/2007/03/20/sata-iops-measurement/
Greg Smith Bottom-Up Database Hardware Benchmarking
Sample disk results
Disk Seq Seq bonnie++ Read-only Commit Drive
Count Rd Wr seeks seeks Rate Model
1 71 58 232 @ 4GB 194 @ 4GB 105/s 7200.4
1 59 54 177 @ 16GB 56 @ 100GB 10212/s WD160
3 125 119 371 @ 16GB 60 @ 100GB 10855/s RAID0
1 254 147 3935@ 32GB 3417@100GB 5005/s SSD
I Commit rate for 7200.4 laptop drive is 1048/s with unsafe
volatile write cache
I Non-laptop spinning drives include a 256MB battery-backed
write cache, Linux SW RAID
Greg Smith Bottom-Up Database Hardware Benchmarking
Using sysbench for database comparisons
I Originally targeted MySQL database testing
I Use current source code from development repo:
https://launchpad.net/sysbench
I Now tests PostgreSQL fairly for OLTP read-only transactions
I Standard OLTP tests quietly fail on PostgreSQL due to
transaction isolation differences
Greg Smith Bottom-Up Database Hardware Benchmarking
sysbench compilation
apt-get install bzr
bzr checkout https://code.launchpad.net/\
sysbench-developers/sysbench/0.4
cd 0.4
./autogen.sh
./configure --with-pgsql
make
Greg Smith Bottom-Up Database Hardware Benchmarking
Server configuration for sysbench results
I Quad-Core Intel i870, 8 Hyper-Threaded Cores
I 16GB DDR3-1600 RAM
I Areca ARC-1210 SATA II PCI-e x8 RAID controller, 256MB
write cache
I DB: 3x640GB Western Digital SATA disks, short-stroked,
Linux software RAID-0
I WAL: 160GB Western Digital SATA disk
I Ubuntu 10.04, Linux Kernel 2.6.32-26-generic x86 64
I OS on separate disk
I XFS filesystem
I Default database configurations
Greg Smith Bottom-Up Database Hardware Benchmarking
sysbench with MySQL
echo "create database sysbench;" | mysql -h localhost -u
root
sysbench --mysql-user=root --db-driver=mysql
--mysql-table-engine=innodb --mysql-db=sysbench
--test=oltp prepare
sysbench ... --oltp-read-only=on --oltp-test-mode=simple
--init-rng --max-requests=0
--max-time=$TIME --num-threads=$THREADS run
sysbench ... cleanup
Greg Smith Bottom-Up Database Hardware Benchmarking
sysbench with PostgreSQL
sudo su - postgres -c "createdb sysbench"
sudo su - postgres -c "psql -c \"alter user postgres with
password ’password’;\""
sysbench --pgsql-user=postgres --pgsql-password=password
--pgsql-db=sysbench --pgsql-host=localhost
--db-driver=pgsql --test=oltp prepare
sysbench ... --oltp-read-only=on --oltp-test-mode=simple
--init-rng --max-requests=0
--max-time=$TIME --num-threads=$THREADS run
sysbench ... cleanup
Greg Smith Bottom-Up Database Hardware Benchmarking
sysbench read-only size scaling, 10,000 rows
Greg Smith Bottom-Up Database Hardware Benchmarking
Simple PostgreSQL Configuration - 2GB or more of RAM
I shared buffers = 512MB
I checkpoint segments = 32
I wal buffers = 16MB
I http://wiki.postgresql.org/wiki/Tuning_Your_
PostgreSQL_Server
I To set these parameters and the also important work mem:
I apt-get install pgtune
I https://github.com/gregs1104/pgtune
Greg Smith Bottom-Up Database Hardware Benchmarking
Serious database application tests
I Include read and write transactions
I Track latency as well as transactions/second throughput
I Note size of database relative to RAM
I Make sure load generator isn’t the bottleneck
I Only real way to test subtle tuning like I/O scheduling
Greg Smith Bottom-Up Database Hardware Benchmarking
What should you do?
I Trust no one
I Don’t start on application benchmarks until you’ve proven
basic performance
I Don’t spend too long on basic performance if you can switch
to application benchmarks
I Vendors alternate among lying, misunderstanding what you
want, and trying to make you feel dumb
I Use simple, standard tools whenever possible to minimize
vendor disputes
I Be prepared to translate to your vendor’s language and
subvert their agenda
I Never spend real money on hardware unless you can return it
if it sucks
Greg Smith Bottom-Up Database Hardware Benchmarking
For more information...
I Performance tuning of PostgreSQL 8.1 through 9.0, from
hardware to scaling via replication
I And lots of hardware, OS tuning, and monitoring
Greg Smith Bottom-Up Database Hardware Benchmarking
Questions?
I The BOFs await...
Greg Smith Bottom-Up Database Hardware Benchmarking
