0

I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control.

Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical sensory data (like room temperature). The real file system is an SD card, so frequent writes destroy it. Storing data in-memory would be a good option.

What is not a solution:

  • Memory storage engine can't handle TEXT columns
  • Aria storage engine can't handle foreign keys
  • MyRocks storage engine is not available for 32-bit platforms
  • SQLite ///:memory: can't handle the "load", throws uncountable 'cannot commit - no transaction is active' errors

What I've tried:

  • using tmpfs for datadir and tmpdir
  • tweaking InnoDB to minimal resource usage
  • disabling doublewrite buffering, disabling change buffering, using READ-UNCOMMITTED isolation

See configuration

But when I purge old data, the DELETE causes ibdata1 to grow nearly to the 100% size of the table I delete 10-15% of the rows from. My guess is that Rollbacks Segments and Undo Tablespace is still very active, but I can't "disable" MVCC altogether.

How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?

3

1 Answer 1

2

Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.

And:

The undo log is usually part of the physical system tablespace, but from MariaDB 10.0, the innodb_undo_directory and innodb_undo_tablespaces system variables can be used to split into different tablespaces and store in a different location (perhaps on a different storage device).

https://mariadb.com/kb/en/innodb-undo-log/

For MEMORY: What about VARCHAR(65532)?

1
  • I've checked: the connecting SQLAlchemy client deletes data in batches. Finally I gave up. Currently on each start a script "preloads" the DB with a modified schema (no FK), Aria and SQLAlchemy works. InnoDB disabled, exploding ibdata1 eliminated.
    – lmagyar
    Feb 10, 2021 at 5:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.