- discuss - lists.mariadb.org

Re: Rocksdb as a replacement for toku?
by pslawek83 22 Aug '23

22 Aug '23

Thanks everyone for the answers. Tested this PAGE_COMPRESSION a bit and make some reading. Not sure if I fully understand how that works... is that correct? 1. Impossible without punch hole as if we don't have it the server will just write partially used pages which will take full page size anyway. But in docs it seems it described its use with punch hole disabled. What's the reason to still have page compression without punch hole? 2. After enabling compression each page needs to be trimmed using punch hole, so if we have 1 TB database and 64k blocks, the database files will have over 15 million fragments ( 1000000000000/64000) So if we get like 50% compression and then we'll be able to put another 1TB of data into these holes we'll be having close to 31 million file fragments. I'm not sure if under such conditions the FS will be able to even allocate space efficiently anymore, copy these files, etc. Or im not getting something? 3. I was testing compression of ~1.5GB table with nice results on default 16kb page size. One thing i noticed is that l was always getting exactly the same compressed size using `du`. However the table read/copy times were different from 40s for uncompressed to 80-400 seconds depending on algo. Is that possible that is because page size is too low? 4. Myrocks seems to be very slow compared to compressed inno. Was also reading docs related to it. For its usecase is it more suitable for storing large rows rather than many small rows? 5. I remember reading that some innodb compression is going to be obsolete. Is that row compression? Or there are no plans to obsolete any compression model? Thanks. Dnia 16 sierpnia 2023 16:08 Gordan Bobic via discuss < discuss(a)lists.mariadb.org > napisał(a): 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(a)roze.lv > wrote: > From: Gordan Bobic via discuss < discuss(a)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 _______________________________________________ discuss mailing list -- discuss(a)lists.mariadb.org To unsubscribe send an email to discuss-leave(a)lists.mariadb.org

3 2

Running events on same time
by kpesio＠gmail.com 17 Aug '23

17 Aug '23

Hi. Could someone confirm in some way about running multiple events at same time. Documentation at https://mariadb.com/kb/en/automating-mariadb-tasks-with-events/ states: "The event scheduler runs as a single thread. If any event is supposed to start while another event is running, its execution will be skipped." I have simulated events triggering at same time, running with delays to get all kinds of overlapping schedules, writing to same table from the event and not missed a single one. The information_schema.events tables column last_executed has - depending on scheduled time - valid values and I can see in the processlist events running at same time. Version I'm running is 10.6.10 on Linux. Tried to cross check against the MySQL documentation but couldn't find anything about this there. So what situation does the documentation refer to or it this something fixed long time ago?

3 5

Rocksdb as a replacement for toku?
by pslawek83 16 Aug '23

16 Aug '23

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

5 6

Query on Building Maria DB for different Compression Algorithm
by Raj, Rahul 10 Aug '23

10 Aug '23

[AMD Official Use Only - General] Hi, I am building MariaDB from source code and have queries on linking of compression algorithm. Before building the source code of MariaDB, I am exporting the path for SNAPPY or LZMA or the algorithm which is currently it supports. Then I build the code with - cmake -B . -DCMAKE_INSTALL_PREFIX=$PWD/maria_bin -DPLUGIN_MROONGA=NO -DPLUGIN_ROCKSDB=NO cmake --build . -j cmake -install . It compiled the code and installed in the specified location. When I checked the lib/plugin folder it has generated, provider_lzma.so or provider_snappy.so files and when I did ldd on that .so file, it has given the path from where it is fetching the liblzma.so.1 or libsnappy.so.1 file. Query: 1. The path which ldd shows for provider_lzma.so or provider_snappy.so is taking form default path, and it is not from the path which I am exporting the library and bin path - could you please explain me the correct steps. 2. Is there any way or any Flag we can set to switch between different compression method during build time itself, and in which log we can check it has linked properly. 3. How to benchmark the linked compression method and how we can see it is using the linked compression method from the custom path of .so files. Kindly help on above query. Thanks Rahul Raj

4 9

Reduce logging output from mariabackup
by Tom Worster 03 Aug '23

03 Aug '23

Is there an option to reduce the volume of information mariabackup writes to the console as it does its magic? Some of the log lines are very welcome, for example I like the first three Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: [00] 2023-08-03 11:35:03 Connecting to MariaDB server host: localhost, user: mariabackup, password: set, port: not set, socket: /run/mysqld/mysqld.sock Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: [00] 2023-08-03 11:35:03 Using server version 10.6.14-MariaDB-1:10.6.14+maria~deb11-log Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: mariabackup based on MariaDB server 10.6.14-MariaDB debian-linux-gnu (x86_64) and the last two are really great, like a gift from god Aug 03 11:36:34 bhs6 v2_backup_maria.sh[1291196]: [00] 2023-08-03 11:36:34 Last binlog file ./bhs8-bin.001617, position 952620696 Aug 03 11:36:34 bhs6 v2_backup_maria.sh[1291196]: [00] 2023-08-03 11:36:34 completed OK! But there's about 500 between these every hour in the journal and most of them are quite boring like this Aug 03 11:36:31 bhs6 v2_backup_maria.sh[1291066]: [01] 2023-08-03 11:36:31 Copying ./sys/sys_config.frm to /pool2/mariabackup/sys/sys_config.frm Aug 03 11:36:31 bhs6 v2_backup_maria.sh[1291066]: [01] 2023-08-03 11:36:31 ...done Or stuff I do not understand and suspect I probably don't need to undersand like Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: [00] 2023-08-03 11:35:03 uses posix_fadvise().

3 5

Expected release date MariaDB 10.6.15
by William Edwards 01 Aug '23

01 Aug '23

Hi, Is MariaDB 10.6.15 expected to be released this week? According to Jira[1], it is 4 days overdue. This version will include a fix that I'm waiting for. Met vriendelijke groeten, William Edwards [1]: https://jira.mariadb.org/projects/MDEV/versions/29013

3 4

Help us redesign the mariadb.org website
by Ian Gilfillan 17 Jul '23

17 Jul '23

We are currently re-designing the mariadb.org website and you are invited to participate in this process. The interactive study shouldn't take longer than 10 to 15 minutes to complete and will close at midnight on Thursday 20 July 2023 (UTC-8). Access the study here: (no registration or login is required) https://study.uxtweak.com/cardsort/gkeYA0HPSXawp6YNsjllU The activity involves a method called Card Sorting, which will influence how we design the information architecture of the website: its structure, navigation and the findability of content and functionality. Your inclusion in this effort is important and will help to inform and assist us in designing an intuitive, usable and effective future website. Many thanks from the website redesign team MariaDB Foundation

1 0

Advice on Replication
by Andrew Gable 13 Jul '23

13 Jul '23

5 4

Re: Sandboxing mariabackup
by Tom Worster 07 Jul '23

07 Jul '23

Hi Daniel, That part is looking good for now but I gotta do fire drills validate the whole shebang. There's something almost blog worthy about this. mysqldump started as a handy import export utility and ended up being an exquisite point-in-time logical SQL snapshot tool. Now xtrabackup and mariabackup are so astonishingly fast at restoration/recovery they can't be ignored. But they are terrifying! They operate directly on the datadir. mysqldump is a nice friendly SQL client like any other. Does that make sense? An SQL client can ruin the DB as well as anything else if it has the right permissions (pun!). Hence subtracting permission to write datadir from the mariabackup process feels like a step to parity. Tom On 7/6/2023 6:52:17 PM, "Daniel Black" <daniel(a)mariadb.org> wrote: >Glad you worked it out. > >I did mean to write this onlist a while ago. Got saved a draft message >and I forgot about it. > >On Thu, Jul 6, 2023 at 10:47 PM Tom Worster <fsb(a)thefsb.org> wrote: >> >> On 7/6/2023 7:14:50 AM, "Daniel Black" <daniel(a)mariadb.org> wrote: >> >> >> I prefer to sandbox systemd services with ProtectSystem=strict. >> >> Especially so when User=root. >> > >> >Should be able to use User=mysql (or whatever the mariadb.service is >> >running as), provided the backup directory is also mysql: owned. >> > >> Thanks Daniel. >> >> Yes. I discovered that in subsequent testing. >> spin >> And I found that only target-dir and tmpdir need to be read-write. I >> forget what made me think that wasn't enough. >> >> To run as mariabackup as mysql user but with read-only access to datadir >> I separated the mariabackup part of the overall hourly backup script >> into its own service unit: >> [Unit] >> Description=mariabackup DBs >> OnFailure=unit-status-mail(a)%n.service unit-status-pushover(a)%n.service >> [Service] >> User=mysql >> Type=oneshot >> ProtectSystem=strict >> ReadWritePaths=/pool2/mariabackup /pool2/mariabackup_tmpdir >> ExecStart=/path/to/backup_maria.sh >> >> This is /path/to/backup_maria.sh. It's on a replica and the test of prev >> and new size are a check (among others) that the replica isn't stuck. >> #!/bin/bash >> prev_backup_size=`du -s /pool2/mariabackup` >> rm -rf /pool2/mariabackup/* >> mariabackup --backup --target-dir=/pool2/mariabackup/ >> --tmpdir=/pool2/mariabackup_tmpdir --user=mariabackup --password=__ >> mariabackup --prepare --target-dir=/pool2/mariabackup/ >> --tmpdir=/pool2/mariabackup_tmpdir >> new_backup_size=`du -s /pool2/mariabackup` >> if [ "$prev_backup_size" = "$new_backup_size" ] >> then >> echo WARNING: Consecutive mariabackup sizes unchanged >> # FIXME send alerts >> fi >>

2 2

Sandboxing mariabackup
by Tom Worster 06 Jul '23

06 Jul '23

I prefer to sandbox systemd services with ProtectSystem=strict. Especially so when User=root. So when ProtectSystem=strict, which dirs need to be ReadWritePaths to run `mariabackup --backup` and `mariabackup --prepare`? So far I got the --target-dir and the --tmpdir. Is that sufficient? tia Tom

2 2