3 ms·

I witnessed MySQL bringing linux servers down two. In my case it happens like this: I have a long running PHP process that constantly fires away mostly SELECT

I witnessed MySQL bringing linux servers down two. 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.

z3t4 7y ago
Try doing some rate limiting in order to not cause the dead lock. Should probably also disable write cache. And if it still doesn't work switch to a bare metal machine. And give it a lot of swap and up the swappiness. Swapping is a much better alternative then crashing. VPS providers doesn't like swap because it will tear their SSD disks, so the swap and swappiness is probably preset too low.
- HugThem 7y ago
  I tried sleeping 0.1s every 5s or so. It did not help. Still crashed. 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.
jmiserez 7y ago
>Afterwards the table data is corrupted beyond repair. That should not happen with a DB even if you turn off the power. Are you sure the hardware is good?
- smueller1234 7y ago
  GP ist using the MyISAM storage engine. It's not crash safe. This is sad but expected behavior. Don't use MyISAM!
  - ygra 7y ago
    Is there any reason to ever use it (or: Why does it still exist)? In-memory databases for caches or other things that are not critical? I have to admit, I was astounded when I first got the error message from MySQL that a table was corrupted and that I should run REPAIR TABLE. That sounded like very weird behavior for a database.
    - HugThem 7y ago
      Is there any reason to ever use it It is faster, uses less disk space and has a more logical filesystem layout.
      - amaccuish 7y ago
        What do you mean by more logical? innodb_file_per_table has existed for a while now.
        HugThem 7y ago
        A flag with that name exists, yes. But it does not seperate table data into one file per table. It will still put stuff related to the tables into the central ibdata1 file. Google "ibdata1 one file per table" to see all the pain it causes.
        amaccuish 7y ago
        False. > 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-file-continuously-growing-in-mysql/ https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-f...
- PeCaN 7y ago
  welcome to MySQL
kokey 7y ago
The most common cause here is something causing a situation where some queries hang or takes a long time to complete, while also locking access to something, while new queries keep coming in. This builds up quickly. 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.
- HugThem 7y ago
  I fire the queries sequentially. So there is never more then one query running.
  - kilburn 7y ago
    The "once per day" part is suspicious. Maybe a scheduled backup is what's running the other query ;)
    - HugThem 7y ago
      I hade the same suspicion. Especially since the provider indeed runs a nightly backup of the VMs. But even after turning that off, the VM stalled the next night again.

z3t4 7y ago

Try doing some rate limiting in order to not cause the dead lock. Should probably also disable write cache. And if it still doesn't work switch to a bare metal machine. And give it a lot of swap and up the swappiness. Swapping is a much better alternative then crashing. VPS providers doesn't like swap because it will tear their SSD disks, so the swap and swappiness is probably preset too low.

HugThem 7y ago
I tried sleeping 0.1s every 5s or so. It did not help. Still crashed. 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.

jmiserez 7y ago

>Afterwards the table data is corrupted beyond repair. That should not happen with a DB even if you turn off the power. Are you sure the hardware is good?

smueller1234 7y ago
GP ist using the MyISAM storage engine. It's not crash safe. This is sad but expected behavior. Don't use MyISAM!
- ygra 7y ago
  Is there any reason to ever use it (or: Why does it still exist)? In-memory databases for caches or other things that are not critical? I have to admit, I was astounded when I first got the error message from MySQL that a table was corrupted and that I should run REPAIR TABLE. That sounded like very weird behavior for a database.
  - HugThem 7y ago
    Is there any reason to ever use it It is faster, uses less disk space and has a more logical filesystem layout.
    - amaccuish 7y ago
      What do you mean by more logical? innodb_file_per_table has existed for a while now.
      - HugThem 7y ago
        A flag with that name exists, yes. But it does not seperate table data into one file per table. It will still put stuff related to the tables into the central ibdata1 file. Google "ibdata1 one file per table" to see all the pain it causes.
        amaccuish 7y ago
        False. > 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-file-continuously-growing-in-mysql/ https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-f...
PeCaN 7y ago
welcome to MySQL

kokey 7y ago

The most common cause here is something causing a situation where some queries hang or takes a long time to complete, while also locking access to something, while new queries keep coming in. This builds up quickly. 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.

HugThem 7y ago
I fire the queries sequentially. So there is never more then one query running.
- kilburn 7y ago
  The "once per day" part is suspicious. Maybe a scheduled backup is what's running the other query ;)
  - HugThem 7y ago
    I hade the same suspicion. Especially since the provider indeed runs a nightly backup of the VMs. But even after turning that off, the VM stalled the next night again.