- discuss - lists.mariadb.org

Redo log and tablespace flushing
by Gordan Bobic 20 Nov '23

20 Nov '23

I have a question that may seem somewhat obscure, but what I really want to know is how the disk flushing and crash safety work. Do tablespace commits get explicitly flushed during normal runtime operation? If we have a write that successfully commits to the redo log and to the binlog, but the tablespace loses, say, 5 seconds worth of commits in an unclean shutdown, would crash recovery deal with it? Is replaying the redo log followed by binlog based recovery sufficient to put the tablespace(s) into a consistent state even if the redo+binary logs are in terms of on-disk state a few seconds ahead of the tablespaces? On other words, provided that write ordering is preserved (ordering as guided by flush calls), can I do the equivalent of LD_PRELOAD=libeatmydata on the tablespace operations safely as long as the redo and binary logs are fsync()-ed reliably?

3 10

centos7 mariadb5.5 -> MariaDB10.3
by Marc 17 Nov '23

17 Nov '23

Ever since I switched from the default included centos mariadb to your repo. The web applications are getting ~1 once a week connection refused or so. I have already scheduled a cron job to restart your server. But still that is not enough. In the previous 10 years or so I never had such issue nor do I have such issues with postgresql. Nothing is in the log file, only the restart. Should such things not be logged by default? How to configure that issues resulting in refusing access or being unable to give access are logged?

2 7

When using mariadb-10.3.38, after upgrading Openssl from 1.1.1 to 3.1 strange compilation errors occur
by Ma Zhenhua 17 Nov '23

17 Nov '23

Hi Team, When using mariadb-10.3.38, after upgrading Openssl from 1.1.1 to 3.1 strange compilation errors occur. /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_error' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_fetch_row' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_num_rows' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_select_db' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_num_fields' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_real_connect' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_close' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_init' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_free_result' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_store_result' collect2: error: ld returned 1 exit status make[1]: *** [../rules.Make:461: cli] Error 1 libyyyy.so uses shared library libmysqlclient.so and both of them use mysql_xxxx. I don't know why prefix "server_" is added. I found a simliar issue where valgrind is used. But in my scenario valgrind is not used. https://jira.mariadb.org/browse/MDEV-28958?page=com.atlassian.jira.plugin.s… Any constructive suggestions are welcomed. Many thanks in advance. Regards, Allen

2 1

How do I get detailed TLS debugging information?
by Nick Lockheart 01 Nov '23

01 Nov '23

Replication master: Server version: 10.5.21-MariaDB-0+deb11u1-log Debian 11 Replication slave: Server version: 10.5.21-MariaDB-0+deb11u1 Debian 11 When `require_secure_transport = on` is enabled on the master, I get: "error reconnecting to master 'user@domain.com:3306' - retry-time: 10 maximum-retries: 86400 message: Access denied for user 'user'@'domain.com' (using password: YES)." If I turn `require_secure_transport` off, replication works. So the error in the log *looks* like an authentication failure message, but it's clearly a TLS issue. journalctl -fu mariadb.service just shows the same: "Access denied for user 'user'@'domain' (using password: YES)" How can I get verbose messages about what is happening with TLS (why is it being rejected)?

3 5

Speeding up a query: how to send 3 million rows faster?
by Ivan Krylov 01 Nov '23

01 Nov '23

Dear MariaDB users, (I've asked the same question at <https://dba.stackexchange.com/q/332557>, so feel free to ignore this one if you've already seen the other.) Our scientific application needs to store and query fundamental parameters for a number of molecules. There are 2 to 28 million rows per molecule, but the number of molecules is expected to stay small (currently 4). Here's our schema: CREATE TABLE `mol_trans` ( `species_id` int(11) DEFAULT NULL, `wl_vac` double DEFAULT NULL, `upper_id` int(11) DEFAULT NULL, `lower_id` int(11) DEFAULT NULL, `prob` double DEFAULT NULL, `flag` tinyint(4) DEFAULT NULL, KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci PARTITION BY LIST (`species_id`) (PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB, PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB, PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB, PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB) (The partitions are here to make it easier to drop a whole molecule if needed, which would otherwise be a painful large DELETE.) I'm using the admittedly old MariaDB 10.3.39 from Debian Buster for tests (connecting via the UNIX domain socket using the command line client), but we've been seeing the same problem on MySQL 5.6 and MariaDB 10.11 on Windows 10. The following query currently takes 1m 7s on my computer, as measured by running `time echo "$QUERY" | mysql "$DATABASE" >/dev/null`: select mtr.prob, mtr.lower_id, mtr.upper_id from mol_trans mtr where ( mtr.species_id=6115 and mtr.wl_vac > 766.0 and mtr.wl_vac < 883.0 and mtr.flag = 1 ) order by mtr.wl_vac; It needs to return 3024559 rows. The query seems to be using the right index, judging by the EXPLAIN output: id: 1 select_type: SIMPLE table: mtr type: ref possible_keys: spid_flag_wl key: spid_flag_wl key_len: 7 ref: const,const rows: 14158123 Extra: Using where With SET PROFILING=ON and the query cache disabled, the single operation with a duration of >1 second in the 23-row profile is "Sending data": Status: Sending data Duration: 66.712277 CPU_user: 66.436978 CPU_system: 0.279932 Context_voluntary: 2156 Context_involuntary: 6964 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: <unknown> Source_file: sql_parse.cc Source_line: 6086 Is there a way to spend less time sending data? I know the query can in theory run faster because it takes less than 10 seconds on SQLite and PostgreSQL, but if we switched our application from database engine to database engine every time we started having performance problems, we would be very far from done by now. I also know that MariaDB can be very fast at transferring data because it takes me only a few minutes more to download the whole database dump. -- Best regards, Ivan

5 13

INSERT ON DUPLICATE UPDATE and primary auto_increment key behaviour
by Richard Ellerbrock 31 Oct '23

31 Oct '23

I have the following table: CREATE TABLE `events_active_test` ( `timestamp` TIMESTAMP NOT NULL, `timestamp_earliest` TIMESTAMP NOT NULL, `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `source` CHAR(50) NOT NULL COLLATE 'utf8mb3_general_ci', `repeats` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `source` (`source`) USING BTREE, INDEX `timestamp` (`timestamp`) USING BTREE, INDEX `timestamp_earliest` (`timestamp_earliest`) USING BTREE ) COLLATE='utf8mb3_general_ci' ENGINE=InnoDB ; Then I insert some test rows (do this 5 times): INSERT INTO events_active_test (timestamp,timestamp_earliest,source) VALUES (NOW(),NOW(),'test') ON DUPLICATE KEY UPDATE repeats=repeats+1,timestamp=NOW(); When I then look at the table id = 1 and repeats = 5 as expected Now I insert a non duplicate key (test2): INSERT INTO events_active_test (timestamp,timestamp_earliest,source) VALUES (NOW(),NOW(),'test2') ON DUPLICATE KEY UPDATE repeats=repeats+1,timestamp=NOW(); The new row now has id = 6 and repeats = 1. This really baffled me as it appears that when using INSERT ON DUPLICATE UPDATE with a primary auto_increment column the auto_increment is increased even if the INSERT fails and the UPDATE occurs. In my case the majority of my INSERT's are duplicates causing massive jumps and gaps in the auto_increment numbers. Is this correct behaviour? Also is there an overhead of having to update the tables auto_increment metadata counter unnecessarily for every failed insert? -- Richard

3 2

Setting a debugging environment
by Gingko 10 Oct '23

10 Oct '23

Hello. This is my first message here, and I don't know if there is any usage about self introducing. I am trying to setup a comfortable developer environment for MariaDB, and especially a debugging environment, on a Linux Ubuntu box (using gdb or ddd, or gdbgui…). This is not obvious as all of them seems to have issues. Anyway, I tried to follow the instructions from that page: https://mariadb.com/kb/en/get-build-and-test-latest-mariadb-the-lazy-way/ But there is something that I am wondering: If I want to see the contents of some variables using these debuggers, is it normal that I very often get the following message? |<optimized out>| I understand what that means, but I am actually trying to debug code compiled with the following options: |-DCMAKE_BUILD_TYPE=Debug| Shouldn't all optimizations have been disabled when compiling in debugging mode? Regards, Gingko

3 2

InnoDB durability
by Federico Razzoli 30 Sep '23

30 Sep '23

Recently Laurynas commented this bug and he brought it up on social media: https://bugs.mysql.com/bug.php?id=75519 I was surprised to read thar, from his comments, this 5.6 bug is still in MySQL 8.0. Did anyone test whether it is in MariaDB too? If so, which versions? Thanks, Federico -- Federico Razzoli Vettabase Ltd director & founder, Database Consultant https://vettabase.com federico.razzoli(a)vettabase.com +44 7739 427279 Telegram: federico_razzoli Skype: fede.razzoli The information in this e-mail and any attachments may be confidential. If you received it by mistake, please inform the sender. Unless you are the intended recipient or his/her representative you are not authorised to, and must not, read, copy, distribute, use or retain this message or any part of it.

3 5

RAM/Swap Usage
by roughnecks 14 Sep '23

14 Sep '23

Hello, first time here :) I've got a single db for roundcube and yet after every couple days my swap for mariadb reaches 200MB and over. To be noted that I'm the sole roundcube's user and that even myself prefer using thunderbird, so I only access roundcube from time to time. Is this normal? I'm running debian stable, upgraded from oldstable when it came out and I remember that in bullseye ram usage was just 50MB or so. Thanks, have a nice day. -- roughnecks

3 10

Increasing RAM usage until oom
by Marco Dickert - evolver group 07 Sep '23

07 Sep '23

Hi folks, we experience a RAM issue with MariaDB (version 10.5.19-MariaDB-0+deb11u2-log) on a standard Debian bullseye system. The problem is that over time MariaDB uses more and more RAM, until the kernel's oom-killer terminates it. The server is a VMware guest with 8G RAM, 2G swap and 4 cores. The most important parameter, innodb_buffer_pool_size, is set to 2G. I know this is quite low for this system, but I want to have proof that MariaDB consumes defininitely more RAM than allowed. According to the calculation in this comment [1], the RAM usage shouldn't exceed 4.2G. Here is the result, including the raw configuration values: ``` MariaDB [(none)]> SELECT ROUND( -> ( @@GLOBAL.key_buffer_size -> + @@GLOBAL.query_cache_size -> + @@GLOBAL.tmp_table_size -> + @@GLOBAL.innodb_buffer_pool_size -> + @@GLOBAL.innodb_log_buffer_size -> + @@GLOBAL.max_connections * ( -> @@GLOBAL.sort_buffer_size -> + @@GLOBAL.read_buffer_size -> + @@GLOBAL.read_rnd_buffer_size -> + @@GLOBAL.join_buffer_size -> + @@GLOBAL.thread_stack -> + @@GLOBAL.binlog_cache_size) -> ) / 1024 / 1024, 1) `total MB`; +----------+ | total MB | +----------+ | 4220.0 | +----------+ 1 row in set (0,001 sec) MariaDB [(none)]> SELECT @@GLOBAL.key_buffer_size, @@GLOBAL.query_cache_size , @@GLOBAL.tmp_table_size, @@GLOBAL.innodb_buffer_pool_size , @@GLOBAL.innodb_log_buffer_size, @@GLOBAL.max_connections , @@GLOBAL.sort_buffer_size, @@GLOBAL.read_buffer_size , @@GLOBAL.read_rnd_buffer_size, @@GLOBAL.join_buffer_size , @@GLOBAL.thread_stack, @@GLOBAL.binlog_cache_size \G 1 row in set (0,000 sec) *************************** 1. row *************************** @@GLOBAL.key_buffer_size: 10485760 @@GLOBAL.query_cache_size: 536870912 @@GLOBAL.tmp_table_size: 16777216 @@GLOBAL.innodb_buffer_pool_size: 2147483648 @@GLOBAL.innodb_log_buffer_size: 134217728 @@GLOBAL.max_connections: 512 @@GLOBAL.sort_buffer_size: 2097152 @@GLOBAL.read_buffer_size: 131072 @@GLOBAL.read_rnd_buffer_size: 262144 @@GLOBAL.join_buffer_size: 262144 @@GLOBAL.thread_stack: 299008 @@GLOBAL.binlog_cache_size: 32768 ``` However, MariaDB uses up to 5.4G so far. And it keeps increasing. Last time the oom-killer was invoked, MariaDB used 10G total, and 7.4G rss (given in 4k pages): ``` [Mi Jul 26 16:01:06 2023] Tasks state (memory values in pages): [Mi Jul 26 16:01:06 2023] [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name ... [Mi Jul 26 16:01:06 2023] [3462912] 10000 3462912 2753196 1895256 20762624 472509 0 mariadbd ... [Mi Jul 26 16:01:06 2023] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mariadb.service,task=mariadbd,pid=3462912,uid=10000 [Mi Jul 26 16:01:06 2023] Out of memory: Killed process 3462912(mariadbd) total-vm:11012784kB, anon-rss:7581024kB, file-rss:0kB,shmem-rss:0kB, UID:10000 pgtables:20276kB oom_score_adj:0 ``` The MariaDB knowledge base [2] says that the problem probably correlates with one of the following options: ``` +----------------------------+----------------------+----------------+ | variable_name | default_value | variable_value | +----------------------------+----------------------+----------------+ | INNODB_IO_CAPACITY_MAX | 18446744073709551615 | 2000 | | QUERY_CACHE_LIMIT | 1048576 | 67108864 | | MAX_CONNECTIONS | 151 | 512 | | TABLE_OPEN_CACHE_INSTANCES | 8 | 7 | | LONG_QUERY_TIME | 10.000000 | 5.000000 | | KEY_BUFFER_SIZE | 134217728 | 10485760 | | QUERY_CACHE_SIZE | 1048576 | 536870912 | | INNODB_LOCK_WAIT_TIMEOUT | 50 | 80 | | INNODB_LOG_BUFFER_SIZE | 16777216 | 134217728 | | HOST_CACHE_SIZE | 128 | 654 | | INNODB_FT_MIN_TOKEN_SIZE | 3 | 2 | | WAIT_TIMEOUT | 28800 | 600 | | INNODB_FAST_SHUTDOWN | 1 | 0 | | INNODB_LOG_FILE_SIZE | 100663296 | 1073741824 | | THREAD_POOL_SIZE | 8 | 4 | | INNODB_BUFFER_POOL_SIZE | 134217728 | 2147483648 | | MAX_ALLOWED_PACKET | 16777216 | 268435456 | | BACK_LOG | 150 | 254 | +----------------------------+----------------------+----------------+ ``` I can't see what setting could probably cause the issue. Some values are Debian defaults. Most of them we set intentionally. Can anyone give a hint on where to look for? Do you need further information? Thanks for any help. [1] https://serverfault.com/a/1020847 [2] https://mariadb.com/kb/en/mariadb-memory-allocation/#how-to-troubleshoot-ou… Regards, Marco

3 4