Hacker News new | ask | show | jobs
by arp242 931 days ago
I haven't really been able to find a way that works well on MySQL (well, MariaDB). Transactions are too unreliable and magical in MariaDB, Memory databases/tables have all sorts of caveats and limitations, and there isn't really anything like PostgreSQL's schemas or templates.

The best I could come up with was to run MariaDB server with libeatmydata, which I believe was pretty much intended for this. It's not very "works on any MariaDB server"-generic, but it's good enough.
1 comments
fierro 930 days ago
dang. Only thing I've found so far is a `mysqldump --no-data` and then a restore from dump. Not fast at all. Maybe you could pre-provision a few thousand DBs this way in parallel and write a service to hand out DB handles...
link
evanelias 930 days ago
Using tmpfs for MySQL/MariaDB's data directory helps tremendously. If you're using Docker natively on Linux, use `docker run --tmpfs /var/lib/mysql ...` and that'll do the trick. Only downside is each container restart is slightly slower due to having to re-init the database instance from scratch.

Tuning the database server settings can help a lot too. You can add overrides to the very end of your `docker run` command-line, so that they get sent as command-line args to the database server. For example, use --skip-performance-schema to avoid the overhead of performance_schema if you don't need it in your test/CI environment.

For MySQL 8 in particular, I've found a few additional options tend to help: --skip-log-bin --skip-innodb-adaptive-hash-index --skip-innodb-log-writer-threads

(Those don't help on MariaDB, since it already defaults to disabling the binary log and adaptive hash index; and it doesn't have separate log writer threads.)

A lot of other options may be workload-specific. My product Skeema [1] can optionally use ephemeral containerized databases [2] for testing DDL and linting database objects, so its workload is very DDL-heavy, which means the settings can be tuned pretty differently than a typical DML-based workload.

[1] https://github.com/skeema/skeema/

[2] https://www.skeema.io/docs/options/#workspace

link