- discuss - lists.mariadb.org

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

testing req/sec
by Marc 26 Apr '24

26 Apr '24

I would like to test how many requests/sec mariadb can process to authenticate users. So I need to be able to test how many cycles I can do of connecting to mariadb, executing the password sql statement and closing the connection. Anyone know what tool I can use for this?

2 3

mysqlcheck/mariadbcheck: consistency check before mysqldump
by Felix Natter 21 Apr '24

21 Apr '24

Dear mariadb experts, I would like to do this to verify my dbs (to detect hardware errors so that I can replay a backup timely): mysqlcheck -u root -pX --check --all-databases --extended Is it definitely read-only? Can I rely on the return code (0=success)? Or do I not need this because "mysqldump" does consistency checks? Many Thanks and Best Regards! Felix -- Felix Natter

2 2

Transaction Log Flushing got worse again in 10.5.23 -> 10.5.24
by Gordan Bobic 16 Apr '24

16 Apr '24

I've mentioned this here a few times before, but it looks like the latest 10.5.x update to 10.5.24 actually made things worse than in 10.5.23. I have attached two screenshots of transaction log checkpoint age over a 5 minute oltp_write_only sysbench test with 10.5.23 and 10.5.24. 10.5.23 starts off with the flushing all or nothing behaviour but it dampens out after the first minute or so. 10.5.24 exhibits it permanently. I ran multiple separate tests and the behaviour is very consistent for each version. 10.5.23 almost (not quite but almost) seems to have had things fixed to a state where things were in 10.5.8. It's pretty bad that whatever causes this undesirable flushing behaviour seems to have been merged as of 10.5.9 (10.5.8 seems to be the newest version that doesn't exhibit it). It's much more concerning that nobody seems to care that the last good major version (10.4) is going EOL soon with apparently no newer long-term tested version that is not broken. I also tested the latest 10.6.17 and that exhibits the same broken behaviour. Is a fix for this behaviour expected any time soon? On high throughput production systems that can have prolonged bursts of large writes that can hit at any time - if they hit at the top of the checkpoint age that the database made no effort to flush, there is no space left in the transaction log to absorb that impact and it leads to stalls. If combined with disk I/O exhaustion, these stalls can be so bad that for a period of tens of seconds or even minutes even the database telemetry gathering starts to fails. I also attached a screenshot with a 10 minute period where 10.5.8 and 10.5.9 were sysbenched back-to-back for 5 minutes each, with an update/restart in the middle. How extensively is the commit that introduced this behaviour built upon in subsequent changes? How much of a rabbit hole am I heading down if I try to cherry-unpick it? The config I tested with is as follows: ``` [mysqld] innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_size = 1G innodb_fast_shutdown = 0 # in case a upgrade introduces redo log format change innodb_io_capacity = 2000 innodb_io_capacity_max = 10000 innodb_log_file_size = 256M innodb_log_write_ahead_size=16K innodb_monitor_enable = all innodb_use_native_aio = 0 # native aio is slow on ZFS innodb_use_atomic_writes = 0 # native aio is slow on ZFS ```

3 5

Windows gssapi plug-in with multiple SIDs/Groups
by darrel.fenstad＠gmail.com 15 Apr '24

15 Apr '24

I am using MariaDB 10.117 and gssapi plug-in to allow SID:BA as follows: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA';" This works fine. But i want to add an additional GROUP besides SID:BA. I have tried: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA' OR gssapi USING 'GROUP:My Group';" However, when I look at mysql - View - user it shows the authentication_string as only GROUP:My Group. I also tried: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA', 'GROUP:My Group'; But that showed authentication_string in mysql - View - user as only SID:BA. Is it possible to specify multiple SIDs and GROUPs and, if so, what is the syntax to do so? Regards, Darrel

2 1

Backup MariaDB Databases for Suprema BioStar 2 Door Access System
by Turritopsis Dohrnii Teo En Ming 15 Apr '24

15 Apr '24

Subject: Backup MariaDB Databases for Suprema BioStar 2 Door Access System Good day from Singapore, On 7 March 2024 Thursday, when I was installing new self-signed SSL certificate for the door access system for a law firm in Singapore, I notice that Suprema BioStar 2 also uses the open source MariaDB database server. If I did not remember wrongly, there are 3 databases. However, the entire platform is a Windows environment, not Linux. How can I backup all the databases in the MariaDB database server? Thank you. Regards, Mr. Turritopsis Dohrnii Teo En Ming Targeted Individual in Singapore Blogs: https://tdtemcerts.blogspot.com https://tdtemcerts.wordpress.com GIMP also stands for Government-Induced Medical Problems.

2 2