5 ms·

Not going to happen. I don't like how InnoDB lays out data on disk. MyIsam and Aria tables are so much nicer with the data structured logically in files which

by TekMol 3y ago

Not going to happen. I don't like how InnoDB lays out data on disk. MyIsam and Aria tables are so much nicer with the data structured logically in files which hold the data of individual tables and directories which hold the data of individual databases. I have innodb=OFF In the mariadb.conf on all my servers.

montroser 3y ago
Agree the disk format is much nicer with MyIsam, but don't you have a much greater chance of corruption in the case of a power failure? InnoDB has some annoying shortcomings, but in return it seems nice that you get your data consistency.
- TekMol 3y ago
  Depends on your definition of "much greater chance of corruption". I have yet to see a single instance of this happening. And I have been running a bunch of servers for decades, each serving complex web applications to hundreds of thousands of users.
djantje 3y ago
You could also do innodb_file_per_table=ON InnoDB supports database transactions, so you/an update/insert can use rollback, which MyIsam and Aria tables can not do (they work like auto commit)
- TekMol 3y ago
  they work like auto commit Which makes the code way simpler and performance way higher.
  - whatwhaaaaat 3y ago
    No it does not. If you tune your innodb for the hardware you have your inserts will be significantly faster. This whole thread reads like a few folks tried some setup without bothering to turn a single nerd knob and then complained about the results. Innodb > myisam in any case where you’re actually inserting.
    - TekMol 3y ago
      Not true. I have been sitting together with folks whose life is configuring MySql and they couldn't tune InnoDB to match the performance of MyIsam. This was for an application which did a long running numbercrunching job on a large database with a mix of inserts/updates/selects. Might be different for other types of workloads. But for this workload, InnoDB did not stand a chance even with a lot of tuning.
      - twodave 3y ago
        Tbh it sounds like a whalescript to me. Often it's not the database's fault the performance is bad ;)
      - sgarland 3y ago
        That depends on what you're doing. For initial bulk file loads using mysqlsh [0], MyISAM doesn't stand a chance because it takes a table lock, so the tool can't parallelize the import like it can with InnoDB. I wrote a small script to test this [1]. tl;dr 12,500,000 rows totaling 2.75 GB took 6 min 57.2374 sec at 6.59 MB/s for InnoDB, and 11 min 18.4550 sec at 4.05 MB/s for MyISAM. During the test, whereas the InnoDB load utilized multiple cores, the MyISAM load only saturated a single core. Now, normal INSERTs are likely another story. Working on that test now. [0]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html [1]: https://gist.github.com/stephanGarland/06ff4820f99e5966ba097aa8b10ec750 https://gist.github.com/stephanGarland/06ff4820f99e5966ba097...
        sgarland 3y ago
        Python script [0] to do bulk INSERTs. I TRUNCATEd the tables, chunked 10,000 INSERTs at a time, and recorded the results. This was split into two passes of 12,500,000 rows each, committing after each chunk of 10K rows. tl;dr On an empty table, InnoDB is 1.7% slower than MyISAM. On a full table (I raised bulk_insert_buffer_size to 128 MiB, which is a MyISAM-only optimization, and is more than enough to handle 10,000 rows of my data [21 MiB]), they are effectively identical, with InnoDB being 0.1% slower than MyISAM. It's worth noting that this is with InnoDB's defaults for doublewrite enabled. If (and only if) you're using ZFS, you can disable that, and get an enormous speed boost. These data dirs are on XFS, so I didn't disable the doublewrite buffer. Finally, I tried some simple `SELECT COUNT(*) WHERE user_id <= ...` - `user_id` is the PK and a UUIDv7, inserted in order so as to avoid page splits for InnoDB. Not as familiar with MyISAM's page layout, but I assume it at least isn't _hurt_ by ordering on insertion. The value selected was near the end (~1000 away), so it had to do a huge covering index scan. For InnoDB, I saw [22.01, 22.39, 22.69] seconds. For MyISAM, I saw [27.12, 27.53, 26.81] seconds. If you're able to give me some specific workloads, or settings to tweak for MyISAM, I'm very willing to redo the test, but as of now I'm unconvinced that MyISAM has an edge. [0]: https://gist.github.com/stephanGarland/cc505a9d9c0d044a340fa19b6d778c07 https://gist.github.com/stephanGarland/cc505a9d9c0d044a340fa...
dylan604 3y ago
How often does one work with the files of a database to care?
- tuwtuwtuwtuw 3y ago
  And at a managed service at that?
waynesonfire 3y ago
Are you trolling? Both MyIsam and Aria are not ACID compliant. May as well use /dev/null.
- _cenw 3y ago
  This. Please do not do this on a production DB for reasons like "I like the file layout more" without knowing what you're losing. Write performance on MyISAM is often worse than using SQLite, for instance.
- phil21 3y ago
  > Are you trolling? Both MyIsam and Aria are not ACID compliant. May as well use /dev/null. This is silly. I would say most databases in production in the world have zero need for ACID compliance. I know plenty of companies making millions of dollars of revenue per month off their MyISAM table database products. Hard to do that with /dev/null I assume. It works fine depending on what you are doing. Granted, there are typically better options these days, but this goes back 15+ years of silliness. Many (most?) web apps are write once, read millions. For high volume/low value data that doesn't need perfect accuracy or consistency you can make MySQL/MariaDB absolutely fly. That said, I hear PSQL can get close these days turning some knobs as well. But, the ACID argument is tired and usually made in places where it simply is irrelevant. Most apps simply don't matter if they drop some data here and there, but developers act like they are building critical banking systems for their ad viewing platform. This is probably a top 10 favorite bike shedding topic I've seen in dev meetings over my career. In 90% or better cases you could have chosen literally any RDBMS and been fine, the arguments about it taking more time than the choice made will ever save.
- jen20 3y ago
  Is /dev/null web scale though? And does it support sharding?
  - SushiHippie 3y ago
    For thos who don't know this masterpiece: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs

by TekMol 3y ago

montroser 3y ago

Agree the disk format is much nicer with MyIsam, but don't you have a much greater chance of corruption in the case of a power failure? InnoDB has some annoying shortcomings, but in return it seems nice that you get your data consistency.

TekMol 3y ago
Depends on your definition of "much greater chance of corruption". I have yet to see a single instance of this happening. And I have been running a bunch of servers for decades, each serving complex web applications to hundreds of thousands of users.

djantje 3y ago

You could also do innodb_file_per_table=ON InnoDB supports database transactions, so you/an update/insert can use rollback, which MyIsam and Aria tables can not do (they work like auto commit)

TekMol 3y ago
they work like auto commit Which makes the code way simpler and performance way higher.
- whatwhaaaaat 3y ago
  No it does not. If you tune your innodb for the hardware you have your inserts will be significantly faster. This whole thread reads like a few folks tried some setup without bothering to turn a single nerd knob and then complained about the results. Innodb > myisam in any case where you’re actually inserting.
  - TekMol 3y ago
    Not true. I have been sitting together with folks whose life is configuring MySql and they couldn't tune InnoDB to match the performance of MyIsam. This was for an application which did a long running numbercrunching job on a large database with a mix of inserts/updates/selects. Might be different for other types of workloads. But for this workload, InnoDB did not stand a chance even with a lot of tuning.
    - twodave 3y ago
      Tbh it sounds like a whalescript to me. Often it's not the database's fault the performance is bad ;)
    - sgarland 3y ago
      That depends on what you're doing. For initial bulk file loads using mysqlsh [0], MyISAM doesn't stand a chance because it takes a table lock, so the tool can't parallelize the import like it can with InnoDB. I wrote a small script to test this [1]. tl;dr 12,500,000 rows totaling 2.75 GB took 6 min 57.2374 sec at 6.59 MB/s for InnoDB, and 11 min 18.4550 sec at 4.05 MB/s for MyISAM. During the test, whereas the InnoDB load utilized multiple cores, the MyISAM load only saturated a single core. Now, normal INSERTs are likely another story. Working on that test now. [0]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html [1]: https://gist.github.com/stephanGarland/06ff4820f99e5966ba097aa8b10ec750 https://gist.github.com/stephanGarland/06ff4820f99e5966ba097...
      - sgarland 3y ago
        Python script [0] to do bulk INSERTs. I TRUNCATEd the tables, chunked 10,000 INSERTs at a time, and recorded the results. This was split into two passes of 12,500,000 rows each, committing after each chunk of 10K rows. tl;dr On an empty table, InnoDB is 1.7% slower than MyISAM. On a full table (I raised bulk_insert_buffer_size to 128 MiB, which is a MyISAM-only optimization, and is more than enough to handle 10,000 rows of my data [21 MiB]), they are effectively identical, with InnoDB being 0.1% slower than MyISAM. It's worth noting that this is with InnoDB's defaults for doublewrite enabled. If (and only if) you're using ZFS, you can disable that, and get an enormous speed boost. These data dirs are on XFS, so I didn't disable the doublewrite buffer. Finally, I tried some simple `SELECT COUNT(*) WHERE user_id <= ...` - `user_id` is the PK and a UUIDv7, inserted in order so as to avoid page splits for InnoDB. Not as familiar with MyISAM's page layout, but I assume it at least isn't _hurt_ by ordering on insertion. The value selected was near the end (~1000 away), so it had to do a huge covering index scan. For InnoDB, I saw [22.01, 22.39, 22.69] seconds. For MyISAM, I saw [27.12, 27.53, 26.81] seconds. If you're able to give me some specific workloads, or settings to tweak for MyISAM, I'm very willing to redo the test, but as of now I'm unconvinced that MyISAM has an edge. [0]: https://gist.github.com/stephanGarland/cc505a9d9c0d044a340fa19b6d778c07 https://gist.github.com/stephanGarland/cc505a9d9c0d044a340fa...

dylan604 3y ago

How often does one work with the files of a database to care?

tuwtuwtuwtuw 3y ago
And at a managed service at that?

waynesonfire 3y ago

Are you trolling? Both MyIsam and Aria are not ACID compliant. May as well use /dev/null.

_cenw 3y ago
This. Please do not do this on a production DB for reasons like "I like the file layout more" without knowing what you're losing. Write performance on MyISAM is often worse than using SQLite, for instance.
phil21 3y ago
> Are you trolling? Both MyIsam and Aria are not ACID compliant. May as well use /dev/null. This is silly. I would say most databases in production in the world have zero need for ACID compliance. I know plenty of companies making millions of dollars of revenue per month off their MyISAM table database products. Hard to do that with /dev/null I assume. It works fine depending on what you are doing. Granted, there are typically better options these days, but this goes back 15+ years of silliness. Many (most?) web apps are write once, read millions. For high volume/low value data that doesn't need perfect accuracy or consistency you can make MySQL/MariaDB absolutely fly. That said, I hear PSQL can get close these days turning some knobs as well. But, the ACID argument is tired and usually made in places where it simply is irrelevant. Most apps simply don't matter if they drop some data here and there, but developers act like they are building critical banking systems for their ad viewing platform. This is probably a top 10 favorite bike shedding topic I've seen in dev meetings over my career. In 90% or better cases you could have chosen literally any RDBMS and been fine, the arguments about it taking more time than the choice made will ever save.
jen20 3y ago
Is /dev/null web scale though? And does it support sharding?
- SushiHippie 3y ago
  For thos who don't know this masterpiece: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs