0

We are running a financial application using MySQL as database.

For performance reasons we are using memory tables for a few of our live tables where the data is populated ever few milli seconds.

To ensure data consistency in all scenarios (planned and unplanned outages) we need to ensure that the data in the memory tables are always persisted for that we are planning to implement shutdown hooks, wherein n case of any shutdown the data would be written in normal tables and restored during start-up.

I am unfortunately not able to find a way to do it.

Any suggestions would be appreciated.

We are using MySQL ver 5.7

Improve this question
4
  • Do you have a workload related benchmark that shows the memory tables actually showing a benefit compared to innodb? because the locking granuality is at the table level. Can you show the table structure and normal set of operations on the table along with what design objectives exist?
    – danblack
    Commented Apr 1, 2020 at 21:00
  • Unfortunately would not be able to share the structure. But I can say that the table has around 30 columns and around 5000 records. The tps is around 500 (50 updates per 100ms). This is a master table which is used in joins etc with other tables. Hope this gives more data points to help us
    – Vivek
    Commented Apr 3, 2020 at 9:34
  • Show the structure, with indexes. Replace the column names and in the same way in the query. If you try the same operations on innodb what throughput do you get? Otherwise I suggest getting a consultant under an NDA to entertain giving advice on less abstract facts.
    – danblack
    Commented Apr 3, 2020 at 11:14
  • From root login, from your MySQL Command Prompt, what is RESULT of SHOW GLOBAL VARIABLES LIKE 'innodb_fast_shutdown'; ?
    – Wilson Hauck
    Commented Dec 9, 2020 at 19:07

1 Answer 1

Reset to default
0

Don't even try.

InnoDB has years of optimization behind it. It persists to disk even across unplanned power failures due to its integrated logs.

Furthermore, because of MEMORY using table locks (as opposed to InnoDB's row locks), there are benchmarks that say that InnoDB is faster than MEMORY. There could be cases where Memory is faster than InnoDB. But between your moderately high write rate and the JOINs, InnoDB is very likely to have less interference between the two tasks.

If you want full persistence, you need to write to the disk 500 times per second. Are you using SSDs? RAID? An ordinary HDD cannot run at 500 flushes per second. If you have a fast enough drive, you may as well let InnoDB take care of everything for you.

InnoDB's automatic saving and restoring across power failures is very likely to be faster, and much less programming effort for you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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