Disk of Yesteryear Compared to Today’s SSD Drives

Дата публикации: 17-01-2020 16:48:46

In my [last blog post](https://www.percona.com/
community-blog/2019/08/01/how-to-build-a-percona-server-stack-on-a-raspberry-pi-3/) I showed you how to get the entire Percona “Stack\" up and running on a Raspberry Pi. This time around, I would like to show the impact on performance between using an SSD hard disk and a standard hard disk.
Disk performance is a key factor in Percona Server for MySQL (or any RDB platform) performance on a Raspberry Pi 4.
Test set up
Each test below was run three times per Hard Disk and I took the best of the three for comparison. Hardware
Raspberry Pi 4+ with 4GB ram.
Disk 1: USB3 Western Digital My Passport Ultra, 1TB
Disk 2: USB3 KEXIN 240GB Portable External SSD Drive
Hardware stayed consistent during test, except for the hard disk that were switched from KEXIN to Western Digital drive. Software
Raspbian Buster
Persona Server Version: 5.7.27-30 built from source. See the above BLOG for install instructions.
Sysbench 1.0.17
Sample my.cnf
Copy
Copied!
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysqld.pid
basedir = /usr/local/mysql
datadir = /data0/mysql/data
tmpdir = /data0/mysql/tmp
general_log_file = /var/log/mysql/mysql-general.log
log-error = /var/log/mysql/mysqld.log
slow_query_log_file = /var/log/mysql/log/slow_query.log
slow_query_log = 0 # Slow query log off
lc-messages-dir = /usr/local/mysql/share
plugin_dir = /usr/local/mysql/lib/mysql/plugin
log-bin = /data0/mysql/binlog/mysql-bin
sync_binlog = 1
expire_logs_days = 5
server-id = 1
binlog_format = mixed
max_allowed_packet = 64M
max_connections = 50
max_user_connections = 40
query_cache_size=0
query_cache_type=0
innodb_data_home_dir = /data0/mysql/data
innodb_log_group_home_dir = /data0/mysql/data
innodb_log_files_in_group = 2
innodb_buffer_pool_size = 1536M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
#innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1
skip-name-resolve=0
thread_pool_size=20
innodb_temp_data_file_path = ../tmp/ibtmp1:12M:autoextend:max:8G
Sysbench MySQL test prep step:
Copy
Copied!
sysbench --db-driver=mysql —mysql-db=sbtest --oltp-table-size=500000 --oltp-tables-count=10 --threads=8 --mysql-host= --mysql-port=3306 --mysql-user= --mysql-password=
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua run
Test 1
This was done using the: KEXIN 240GB Portable External SSD Drive. Sysbench command:
Copy
Copied!
sysbench --db-driver=mysql --mysql-db=sbtest --report-interval=2 --mysql-table-engine=innodb --oltp-table-size=500000 --oltp-tables-count=10 --oltp-test-mode=complex --threads=10 --time=150 —mysql-host= --mysql-port=3306 —mysql-user= —mysql-password= /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run
Output:
Copy
Copied!
SQL statistics:
queries performed:
read: 486542
write: 139012
other: 69506
total: 695060
transactions: 34753 (231.62 per sec.)
queries: 695060 (4632.45 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.0362s
total number of events: 34753
Latency (ms):
min: 20.28
avg: 43.16
max: 94.32
95th percentile: 57.87
sum: 1500044.61
Threads fairness:
events (avg/stddev): 3475.3000/368.77
execution time (avg/stddev): 150.0045/0.01
As you can see the performance with the KEXIN (SSD) Drive was pretty good:
Copy
Copied!
transactions: 34753 (231.62 per sec.)
queries: 695060 (4632.45 per sec.)
Test 2
This was done using the: Western Digital My Passport Ultra 1TB drive.
Copy
Copied!
SQL statistics:
queries performed:
read: 60984
write: 17424
other: 8712
total: 87120
transactions: 4356 (29.00 per sec.)
queries: 87120 (579.94 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.2160s
total number of events: 4356
Latency (ms):
min: 23.26
avg: 344.75
max: 1932.12
95th percentile: 733.00
sum: 1501739.03
Threads fairness:
events (avg/stddev): 435.6000/5.71
execution time (avg/stddev): 150.1739/0.05
As you can see the performance on the Western Digital Drive was really bad:
Copy
Copied!
transactions: 4356 (29.00 per sec.)
queries: 87120 (579.94 per sec.)
Disk IO Tests
KEXIN:
Copy
Copied!
Operations performed: 208123 Read, 138748 Write, 443904 Other = 790775 Total
Read 3.1757Gb Written 2.1171Gb Total transferred 5.2928Gb (18.066Mb/sec)
1156.24 Requests/sec executed
Test execution summary:
total time: 300.0004s
total number of events: 346871
total time taken by event execution: 113.1569
per-request statistics:
min: 0.02ms
avg: 0.33ms
max: 31.07ms
approx. 95 percentile: 0.60ms
Threads fairness:
events (avg/stddev): 346871.0000/0.00
execution time (avg/stddev): 113.1569/0.00
Western Digital:
Copy
Copied!
Operations performed: 24570 Read, 16380 Write, 52352 Other = 93302 Total
Read 383.91Mb Written 255.94Mb Total transferred 639.84Mb (2.1327Mb/sec)
136.50 Requests/sec executed
Test execution summary:
total time: 300.0103s
total number of events: 40950
total time taken by event execution: 230.0220
per-request statistics:
min: 0.03ms
avg: 5.62ms
max: 692.52ms
approx. 95 percentile: 13.96ms
Threads fairness:
events (avg/stddev): 40950.0000/0.00
execution time (avg/stddev): 230.0220/0.00
Conclusion
As you can see the transactions per second between the Western Digital Drive and the KEXIN Drive was more than 12.5% slower. The queries per second between the Western Digital Drive and KEXIN drive were more than 12.5% slower. Even the sysbench showed an extreme difference between the two drives. There is a 13.36ms difference in the 95% percentile. KEXIN:
Copy
Copied!
transactions: 34753 (231.62 per sec.)
queries: 695060 (4632.45 per sec.)
Western Digital:
Copy
Copied!
transactions: 4356 (29.00 per sec.)
queries: 87120 (579.94 per sec.)
With the cost of SSD drives dropping, we can see that the Raspberry Pi 4, 4GB with an SSD drive is a good choice for a small business (or anyone) that needs a good robust database at an affordable price range.
The content in this blog is provided in good faith by members of the open source community. Percona has not edited or tested the technical content. Views expressed are the authors’ own. When using the advice from this or any other online resource test ideas before applying them to your production systems, and always secure a working back up.
Photo by Enrico Sottocorna on Unsplash
Disk of Yesteryear Compared to Today’s SSD Drives appeared first on MariaDB.org

Основное содержимое страницы с новостью.

Схожие новости

#	Наименование новости	Тональность	Информативность	Дата публикации
1	Sysbench vs MySQL on a small server: no new regressions, many old ones	0	8.8	24-03-2026
2	How to Build a Percona Server “Stack” on a Raspberry Pi 3+	0	6.51	01-08-2019
3	Off-CPU Analysis Attempt to Find the Reason of Performance Regression in MariaDB 10.4	0	11.2	09-05-2021
4	Sysbench vs MySQL on a small server: another way to view the regressions	0	9.01	09-04-2026
5	MariaDB 10.5 and Memory Instrumentation in Performance Schema – Basic Checks	0	7.67	24-08-2020
6	MySQL 9.7.0 vs sysbench on a small server	0	8.98	10-04-2026
7	CPU-bound sysbench on a large server: Postgres, MySQL and MariaDB	0	5.53	04-04-2026
8	The Insert Benchmark vs MariaDB 10.2 to 13.0 on a 24-core server	0	12.6	08-04-2026
9	Sysbench vs MariaDB on a small server: using the same charset for all versions	0	8.32	06-04-2026
10	HammerDB tproc-c on a large server, Postgres and MySQL	0	6.5	15-02-2026

Классификация: . Схожих патентов: 0. Схожих новостей: 10. Тональность: 0. Информативность: 5.76. Источник: mariadb.org.