May 2024 - discuss - lists.mariadb.org

Deadlock Errors
by Scott Canaan 10 May '24

10 May '24

We have a 3-node Galera cluster running MariaDB 10.5.23. A couple months ago the cluster had an issue and one of my coworkers handled it. We have several cron jobs that run against the databases to gather statistics and other information. One of those jobs runs every 15 minutes. Since the last issue, one SQL in that script has been throwing a deadlock error on two of the three nodes in the cluster: ERROR 1213 (40001) at line 14: Deadlock found when trying to get lock; try restarting transaction I've been monitoring and I don't see the job running more than once at the same time on the same server. The SQL that it is failing on is: insert into rit_mdb_track_logins select report_date, user, count(*) logins from rit_mdb_track_login_detail group by report_date, user; I've run this SQL manually and it works fine. I'm not sure where to go from here. Scott Canaan '88 Sr Database Administrator Information & Technology Services Finance & Administration Rochester Institute of Technology o: (585) 475-7886 | f: (585) 475-7520 srcdco(a)rit.edu<mailto:srcdco@rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

3 5

TCMalloc with Maria and THPs
by Simon Avery 08 May '24

08 May '24

Hello I'm looking to test tcmalloc for use on our ~80 Maria 10.11.[6-7] servers, running either Rocky 8/9 or Debian 12. A few years ago we moved to using jemalloc which helped resolve or reduce many of the memory leaks on these servers from using the distro's default malloc. We still have a couple of machines where Maria eventually uses all the memory until oomkiller steps in - although we've largely mitigated that by adopting weekly restarts for them. Thanks to Sergei Golubchik for his most useful post here on the 5th on the subject of memory leaks, which has got us looking at tcmalloc and also raises the question of Transparent Huge Pages. Sergei cites several reliable sources recommending turning them off for MariaDb and similar databases. Now - my question; tcmalloc's documentation on tuning at https://google.github.io/tcmalloc/tuning.html says "TCMalloc heavily relies on Transparent Huge Pages (THP)." And explains it's built and tested with THPs enabled and seems to strongly recommend leaving them on. That contradicts the above articles, leaving me confused about which path is best practice. It seems that THP's should be on as advised by tcmalloc, but also off when running MariaDb. The true answer possibly depends on which has the highest priority in terms of database-specific performance and the ability to manage the memory tightly. What are others' views, please? I'm searching in particular for best practice and real world experiences with tcmalloc. Also - if it's unlikely to improve upon jemalloc. Simon Avery Linux Sysadmin: ATASS Sports Oxygen House | Grenadier Road, Exeter Business Park | EX1 3LH t: 01392 440 400 e: simon.avery(a)atass-sports.co.uk<mailto:simon.avery@atass-sports.co.uk> www.atass-sports.co.uk<http://www.atass-sports.co.uk/> Follow us on X: @atassSports<https://twitter.com/atassSports> NOTICE This email and any attachments confidential and intended solely for the use of the individual to whom it is addressed. If you are not the intended recipient be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. Please notify the sender immediately. ATASS Ltd is incorporated in England and Wales with company number 04807405. See our website for further details and our privacy policy

2 3

Detect INSERT vs REPLACE in BEFORE INSERT trigger
by Alex Hermann 07 May '24

07 May '24

Hi, Is there a (built-in) way to detect from within a BEFORE INSERT trigger whether it was triggered by an INSERT or REPLACE statement? I could surround every REPLACE statement with setting and clearing a variable. Problem is that this could easily be forgotten as there are multiple applications accessing the table. I'd rather not wrap access in a stored procedure or worse. What will not work is to do a SELECT (tailored to every UNIQUE key) to determine if a record will be replaced. I do not need to know if a record _will_ be replaced, just that the intention is that it _can_ be replaced. Background: the trigger does a validation that is not enforcable via a UNIQUE key. This precludes an INSERT ... ON DUPLICATE KEY UPDATE ... construction. When the validation finds a conflicting row, the query must fail when the _intent_ is to add a new record (INSERT), but succeed when the _intent_ is to replace (REPLACE) that row. -- Regards, Alex Hermann

3 2

Excessive memory usage 10.11.6
by Clint Todish 05 May '24

05 May '24

5 6

Re: Detect INSERT vs REPLACE in BEFORE INSERT trigger
by Jaco Kroon 03 May '24

03 May '24

Hi Alex, >> May I assume that it's also not quite as trivial as doing something >> like this: >> >> CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$') > Correct. It does its own conflict detection as UNIQUE keys can't > represent it. I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ... timing could then possibly again be an issue depending on whether the function will still see the old data or not (https://mariadb.com/kb/en/constraint/#check-constraints has some info, but testing will be required). "One can use most deterministic functions in a constraint, including UDFs <https://mariadb.com/kb/en/user-defined-functions/>." Kind regards, Jaco

2 3

SOUNDEX function
by lucyfrost＠tutanota.com 01 May '24

01 May '24

I'm developing a project which uses soundex matching (amongside other options more sophisticated than that). This is custom software with a mariad back end. MariaDB soundex built-in produces codes of apparently arbitary length; the usual is 4 characters. I've come to the conclusion that the best way forward is to mimic Maria's soundex in the code. I cannt find a desciption of the algorith Maria uses though: the only source code I can find is in mf_soundex.c, and that is explicitly limited wo 4 characters. But I have installed triggers which generate codes such as A4152636125262 Where is the soundex function found? Anyone know?

2 1