7 ms·

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...
        HugThem 7y ago
        only the following is stored in ibdata1 You say "only", I say "clusterfuck". 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.
        amaccuish 7y ago
        Ok but I don't get why you're so obsessed with the fs layout anyway. You should mostly treat it as a black box. And the point of ibdata1 is safety, which as you stated higher up is a serious problem with MyISAM. Even if it's not oom situations, you'll end up stuck sooner or later. You have been warned.
        HugThem 7y ago
        You should mostly treat it as a black box Again, check the very link you posted. People do that. Until shit hits the fan. And then they have to take that black box apart. Which is not easy.
- smueller1234 7y ago
  Any remaining reasonable use cases would be sufficiently corner-casey that that the first order approximation is "if you want it to behave like a database, no, you do not want 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(?).

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...
      - HugThem 7y ago
        only the following is stored in ibdata1 You say "only", I say "clusterfuck". 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.
        amaccuish 7y ago
        Ok but I don't get why you're so obsessed with the fs layout anyway. You should mostly treat it as a black box. And the point of ibdata1 is safety, which as you stated higher up is a serious problem with MyISAM. Even if it's not oom situations, you'll end up stuck sooner or later. You have been warned.
        HugThem 7y ago
        You should mostly treat it as a black box Again, check the very link you posted. People do that. Until shit hits the fan. And then they have to take that black box apart. Which is not easy.
smueller1234 7y ago
Any remaining reasonable use cases would be sufficiently corner-casey that that the first order approximation is "if you want it to behave like a database, no, you do not want 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(?).