CODEWITHSUNDEEP
mysqldatabasememory
Dragomirus
Dragomirus

Reputation: 449

MySQL database running in memory for speed up the execution of Queries

Is it possible to store whole mysql database in memory. Should I create special query to do that? What kind of query I've to run? I want to speed up inserts, updates and deletes operation from specific table and for now that's the only possible way, I think.

Upvotes: 0

Views: 2559

Answers (2)

symcbean
symcbean

Reputation: 48357

This should be a comment - but its a bit long.

What is it that you are trying to achieve by this?

You will have 2 problems as a result: the data will need to be repopulated after a power cycle (or mysql restart) and will be limited by tmp_table_size and max_heap_table_size.

If you merely want to speed up the raw data access, then Innodb will cache as much of the data in memory as you allow it to. MyISAM will cache indexes in memory (and on Linux systems and similar, table data is cached - but not indefinitely). But the first place you should be looking if you are having performance issues is at the queries being run against the database.

If you deliberately want the data to be be unavailable in backups or after a reboot, then keep the data files on an encrypted filesystem (with the key entered at mount time).

Upvotes: 0

Balazs Vago
Balazs Vago

Reputation: 651

You can create a table that utilizes the MEMORY engine. At service start-up, you populate the table from another that is present on the disk, and synchronize it back to the table that is present on the disk at some intervals. INSERT INTO ... SELECT * FROM ...

Note: Be aware that when the server or the mysql service stops, all data stored in the table is lost!

More info on the MEMORY engine can be found at https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

Upvotes: 1

Related Questions