Month: March 2008

Sysbench fileio vs Large EC2 Part 2

Posted by

Overview:

I reran the sysbench fileio benchmarking tool on a large EC2 instance. This is basically the middle range offering from Amazon. The large instance runs a 64 bit OS of your choice and safely sits in the commodity PC/Server range (with a little extra memory).

If you missed the first article, I ran the same sysbench fileio benchmark on the small EC2 instance

Install:

  1. Follow the instructions to install sysbench from that article.
  2. Grab to shell script which will run the same tests.
  3. Run baby run.

Summary:

  1. The root partition mountpoint still is much much better than the /mnt
  2. The large instance outperforms the small instance by a large margin.
  3. There was no improvement in the /mnt performance between small and large EC2 instance.


Results:

Raw Results:
(with EC2 large instance CPU/Memory and disk specification)


[root@ip-10-251-159-159 ~]# uname -a
Linux ip-10-251-159-159 2.6.16.33-xenU #2 SMP Wed Aug 15 17:27:36 SAST 2007 x86_64 x86_64 x86_64 GNU/Linux
[root@ip-10-251-159-159 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  767M  8.6G   9% /
/dev/sdb              414G  199M  393G   1% /mnt
[root@ip-10-251-159-159 ~]# cat /proc/cpuinfo
processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 33
model name      : Dual Core AMD Opteron(tm) Processor 270
stepping        : 2
cpu MHz         : 2004.540
cache size      : 1024 KB
physical id     : 0
siblings        : 1
core id         : 0
cpu cores       : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni lahf_lm cmp_legacy
bogomips        : 4009.78
TLB size        : 1024 4K pages
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: ts fid vid ttp

processor       : 1
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 33
model name      : Dual Core AMD Opteron(tm) Processor 270
stepping        : 2
cpu MHz         : 2004.540
cache size      : 1024 KB
physical id     : 1
siblings        : 1
core id         : 0
cpu cores       : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni lahf_lm cmp_legacy
bogomips        : 4009.78
TLB size        : 1024 4K pages
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: ts fid vid ttp

[root@ip-10-251-159-159 ~]# free
           total       used       free     shared    buffers     cached
Mem:       7864320     218748    7645572          0       3664      37612
-/+ buffers/cache:     177472    7686848
Swap:            0          0          0

[root@ip-10-251-159-159 ~]# cat /proc/meminfo
MemTotal:      7864320 kB
MemFree:       7645572 kB
Buffers:          3680 kB
Cached:          37596 kB
SwapCached:          0 kB
Active:          29264 kB
Inactive:        15376 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      7864320 kB
LowFree:       7645572 kB
SwapTotal:           0 kB
SwapFree:            0 kB
Dirty:              28 kB
Writeback:           0 kB
Mapped:           6584 kB
Slab:             9344 kB
CommitLimit:   3932160 kB
Committed_AS:     9304 kB
PageTables:        604 kB
VmallocTotal: 34359738367 kB
VmallocUsed:       124 kB
VmallocChunk: 34359738243 kB

[root@ip-10-251-159-159 ~]# cat /etc/fstab
# Default /etc/fstab
# Supplied by: Amazon EC2 public image
/dev/sda1 /     ext3    defaults 1 1
/dev/sdb  /mnt  ext3    defaults 0 0
none      /proc proc    defaults 0 0
none      /sys  sysfs   defaults 0 0

 Test on /dev/sda1 

[root@ip-10-251-159-159 bench]# ./sysbench_fileio.sh
sysbench v0.4.8:  multi-threaded system evaluation benchmark

1 files, 131072Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  785793 Read, 0 Write, 0 Other = 785793 Total
Read 11.99Gb  Written 0b  Total transferred 11.99Gb  (204.63Mb/sec)
13096.48 Requests/sec executed

Test execution summary:
  total time:                          60.0003s
  total number of events:              785793
  total time taken by event execution: 57.9662
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0001s
       max:                            0.0600s
       approx.  95 percentile:         0.0001s

Threads fairness:
  events (avg/stddev):           785793.0000/0.00
  execution time (avg/stddev):   57.9662/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Done.

Operations performed:  1000000 Read, 0 Write, 0 Other = 1000000 Total
Read 15.259Gb  Written 0b  Total transferred 15.259Gb  (394.52Mb/sec)
25248.99 Requests/sec executed

Test execution summary:
  total time:                          39.6055s
  total number of events:              1000000
  total time taken by event execution: 2531.7466
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0025s
       max:                            0.0387s
       approx.  95 percentile:         0.0027s

Threads fairness:
  events (avg/stddev):           15625.0000/2.60
  execution time (avg/stddev):   39.5585/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 623302 Write, 0 Other = 623302 Total
Read 0b  Written 9.5108Gb  Total transferred 9.5108Gb  (148.35Mb/sec)
9494.70 Requests/sec executed

Test execution summary:
  total time:                          65.6474s
  total number of events:              623302
  total time taken by event execution: 64.1263
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0001s
       max:                            12.6994s
       approx.  95 percentile:         0.0001s

Threads fairness:
  events (avg/stddev):           623302.0000/0.00
  execution time (avg/stddev):   64.1263/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 460201 Write, 0 Other = 460201 Total
Read 0b  Written 7.0221Gb  Total transferred 7.0221Gb  (119.83Mb/sec)
7668.98 Requests/sec executed

Test execution summary:
  total time:                          60.0081s
  total number of events:              460201
  total time taken by event execution: 3838.8304
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0083s
       max:                            59.9959s
       approx.  95 percentile:         0.0001s

Threads fairness:
  events (avg/stddev):           7190.6406/10250.84
  execution time (avg/stddev):   59.9817/0.03

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 1024Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 1Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 890007 Write, 0 Other = 890007 Total
Read 0b  Written 13.58Gb  Total transferred 13.58Gb  (231.76Mb/sec)
14832.37 Requests/sec executed

Test execution summary:
  total time:                          60.0044s
  total number of events:              890007
  total time taken by event execution: 3837.5238
  per-request statistics:
       min:                            0.0003s
       avg:                            0.0043s
       max:                            15.4482s
       approx.  95 percentile:         0.0056s

Threads fairness:
  events (avg/stddev):           13906.3594/53.51
  execution time (avg/stddev):   59.9613/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 16384Kb each, 2048Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  8349 Read, 0 Write, 0 Other = 8349 Total
Read 130.45Mb  Written 0b  Total transferred 130.45Mb  (2.1739Mb/sec)
139.13 Requests/sec executed

Test execution summary:
  total time:                          60.0086s
  total number of events:              8349
  total time taken by event execution: 59.9863
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0072s
       max:                            1.4351s
       approx.  95 percentile:         0.0127s

Threads fairness:
  events (avg/stddev):           8349.0000/0.00
  execution time (avg/stddev):   59.9863/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  8953 Read, 0 Write, 0 Other = 8953 Total
Read 139.89Mb  Written 0b  Total transferred 139.89Mb  (2.3145Mb/sec)
148.13 Requests/sec executed

Test execution summary:
  total time:                          60.4417s
  total number of events:              8953
  total time taken by event execution: 3859.6431
  per-request statistics:
       min:                            0.0062s
       avg:                            0.4311s
       max:                            1.0842s
       approx.  95 percentile:         0.6042s

Threads fairness:
  events (avg/stddev):           139.8906/1.52
  execution time (avg/stddev):   60.3069/0.17

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 25372 Write, 0 Other = 25372 Total
Read 0b  Written 396.44Mb  Total transferred 396.44Mb  (6.3057Mb/sec)
403.57 Requests/sec executed

Test execution summary:
  total time:                          62.8695s
  total number of events:              25372
  total time taken by event execution: 62.8041
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0025s
       max:                            6.2624s
       approx.  95 percentile:         0.0001s

Threads fairness:
  events (avg/stddev):           25372.0000/0.00
  execution time (avg/stddev):   62.8041/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 22442 Write, 0 Other = 22442 Total
Read 0b  Written 350.66Mb  Total transferred 350.66Mb  (5.8438Mb/sec)
374.00 Requests/sec executed

Test execution summary:
  total time:                          60.0046s
  total number of events:              22442
  total time taken by event execution: 3839.7357
  per-request statistics:
       min:                            0.0013s
       avg:                            0.1711s
       max:                            40.0358s
       approx.  95 percentile:         0.0841s

Threads fairness:
  events (avg/stddev):           350.6562/8.07
  execution time (avg/stddev):   59.9959/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...


 On /mnt 

[root@ip-10-251-159-159 bench]# cd /mnt
[root@ip-10-251-159-159 mnt]# mkdir bench
[root@ip-10-251-159-159 mnt]# cd bench/
[root@ip-10-251-159-159 bench]# cp ~/bench/sysbench_fileio.sh .
[root@ip-10-251-159-159 bench]# ./sysbench_fileio.sh
sysbench v0.4.8:  multi-threaded system evaluation benchmark

1 files, 131072Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  9686 Read, 0 Write, 0 Other = 9686 Total
Read 151.34Mb  Written 0b  Total transferred 151.34Mb  (2.5224Mb/sec)
161.43 Requests/sec executed

Test execution summary:
  total time:                          60.0009s
  total number of events:              9686
  total time taken by event execution: 59.9755
  per-request statistics:
       min:                            0.0003s
       avg:                            0.0062s
       max:                            0.1398s
       approx.  95 percentile:         0.0100s

Threads fairness:
  events (avg/stddev):           9686.0000/0.00
  execution time (avg/stddev):   59.9755/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  10849 Read, 0 Write, 0 Other = 10849 Total
Read 169.52Mb  Written 0b  Total transferred 169.52Mb  (2.8097Mb/sec)
179.82 Requests/sec executed

Test execution summary:
  total time:                          60.3318s
  total number of events:              10849
  total time taken by event execution: 3850.1830
  per-request statistics:
       min:                            0.0077s
       avg:                            0.3549s
       max:                            0.7746s
       approx.  95 percentile:         0.4958s

Threads fairness:
  events (avg/stddev):           169.5156/3.16
  execution time (avg/stddev):   60.1591/0.10

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 15936 Write, 0 Other = 15936 Total
Read 0b  Written 249Mb  Total transferred 249Mb  (4.1233Mb/sec)
263.89 Requests/sec executed

Test execution summary:
  total time:                          60.3881s
  total number of events:              15936
  total time taken by event execution: 60.3480
  per-request statistics:
       min:                            0.0001s
       avg:                            0.0038s
       max:                            0.8638s
       approx.  95 percentile:         0.0002s

Threads fairness:
  events (avg/stddev):           15936.0000/0.00
  execution time (avg/stddev):   60.3480/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 16206 Write, 0 Other = 16206 Total
Read 0b  Written 253.22Mb  Total transferred 253.22Mb  (4.1737Mb/sec)
267.12 Requests/sec executed

Test execution summary:
  total time:                          60.6696s
  total number of events:              16206
  total time taken by event execution: 3881.9322
  per-request statistics:
       min:                            0.0001s
       avg:                            0.2395s
       max:                            60.6637s
       approx.  95 percentile:         0.0002s

Threads fairness:
  events (avg/stddev):           253.2188/1423.67
  execution time (avg/stddev):   60.6552/0.02

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 1024Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 1Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 14826 Write, 0 Other = 14826 Total
Read 0b  Written 231.66Mb  Total transferred 231.66Mb  (3.828Mb/sec)
244.99 Requests/sec executed

Test execution summary:
  total time:                          60.5170s
  total number of events:              14826
  total time taken by event execution: 3855.8588
  per-request statistics:
       min:                            0.0004s
       avg:                            0.2601s
       max:                            1.7562s
       approx.  95 percentile:         0.7735s

Threads fairness:
  events (avg/stddev):           231.6562/6.36
  execution time (avg/stddev):   60.2478/0.08

sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 16384Kb each, 2048Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  8373 Read, 0 Write, 0 Other = 8373 Total
Read 130.83Mb  Written 0b  Total transferred 130.83Mb  (2.1804Mb/sec)
139.54 Requests/sec executed

Test execution summary:
  total time:                          60.0027s
  total number of events:              8373
  total time taken by event execution: 59.9801
  per-request statistics:
       min:                            0.0003s
       avg:                            0.0072s
       max:                            0.4734s
       approx.  95 percentile:         0.0107s

Threads fairness:
  events (avg/stddev):           8373.0000/0.00
  execution time (avg/stddev):   59.9801/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  10034 Read, 0 Write, 0 Other = 10034 Total
Read 156.78Mb  Written 0b  Total transferred 156.78Mb  (2.5952Mb/sec)
166.09 Requests/sec executed

Test execution summary:
  total time:                          60.4118s
  total number of events:              10034
  total time taken by event execution: 3855.7836
  per-request statistics:
       min:                            0.0066s
       avg:                            0.3843s
       max:                            1.0552s
       approx.  95 percentile:         0.5357s

Threads fairness:
  events (avg/stddev):           156.7812/2.78
  execution time (avg/stddev):   60.2466/0.10

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 12932 Write, 0 Other = 12932 Total
Read 0b  Written 202.06Mb  Total transferred 202.06Mb  (3.3463Mb/sec)
214.16 Requests/sec executed

Test execution summary:
  total time:                          60.3847s
  total number of events:              12932
  total time taken by event execution: 60.3509
  per-request statistics:
       min:                            0.0002s
       avg:                            0.0047s
       max:                            3.6623s
       approx.  95 percentile:         0.0002s

Threads fairness:
  events (avg/stddev):           12932.0000/0.00
  execution time (avg/stddev):   60.3509/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 13200 Write, 0 Other = 13200 Total
Read 0b  Written 206.25Mb  Total transferred 206.25Mb  (3.4138Mb/sec)
218.48 Requests/sec executed

Test execution summary:
  total time:                          60.4171s
  total number of events:              13200
  total time taken by event execution: 3864.5972
  per-request statistics:
       min:                            0.0003s
       avg:                            0.2928s
       max:                            3.7448s
       approx.  95 percentile:         2.3343s

Threads fairness:
  events (avg/stddev):           206.2500/5.68
  execution time (avg/stddev):   60.3843/0.01

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...

Seeker io benchmark on small EC2

Posted by

1

Frederic posted an interesting link to the seeker io script in the sysbench fileio vs small EC2 article.

He was concerned that the seeker was showing difference numbers to sysbench fileio, especially on the /mnt mountpoint.

I read the thread and there was another comment made concerning how Xen could use a sparse file image to improve the io performance. I tested that theory out and found as soon as I created a reasonable sized file on /mnt, the performance dropped significantly to less than 20% of the empty mountpoint performance.

I appreciate the feedback I receive via comments. Most of the feedback I have derive purely from Google web analytics and sitemap tools.

Results:



[root@domU-12-31-35-00-04-61 ~]# sudo hdparm -t /dev/sda1

/dev/sda1:
Timing buffered disk reads:  216 MB in  3.01 seconds =  71.68 MB/sec
[root@domU-12-31-35-00-04-61 ~]# sudo hdparm -t /dev/sda2

/dev/sda2:
Timing buffered disk reads:  380 MB in  3.00 seconds = 126.51 MB/sec


[root@domU-12-31-35-00-04-61 ~]# ./seeker /dev/sda1
Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda1 [1025MB], wait 30 seconds..............................
Results: 278 seeks/second, 3.60 ms random access time

./seeker /dev/sda2

Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda2 [152688MB], wait 30 seconds..............................
Results: 5741 seeks/second, 0.17 ms random access time

 Add a file to /mnt to test the sparse Xen image idea 

dd if=/dev/zero of=/mnt/zerofile bs=1024M

[root@domU-12-31-35-00-04-61 mnt]# ls -l
total 7347232
drwx------  2 root root      16384 Mar 16 12:38 lost+found
-rw-r--r--  1 root root        710 Mar 16 23:37 randomfile
-rw-r--r--  1 root root 7516192768 Mar 16 23:44 zerofile
[root@domU-12-31-35-00-04-61 mnt]# cd
[root@domU-12-31-35-00-04-61 ~]# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1              1032056    885984     93592  91% /
/dev/sda2            153899044   7539888 138541532   6% /mnt
[root@domU-12-31-35-00-04-61 ~]# ./seeker /dev/sda2
Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda2 [152688MB], wait 30 seconds.............................
Results: 1101 seeks/second, 0.91 ms random access time
[root@domU-12-31-35-00-04-61 ~]# ./seeker /dev/sda2
Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda2 [152688MB], wait 30 seconds......................
Results: 582 seeks/second, 1.72 ms random access time
[root@domU-12-31-35-00-04-61 ~]# ./seeker /dev/sda2
Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda2 [152688MB], wait 30 seconds.....................
Results: 177 seeks/second, 5.62 ms random access time
[root@domU-12-31-35-00-04-61 ~]# ./seeker /dev/sda2
Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda2 [152688MB], wait 30 seconds.........................
Results: 881 seeks/second, 1.13 ms random access time
[root@domU-12-31-35-00-04-61 ~]# w
23:50:30 up  4:07,  1 user,  load average: 0.62, 3.77, 3.39
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     ttyp0    203-19-70-133.pe 19:47    0.00s  0.01s  0.00s w
[root@domU-12-31-35-00-04-61 ~]# ./seeker /dev/sda2
Seeker v2.0, 2007-01-15, http://www.linuxinsight.com/how_fast_is_your_disk.html
Benchmarking /dev/sda2 [152688MB], wait 30 seconds....................
Results: 355 seeks/second, 2.82 ms random access time

Sysbench fileio vs EC2 Part 1

Posted by

6

Overview:

Peter Zaitsev’s recent article about Evaluating IO subsystem performance for MySQL spurred my interest in doing something similar on EC2.

I have covered running sysbench against MySQL on EC2 however not specifically used sysbench to test IO. Rather I had used bonnie++ and iozone to do that.

I don’t have a lot of respect for the EC2 small instance. Whilst it was reasonable in the middle of 2006 when Amazon EC2 was launched, you can (even in Australia) pick up Dual and Quad Core CPUs with enough memory for 32 bit OS for a reasonable amount nowadays.
The main problem as I see it for my specific interest is the lack of grunt in network and IO bandwidth. Having the idea of Grid or Utility or the latest “Cloud” computing falls apart when you have to hit disk or worse need to synchronize across a slow network.

So I was mildly surprised when the small instance was able to match and out-perform Peter’s benchmarks on the local disk at least.

Summary:

Using Peter’s benchmark description as a key, I reran his tests on a small EC2 instance. The local disk matched and outperformed his benchmark with no changes to any settings.
Unfortunately the mounted filesystem /mnt was much much worse.

If you are using MySQL or any IO intensive application, I would do the following:

  1. Make your image specify as large a local mount as possible. You will need the raw IO speed for anything which requires fast reads and fast writes.
  2. If you can take the risk use the Linux file cache. Especially if you have to use /mnt.
  3. Treat the raw IO performance of /mnt as slow disk, even in some respects like a backup location. It has a requests/sec and io transfer rates little better than USB 1.0 High speed. Certainly not even in the realm of Firewire.
  4. Get as much into memory as possible. This means narrowing table and indexes sizes or going with the more expensive large and extra large instances.

Install:

  1. wget sysbench from sourceforge.
  2. yum install gcc (or apt-get gcc) if not already installed
  3. configure, make and install sysbench
  4. Run this simple shell script sysbench_fileio.sh to replicate Peter’s benchmark.
  5. Free feel to publish your results, either here as a comment or on your own blog. Link or drop me a note and I will link to your benchmark results and we can tally up and see for whom the IO tolls.

Documentation (explains the various options)

http://sysbench.sourceforge.net/docs/#fileio_mode

Configuration:

Amazon small EC2 instance, running CentOS 4.4



[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  855M  8.6G   9% /
/dev/sda2             147G  188M  140G   1% /mnt
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/sda1 /     ext3    defaults 1 1
/dev/sda2 /mnt  ext3    defaults 1 2
/dev/sda3 swap  swap    defaults 0 0
none      /proc proc    defaults 0 0
none      /sys  sysfs   defaults 0 0

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# uname -a
Linux domU-12-31-38-00-20-02 2.6.16-xenU #1 SMP Mon May 28 03:41:49 SAST 2007 i686 athlon i386 GNU/Linux
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/redhat-release
CentOS release 4.4 (Final)
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /proc/cpuinfo
processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 65
model name      : Dual-Core AMD Opteron(tm) Processor 2218 HE
stepping        : 3
cpu MHz         : 2600.000
cache size      : 1024 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm cr8legacy ts fid vid ttp tm stc
bogomips        : 5201.75

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# ipcs

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# free
     total       used       free     shared    buffers     cached
Mem:       1740944     533224    1207720          0     217020     263060
-/+ buffers/cache:      53144    1687800
Swap:       917496          0     917496

Results:

Raw Results:



wget http://optusnet.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz

yum install gcc sysstat

tar -xzvf sysbench-0.4.8.tar.gz
cd sysbench-0.4.8
./configure --without-mysql
make
make install

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  855M  8.6G   9% /
/dev/sda2             147G  188M  140G   1% /mnt
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/sda1 /     ext3    defaults 1 1
/dev/sda2 /mnt  ext3    defaults 1 2
/dev/sda3 swap  swap    defaults 0 0
none      /proc proc    defaults 0 0
none      /sys  sysfs   defaults 0 0

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# uname -a
Linux domU-12-31-38-00-20-02 2.6.16-xenU #1 SMP Mon May 28 03:41:49 SAST 2007 i686 athlon i386 GNU/Linux
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/redhat-release
CentOS release 4.4 (Final)
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /proc/cpuinfo
processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 65
model name      : Dual-Core AMD Opteron(tm) Processor 2218 HE
stepping        : 3
cpu MHz         : 2600.000
cache size      : 1024 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm cr8legacy ts fid vid ttp tm stc
bogomips        : 5201.75

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# ipcs

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# free
      total       used       free     shared    buffers     cached
Mem:       1740944     533224    1207720          0     217020     263060
-/+ buffers/cache:      53144    1687800
Swap:       917496          0     917496

http://sysbench.sourceforge.net/docs/#fileio_mode

 Sysbench FileIO test options 

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# sysbench --test=fileio help
sysbench v0.4.8:  multi-threaded system evaluation benchmark

fileio options:
--file-num=N                  number of files to create [128]
--file-block-size=N           block size to use in all IO operations [16384]
--file-total-size=SIZE        total size of files to create [2G]
--file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
--file-io-mode=STRING         file operations mode {sync,async,fastmmap,slowmmap} [sync]
--file-extra-flags=STRING     additional flags to use on opening files {sync,dsync,direct} []
--file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all=[on|off]     do fsync() after each write operation [off]
--file-fsync-end=[on|off]     do fsync() at the end of test [on]
--file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
--file-rw-ratio=N             reads/writes ratio for combined test [1.5]


 Peter Zaitsev's 120M prepare file command 

sysbench --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=1 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=128M prepare

sysbench v0.4.8:  multi-threaded system evaluation benchmark

1 files, 131072Kb each, 128Mb total
Creating files for the test...

 Running the test with the 120M file 

sysbench --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0  --file-total-size=128M --file-test-mode=rndrd run

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  590164 Read, 0 Write, 0 Other = 590164 Total
Read 9.0052Gb  Written 0b  Total transferred 9.0052Gb  (153.69Mb/sec)
9836.03 Requests/sec executed

Test execution summary:
total time:                          60.0002s
total number of events:              590164
total time taken by event execution: 59.3742
per-request statistics:
  min:                            0.0001s
  avg:                            0.0001s
  max:                            0.0980s
  approx.  95 percentile:         0.0001s

Threads fairness:
events (avg/stddev):           590164.0000/0.00
execution time (avg/stddev):   59.3742/0.00

 Rerunning with 64 threads 

sysbench --num-threads=64 --test=fileio --max-time=60 \
--max-requests=1000000  --file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0  --file-total-size=128M --file-test-mode=rndrd run
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  745086 Read, 0 Write, 0 Other = 745086 Total
Read 11.369Gb  Written 0b  Total transferred 11.369Gb  (194.02Mb/sec)
12417.46 Requests/sec executed

Test execution summary:
total time:                          60.0031s
total number of events:              745086
total time taken by event execution: 3837.4047
per-request statistics:
  min:                            0.0003s
  avg:                            0.0052s
  max:                            0.1228s
  approx.  95 percentile:         0.0325s

Threads fairness:
events (avg/stddev):           11641.9688/8.86
execution time (avg/stddev):   59.9594/0.02

 Running in single thread with random write i.e. --file-test-mode=rndwr


sysbench --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=1 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=128M --num-threads=1 --file-test-mode=rndwr run

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 93104 Write, 0 Other = 93104 Total
Read 0b  Written 1.4207Gb  Total transferred 1.4207Gb  (24.227Mb/sec)
1550.55 Requests/sec executed

Test execution summary:
total time:                          60.0457s
total number of events:              93104
total time taken by event execution: 59.9639
per-request statistics:
  min:                            0.0000s
  avg:                            0.0006s
  max:                            1.8278s
  approx.  95 percentile:         0.0001s

Threads fairness:
events (avg/stddev):           93104.0000/0.00
execution time (avg/stddev):   59.9639/0.00

 with 64 threads --num-threads=64

sysbench --test=fileio --num-threads=64 --max-time=60 \
--max-requests=1000000 --file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0  --file-total-size=128M \
--file-test-mode=rndwr run

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 109175 Write, 0 Other = 109175 Total
Read 0b  Written 1.6659Gb  Total transferred 1.6659Gb  (24.115Mb/sec)
1543.37 Requests/sec executed

Test execution summary:
total time:                          70.7379s
total number of events:              109175
total time taken by event execution: 4329.6293
per-request statistics:
  min:                            0.0000s
  avg:                            0.0397s
  max:                            70.7328s
  approx.  95 percentile:         0.0001s

Threads fairness:
events (avg/stddev):           1705.8594/6223.80
execution time (avg/stddev):   67.6505/3.06

 Cleaning up for test with multiple files 

sysbench --test=fileio --num-threads=64 --max-time=60 \
--max-requests=1000000  --file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0  --file-total-size=128M --file-test-mode=rndwr cleanup
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...

 Preparing files 

sysbench --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=128M --num-threads=64 --file-test-mode=rndwr prepare

sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 1024Kb each, 128Mb total
Creating files for the test...

 rerunning with 128 files to total of 128M 

sysbench --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=128M --num-threads=64 --file-test-mode=rndwr run

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 1Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 126633 Write, 0 Other = 126633 Total
Read 0b  Written 1.9323Gb  Total transferred 1.9323Gb  (25.607Mb/sec)
1638.85 Requests/sec executed

Test execution summary:
total time:                          77.2695s
total number of events:              126633
total time taken by event execution: 4561.7451
per-request statistics:
  min:                            0.0002s
  avg:                            0.0360s
  max:                            28.6922s
  approx.  95 percentile:         0.0069s

Threads fairness:
events (avg/stddev):           1978.6406/28.82
execution time (avg/stddev):   71.2773/5.32

 Preparing the 128 files to a total of 2Gig test 

sysbench --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=2G --num-threads=1 --file-test-mode=rndwr prepare

 The run 

sysbench --init-rng=1 --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=2G --num-threads=1 --file-test-mode=rndrd run

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  14094 Read, 0 Write, 0 Other = 14094 Total
Read 220.22Mb  Written 0b  Total transferred 220.22Mb  (3.6701Mb/sec)
234.89 Requests/sec executed

Test execution summary:
total time:                          60.0034s
total number of events:              14094
total time taken by event execution: 59.9868
per-request statistics:
  min:                            0.0001s
  avg:                            0.0043s
  max:                            0.2521s
  approx.  95 percentile:         0.0129s

Threads fairness:
events (avg/stddev):           14094.0000/0.00
execution time (avg/stddev):   59.9868/0.00

 2Gig file test with 64 threads 

sysbench --init-rng=1 --test=fileio --max-time=60 --max-requests=1000000  \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0  \
--file-total-size=2G --num-threads=64 --file-test-mode=rndrd run

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  24639 Read, 0 Write, 0 Other = 24639 Total
Read 384.98Mb  Written 0b  Total transferred 384.98Mb  (6.3875Mb/sec)
408.80 Requests/sec executed

Test execution summary:
total time:                          60.2719s
total number of events:              24639
total time taken by event execution: 3847.3337
per-request statistics:
  min:                            0.0023s
  avg:                            0.1561s
  max:                            0.5804s
  approx.  95 percentile:         0.3490s

Threads fairness:
events (avg/stddev):           384.9844/0.94
execution time (avg/stddev):   60.1146/0.08

 2 Gig file test single thread random writes --file-test-mode=rndwr 

sysbench --init-rng=1 --test=fileio --max-time=60 \
--max-requests=1000000  --file-num=128 --file-extra-flags=direct \
--file-fsync-freq=0  --file-total-size=2G --num-threads=1 \
--file-test-mode=rndwr run

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 52960 Write, 0 Other = 52960 Total
Read 0b  Written 827.5Mb  Total transferred 827.5Mb  (13.748Mb/sec)
879.89 Requests/sec executed

Test execution summary:
total time:                          60.1893s
total number of events:              52960
total time taken by event execution: 60.1396
per-request statistics:
  min:                            0.0001s
  avg:                            0.0011s
  max:                            2.1815s
  approx.  95 percentile:         0.0001s

Threads fairness:
events (avg/stddev):           52960.0000/0.00
execution time (avg/stddev):   60.1396/0.00


 2 Gig file test 64 threads random writes --file-test-mode=rndwr 

sysbench --init-rng=1 --test=fileio --max-time=60 \
--max-requests=1000000  --file-num=128 --file-extra-flags=direct \
--file-fsync-freq=0  --file-total-size=2G --num-threads=64 \
--file-test-mode=rndwr run

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 38767 Write, 0 Other = 38767 Total
Read 0b  Written 605.73Mb  Total transferred 605.73Mb  (10.038Mb/sec)
642.45 Requests/sec executed

Test execution summary:
total time:                          60.3424s
total number of events:              38767
total time taken by event execution: 3861.3838
per-request statistics:
  min:                            0.0001s
  avg:                            0.0996s
  max:                            4.8871s
  approx.  95 percentile:         0.8721s

Threads fairness:
events (avg/stddev):           605.7344/14.70
execution time (avg/stddev):   60.3341/0.00

 Rerunning the whole test set on /mnt 

cd /mnt
vi sysbench_fileio.sh
chmod 700 sysbench_fileio.sh
./sysbench_fileio.sh

sysbench v0.4.8:  multi-threaded system evaluation benchmark

1 files, 131072Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!


Time limit exceeded, exiting...
Done.

Operations performed:  9782 Read, 0 Write, 0 Other = 9782 Total
Read 152.84Mb  Written 0b  Total transferred 152.84Mb  (2.547Mb/sec)
163.01 Requests/sec executed

Test execution summary:
total time:                          60.0092s
total number of events:              9782
total time taken by event execution: 59.9964
per-request statistics:
  min:                            0.0003s
  avg:                            0.0061s
  max:                            0.1835s
  approx.  95 percentile:         0.0100s

Threads fairness:
events (avg/stddev):           9782.0000/0.00
execution time (avg/stddev):   59.9964/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!

Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  10799 Read, 0 Write, 0 Other = 10799 Total
Read 168.73Mb  Written 0b  Total transferred 168.73Mb  (2.7943Mb/sec)
178.83 Requests/sec executed

Test execution summary:
total time:                          60.3856s
total number of events:              10799
total time taken by event execution: 3852.0646
per-request statistics:
  min:                            0.0098s
  avg:                            0.3567s
  max:                            0.9908s
  approx.  95 percentile:         0.6044s

Threads fairness:
events (avg/stddev):           168.7344/4.29
execution time (avg/stddev):   60.1885/0.11

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 15274 Write, 0 Other = 15274 Total
Read 0b  Written 238.66Mb  Total transferred 238.66Mb  (3.9774Mb/sec)
254.55 Requests/sec executed

Test execution summary:
total time:                          60.0029s
total number of events:              15274
total time taken by event execution: 59.9853
per-request statistics:
  min:                            0.0003s
  avg:                            0.0039s
  max:                            0.3117s
  approx.  95 percentile:         0.0106s

Threads fairness:
events (avg/stddev):           15274.0000/0.00
execution time (avg/stddev):   59.9853/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 13360 Write, 0 Other = 13360 Total
Read 0b  Written 208.75Mb  Total transferred 208.75Mb  (3.4615Mb/sec)
221.54 Requests/sec executed

Test execution summary:
total time:                          60.3062s
total number of events:              13360
total time taken by event execution: 3847.7447
per-request statistics:
  min:                            0.0003s
  avg:                            0.2880s
  max:                            60.2694s
  approx.  95 percentile:         0.0175s

Threads fairness:
events (avg/stddev):           208.7500/1157.46
execution time (avg/stddev):   60.1210/0.07

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 1024Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 1Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 14728 Write, 0 Other = 14728 Total
Read 0b  Written 230.12Mb  Total transferred 230.12Mb  (3.8192Mb/sec)
244.43 Requests/sec executed

Test execution summary:
total time:                          60.2541s
total number of events:              14728
total time taken by event execution: 3846.4353
per-request statistics:
  min:                            0.0004s
  avg:                            0.2612s
  max:                            1.5574s
  approx.  95 percentile:         0.5732s

Threads fairness:
events (avg/stddev):           230.1250/10.67
execution time (avg/stddev):   60.1006/0.03

sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, 16384Kb each, 2048Mb total
Creating files for the test...
sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  8496 Read, 0 Write, 0 Other = 8496 Total
Read 132.75Mb  Written 0b  Total transferred 132.75Mb  (2.2124Mb/sec)
141.60 Requests/sec executed

Test execution summary:
total time:                          60.0016s
total number of events:              8496
total time taken by event execution: 59.9901
per-request statistics:
  min:                            0.0003s
  avg:                            0.0071s
  max:                            0.2733s
  approx.  95 percentile:         0.0107s

Threads fairness:
events (avg/stddev):           8496.0000/0.00
execution time (avg/stddev):   59.9901/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  9497 Read, 0 Write, 0 Other = 9497 Total
Read 148.39Mb  Written 0b  Total transferred 148.39Mb  (2.4566Mb/sec)
157.22 Requests/sec executed

Test execution summary:
total time:                          60.4044s
total number of events:              9497
total time taken by event execution: 3854.1290
per-request statistics:
  min:                            0.0094s
  avg:                            0.4058s
  max:                            1.1867s
  approx.  95 percentile:         0.7207s

Threads fairness:
events (avg/stddev):           148.3906/3.90
execution time (avg/stddev):   60.2208/0.12

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  0 Read, 12236 Write, 0 Other = 12236 Total
Read 0b  Written 191.19Mb  Total transferred 191.19Mb  (3.1862Mb/sec)
203.91 Requests/sec executed

Test execution summary:
total time:                          60.0056s
total number of events:              12236
total time taken by event execution: 59.9917
per-request statistics:
  min:                            0.0003s
  avg:                            0.0049s
  max:                            0.3623s
  approx.  95 percentile:         0.0117s

Threads fairness:
events (avg/stddev):           12236.0000/0.00
execution time (avg/stddev):   59.9917/0.00

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed:  0 Read, 12251 Write, 0 Other = 12251 Total
Read 0b  Written 191.42Mb  Total transferred 191.42Mb  (3.1792Mb/sec)
203.47 Requests/sec executed

Test execution summary:
total time:                          60.2109s
total number of events:              12251
total time taken by event execution: 3847.4821
per-request statistics:
  min:                            0.0039s
  avg:                            0.3141s
  max:                            1.8187s
  approx.  95 percentile:         0.6672s

Threads fairness:
events (avg/stddev):           191.4219/8.33
execution time (avg/stddev):   60.1169/0.04

sysbench v0.4.8:  multi-threaded system evaluation benchmark

Removing test files...

MySQL Master-Master replication table sync

Posted by

I saw a post by Baron mentioning that his tool maatkit is best for handling situations where a master-master replication setup has got out of sync.

If you think Baron was blowing his own trumpet he has good reason to. I have used his mk-archiver tool as part of the Maatkit to make the problem of archiving and purging data much easier. This was much easier than rolling my own solution.

Anyhow. I have a master-master replication just lying around to test this kind of stuff after finishing the multi-instance master-master replication pair last week. Plus I have already had some past experience using table-checksum tool which is part of Maatkit (or MySQL toolkit as it used to be known). Amazing how you publish some stuff and then you get the next idea for an article almost immediately.

Overview:

To test Baron’s assertion that Maatkit mk-table-sync is the best tool to re-sync a master-master replication pair.

Install:

  1. Setup a master-master replication pair.
  2. Download and install Maatkit.
  3. Load the Sakila sample database.
  4. Make some changes on one master which are not replicated to the other.
  5. Verify that the tables are out-of-sync using mk-table-checksum
  6. Use mk-table-sync to re-sync the tables.

Summary:

Again Maatkit lives up to my expectations. Both mk-table-checksum and mk-table-sync discovered and re-synced the tables without any issues.
On a side note, without this toolkit, you could still have rebuilt the other master from the dedicated slave, but this is so much faster and easier.

Ideas for the motivated reader:

  1. As always be careful when using set global sql_slave_skip_counter = 1;
  2. Download and test the Maatkit tools for yourself.
  3. Run mk-table-checksum periodically to discover if your slaves are actually consistent with your master.
  4. Having multiple recovery methods is good, you should be experienced and confident to use them all.

Detailed Screen Dump with comments:



Maatkit Dependencies

yum install perl-DBI perl-DBD-MySQL gcc
Time::HiRes

wget http://search.cpan.org/CPAN/authors/id/J/JH/JHI/Time-HiRes-1.9712.tar.gz

download maatkit-1753 from Sourceforge

perl Makefile.PL
make install

 Break the master-master replication 

On master2:

stop slave;

On master1:

mysql> use sakila
Database changed

mysql> insert into film values(1001,'Welcome to DBA Dojo','A place on the way towards being a DBA',2007,1,1,30,45,25.00,90000.00,'G','Behind the Scenes',now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from film where film_id = 1001;
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| film_id | title               | description                            | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features  | last_update         |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
|    1001 | Welcome to DBA Dojo | A place on the way towards being a DBA |         2007 |           1 |                    1 |              30 | 45.00       |     25 | 999.99           | G      | Behind the Scenes | 2008-03-03 05:42:58 |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
            film_id: 1001
              title: Welcome to DBA Dojo
        description: A place on the way towards being a DBA
       release_year: 2007
        language_id: 1
original_language_id: 1
    rental_duration: 30
        rental_rate: 45.00
             length: 25
   replacement_cost: 999.99
             rating: G
   special_features: Behind the Scenes
        last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

 On master 2 

mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from film where film_id = 1001\G
Empty set (0.00 sec)

 On Master 1 run a simple table checksum 


mk-table-checksum h=master1,u=root,p=$PASSWD h=master1 --databases=sakila

DATABASE TABLE         CHUNK HOST    ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
sakila   actor             0 master1 MyISAM       NULL       3596356558    0    0 NULL NULL
sakila   actor             0 master1 MyISAM       NULL       3596356558    0    0 NULL NULL
sakila   address           0 master1 MyISAM       NULL       3083097758    0    0 NULL NULL
sakila   category          0 master1 MyISAM       NULL       2281594170    0    0 NULL NULL
sakila   category          0 master1 MyISAM       NULL       2281594170    0    0 NULL NULL
sakila   city              0 master1 MyISAM       NULL       1881669182    0    0 NULL NULL
sakila   address           0 master1 MyISAM       NULL       3083097758    0    0 NULL NULL
sakila   city              0 master1 MyISAM       NULL       1881669182    0    0 NULL NULL
sakila   country           0 master1 MyISAM       NULL       3658016321    0    0 NULL NULL
sakila   country           0 master1 MyISAM       NULL       3658016321    0    0 NULL NULL
sakila   customer          0 master1 MyISAM       NULL       1332169016    0    0 NULL NULL
sakila   customer          0 master1 MyISAM       NULL       1332169016    0    0 NULL NULL
sakila   film              0 master1 MyISAM       NULL       1490639089    0    0 NULL NULL
sakila   film              0 master1 MyISAM       NULL       1490639089    0    0 NULL NULL
sakila   film_actor        0 master1 MyISAM       NULL       3128610213    0    0 NULL NULL
sakila   film_actor        0 master1 MyISAM       NULL       3128610213    0    0 NULL NULL
sakila   film_category     0 master1 MyISAM       NULL       3646644932    0    0 NULL NULL
sakila   film_category     0 master1 MyISAM       NULL       3646644932    0    0 NULL NULL
sakila   film_text         0 master1 MyISAM       NULL       2391883145    0    0 NULL NULL
sakila   film_text         0 master1 MyISAM       NULL       2391883145    0    0 NULL NULL
sakila   inventory         0 master1 MyISAM       NULL       3471334076    0    0 NULL NULL
sakila   inventory         0 master1 MyISAM       NULL       3471334076    0    0 NULL NULL
sakila   language          0 master1 MyISAM       NULL         19972916    0    0 NULL NULL
sakila   language          0 master1 MyISAM       NULL         19972916    0    0 NULL NULL
sakila   payment           0 master1 MyISAM       NULL        684052380    1    0 NULL NULL
sakila   payment           0 master1 MyISAM       NULL        684052380    0    0 NULL NULL
sakila   rental            0 master1 MyISAM       NULL       2658764859    0    0 NULL NULL
sakila   rental            0 master1 MyISAM       NULL       2658764859    0    0 NULL NULL
sakila   staff             0 master1 MyISAM       NULL       1172551672    0    0 NULL NULL
sakila   staff             0 master1 MyISAM       NULL       1172551672    0    0 NULL NULL
sakila   store             0 master1 MyISAM       NULL       1107595282    0    0 NULL NULL
sakila   store             0 master1 MyISAM       NULL       1107595282    0    0 NULL NULL

 Check the local slave 


mk-table-checksum h=master1,u=root,p=$PASSWD h=slave1 --databases=sakila
DATABASE TABLE         CHUNK HOST    ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
sakila   actor             0 master1 MyISAM       NULL       3596356558    0    0 NULL NULL
sakila   actor             0 slave1  MyISAM       NULL       3596356558    0    0 NULL NULL
sakila   address           0 master1 MyISAM       NULL       3083097758    0    0 NULL NULL
sakila   address           0 slave1  MyISAM       NULL       3083097758    0    0 NULL NULL
sakila   category          0 master1 MyISAM       NULL       2281594170    0    0 NULL NULL
sakila   category          0 slave1  MyISAM       NULL       2281594170    0    0 NULL NULL
sakila   city              0 master1 MyISAM       NULL       1881669182    0    0 NULL NULL
sakila   city              0 slave1  MyISAM       NULL       1881669182    0    0 NULL NULL
sakila   country           0 master1 MyISAM       NULL       3658016321    0    0 NULL NULL
sakila   country           0 slave1  MyISAM       NULL       3658016321    0    0 NULL NULL
sakila   customer          0 master1 MyISAM       NULL       1332169016    0    0 NULL NULL
sakila   customer          0 slave1  MyISAM       NULL       1332169016    0    0 NULL NULL
sakila   film              0 master1 MyISAM       NULL       1490639089    0    0 NULL NULL
sakila   film              0 slave1  MyISAM       NULL       1490639089    0    0 NULL NULL
sakila   film_actor        0 master1 MyISAM       NULL       3128610213    0    0 NULL NULL
sakila   film_actor        0 slave1  MyISAM       NULL       3128610213    0    0 NULL NULL
sakila   film_category     0 master1 MyISAM       NULL       3646644932    0    0 NULL NULL
sakila   film_category     0 slave1  MyISAM       NULL       3646644932    0    0 NULL NULL
sakila   film_text         0 master1 MyISAM       NULL       2391883145    0    0 NULL NULL
sakila   film_text         0 slave1  MyISAM       NULL       2391883145    0    0 NULL NULL
sakila   inventory         0 master1 MyISAM       NULL       3471334076    0    0 NULL NULL
sakila   inventory         0 slave1  MyISAM       NULL       3471334076    0    0 NULL NULL
sakila   language          0 master1 MyISAM       NULL         19972916    0    0 NULL NULL
sakila   language          0 slave1  MyISAM       NULL         19972916    0    0 NULL NULL
sakila   payment           0 master1 MyISAM       NULL        684052380    0    0 NULL NULL
sakila   payment           0 slave1  MyISAM       NULL        684052380    0    0 NULL NULL
sakila   rental            0 master1 MyISAM       NULL       2658764859    0    0 NULL NULL
sakila   rental            0 slave1  MyISAM       NULL       2658764859    0    0 NULL NULL
sakila   staff             0 slave1  MyISAM       NULL       1172551672    0    0 NULL NULL
sakila   staff             0 master1 MyISAM       NULL       1172551672    0    0 NULL NULL
sakila   store             0 master1 MyISAM       NULL       1107595282    0    0 NULL NULL
sakila   store             0 slave1  MyISAM       NULL       1107595282    0    0 NULL NULL


 Creating the CHECKSUM table 

mysql> use mysql
Database changed
mysql> CREATE TABLE checksum (
   ->     db         char(64)     NOT NULL,
   ->     tbl        char(64)     NOT NULL,
   ->     chunk      int          NOT NULL,
   ->     boundaries char(64)     NOT NULL,
   ->     this_crc   char(40)     NOT NULL,
   ->     this_cnt   int          NOT NULL,
   ->     master_crc char(40)         NULL,
   ->     master_cnt int              NULL,
   ->     ts         timestamp    NOT NULL,
   ->     PRIMARY KEY (db, tbl, chunk)
   ->  ) ENGINE=InnoDB;

 Forget the port and you will get this error 

mk-table-checksum h=master1,u=root,p=$PASSWD h=master2,u=root,p=$PASSWD \
--databases=sakila

DBI connect(';host=master2;mysql_read_default_group=mysql','root',...) failed: Can't connect to MySQL server on 'master2

 And we have a winner, mk-table-checksum has found the issue with the film table 

mk-table-checksum h=master1,u=root,p=$PASSWD \
h=master2,u=root,p=$PASSWD,P=3308 --databases=sakila

DATABASE TABLE         CHUNK HOST    ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
...
sakila   film              0 master1 MyISAM       NULL       1490639089    0    0 NULL NULL
sakila   film              0 master2 MyISAM       NULL       1421174266    0    0 NULL NULL
...

 Now use Baron's mk-table-sync example... testing first. 

mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--test

# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
#      0       0      0      0 Chunk     sakila.film

 Run the real thing 

mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--verbose --execute

# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
#      0       1      0      0 Chunk     sakila.film


 On master2 

mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
            film_id: 1001
              title: Welcome to DBA Dojo
        description: A place on the way towards being a DBA
       release_year: 2007
        language_id: 1
original_language_id: 1
    rental_duration: 30
        rental_rate: 45.00
             length: 25
   replacement_cost: 999.99
             rating: G
   special_features: Behind the Scenes
        last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

 On Slave 2, which is a cascade slave of master 1 

mysql -u root -p$PASSWD -S /tmp/mysql2.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.20-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sakila
Database changed
mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
            film_id: 1001
              title: Welcome to DBA Dojo
        description: A place on the way towards being a DBA
       release_year: 2007
        language_id: 1
original_language_id: 1
    rental_duration: 30
        rental_rate: 45.00
             length: 25
   replacement_cost: 999.99
             rating: G
   special_features: Behind the Scenes
        last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)