MySQL – MySQL on MacBook & Simple Benchmarking

1. Installation Steps

Environment

• Operating System: macOS Sierra
• Processor: 4 GHz Intel Core i7
• Memory: 32 GB 1600 MHz DDR3
• Storage: 256 GB SSD

Prerequisite Tools

• Homebrew

Install MySQL through Homebrew

$ brew install mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.16.sierra.bottle.1.tar.gz
######################################################################## 100.0%
==> Pouring mysql-5.7.16.sierra.bottle.1.tar.gz
==> Using the sandbox
==> /usr/local/Cellar/mysql/5.7.16/bin/mysqld --initialize-insecure --user=AzureQ --basedir=/usr/local/Cellar/mysql/5.7.16 --datadir=/usr/local/var/mysql --tmpdir=/tmp
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
 mysql_secure_installation

To connect run:
 mysql -uroot

To have launchd start mysql now and restart at login:
 brew services start mysql
Or, if you don't want/need a background service you can just run:
 mysql.server start
==> Summary
🍺 /usr/local/Cellar/mysql/5.7.16: 13,511 files, 439M

Start MySQL

$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

2. Simple Benchmarking

Install SysBench

SysBench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load. The idea of this benchmark suite is to quickly get an impression about system performance without setting up complex database benchmarks or even without installing a database at all.

GitHub: https://github.com/akopytov/sysbench

$ brew install sysbench
==> Downloading https://homebrew.bintray.com/bottles/sysbench-0.4.12_2.sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring sysbench-0.4.12_2.sierra.bottle.tar.gz
🍺 /usr/local/Cellar/sysbench/0.4.12_2: 7 files, 144.9K

Prepare data

Create a database sysbench in MySQL.

$ mysql -uroot
mysql> create database sysbench;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| sysbench           |
+--------------------+
5 rows in set (0.00 sec)

Populate a 1 million rows table sbtest under sysbench database.

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=sysbench --mysql-user=root prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
Done
$ mysql -uroot
mysql> use sysbench;
Database changed

mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest             |
+--------------------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.18 sec)

mysql> select * from sbtest limit 5;
+----+---+---+----------------------------------------------------+
| id | k | c | pad |
+----+---+---+----------------------------------------------------+
| 1 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 2 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 3 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 4 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 5 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+----+---+---+----------------------------------------------------+
5 rows in set (0.00 sec)

Benchmark

Run the benchmark in read-only mode for 60 seconds, with 8 concurrent threads.

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=sysbench --mysql-user=root --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
 queries performed:
 read: 2955092
 write: 0
 other: 422156
 total: 3377248
 transactions: 211078 (3517.86 per sec.)
 deadlocks: 0 (0.00 per sec.)
 read/write requests: 2955092 (49250.01 per sec.)
 other operations: 422156 (7035.72 per sec.)

Test execution summary:
 total time: 60.0019s
 total number of events: 211078
 total time taken by event execution: 477.8932
 per-request statistics:
 min: 1.69ms
 avg: 2.26ms
 max: 20.98ms
 approx. 95 percentile: 2.76ms

Threads fairness:
 events (avg/stddev): 26384.7500/32.33
 execution time (avg/stddev): 59.7367/0.00

Interesting information:

1. 211078 transactions are performed within 60 seconds(3517.86 per second)
2. Time statistics about transaction execution: min, avg, max, 95%
3. Threads fairness statistics, which show how fair the simulated workload was

Clean Up

Delete the table SysBench created for the benchmark

$ sysbench --test=oltp --mysql-db=sysbench --mysql-user=root cleanup
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Dropping table 'sbtest'...
Done.