How to Run MySQL or MariaDB in a Cloud Sandbox

sbx new --name mysql --type default --no-env
id=sb_7hicnttcab    # the id that sbx new printed

sbx exec $id --timeout 300 "docker run -d --name mysql --restart unless-stopped \
  -e MYSQL_ROOT_PASSWORD=pw -p 127.0.0.1:3306:3306 -v mysqldata:/var/lib/mysql mysql:8.4"
sbx exec $id "until docker exec mysql mysql -uroot -ppw -h127.0.0.1 -e 'select 1' >/dev/null 2>&1; do sleep 0.5; done
docker exec mysql mysql -uroot -ppw -e 'select version()' 2>/dev/null"

version()
8.4.13

[Note] [Entrypoint]: Temporary server started.
[Note] [Entrypoint]: Temporary server stopped
[Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.13'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306

sbx exec $id --timeout 300 "curl -fsSLO https://downloads.mysql.com/docs/sakila-db.tar.gz && tar xzf sakila-db.tar.gz
docker exec -i mysql mysql -uroot -ppw < sakila-db/sakila-schema.sql
docker exec -i mysql mysql -uroot -ppw < sakila-db/sakila-data.sql"

+---------+----------+-------+
| rentals | payments | films |
+---------+----------+-------+
|   16044 |    16044 |  1000 |
+---------+----------+-------+

SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM sakila.payment p
JOIN sakila.rental r ON r.rental_id = p.rental_id
JOIN sakila.inventory i ON i.inventory_id = r.inventory_id
JOIN sakila.film_category fc ON fc.film_id = i.film_id
JOIN sakila.category c ON c.category_id = fc.category_id
GROUP BY c.name ORDER BY revenue DESC LIMIT 5;

sbx scp ./top.sql $id:/home/user/
sbx exec $id "docker exec -i mysql mysql -uroot -ppw -t < top.sql"

+-----------+---------+---------+
| category  | rentals | revenue |
+-----------+---------+---------+
| Sports    |    1179 | 5314.21 |
| Sci-Fi    |    1101 | 4756.98 |
| Animation |    1166 | 4656.30 |
| Drama     |    1060 | 4587.39 |
| Comedy    |     941 | 4383.58 |
+-----------+---------+---------+

sbx exec $id --timeout 300 "sudo apt-get update -qq && sudo DEBIAN_FRONTEND=noninteractive apt-get install -y -qq mysql-client && mysqlslap --version"

mysqlslap  Ver 8.0.46-0ubuntu0.24.04.4 for Linux on x86_64 ((Ubuntu))

export MYSQL_PWD=pw
mysqlslap -h 127.0.0.1 -uroot --create-schema=sakila \
  --query="SELECT c.name, SUM(p.amount) FROM payment p JOIN rental r USING (rental_id) JOIN inventory i USING (inventory_id) JOIN film_category fc USING (film_id) JOIN category c USING (category_id) GROUP BY c.name" \
  --concurrency=1,4,16 --iterations=3 --number-of-queries=160
mysqlslap -h 127.0.0.1 -uroot --auto-generate-sql --auto-generate-sql-load-type=mixed \
  --concurrency=1,4,16 --iterations=3 --number-of-queries=2000

sbx scp ./slap.sh $id:/home/user/
sbx exec $id --timeout 300 "bash slap.sh"

Benchmark
	Average number of seconds to run all queries: 5.057 seconds
	Minimum number of seconds to run all queries: 4.191 seconds
	Maximum number of seconds to run all queries: 5.496 seconds
	Number of clients running queries: 1
	Average number of queries per client: 160
…
Benchmark
	Average number of seconds to run all queries: 1.587 seconds
	…
	Number of clients running queries: 16
	Average number of queries per client: 10
…
Benchmark
	Average number of seconds to run all queries: 0.633 seconds
	…
	Number of clients running queries: 16
	Average number of queries per client: 125

sbx ssh-config > sbx_ssh_config
cat sbx_ssh_config

# Sandboxer (sbx ssh-config)
Host *.sbx
  User user
  IdentityFile ~/.ssh/sandboxer_ed25519
  IdentitiesOnly yes
  ProxyCommand sbx proxy %h
  UserKnownHostsFile ~/.config/sandboxer/known_hosts
  StrictHostKeyChecking yes
  ServerAliveInterval 30

sbx ssh $id true
ssh -F ./sbx_ssh_config -f -N -o ExitOnForwardFailure=yes -L 13306:127.0.0.1:3306 $id.sbx

import statistics, time
import pymysql

conn = pymysql.connect(host="127.0.0.1", port=13306, user="root", password="pw", database="sakila")
with conn.cursor() as cur:
    cur.execute("SELECT VERSION()")
    print(cur.fetchone()[0])
    times = []
    for _ in range(20):
        t0 = time.perf_counter()
        cur.execute("SELECT title, rental_rate FROM film WHERE title LIKE %s ORDER BY title LIMIT 3", ("AC%",))
        rows = cur.fetchall()
        times.append((time.perf_counter() - t0) * 1000)
    print(rows)
    print(f"median {statistics.median(times):.1f} ms, min {min(times):.1f} ms over {len(times)} queries")

8.4.13
(('ACADEMY DINOSAUR', Decimal('0.99')), ('ACE GOLDFINGER', Decimal('4.99')))
median 154.5 ms, min 154.0 ms over 20 queries