February 2025 - discuss - lists.mariadb.org

[Maria-discuss] explain for insert/update/delete
by MARK CALLAGHAN 11 Jul '25

11 Jul '25

Does MariaDB have any plans to support explain for insert/update/delete/replace/CTAS? There have been feature requests open for a very long time on official MySQL without any progress. I have had a few problems in production from 'bad' delete statements that had a very different plan than the select statement with the same where clause. The only way to check for a bad plan today is to use SHOW INNODB STATUS to determine whether the statement locks all rows in a table. http://bugs.mysql.com/bug.php?id=14745 http://bugs.mysql.com/bug.php?id=35355 -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 21 May '25

21 May '25

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.

5 4

Re: Fixing typos
by Lakhin Vasilii 29 Apr '25

29 Apr '25

Forgot to attach the patch I mentioned.

5 11

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

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

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

This month in the MariaDB Foundation
by Anna Widenius 21 Feb '25

21 Feb '25

"Focus, focus, focus! That’s the mantra of any successful organisation. With great input from our Board, we’ve managed to condense our strategic planning for 2025 into six goals." Read Kaj Arnö’s blog about the MariaDB Foundation’s goals for 2025 - https://mariadb.org/this-month-2025-jan/ Anna Widenius Chief of Staff, MariaDB Foundation

1 0

Vector indexing questions for deep-image-96-angular dataset
by shiyer22＠gmail.com 19 Feb '25

19 Feb '25

Greetings! I am doing some benchmarking on MariaDB and got to the deep-image-96-angular dataset from https://github.com/erikbern/ann-benchmarks. This dataset has 9.99 million vectors of dimension 96. I am running the vector index create with M=24,ef_construction = 200 (similar parameters as pgvector in ANN-Benchmark). I wanted to check and confirm a couple of observations based on the progress of the index create at present - MariaDB [(none)]> show processlist; +----+--------+-----------+------+---------+------+-------------------+----------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+--------+-----------+------+---------+------+-------------------+----------------------------------------------------------+----------+ | 4 | ubuntu | localhost | ann | Query | 4092 | copy to tmp table | ALTER TABLE t1 ADD VECTOR INDEX (v) M=24 DISTANCE=cosine | 9.636 | MariaDB [(none)]> select trx_rows_modified from information_schema.innodb_trx; +-------------------+ | trx_rows_modified | +-------------------+ | 125215100 | +-------------------+ 1 row in set (0.004 sec) -rw-rw---- 1 ubuntu ubuntu 40420507648 Feb 19 06:20 undo002 Can you please confirm that the undo log reaching 40GB+ is expected for a progress of 9%? Did I miss something? I have configured the hnsw cache size and buffer pool to 16GB each. Should I increase them further? I want to benchmark for "hnsw index fits in memory" use-case. MariaDB [(none)]> show variables like '%hnsw%'; +------------------------+-------------+ | Variable_name | Value | +------------------------+-------------+ | mhnsw_default_distance | euclidean | | mhnsw_default_m | 24 | | mhnsw_ef_search | 200 | | mhnsw_max_cache_size | 17179869184 | +------------------------+-------------+ 4 rows in set (0.001 sec) Just to let know, I am working on the vector plugin for MySQL (MyVector) Thanks!

3 4

Fixing typos
by qobood＠gmail.com 15 Feb '25

15 Feb '25

Greetings! I've made a simple patch with some corrected typos, is there an interest for this initiative? If so, I can continue doing this thing, requesting approval for this patch and next ones.

1 0

Rebuild repodata for EL7 repo
by Software Mirror Team 05 Feb '25

05 Feb '25

Hi, Would it be possible to rebuild repodata for https://mirror.mariadb.org/yum/10.11/rhel/7/x86_64/? We are using Foreman (https://theforeman.org/) to mirror different software in our network. All other MariaDB repos are fine, but for 10.11/rhel/7 we are getting "404, message='Not Found', url=URL('https://mirror.mariadb.org/yum/10.11/rhel/7/x86_64/srpms/MariaDB-10.11.7-1.…')" It looks like the repodata still contains references to removed files. Our apologies if this list is not appropriate. Thank you in advance. Best regards, IIS Mirror team

2 2