- discuss - lists.mariadb.org

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

Odp: Re: Re: Rocksdb as a replacement for toku?
by pslawek83 25 Aug '23

25 Aug '23

Hi Marko, thanks for the response ... so for the DOCS part > h mariadb.com ttps://mariadb.com/kb/en/innodb-page-compression/ > When InnoDB page compression is used, InnoDB may still write the compressed page to the tablespace file with the original size of the uncompressed page, which would be equivalent to the value of the mariadb.com innodb_page_size system variable. This is done by design, because when InnoDB's I/O code needs to read the page from disk, it can only read the full page size. However, this is obviously not optimal. > This punch hole technique allows InnoDB to read the compressed page from disk as the full page size, even though the compressed page really takes up less space on the file system. So as far as i understand if compression is enabled the server will write eg. 40% utilized page which will later be decompressed when read, but this compression/decompression won't have any advangage if there's no punch hole support. The only difference will be that the block will be filled with zeros for the empty space at the end. Or is it possible for PAGE_COMPRESSION to still be used without punch hole (just not that effective for example)? Also tested this row compression, and yes page compression seems much better. Is it possible to get rid of punch hole by just re-mapping the pages inside these innodb files? I was thinking about this... if it's possible to change the data structure to divide the file into 512b blocks while the beginning of each block will mark page number (0xffffffff for blocks which are part of previous page). To increase page size we'd need to add another page with same number at the end of the file, later continuous recycled blocks can be added to a list and used to store smaller pages. To make this atomic, first whole block would be allocated and written with 0xfffffffe as a dummy id and then this placeholder id could be replaced with real block number). The problem with this could be that the file would get 100% fragmented over time (but probably will work ok with NVME). The mapping could be read back by reading whole datafile during server start and it'd need 125MB of memory for 1TB of compressed data to store the mapping in memory for fast access. What you thinik? Would it be easy to add it on top of current code (if there's any API maybe, so for example i can do this) and would it even make sense? Best, Slawomir. Dnia 21 sierpnia 2023 07:08 Marko Mäkelä < marko.makela(a)mariadb.com > napisał(a): On Sun, Aug 20, 2023 at 6:12 PM pslawek83 via discuss <discuss(a)lists.mariadb.org> wrote: Thanks everyone for the answers. Tested this PAGE_COMPRESSION a bit and make some reading. Not sure if I fully understand how that works... is that correct? 1. Impossible without punch hole as if we don't have it the server will just write partially used pages which will take full page size anyway. But in docs it seems it described its use with punch hole disabled. What's the reason to still have page compression without punch hole? Can you please point to the misleading part of the documentation so that it can be corrected? 2. After enabling compression each page needs to be trimmed using punch hole, so if we have 1 TB database and 64k blocks, the database files will have over 15 million fragments (1000000000000/64000) That is correct. So if we get like 50% compression and then we'll be able to put another 1TB of data into these holes we'll be having close to 31 million file fragments. I'm not sure if under such conditions the FS will be able to even allocate space efficiently anymore, copy these files, etc. Or im not getting something? For what it is worth, around the same time when the page_compressed was being developed for MariaDB Server 10.1, something similar was being worked on at Oracle, I think for MySQL 5.7. To my understanding, the same hardware partner worked with both companies. The obvious answer to smalldatum.blogspot.com https://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.… would have been en.wikipedia.org https://en.wikipedia.org/wiki/Fusion-io_NVMFS if it had worked without kernel panics or existed in a mainline kernel. I don’t even know if it ever was open sourced. 3. I was testing compression of ~1.5GB table with nice results on default 16kb page size. One thing i noticed is that l was always getting exactly the same compressed size using `du`. However the table read/copy times were different from 40s for uncompressed to 80-400 seconds depending on algo. Is that possible that is because page size is too low? As far as I remember, most compression algorithms that are derivatives of the LZ77 family have an input buffer size of 32 KiB. From that point of view, it would seem to make sense to use innodb_page_size=32k or innodb_page_size=64k instead of the default. Furthermore, I think that it was a mistake to implement bzip2 support at all. That algorithm would work with much larger buffer sizes. 4. Myrocks seems to be very slow compared to compressed inno. Was also reading docs related to it. For its usecase is it more suitable for storing large rows rather than many small rows? To my understanding, the LSM trees that MyRocks is built on work best in an insert-only workload. On top of LSM trees, MyRocks implements some caches and Bloom filters. If you have few updates or deletes and most reads are accessing recently inserted data, it could work. 5. I remember reading that some innodb compression is going to be obsolete. Is that row compression? Or there are no plans to obsolete any compression model? I wanted to deprecate and remove the ROW_FORMAT=COMPRESSED that I designed and implemented in the InnoDB Plugin for MySQL 5.1 based on some high level ideas of Heikki Tuuri between 2005 and 2007. jira.mariadb.org https://jira.mariadb.org/browse/MDEV-22367 was the first step towards that. Based on community feedback, this won’t be happening. Even after some data structure cleanup that took place in MariaDB Server 10.6, supporting the format incurs an overhead of some 32 to 40 bytes per buffer page descriptor, which is not insignificant. Removing both ROW_FORMAT=COMPRESSED and the adaptive hash index would shrink the block descriptor to 80 bytes from its current 10.6 size of 160 bytes. The write performance with ROW_FORMAT=COMPRESSED should have been degraded when I implemented the easier-to-parse log record format in MDEV-12353 (MariaDB Server 10.5). There are open bugs about ROW_FORMAT=COMPRESSED, such as jira.mariadb.org https://jira.mariadb.org/browse/MDEV-31574. I would say that the InnoDB ROW_FORMAT=COMPRESSED seemed like a good idea back when it was implemented, when HDD storage was the only practical option. Just like the InnoDB change buffer, which I removed in MariaDB Server 11.0 after having to deal with too many cases of corruption: fosdem.org https://fosdem.org/2023/schedule/event/innodb_change_buffer/ With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc

3 3

Re: Rocksdb as a replacement for toku?
by pslawek83 22 Aug '23

22 Aug '23

Thanks everyone for the answers. Tested this PAGE_COMPRESSION a bit and make some reading. Not sure if I fully understand how that works... is that correct? 1. Impossible without punch hole as if we don't have it the server will just write partially used pages which will take full page size anyway. But in docs it seems it described its use with punch hole disabled. What's the reason to still have page compression without punch hole? 2. After enabling compression each page needs to be trimmed using punch hole, so if we have 1 TB database and 64k blocks, the database files will have over 15 million fragments ( 1000000000000/64000) So if we get like 50% compression and then we'll be able to put another 1TB of data into these holes we'll be having close to 31 million file fragments. I'm not sure if under such conditions the FS will be able to even allocate space efficiently anymore, copy these files, etc. Or im not getting something? 3. I was testing compression of ~1.5GB table with nice results on default 16kb page size. One thing i noticed is that l was always getting exactly the same compressed size using `du`. However the table read/copy times were different from 40s for uncompressed to 80-400 seconds depending on algo. Is that possible that is because page size is too low? 4. Myrocks seems to be very slow compared to compressed inno. Was also reading docs related to it. For its usecase is it more suitable for storing large rows rather than many small rows? 5. I remember reading that some innodb compression is going to be obsolete. Is that row compression? Or there are no plans to obsolete any compression model? Thanks. Dnia 16 sierpnia 2023 16:08 Gordan Bobic via discuss < discuss(a)lists.mariadb.org > napisał(a): Larger pages will yield higher compression ratios, as will zstd (slightly slower than lz4). Mileage varies, but on some of my databases, between the 64KB pages (zfs recordsize) and zstd, my compression ratios approximately doubled, from about 1.8:1 to a little under 4:1. This will all heavily depend on the type of data you are storing, though. zstd has adjustable compression, so if you have tons of CPU to spare, you could turn it up from the default of 3 (probably optimal cost/size compromise) to high teens for a compression boost. On Wed, 16 Aug 2023, 14:34 Reinis Rozitis, < r(a)roze.lv > wrote: > From: Gordan Bobic via discuss < discuss(a)lists.mariadb.org > > > One of the best compromises is to configure InnoDB with 64KB pages, put it on ZFS with recordsize=64K, and enable zstd compression on ZFS. Sorry for hijacking the thread a bit. After the discontinuation of tokudb I chose the innodb+zfs route (bad previous/early experience with rocks, also million knobs to adjust for your workload etc) but at that time didn't think of increasing the Innodb page size to 64KB - do you have any (even rough) numbers of 16 vs 64KB (on zfs)? Also, what would be the benefit of zstd vs lz4? Higher compression ratio? rr _______________________________________________ discuss mailing list -- discuss(a)lists.mariadb.org To unsubscribe send an email to discuss-leave(a)lists.mariadb.org

3 2

Running events on same time
by kpesio＠gmail.com 17 Aug '23

17 Aug '23

Hi. Could someone confirm in some way about running multiple events at same time. Documentation at https://mariadb.com/kb/en/automating-mariadb-tasks-with-events/ states: "The event scheduler runs as a single thread. If any event is supposed to start while another event is running, its execution will be skipped." I have simulated events triggering at same time, running with delays to get all kinds of overlapping schedules, writing to same table from the event and not missed a single one. The information_schema.events tables column last_executed has - depending on scheduled time - valid values and I can see in the processlist events running at same time. Version I'm running is 10.6.10 on Linux. Tried to cross check against the MySQL documentation but couldn't find anything about this there. So what situation does the documentation refer to or it this something fixed long time ago?

3 5