April 2025 - discuss - lists.mariadb.org

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

10 Apr '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 17

Ansible role to deploy madiadb gaelra cluster using containers
by Engelmann Florian 08 Apr '25

08 Apr '25

Hi, is there any production ready ansible role to deploy a mariadb galera cluster using docker container (not packages)? All the best Florian EveryWare AG Florian Engelmann Cloud Platform Architect Zurlindenstrasse 52a CH-8003 Zürich T +41 44 466 60 00 F +41 44 466 60 10 florian.engelmann(a)everyware.ch www.everyware.ch

2 1

Moving forward the pull request
by pslawek83 08 Apr '25

08 Apr '25

Hello Everyone, made some changes to my pull request. How to move it forward? Want to see if design is ok to make some final code changes. github.com https://github.com/MariaDB/server/pull/3891 Thanks

1 0

presenting binary data as hex strings by default
by Jaco Kroon 07 Apr '25

07 Apr '25

Hi All, This would be a mis-feature probably, but please bear with me. Getting binary data dumped to a terminal is not fun. Is there perhaps a way to set it such that *binary types would be displayed/presented as hex string rather than raw binary? In particular when using CLI tools? I'm aware of the HEX() function which achieves same, but you need to do that for every column, when inspecting data and just starting off with something like "select * from" ... I suspect having a flag to inform the cli client tools to present such binary columns as hexadecimal strings instead of dumping raw binary data to the terminal would be most helpful. Sorry, just had a round of screeching in my ears from "bell" characters and was just wondering if there's a way to not only stop that, but present the data in a more sensible manner regardless, ideally some "opt in" mechanism to maintain backwards compatibility. Kind regards, Jaco

2 1

Adding an index to a large table
by edi mari 04 Apr '25

04 Apr '25

Hi, I have a 200GB table with a foreign key constraint, and I need to drop an existing index and add a non-unique index to it. What is the best approach to do this with minimal impact on users? Thanks Edi

6 12

it is normal to have a memory usage of 85%?
by cata 04 Apr '25

04 Apr '25

Hi everybody, I am new here and I am confronted with a situation, so I will start with the questions first: 1. It is normal that on my server, mariadb to use almost 85% of available ram or something is wrong? 2. In case I want to limit the whole application (mariadb) to not use more than 5.5 GB RAM from total of 7.8GB it could be possible - without affecting it's performance? 3. Is anything to optimize (change) now? If yes, which variable? 4. Do we need more RAM for this server or actual RAM could be enough? See below: Operating system: "Debian 12.10 (bookworm)" MariaDB Version: 10.11.11-MariaDB-0+deb12u1-log The server was configured (limited) to use for InnoDB -> max 3GB Ram MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 3221225472 | +-------------------------+------------+ 1 row in set (0.001 sec) But, on that server *we are running also MySam, Memory *and Innodb, in total, ~70 databases (8 MySam, 1 MEMORY, and the rest are InnoDB) MariaDB [mysql]> select count(distinct db) from db; +--------------------+ | count(distinct db) | +--------------------+ | 73 | +--------------------+ 1 row in set (0.001 sec) MariaDB [mysql]> SYSTEM 4CPU, 7.8GB RAM root@adsns-db-node0:~# df -h Filesystem Size Used Avail Use% Mounted on udev 3.8G 0 3.8G 0% /dev tmpfs 776M 764K 775M 1% /run /dev/sda1 150G 48G 97G 34% / tmpfs 3.8G 0 3.8G 0% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock /dev/sda15 241M 138K 241M 1% /boot/efi tmpfs 776M 0 776M 0% /run/user/0 root@adsns-db-node0:~# I found some articles on internet regarding /dev/shm, which could influence mysql performance. In my case /dev/shm is not used even defined, so the system could use the whole available RAM - 7.8GB RAM, so I excluded this as possible cause. MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer_size%'; +----------------------------+-----------+ | Variable_name | Value | +----------------------------+-----------+ | aria_pagecache_buffer_size | 134217728 | | aria_sort_buffer_size | 268434432 | | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size | 16777216 | | innodb_sort_buffer_size | 1048576 | | join_buffer_size | 4194304 | | key_buffer_size | 10485760 | | mrr_buffer_size | 262144 | | myisam_sort_buffer_size | 134216704 | | preload_buffer_size | 32768 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 2097152 | +----------------------------+-----------+ 13 rows in set (0.001 sec) Traffic is not high, just 123 queries per second. MariaDB [(none)]> status; -------------- mysql Ver 15.1 Distrib 10.11.11-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper Connection id: 877596 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.11.11-MariaDB-0+deb12u1-log Debian 12 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb3 Conn. characterset: utf8mb3 UNIX socket: /run/mysqld/mysqld.sock Uptime: 7 days 3 hours 24 min 6 sec Threads: 78 Questions: 75901186 Slow queries: 4891595 Opens: 12573592 Open tables: 2000 Queries per second avg: 123.007 -------------- MariaDB [(none)]> Even I am restarting mariadb, after a short time, the memory usage is 85% root@adsns-db-node0:~# free -g total used free shared buff/cache available Mem: 7 6 0 0 0 1 Swap: 0 0 0 root@adsns-db-node0:~# The swap is not used, just the memory, which is ok. CPU usage is less than ~10%, so is ok. See below, output of top. %Cpu(s): 2.2 us, 0.7 sy, 0.0 ni, 96.2 id, 0.4 wa, 0.0 hi, 0.4 si, 0.0 st MiB Mem : 7750.8 total, 161.4 free, 6351.8 used, 712.6 buff/cache MiB Swap: 0.0 total, 0.0 free, 0.0 used. 1399.0 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1555028 mysql 20 0 7752424 6.5g 15476 S 10.6 85.2 188:29.16 mariadbd root@adsns-db-node0:~# pstree |grep maria |-mariadbd---51*[{mariadbd}] root@adsns-db-node0:~# All 51 childs of mariadb are using 85% from the system memory (each child ~1.6% of total RAM). I found a mysql calculator and changing innodb_buffer_pool_size - its default value 8M - with 3G, I got ~3.7GB ... still not 6.6G (aprox 85% from total RAM). See: https://www.mysqlcalculator.com/ 5. Why this difference, from 3.7GB up to 6.6GB? 6. Where could it be the rest of 2GB which is used by mariadb (from 3.7GB up to 6.6GB)? Any help will be appreciated. Catal

4 5

InnoDB buffer pool usage drops regularly since 10.11.10 / 11.4.4
by William David Edwards 02 Apr '25

02 Apr '25

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

3 4

active-backup or active-active solution in docker swarm
by vbvbrj 01 Apr '25

01 Apr '25

Hello. Did anyone managed to create a master-backup or master-master deployment of mariadb in docker swarm to work consistent and stable? Community, please help me with a yaml config in docker swarm to deploy a master-active or better master-master config of mariadb which will handle servers restart on full stack shutdown and restart without any implications. Can someone who did this share theirs yaml config for docker swarm? I tried the examples on percona page and as data was populated the synconizations of services were lost. Thank you.

1 0