Arch Linux

Cator Canulis

Member

Registered: 2018-03-23

Posts: 13

Using a RAM-disk as storage for MariaDB / InnoDB

Hello everyone,

I've set up a ramdisk with this configuration:

# RAM-Disk
tmpfs /mnt/rd tmpfs rw,gid=977,uid=977,mode=0750,noatime,size=1024M 0 0

977 is the gid and uid of the user mysql.

The ramdisk is backed up to a directory on my harddrive. Once every 10 minutes but in any case when the system powers down or reboots.

On boot the ramdisk is filled with the harddrive data.

My idea is to use the ramdisk as a storage device for the mariadb database.

I've set the server up with this command:

sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/mnt/rd

/mnt/rd is readable and writeable by mysql user and group.

But the server start fails with this message:

Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [Note] InnoDB: Buffer pool(s) load completed at 191120 20:46:38
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [Note] Server socket created on IP: '::'.
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.db' doesn't exist
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [ERROR] Aborting
Nov 20 20:46:39 doc4 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Nov 20 20:46:39 doc4 systemd[1]: mariadb.service: Failed with result 'exit-code'.
Nov 20 20:46:39 doc4 systemd[1]: Failed to start MariaDB 10.4.10 database server.

When I set up the server with:

sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

everything works as expected. What am I doing wrong? Might the disk be too small?

Thanks in advance

Markus

ewaller

Administrator

From: Pasadena, CA

Registered: 2009-07-13

Posts: 19,772

Re: Using a RAM-disk as storage for MariaDB / InnoDB

Seems like a bad idea to me.  I'm pretty sure that violates ACID

When you back it up, how are you doing that?  Are you shutting down the database engine first?

---

Nothing is too wonderful to be true, if it be consistent with the laws of nature -- Michael Faraday
Sometimes it is the people no one can imagine anything of who do the things no one can imagine. -- Alan Turing
---
How to Ask Questions the Smart Way

Cator Canulis

Member

Registered: 2018-03-23

Posts: 13

Re: Using a RAM-disk as storage for MariaDB / InnoDB

Why would it violate acid?

Backup as follows:
rsync -avu --delete /mnt/rd /var/rd_backup

I would shut down the dabase first. But it isn't eben starting.

Slithery

Administrator

From: Norfolk, UK

Registered: 2013-12-01

Posts: 5,776

Re: Using a RAM-disk as storage for MariaDB / InnoDB

Have you edited the mariadb service file to use your new directory?

---

No, it didn't "fix" anything. It just shifted the brokeness one space to the right. - jasonwryan
Closing -- for deletion; Banning -- for muppetry. - jasonwryan

aur - dotfiles

sabroad

Member

Registered: 2015-05-24

Posts: 242

Re: Using a RAM-disk as storage for MariaDB / InnoDB

sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/mnt/rd
[...]But the server start fails with this message:
Nov 20 20:46:38 doc4 mysqld[527]: 2019-11-20 20:46:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

You've initialised the system tables to the datadir, now you need to configure the server (daemon) to use that location.

---

--
saint_abroad

ewaller

Administrator

From: Pasadena, CA

Registered: 2009-07-13

Posts: 19,772

Re: Using a RAM-disk as storage for MariaDB / InnoDB

Why would it violate acid?

Durability.  After a transaction occurs, there is up to a 10 minute gap in which that transaction can be lost should the system hiccup, or power fail.
I also worry about copying a live database; OTOH, down times every 10 minutes seems like a low availability.

---

Nothing is too wonderful to be true, if it be consistent with the laws of nature -- Michael Faraday
Sometimes it is the people no one can imagine anything of who do the things no one can imagine. -- Alan Turing
---
How to Ask Questions the Smart Way

sabroad

Member

Registered: 2015-05-24

Posts: 242

Re: Using a RAM-disk as storage for MariaDB / InnoDB

The ramdisk is backed up to a directory on my harddrive. Once every 10 minutes but in any case when the system powers down or reboots.

To echo warnings of durability, you have none in this setup as crash or power failure can occur mid-rsync.

Consider instead tuning on-disk MariaDB for reduced durability: at this point throughput is going to be CPU limited anyways.

---

--
saint_abroad