Rocksdb as a replacement for toku?
Hi Guys, i'm looking for tokudb replacement and i'm again thinking about rocksdb. Is it mature enough to be used as an engine for statistical tables (several billions rows, around 2TB in size?). - Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?) - Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?) - Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention? Thanks
You may find that InnoDB with row compression and 64KB pages compares relatively favourably, if that is an option for you. On Tue, 15 Aug 2023, 11:44 pslawek83 via discuss, <discuss@lists.mariadb.org> wrote:
Hi Guys, i'm looking for tokudb replacement and i'm again thinking about rocksdb. Is it mature enough to be used as an engine for statistical tables (several billions rows, around 2TB in size?). - Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?) - Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?) - Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention?
Thanks _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Hi! I confirm I was also my choice, the migration from TokuDB to InnoDB with row compression is a really good option. BR,
Le 15 août 2023 à 12:52, Gordan Bobic via discuss <discuss@lists.mariadb.org> a écrit :
You may find that InnoDB with row compression and 64KB pages compares relatively favourably, if that is an option for you.
On Tue, 15 Aug 2023, 11:44 pslawek83 via discuss, <discuss@lists.mariadb.org> wrote: Hi Guys, i'm looking for tokudb replacement and i'm again thinking about rocksdb. Is it mature enough to be used as an engine for statistical tables (several billions rows, around 2TB in size?). - Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?) - Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?) - Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention?
Thanks _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Hi, sharing a bit of experience of working with rocksdb for 5y+ now in a mixed InnoDB / RocksDB setup. Drawback of rocksdb are lacking foreign keys and a complex configuration with many parameters (defaults in MariaDB seem quite reasonable though), also there are limitations regarding well-performing charsets and DDL changes can be more complicated than on Innodb due to the max row lock limit rocksdb applies. Advantages are much faster inserts and data loading and much better compression by using zstd. We are working with a page size of 64k for rocksdb and it works well. *- Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?)* not possible *- Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?)* yes, it is a potential danger *- Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention?* not much experience with partitioned tables but they are supported Good luck! Am Di., 15. Aug. 2023 um 13:29 Uhr schrieb jocelyn fournier via discuss < discuss@lists.mariadb.org>:
Hi!
I confirm I was also my choice, the migration from TokuDB to InnoDB with row compression is a really good option.
BR,
Le 15 août 2023 à 12:52, Gordan Bobic via discuss < discuss@lists.mariadb.org> a écrit :
You may find that InnoDB with row compression and 64KB pages compares relatively favourably, if that is an option for you.
On Tue, 15 Aug 2023, 11:44 pslawek83 via discuss, < discuss@lists.mariadb.org> wrote: Hi Guys, i'm looking for tokudb replacement and i'm again thinking about rocksdb. Is it mature enough to be used as an engine for statistical tables (several billions rows, around 2TB in size?). - Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?) - Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?) - Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention?
Thanks _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Large pages (64KB vs default 16KB) significantly improve the compression ratio with page compression, at the cost of inducing misalignment with the storage stack. One of the best compromises is to configure InnoDB with 64KB pages, put it on ZFS with recordsize=64K, and enable zstd compression on ZFS. That leaves both compression and alignment to ZFS to take care of. Throughput and compression in that combination are both excellent, a much better compromise than myrocks IMVHO. On Tue, 15 Aug 2023, 13:29 jocelyn fournier, <jocelyn.fournier@gmail.com> wrote:
Hi!
I confirm I was also my choice, the migration from TokuDB to InnoDB with row compression is a really good option.
BR,
Le 15 août 2023 à 12:52, Gordan Bobic via discuss < discuss@lists.mariadb.org> a écrit :
You may find that InnoDB with row compression and 64KB pages compares relatively favourably, if that is an option for you.
On Tue, 15 Aug 2023, 11:44 pslawek83 via discuss, < discuss@lists.mariadb.org> wrote: Hi Guys, i'm looking for tokudb replacement and i'm again thinking about rocksdb. Is it mature enough to be used as an engine for statistical tables (several billions rows, around 2TB in size?). - Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?) - Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?) - Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention?
Thanks _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
From: Gordan Bobic via discuss <discuss@lists.mariadb.org>
One of the best compromises is to configure InnoDB with 64KB pages, put it on ZFS with recordsize=64K, and enable zstd compression on ZFS.
Sorry for hijacking the thread a bit. After the discontinuation of tokudb I chose the innodb+zfs route (bad previous/early experience with rocks, also million knobs to adjust for your workload etc) but at that time didn't think of increasing the Innodb page size to 64KB - do you have any (even rough) numbers of 16 vs 64KB (on zfs)? Also, what would be the benefit of zstd vs lz4? Higher compression ratio? rr
Larger pages will yield higher compression ratios, as will zstd (slightly slower than lz4). Mileage varies, but on some of my databases, between the 64KB pages (zfs recordsize) and zstd, my compression ratios approximately doubled, from about 1.8:1 to a little under 4:1. This will all heavily depend on the type of data you are storing, though. zstd has adjustable compression, so if you have tons of CPU to spare, you could turn it up from the default of 3 (probably optimal cost/size compromise) to high teens for a compression boost. On Wed, 16 Aug 2023, 14:34 Reinis Rozitis, <r@roze.lv> wrote:
From: Gordan Bobic via discuss <discuss@lists.mariadb.org>
One of the best compromises is to configure InnoDB with 64KB pages, put it on ZFS with recordsize=64K, and enable zstd compression on ZFS.
Sorry for hijacking the thread a bit. After the discontinuation of tokudb I chose the innodb+zfs route (bad previous/early experience with rocks, also million knobs to adjust for your workload etc) but at that time didn't think of increasing the Innodb page size to 64KB - do you have any (even rough) numbers of 16 vs 64KB (on zfs)?
Also, what would be the benefit of zstd vs lz4? Higher compression ratio?
rr
participants (5)
-
Gordan Bobic -
jocelyn fournier -
Jonas Krauss -
pslawek83 -
Reinis Rozitis