A good way to catch this would be to have something log the list of running queries every couple of seconds. Look at this log after the crash and you'll hopefully be able to identify which are the long running processes, and which are the regular queries that builds up.
To fix it would be a combination of making the queries that cause the locking to be less like that, also perhaps putting in a limit on how many queries can build up and also implement a way for the regular queries that build up to time out or fail quicker or more gracefully.
I don't think swapping would even occur since neither mysql nor php grow their memory usage over time.
It's not an SSD. It's good old rusty HD.
That should not happen with a DB even if you turn off the power. Are you sure the hardware is good?
Don't use MyISAM!
This being said, at least some years ago, a use case I saw that held SOME water then was generating MyISAM tables offline, importing them as-is into a running MySQL (or taking an instance offline and bringing it back up) and then serving from it read-only. At least at the time, this provided better RO performance than InnoDB. I wouldn't be surprised if that was still true. Please don't do that at home!
Also, I think until the previous-to-most-recent release, some internal tables were still MyISAM, causing MySQL overall to have some very rare cases of not being crash safe. Again, I think that's since been resolved in 5.8(?).
Is there any reason to ever use it
Google "ibdata1 one file per table" to see all the pain it causes.
> But it does not seperate table data into one file per table
That's because if you didn't have it set when creating the database, it won't move data to the new fs layout when you set the setting on, without an OPTIMIZE. If you had it on from the beginning, table data is per file. I literally just did an ls on my /va/lib/mysql and there's a folder per database, in which there are 2 files per table (.frm and .ibd).
When innodb_file_per_table is on, and the database has been OPTIMIZEd, only the following is stored in ibdata1 [0]:
- data dictionary aka metadata of InnoDB tables
- change buffer
- doublewrite buffer
- undo logs
[0] https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-f...
only the following is stored in ibdata1
Just look at the very page you linked to. It's a totally confusing concept that befuddles users and causes questions "we often receive", starts "panic", can "unfortunately" not easily be analyzed and you might need to "kill threads" and initiate "rollbacks" to fix the problems it brings.
MyISAM got that right. One dir per database.
You should mostly treat it as a black box
In my case it happens like this:
I have a long running PHP process that constantly fires away mostly SELECT but also a bunch of INSERT and UPDATE statements and also some DELETEs.
Since the DB and the key files do not fit into memory, its all disk bound work.
All tables are MyISAM.
Like clockwork, this stalls the virtual machine once per day.
All I can do is to hard power down the VM and restart it. Afterwards the table data is corrupted beyond repair.
Not sure it is related to memory though. Because the memory usage of PHP and MySQL seem to be constant. Most RAM seems to be used by Linux for caches.