December 2024 - discuss - lists.mariadb.org

increasing InnoDB history length and stalls on MariaDB 11.4
by kazuhiko＠nexedi.com 09 Jan '25

09 Jan '25

Hello, Our application has a sustained rate of DML queries in parallel with DQL. We can achieve good performance using innodb-locks-unsafe-for-binlog on MariaDB 10.4, with careful consideration of its 'unsafeness'. But innodb-locks-unsafe-for-binlog is removed in MariaDB 10.5 or later and our application runs much slower without it because of gap locking. https://mariadb.com/docs/server/ref/mdb/cli/mariadbd/innodb-locks-unsafe-fo… To achieve a good performance as before, we now use READ COMMITTED transaction isolation for DML and use REPEATABLE READ isolation for DQL on MariaDB 11.4. This way guarantees the consistency for our application. Our database contains totally 13.7G rows and uses 4.5TB storage. Here is the configuration : innodb_buffer_pool_size = 17179869184 (16GB) innodb_buffer_pool_instances = 16 (no meaning in 11.4) innodb_log_file_size = 134217728 (128MB) innodb_flush_method = O_DIRECT Please see the attached graphs drawing 'InnoDB history length (right axis)' and 'free redo log in % (left axis)' (i.e. 100 - Redo Log Occupancy) while running our reference workload. https://mariadb.com/kb/en/innodb-redo-log/#determining-the-redo-log-occupan… These tests are done with the same workload, but only MariaDB version and/or configuration is different. (Please disregard the 'warehouse history length' and 'activities history length' series in those plots, they are not relevant to the current issue.) On MariaDB 10.4, the performance and the behaviour between 'using innodb-locks-unsafe-for-binlog' and 'using READ COMMITTED and REPEATABLE READ' are similar. But on MariaDB 11.4, I found two serious issues. Issue 1: InnoDB history length keeps growing while running lots of DML and DQL queries in parallel. InnoDB history length is stable around 5k on 10.4, but it keeps growing on 11.4 up to 100k until the end. In our experience, increases in history list length are roughly correlated with poor query performance (causing queries to "step over tombstones" ?), and we do our best to avoid having long-running transactions in order to allow older snapshots to be freed ASAP. We are satisfied with the status on 10.4, where history list is constant. What is the reason behind such increases on 11.4 and is there any configuration to avoid such ? Issue 2: MariaDB stalls when free redo log is too small. When MariaDB stalls, even a quite simple query takes too long. (table definition) CREATE TABLE `catalog` ( `uid` bigint(20) unsigned NOT NULL, `path` varchar(255) NOT NULL DEFAULT '', ... PRIMARY KEY (`uid`), KEY `Path` (`path`), ... ) (from pt-query-digest output) # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 91 # Exec time 2 716s 1s 17s 8s 15s 5s 7s # Lock time 0 22ms 156us 491us 238us 366us 70us 204us # Rows sent 0 663 3 10 7.29 8.91 1.09 6.98 # Rows examine 0 663 3 10 7.29 8.91 1.09 6.98 # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 52.10k 330 749 586.24 685.39 68.46 563.87 # Query size 0 470.83k 3.56k 5.26k 5.17k 5.20k 225.56 5.20k SELECT uid, path FROM catalog WHERE path in ('...', /*... omitted 80 items ...*/)\G (here, these SELECT are issued in READ COMMITTED isolation level) Our understanding is that the stalls are caused by the undo log cleanup job, and/or the innodb buffer pool writeback job. Increasing the undo log size a lot seems to reduce the frequency of those stalls. For our reference workload here, we still have stalls with 1GB log file but we don't have stalls with 2GB or more log file. If we need 'big enough' log file size to avoid stalls, will the ideal size be proportional to the amount of data or proportional to the buffer pool size ? We have never had such stall on MariaDB 10.4 even with small 128MB log file. How this difference happen and is there any configuration to improve the behaviour ? Thanks in advance ! Best regards, Kazuhiko SHIOZAKI

4 15

Re: Question: Performance impact of adding Nullable FK to a new column in large tables
by Guillermo Céspedes Tabárez 03 Jan '25

03 Jan '25

Hi Jeff, Thank you for your response! To clarify, my question is more hypothetical and focused on understanding how the database engine handles this specific scenario. If the column being added is new and starts with all values as NULL, does it make sense to temporarily disable FOREIGN_KEY_CHECKS during the ALTER TABLE operation? My main concern is whether the database performs any additional checks or processes, even though the column is empty (NULL), or if the operation is already optimized and skipping the checks automatically. I'm not debating the use of foreign keys or their impact on runtime performance. I'm specifically curious if disabling FOREIGN_KEY_CHECKS in this situation saves any resources during the ALTER TABLE, or if it's unnecessary because the database doesn't do anything in this case that would benefit from skipping checks. Best regards, Guillermo El vie, 20 dic 2024 a la(s) 5:14 p.m., Jeff Dyke (jeff.dyke(a)gmail.com) escribió: > > While i'm not really sure what performance issues you are referring to. FK are going to have a small performance hit on row changes, but for data integrity, its what you need. When you start to design FKs with Null values, you are talking some of the work off of the database work that can be done for you, and moving it to your developers. Also parent_id should be indexed to assist in select performance. > > Most times that I have seen this done in the past, its normally removed and the index is kept. if parent_ids are going to be duplicated and only null for a period of time, that is a good one to many relationships, that will enforce integrity once populated. > > If you think this will improve overall performance, that is not the goal of Foreign Keys. > > > On Fri, Dec 20, 2024 at 11:03 AM Guillermo Céspedes Tabárez via discuss <discuss(a)lists.mariadb.org> wrote: >> >> Hi, >> >> I’d like to understand the performance impact of adding a FOREIGN KEY >> constraint to a new column in a large table. If the column is nullable >> and defaults to NULL, does the engine perform any checks or >> validations on existing rows? Or is the operation optimized since the >> column starts with all values as NULL? >> >> Here’s a simplified example: >> >> ALTER TABLE child_table >> ADD COLUMN parent_id INT NULL, >> ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES >> parent_table (id) >> ON UPDATE CASCADE ON DELETE SET NULL; >> >> Both child_table and parent_table are large, and the new column >> (parent_id) is empty initially. >> >> Does adding the foreign key have any measurable performance impact in >> this case? Additionally, would it make sense to temporarily disable >> FOREIGN_KEY_CHECKS for this ALTER TABLE operation and then re-enable >> it? Could this save any resources or improve performance? >> >> Thanks for any insights! >> >> Best regards, >> Guillermo. >> _______________________________________________ >> discuss mailing list -- discuss(a)lists.mariadb.org >> To unsubscribe send an email to discuss-leave(a)lists.mariadb.org

2 1

Re: No Mariadb 10.11 enterprise?
by highclass99 22 Dec '24

22 Dec '24

I did look into purchasing MariaDB Enterprise Server in the past for a service I was helping, but in the end stuck with MariaDB Community Server, because I was worried we might have trouble migrating back to the community server version if needed as the client was not 100% sure they could keep paying for all the instances they needed in the future. If on a certain MariaDB Enterprise version, would it be binary compatible with the same MariaDB Community Server version? (as long as not using features only in the MariaDB Community Server version) Thank you. Have a nice day. On Wed, Dec 18, 2024 at 9:27 PM Sergei Golubchik <serg(a)mariadb.com> wrote: > Hi, highclass99, > > On Dec 14, highclass99 via discuss wrote: > > > > I noticed on https://mariadb.com/docs/server/release-notes/ that after > > MariaDB 10.6 enterprise there is no MariaDB 10.11 enterprise but only > 11.4 > > enterprise release candidate right now. > > > > Does this mean there will be no MariaDB 10.11 enterprise? > > Is there a reason for MariaDB to be skipping the LTS version for > enterprise? > > Yes, it does. Yes, there is. > > While MariaDB Community Server's focus is on rapidly delivering new > features to users - which is why there is a rolling release line, > MariaDB Enterprise Server focus is on stability and long life cycle. > > In other words, MariaDB Enterprise Server is maintained longer and is > not released as often. It still gets new features through carefully > selected backports, see any release notes, e.g. > > https://mariadb.com/resources/blog/mariadb-enterprise-server-q4-2024-mainte… > > At least this is the current model. It might change in the future. > > Regards, > Sergei > Chief Architect, MariaDB Server > and security(a)mariadb.org >

1 0

Question: Performance impact of adding Nullable FK to a new column in large tables
by Guillermo Céspedes Tabárez 20 Dec '24

20 Dec '24

Hi, I’d like to understand the performance impact of adding a FOREIGN KEY constraint to a new column in a large table. If the column is nullable and defaults to NULL, does the engine perform any checks or validations on existing rows? Or is the operation optimized since the column starts with all values as NULL? Here’s a simplified example: ALTER TABLE child_table ADD COLUMN parent_id INT NULL, ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table (id) ON UPDATE CASCADE ON DELETE SET NULL; Both child_table and parent_table are large, and the new column (parent_id) is empty initially. Does adding the foreign key have any measurable performance impact in this case? Additionally, would it make sense to temporarily disable FOREIGN_KEY_CHECKS for this ALTER TABLE operation and then re-enable it? Could this save any resources or improve performance? Thanks for any insights! Best regards, Guillermo.

1 0

No Mariadb 10.11 enterprise?
by highclass99 13 Dec '24

13 Dec '24

Hi, I noticed on https://mariadb.com/docs/server/release-notes/ that after MariaDB 10.6 enterprise there is no MariaDB 10.11 enterprise but only 11.4 enterprise release candidate right now. Does this mean there will be no MariaDB 10.11 enterprise? Is there a reason for MariaDB to be skipping the LTS version for enterprise? Thank you.

1 0

InnoDB buffer pool usage drops regularly since 10.11.10 / 11.4.4
by William David Edwards 10 Dec '24

10 Dec '24

Hi, We see an odd pattern where InnoDB buffer pool usage drops regularly. Attached are two screenshots of memory usage on an 11.4.4 node: one at 20:15 with an InnoDB buffer pool of 47,5 GiB, and 5 minutes later with a 3,69 GiB one. The times at which this occurs are -seemingly- random. Other internal memory structures are stable. On at least one node with this issue, the InnoDB buffer pool is smaller (48 GiB) than the sum of data + indexes (87,57 GiB). As far as I can see (graphing history is limited), this has started occurring after upgrading from MariaDB 10.11.9 to 10.11.10 and from 11.4.3 to 11.4.4 respectively. At first sight, in the pre-release notes for 10.11.11 and 11.4.5, I don't see any related issues. To make absolutely sure this is related to the upgrade, we will be downgrading a troubled node tonight, to see if it fixes anything. I will report back. In userland, these drops cause a domino effect: no cache -> more I/O -> slower queries -> nuisance in application. Has anyone else seen this pattern, possibly after upgrading to the aforementioned versions? Although 1) neither of these versions are bleeding edge and 2) I haven't seen any such reports, asking is worth a shot as this started occurring on several machines after upgrading with no configuration nor (obvious) usage changes. With kind regards, William David Edwards

2 3

Support for galera clusters
by Dan O'Reilly 05 Dec '24

05 Dec '24

I can't find a place in the galera docs for a problem I'm having, so asking here. I have a 3 node mariadb Galera cluster (2 db's and an arbitrator node). On the arbitrator, when I start gardb manually and specify the log file as -l "/var/log/gardb.log" all is fine. If I try starting it as a service (RHEL 9) using the LOG_FILE in the config file, it always encounters "Failed to open '/var/log/gardbd.log' for appending: 2 (no such file or directory" I can find ZERO documentation on what's going on there." This is gardb version 26.4.14.r, installed as part of mariadb-server-galera.

1 0

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 05 Dec '24

05 Dec '24

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

3 2