Ever wondered what kind of QPS benchmarks you can squeeze out of a $60 computer? Well look no further, because you’re about to find out!
A little while back, I ordered a Raspberry Pi. What better way to start hacking on a Pi than to get MySQL installed and start running benchmarks, right?
Right?
In this article, I’m gonna walk you through all the steps, from getting Linux installed and configured, getting the MySQL server up-and-running, how to connect via SSH / MySQL from another local machine, and some benchmark results.
Prepping the Pi
There’s a bunch of RPi models out there, and I specifically ordered a Raspberry Pi 4 model B.
Given it’s price of $62, the specs are actually not bad:
- Quad core 1.5Ghz ARM processor
- 4 GB RAM
- For storage, a micro-SD card slot
- IO: Ethernet, USB 2 and 3, wifi, microHDMI
Unfortunately, it does not come with anything else. I bought a pack of microSD cards, and also needed an HDMI -> microHDMI cable for connecting a monitor. Though it does have a dedicated power input, it can also power over the usb-C slot, so I opted for that.
After having all the necessary connections, the next step is getting an OS on this thing. This step was actually shockingly easy. Raspberry Pi provides a tool called the Raspberry Pi Imager which makes it simple to get any common Linux build loaded onto a storage device.
Since this Raspberry Pi uses a micro-SD card as its primary nonvolatile storage, getting it loaded directly onto that will be ideal. In my case, I was running this in my Macbook Pro, which thankfully has a built-in SD card slot. I also have an SD / micro-SD car adapter:
This means that loading up an OS is as easy as:
- Put a desired micro SD card into the adapter and insert into MBP
- Open up Raspberry Pi Imager and select your device type, OS, and storage medium
- Click the button to start the installation process (takes ~10 mins or so)
Once the install is complete, put the micro SD card into the Pi, plug in the other I/O devices (monitor, keyboard, mouse) and then plug in the USB-C and watch it power up.
At this point, you’ll want the Pi plugged in to your monitor so you can navigate through all the standard OS-setup steps. I won’t bore you with those details - pretty self explanatory. As you’re going through the steps, make sure to either (A) connect to your local Wifi network or (B) plug in your Pi directly to a Ethernet port on your local router / switch. In the pictures I show an Ethernet port plugged in, but I’ll actually just be using wifi for convenience. I have a decent wifi 6 local mesh setup, which should be plenty fast to saturate MySQL with load on the Pi.
I will say that I actually initially tried out Raspberry Pi OS. However, this is Debian based, but I’m much more familiar with Ubuntu. After flailing with RPiOS for a bit, I decided to cut my loss and switch over to Ubuntu. Getting that is actually really easy also, as Ubuntu is one of the options on the Raspberry Pi Imager. If its your first time using that to install Ubuntu, you’ll just have to wait a little longer for the imager to download it before installing into the storage device.
Setup SSH
You’ll want to ensure that you allow for SSH connections to the pi if you want to follow along with what I’m gonna show here.
For this, install and start openssh-server.
$ sudo apt install openssh-server -y
$ sudo systemctl enable ssh
$ sudo systemctl start ssh
To make sure it is up and running, check with this:
$ sudo systemctl status ssh
Configuring MySQL
With Ubuntu powered up and ready to use, the next step is to install and prepare MySQL. Getting this installed and the server started is a breeze on Ubuntu. Open up a terminal and do a:
$ sudo apt update
$ sudo apt install mysql-server
$ sudo service mysql start
I will want this server accessible to the outside world (my Macbook), so there’s a few things we need to configure.
First, open up /etc/mysql/mysql.conf.d/mysqld.cnf and set both bind-address and mysqlx-bind-address to 0.0.0.0.
This will allow MySQL to receive connections from other hosts / IP addresses.
After saving, you’ll need to restart the server for this to take effect:
sudo service mysql restart
Also, lets make make sure that port 3306 is able to receive connections on this machine (3306 is the default one for MySQL):
$ sudo ufw allow 3306
Then, connect to MySQL.
You can use sudu to start it up the first time, but we’ll fix that ASAP by adding another user that we’ll use for testing out the DB.
$ sudo mysql
mysql>
mysql> CREATE USER 'ben'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ben'@'%' WITH GRANT OPTION;
OK, maybe not the best idea to give this user ALL those privileges, but I’m going to run with it because I want this to be easy.
Connecting to the Pi
I want to run my benchmarks form a different local computer, and send all of the queries over the network to the Pi. This will give be a better sense of what it is actually capable of, since I won’t be running the workload scripts on the Pi, taking up valuable and scarce CPU resources. How exactly you will need to connect may vary a bit depending on the computer you’re connecting from as well as your network settings and configuration. In my case, I want to talk to my Pi from my Macbook Pro. If your Mac and Pi are both on the same local network and you’ve completed all the previous steps, then you should be able to connect via ssh.
On the Pi, run the following to get your username:
$ whoami
And this to get your hostname:
$ hostname -f
In my case it is ben and ubuntu-1.
Switching over to the Mac, you can test the connection by SSHing into the Pi:
$ ssh ben@ubuntu-1
ben@ubuntu-1's password:
Welcome to Ubuntu 24.04 LTS (GNU/Linux 6.8.0-1004-raspi aarch64)
...
ben@ubuntu-1:~$
Hopefully that works for you also! You can also check the MySQL connection. If the SSH command worked, and you configured everything else correctly earlier, the following should work (just change the username, hostname, and password as needed).
$ mysql -u ben -ppassword -h ubuntu-1 -P 3306
Benchmarking the Pi with TPCC
With the connection established, next is to benchmark this puppy! Broadly speaking, benchmarks should not be trusted or taken at face value, particularly when comparing one DB to another. There’s just too many factors that can be tweaks to make one look better than the other.
However, for this I’m really just trying to identify what the limits of MySQL on a device like a Pi is. For the benchmarks, I’m going to use the widely-well-regarded TPCC benchmark. More specifically, I’m going to use Percona’s custom version of TPCC for MySQL. As you’ll see, I’m also going to end up trying out some other, read-only benchmarks.
On the computer connected to the Pi, you’ll want to check out that sysbench repo.
Then, create the sbt database in MySQL:
$ mysql -u ben -ppassword -h ubuntu-1
mysql> CREATE DATABASE sbt;
Query OK, 1 row affected (0.03 sec)
mysql> exit;
$
Next, in the sysbench repo, prepare the benchmark:
$ ./tpcc.lua --mysql-user=ben --mysql-db=sbt \
--mysql-host=ubuntu-1 --mysql-password=password \
--time=10 --threads=8 --report-interval=1 --tables=1 \
--scale=1 --db-driver=mysql prepare
And now run:
$ ./tpcc.lua --mysql-user=ben --mysql-db=sbt \
--mysql-host=ubuntu-1 --mysql-password=password \
--time=20 --threads=32 --report-interval=1 --tables=1 \
--scale=1 --db-driver=mysql run
And the results are:
SQL statistics:
queries performed:
read: 3080
write: 3086
other: 608
total: 6774
transactions: 256 (10.95 per sec.)
queries: 6774 (289.62 per sec.)
ignored errors: 3 (0.13 per sec.)
reconnects: 0 (0.00 per sec.)
Wow. Terrible.
A whopping 289 queries per second. We shouldn’t expect much from meager hardware, but that’s really bad. The bottleneck here probably has to be with the poor write performance on the Pi. The main storage hardware is a micro-SD which isn’t known for it’s performance, of course. It could also be made worse by bad defaults configuration of MySQL.
Read-Only Benchmarks
I want to see what the absolute limit of QPS is on this device, so let’s try two other things: (A) a read-only workload with TPCC and (B) a custom benchmark.
I’ll run sysbench again but this time use the oltp_read_only benchmark instead.
As the name suggests, this contains only read workload.
To run, first create a database named oltp on the Pi MySQL instance.
Then, from your benchmark computer, execute:
$ sysbench --mysql-user=ben --mysql-db=oltp \
--mysql-host=ubuntu-1 --mysql-password=password \
--threads=50 --time=30 --tables=1 \
oltp_read_only prepare
$ sysbench --mysql-user=ben --mysql-db=oltp \
--mysql-host=ubuntu-1 --mysql-password=password \
--threads=50 --time=30 --tables=1 \
oltp_read_only run
And here are the results:
SQL statistics:
queries performed:
read: 34762
write: 0
other: 4966
total: 39728
transactions: 2483 (81.38 per sec.)
queries: 39728 (1302.02 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Better. From the 100s to the 1000+. Also, I know that I have at least nearly maxxed out what the CPU is capable of, given what is shown in the resource monitor on the Pi:
Not bad, but still not really that impressive.
No GUI
Something I realized is that just running the GUI / windowing system is a chore for something as small as a Pi. Doing so takes up enough resources to cause noticeable performance issues. For example, take a look at the end of that CPU graph above. The 30-second window where the CPU was at near 100% is when the OLTP benchmark was running. However, that last spike at the end was simply cause by going through the menu and opening up the screenshot app. If that can shoot the CPU to 100%, then we have a problem!
I want to try the same benchmark again, but without the window server running on the device. To do this, I enable multi-user mode and then restart:
$ sudo systemctl set-default multi-user
$ reboot
This starts up the computer again without the windowing system running.
Running the same oltp_read_only benchmark with Ubuntu booted up in this mode got me:
SQL statistics:
queries performed:
read: 37016
write: 0
other: 5288
total: 42304
transactions: 2644 (86.64 per sec.)
queries: 42304 (1386.22 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Only very slightly better. Might be statistically insignificant, but I’ll take it regardless.
Maxxing Out
To really see how much we can push MySQL on this thing, let’s try a custom benchmark. I have some custom scripts for generating, populating, and running a benchmark on a simple, four-table database. The schema script:
DROP DATABASE IF EXISTS quiz;
CREATE DATABASE IF NOT EXISTS quiz;
USE quiz;
CREATE TABLE question (
question_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
correct_answer_id BIGINT UNSIGNED NOT NULL,
text varchar(1024) NOT NULL,
answer_count INT DEFAULT 0,
PRIMARY KEY (question_id)
);
CREATE TABLE answer (
answer_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
question_id BIGINT UNSIGNED NOT NULL,
text varchar(1024) NOT NULL,
PRIMARY KEY (answer_id)
);
CREATE TABLE user (
user_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
first_name varchar(32) NOT NULL,
last_name varchar(32) NOT NULL,
username varchar(32) NOT NULL,
email varchar(128) NOT NULL,
bio varchar(1024),
pfp varchar(512),
PRIMARY KEY (user_id)
);
CREATE TABLE attempt (
attempt_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
question_id BIGINT UNSIGNED NOT NULL,
attempted_at DATETIME NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
selection_id BIGINT UNSIGNED NOT NULL,
feedback VARCHAR(1024),
wrong_id_1 BIGINT UNSIGNED,
wrong_id_2 BIGINT UNSIGNED,
wrong_id_3 BIGINT UNSIGNED,
wrong_id_4 BIGINT UNSIGNED,
PRIMARY KEY (attempt_id)
);
To load this into the database, I can run this from my Mac:
$ cat schema.sql | mysql -u ben -ppassword -h ubuntu-1
Next, the script to populate with data:
import sys
import random
import string
from datetime import datetime
from faker import Faker
fake = Faker()
SCALE = int(sys.argv[-1])
class InsertBuilder:
def __init__(self, table_name, columns):
self.table_name = table_name
self.columns = columns
self.rows = []
self.batch = 1000
def add_row(self, row):
self.rows.append(row)
def dump(self):
result = ''
for i in range (0, len(self.rows), self.batch): # 1k row inserts at a time
result += "INSERT INTO " + self.table_name + " (" + ', '.join(self.columns) + ") VALUES ";
j = i
while j < i+self.batch and j < len(self.rows):
result += valueify(self.rows[j])
j+=1
if j != i+self.batch and j != len(self.rows):
result += ',\n'
result += ';\n'
print(result)
self.rows = []
def valueify(values):
result = "("
for i in range(len(values)):
element = values[i]
if type(element) == type(0) or type(element) == type(1.0):
result += str(element)
else:
result += "'" + element + "'"
if i < len(values)-1:
result += ", "
return result + ")"
def user_inserts():
ib = InsertBuilder('user', ['first_name', 'last_name', 'username', 'email', 'bio', 'pfp']);
for i in range(SCALE):
ib.add_row([fake.first_name(), fake.last_name(),
fake.word() + fake.word() + str(random.randint(0, 20)),
fake.word() + '@' + fake.word() + '.com',
fake.sentence(nb_words=10),
'/' + fake.word() + '/' + fake.word() + '.jpg'])
if i % 1000 == 0:
ib.dump()
ib.dump()
print('done with users', file=sys.stderr)
def question_inserts():
ib = InsertBuilder('question', ['correct_answer_id', 'text', 'answer_count']);
for i in range(SCALE*2):
ib.add_row([random.randint(1, SCALE*2),
fake.sentence(nb_words=10),
random.randint(0, 100000)])
if i % 1000 == 0:
ib.dump()
ib.dump()
print('done with questions', file=sys.stderr)
def answer_inserts():
ib = InsertBuilder('answer', ['question_id', 'text']);
for i in range(SCALE*2):
ib.add_row([random.randint(1, SCALE*2),
fake.sentence(nb_words=4)])
if i % 1000 == 0:
ib.dump()
ib.dump()
print('done with answers', file=sys.stderr)
def attempt_inserts():
ib = InsertBuilder('attempt', ['question_id', 'attempted_at', 'user_id', 'selection_id', 'feedback', 'wrong_id_1', 'wrong_id_2', 'wrong_id_3', 'wrong_id_4']);
for i in range(SCALE*20):
t = fake.date_time_between(start_date='-1y', end_date='now')
ts = t.strftime('%Y-%m-%d %H:%M:%S')
ib.add_row([random.randint(1, SCALE*2),
ts,
random.randint(1, SCALE),
random.randint(1, SCALE*5),
fake.sentence(nb_words=10),
random.randint(1, SCALE*5),
random.randint(1, SCALE*5),
random.randint(1, SCALE*5),
random.randint(1, SCALE*5)])
if i % 1000 == 0:
ib.dump()
ib.dump()
print('done with attempts', file=sys.stderr)
print('USE quiz;')
user_inserts()
question_inserts()
answer_inserts()
attempt_inserts()
This can load data of various scales.
For this, let’s go with something small like 1000:
$ python3 populate.py 1000 | mysql -u ben -ppassword -h ubuntu-1
Finally, the script to run a super simple, read-only workload on it:
import threading
import time
import random
import sys
import MySQLdb
connections = []
threads = []
SCALE = int(sys.argv[-3])
QUERIES = int(sys.argv[-2])
THREADS = int(sys.argv[-1])
def make_connection():
connection = MySQLdb.connect(
host='ubuntu-1',
user='ben',
passwd='password',
db='quiz')
return connection
def run_load(index):
cursor = connections[index].cursor()
for i in range(QUERIES//4):
cursor.execute('SELECT username FROM user WHERE user_id = %s;', (random.randint(1, SCALE),))
cursor.execute('SELECT correct_answer_id FROM question WHERE question_id = %s;', (random.randint(1, SCALE*2),))
cursor.execute('SELECT question_id FROM answer WHERE answer_id = %s;', (random.randint(1, SCALE*2),))
cursor.execute('SELECT question_id FROM attempt WHERE attempt_id = %s;', (random.randint(1, SCALE*20),))
cursor.fetchone()
if __name__ =="__main__":
for i in range (0, THREADS):
c = make_connection()
connections.append(c)
for i in range (0, THREADS):
t = threading.Thread(target=run_load, args=(i,))
threads.append(t)
print('starting query workload')
start = time.time()
for t in threads:
t.start()
for t in threads:
t.join()
elapsed = time.time() - start
print('completed in', round(elapsed,2), 'seconds')
print('QPS =', round((QUERIES*THREADS)/elapsed,2))
To run the script with 50 connection threads:
$ python3 load.py 1000 1000 50
And the results are the best!
starting query workload
completed in 15.33 seconds
QPS = 3262.22
Looking good! One more things I wanted to try. Some of the defaults that MySQL ships with (specifically MySQL 8+) are not well-tuned. I’m going to try another benchmark with a few configuration options added:
innodb-adaptive-hash-index=TRUE
skip-log-bin
innodb-buffer-pool-size=1G
I’m also gonna run the benchmark longer (2k queries per connection instead of 1k) to allow the AHI and buffer pool tweaks to hopefully have more effect.
$ python3 load.py 1000 2000 50
And the result:
starting query workload
completed in 29.01 seconds
QPS = 3446.84
It got a little better, and that will have to do for now.
Conclusion
Overall, this was a great experiment in stretching a Pi’s ability to run MySQL. When all said and done, I was a little underwhelmed with what I got out of it. What if I want to get 10k QPS on a Pi? 100k? I mean, very unrealistic for a single Pi, but would be fun to achieve. Maybe I should buy a bunch a run a Vitess cluster on Pis next.