March 2025 - discuss - lists.mariadb.org

Re: Fixing typos
by Lakhin Vasilii 14 Apr '25

14 Apr '25

Forgot to attach the patch I mentioned.

5 9

Busy_time rate is a million times higher from 11.4.4 to 11.4.5
by mdbml.myself854＠passmail.net 14 Apr '25

14 Apr '25

Good day When I start a 11.4.4 image and I watch USER_STATISTICS.BUSY_TIME refreshing it every second or so, it increases with about 0.0002 at a time (for a user that's not really doing anything). But when I do the same in a 11.4.5 image it goes up by about 200 at a time (while the user is still not doing anything). I think that's wrong because it's supposed to be "number of seconds of activity". I couldn't find anything about it in the release notes https://mariadb.com/kb/en/mariadb-11-4-5-… [View More]

3 3

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 … [View More]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 [View Less]

4 17

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

10.11.8 writing all binary logs in row format (even though format is MIXED)
by mariadb＠Biblestuph.com 03 Apr '25

03 Apr '25

Doing a little fishing here ... Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED? This replica has been running for quite some time, though admittedly I had to restore it from a backup about 3 weeks ago as the result of a server crash. But it had been writing data fine over those three weeks (and prior); starting on March 2nd it started writing all of its … [View More]

2 3

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 … [View More]

3 4

is blocked because of many connection
by Marc 31 Mar '25

31 Mar '25

I am constantly getting abuse from azure, amazon, digital ocean cloud. With previous mysql versions I never got this message. I assume you have set this to a default. I don't want to have this limit on ip but on ip+database, how can I change this?

6 40

Re: Troubleshooting major performance issue.
by pslawek83 31 Mar '25

31 Mar '25

1 0

Troubleshooting major performance issue.
by Olivier Miquel 30 Mar '25

30 Mar '25

4 11

Processes getting locked out "Opening tables"
by Derick Turner 28 Mar '25

28 Mar '25

Hi all, We have been having some transient issues with processes sticking on the Opening tables state, which means that the total number of processes grows till max processes is hit/locking out the database. OS Ubuntu sever 24.04.02 MariaDB 11.4.5 It appears to be InnoDB deadlocks. So the example today the output from SHOW ENGINE INNODB STATUS \G showed that there was a deadlock between a couple of transactions: MariaDB [(none)]> SHOW ENGINE INNODB STATUS \G *******************… [View More]******** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2025-03-21 12:49:57 0x7200aeae76c0 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 49 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1541 srv_active, 247133 srv_idle srv_master_thread log flush and writes: 248664 ---------- SEMAPHORES ---------- ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-03-18 15:35:00 0x72067aea16c0 *** (1) TRANSACTION: TRANSACTION 57050747951, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1 MariaDB thread id 17, OS thread handle 125372157531840, query id 101395 *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2549335 page no 82714 n bits 320 index mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747951 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 393062613639666233633334373364663239333634323134316333303735; asc 90ba69fb3c3473df293642141c3075; (total 40 bytes); 1: len 8; hex 80000000003ac8e3; asc : ;; *** CONFLICTING WITH: RECORD LOCKS space id 2549335 page no 82714 n bits 320 index mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747928 lock mode S Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 393062613639666233633334373364663239333634323134316333303735; asc 90ba69fb3c3473df293642141c3075; (total 40 bytes); 1: len 8; hex 80000000003ac8e3; asc : ;; Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 393062613636353139363935316562666532646631636535653238326366; asc 90ba665196951ebfe2df1ce5e282cf; (total 40 bytes); 1: len 8; hex 8000000000b78d51; asc Q;; RECORD LOCKS space id 2549335 page no 82714 n bits 320 index mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747951 lock mode S Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 393062613639666233633334373364663239333634323134316333303735; asc 90ba69fb3c3473df293642141c3075; (total 40 bytes); 1: len 8; hex 80000000003ac8e3; asc : ;; Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 393062613636353139363935316562666532646631636535653238326366; asc 90ba665196951ebfe2df1ce5e282cf; (total 40 bytes); 1: len 8; hex 8000000000b78d51; asc Q;; *** (2) TRANSACTION: TRANSACTION 57050747928, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1 MariaDB thread id 20, OS thread handle 125372148479680, query id 101369 Write_rows_log_event::write_row(-1) on table `mdl_files` *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2549335 page no 82714 n bits 320 index mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747928 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 393062613639666233633334373364663239333634323134316333303735; asc 90ba69fb3c3473df293642141c3075; (total 40 bytes); 1: len 8; hex 80000000003ac8e3; asc : ;; *** CONFLICTING WITH: RECORD LOCKS space id 2549335 page no 82714 n bits 320 index mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747928 lock mode S Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 393062613639666233633334373364663239333634323134316333303735; asc 90ba69fb3c3473df293642141c3075; (total 40 bytes); 1: len 8; hex 80000000003ac8e3; asc : ;; Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 393062613636353139363935316562666532646631636535653238326366; asc 90ba665196951ebfe2df1ce5e282cf; (total 40 bytes); 1: len 8; hex 8000000000b78d51; asc Q;; RECORD LOCKS space id 2549335 page no 82714 n bits 320 index mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747951 lock mode S Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 393062613639666233633334373364663239333634323134316333303735; asc 90ba69fb3c3473df293642141c3075; (total 40 bytes); 1: len 8; hex 80000000003ac8e3; asc : ;; Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 393062613636353139363935316562666532646631636535653238326366; asc 90ba665196951ebfe2df1ce5e282cf; (total 40 bytes); 1: len 8; hex 8000000000b78d51; asc Q;; *** WE ROLL BACK TRANSACTION (2) I'm assuming that the rest of the processes were waiting on the rollback to happen before they did anything else? So my questions are: Am I right in that? Is there anything which can be done to speed up rollback? Is there anything which can be done to prevent that rollback from blocking all of the other processes, the majority of which were against completely different databases? When I was investigating this the suggestion was to dump and reload all of the databases. We have 221 databases in the cluster which takes up 880GB of filesystem space. A chunk of that is the ibdata1 file which is 290GB on that server. (we now have autoshrink on but only after we'd upgraded to a version which supports it. File per table is on, so much of this is unused space.) We manage this by pointing the webservers to a different node and waiting for the deadlock to clear but I would like to fix it properly. Thanks in advance Derick -- Derick Turner - He/Him [View Less]

2 12