tag:blogger.com,1999:blog-9188714267863327820.post5049540223277521744..comments2024-03-20T03:06:52.713-04:00Comments on J-F Gagné's MySQL Blog: Why we still need MyISAM (for read-only tables)Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-9188714267863327820.post-53259100982132612022017-10-20T09:35:26.126-04:002017-10-20T09:35:26.126-04:00Checksums should have been added right before they...Checksums should have been added right before they made it crash safe for single-writer, multiple reader. Alas, it wasn't improved and here we are.<br /><br />And clustered indexes make you pay an extra index lookup when the secondary index access isn't covering, so you search the PK to get the missing rows. <br />Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-11945029642824690032017-08-16T19:21:45.638-04:002017-08-16T19:21:45.638-04:00aria...aria...MShttps://www.blogger.com/profile/05261386457236669744noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-4725354032504288252017-08-16T00:59:52.549-04:002017-08-16T00:59:52.549-04:00We saw similar results at my last job with zlib li...We saw similar results at my last job with zlib lib levels with column compression: https://medium.com/@Pinterest_Engineering/evolving-mysql-compression-part-2-2c3eb0101205Robhttps://www.blogger.com/profile/13656862909374634867noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-22912820064528857012017-08-15T16:07:18.901-04:002017-08-15T16:07:18.901-04:00I also once had a system storing thousands of writ...I also once had a system storing thousands of write-once read-only tables. Each table contained 24hrs of log data and most of the time was unlikely to be read more than 3 times before it was purged, when it was read it would typically be to aggregate all data in the table, so a SELECT operation needing to perform a complete table scan was not a problem. I mention it because the compression Anonymoushttps://www.blogger.com/profile/11231963268741754635noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-70255468394042640082017-08-14T13:45:29.343-04:002017-08-14T13:45:29.343-04:00I do not dispute that MyISAM has smaller footprint...I do not dispute that MyISAM has smaller footprint than InnoDB. However, I have various comments on the topic and the comments.<br /><br />* From numerous measurements, I have derived this Rule of Thumb: "The disk footprint for InnoDB is 2x-3x that of MyISAM." (There are, of course, exceptions.)<br /><br />* Clustering of PK can be simulated by adding suitable composite indexes to a Anonymoushttps://www.blogger.com/profile/14417893829463978050noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-57673474713947222582017-08-11T10:56:03.042-04:002017-08-11T10:56:03.042-04:00I like MyRocks, but I wish that MyISAM development...I like MyRocks, but I wish that MyISAM development continued. A few more (small) features would make a big difference. The MySQL world needs more than one storage engine.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-86870839007192484782017-08-11T04:33:57.697-04:002017-08-11T04:33:57.697-04:00Very good analysis, JF!
Compressed MyISAM is going...Very good analysis, JF!<br />Compressed MyISAM is going to be the only cheap alternative for read-only tables coming MySQL 8.0 (until it is deprecated and then dismissed, that is.)<br /><br />The other viable case for slave-side storage was archive + partitioning, which compresses data up to 90% and allows statistical queries (i.e. aggregate queries based on the partitioning fields) to be as fastGiuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-84671149529844853152017-08-11T01:57:16.171-04:002017-08-11T01:57:16.171-04:00I do not know how well TokuDB and MyRocks will com...I do not know how well TokuDB and MyRocks will compress this data, we still have to test. But TokuDB and MyRocks do not come out of the box on Oracle MySQL. I think TokuDB is included by default in Percona Server and in MariaDB, but I do not know how well it is supported. MyRocks is (or will soon be) in MariaDB and Percona Server, but I do know how it will work with 200K+ tables (Jean-François Gagnéhttps://www.blogger.com/profile/12267071794432977020noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-58608603643860557882017-08-10T22:58:11.536-04:002017-08-10T22:58:11.536-04:00If taking TokuDB and Myrocks into consider, is the...If taking TokuDB and Myrocks into consider, is the data size of MyISAM still the smallest one?Anonymoushttps://www.blogger.com/profile/10307833176432787333noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-45866589875604842402017-08-10T11:05:49.306-04:002017-08-10T11:05:49.306-04:00- data corruption can occur at many levels. Verify...- data corruption can occur at many levels. Verifying them at the storage or filesystem level indeed detects corruption in some cases, but not the others. For instance, network-level corruption while copying and restoring a backup over the network would not be detected at the storage or filesystem levels.<br /><br />- yes, I was referring to hot backups. XtraBackup, for example, copies all MyISAMAlexey Kopytovhttps://www.blogger.com/profile/11158407150636406809noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-84598850737764484722017-08-10T08:18:56.751-04:002017-08-10T08:18:56.751-04:00Thanks for raising those points Alexey. Some comm...Thanks for raising those points Alexey. Some comments:<br /><br />-Regarding the lack of checksums, you are right that InnoDB's page checksum allows to avoid corruption in the storage by failing a read if the disks are lying to us. This could easily be worked around by having a more reliable storage stack that provides checksums, ZFS as an example.<br /><br />-Regarding the lack of a Jean-François Gagnéhttps://www.blogger.com/profile/12267071794432977020noreply@blogger.comtag:blogger.com,1999:blog-9188714267863327820.post-5352930708409960942017-08-10T06:49:11.254-04:002017-08-10T06:49:11.254-04:00Thanks, I've been looking for a use case for M...Thanks, I've been looking for a use case for MyISAM for a long time, and this one looks convincing.<br /><br />Still, as a caveat for readers, MyISAM has some drawbacks that may be important even in this specific use case (compressed read-only data):<br /><br />- lack of checksums (=> silent data corruptions!)<br />- partitioning is deprecated in 5.7, will be impossible in 8.0+<br />- Alexey Kopytovhttps://www.blogger.com/profile/11158407150636406809noreply@blogger.com