CODEWITHSUNDEEP
mysqlmariadb
Victor Otávio
Victor Otávio

Reputation: 455

Does MySQL or MariaDB have in-memory database?

I'm doing unit/integration tests. SQLite doesn't support RIGHT JOIN and FULL OUTER JOIN. Is there any way to work with MySQL (or MariaDB) completely stored in memory? MySQL has MEMORY table engine, however this may generate inconsistency in my tests.

I need some alternative to :memory: from SQLite but with the same features as MySQL. My problem is performance. SQLite database in-memory speeds up my testing process, however some queries aren't compatible with SQLite. I also do not find it good practice to do the tests in SQLite if the production database is MariaDB.

Upvotes: 12

Views: 14494

Answers (3)

Chl
Chl

Reputation: 421

Here is a hack with a systemd unit, which will copy the MariaDB/MySQL storage on a tmpfs.
It is written for a standard Ubuntu/Debian installation but should be adaptable for other environments. Add sudo where needed.

Risk of data loss: study thoroughly to see if it fits your use case.
(and also check the database size.)

# This systemd unit puts the MariaDB/MySQL storage on tmpfs and (tries to) sync
# it back at shutdown.

# Of course, in case of power failure or any problem preventing this
# unit to stop properly and sync back the data to disk : DATA LOSS.

# To install on Debian/Ubuntu :
# - apt install rsync
# - systemctl stop mariadb
# - write this content to /etc/systemd/system/mariadbontmpfs.service
# - systemctl enable mariadbontmpfs.service
# - systemctl start mariadbontmpfs.service
# - systemctl start mariadb

# This file is free software licensed under the WTFPL (v2)

[Unit]
Description=Put /var/lib/mysql on tmpfs before MariaDB/MySQL starts
Before=mariadb.service mysql.service
ConditionPathIsDirectory=/var/lib/mysql
ConditionPathExists=/usr/bin/rsync

[Install]
WantedBy=multi-user.target

[Service]
Type=oneshot
RemainAfterExit=yes
Restart=no

# We double-check that MariaDB is not running before doing anything
ExecCondition=/bin/sh -c "systemctl --quiet is-active mariadb mysql && echo 'MariaDB or MySQL still active' && exit 255 || exit 0"

# If the ON-DISK directory doesn't exist, simply rename /var/lib/mysql to
# /var/lib/mysql.ON-DISK and recreate /var/lib/mysql
ExecStart=/bin/sh -c "if [ ! -e /var/lib/mysql.ON-DISK ] ; then mv -v /var/lib/mysql /var/lib/mysql.ON-DISK && mkdir -v /var/lib/mysql && chown -v mysql:mysql /var/lib/mysql; fi"
# Mount the tmpfs
ExecStart=/bin/mount -v -t tmpfs -o uid=mysql,gid=mysql,mode=0755 none /var/lib/mysql
# Copy the ON-DISK content to the tmpfs
ExecStart=/usr/bin/rsync -avH --delete /var/lib/mysql.ON-DISK/ /var/lib/mysql/

# At stop :
ExecStop=/bin/sh -c "echo 'Syncing tmpfs:/var/lib/mysql to disk'"
ExecStop=/usr/bin/rsync -avH --delete /var/lib/mysql/ /var/lib/mysql.ON-DISK/
ExecStop=/bin/umount -v /var/lib/mysql
ExecStop=/bin/sh -c "rmdir -v /var/lib/mysql && mv -v /var/lib/mysql.ON-DISK /var/lib/mysql"

Full file available here.

Upvotes: 0

Victor Otávio
Victor Otávio

Reputation: 455

After a few years of research and testing of different approaches to this question. The best way I found to deal with the problem was using Docker. More precisely using tmpfs volumes. This way I guarantee that the databases will not persist on the disk and will not suffer from I/O blocking.

For those interested, Docker has its own documentation on this type of volume: https://docs.docker.com/storage/tmpfs/

Upvotes: 2

bignose
bignose

Reputation: 32347

MariaDB has the MEMORY storage engine:

It is best-used for read-only caches of data from other tables, or for temporary work areas.

That sounds exactly right for quick setup and teardown of a database during automated testing.

Upvotes: 3

Related Questions