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.
-
read this documentation for speed up query execution linkKishan Oza– Kishan Oza2017-02-22 11:58:49 +00:00Commented Feb 22, 2017 at 11:58
-
Do you mean having so much RAM that the server can just keep everything there and only needs to use disk to dump changes? Or having a temporary lightning fast database that can be removed on shut-down?Álvaro González– Álvaro González2017-02-22 13:04:08 +00:00Commented Feb 22, 2017 at 13:04
Add a comment
|
2 Answers
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
Comments
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).
