1

I am using mariadb 10.4 with nignx with joomla 4 on Debian 8 system.

Is it possible for a table that is already created with records in a working database to be moved on memory?

I find only tutorial to create the table from scratch not for changing it after it is created.

Is it possible to have some records on the disk and the rest on the memory?

  • 1
    Data is cached in memory anyway, are you sure you need to make a memory table of it? – Lennart Jul 13 at 14:08
1

Yes:

ALTER TABLE the_table ENGINE=MEMORY;

That said, I must strongly advice against doing this because the contents of memory tables are lost once the MariaDB server is shut down.

InnoDB is perfect for having some rows in memory and some on disk: It will put as much into memory as it can, and if there isn't enough memory for all of them, then it will only keep those that are accessed the most frequently. Also, it will persist the data to disk so that it's not lost when the MariaDB server is shut down.

  • Thanks for the tip about lost in data on shut down. Is there a variable that controls how much memory and you know the big table will use? – Christoforos Jul 13 at 13:48
  • @Christoforos - Simply set innodb_buffer_pool_size to 70% of available memory. InnoDB will do the rest, and do it efficiently. – Rick James Jul 13 at 16:09
  • Creating a copy is much safer, than doing an ALTER TABLE. If this involves cannot be done because of table names, than you could change the table name in the DB, and name the memory table as needed by your application. (This would be a nice test to see if @dbdemon is right with his statement (which he is as far as I can think)) – Luuk Jul 13 at 19:08
  • @Christoforos You have to keep in mind that mariadb/mysql stores a lot of things in the RAM - MYISAM indexes, InnoDB buffers, temptables (non-disk) and session related buffers for each active connection. mysqltuner script (apt install mysqltuner) can count the max possible RAM consumption as well as propose some reasonable advices about your configuration. Nowadays when 32-64GB of RAM becomes usual the max possible memory consumption can be (TOTAL-2GB) if it's a dedicated debian/centos server for DB. – Kondybas Jul 13 at 20:23
0

One of the ways to create a table is:

CREATE TABLE tempTable ENGINE=MEMORY SELECT * FROM myTable

This will create a new table named tempTable which has the same definition as myTable (see: https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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