- discuss - lists.mariadb.org

[Maria-discuss] Checkpoint Age and Redo Log Flushing in 10.5+
by Gordan Bobic 14 May '23

14 May '23

Ever since 10.5, the redo log flushing seems to have become ... suboptimal. It looks like the database does nothing at all to flush the logs until it hits the hwm, and then it goes into overdrive and flushes everything to tablespaces fast, before it goes back to doing nothing until the log fills up. This causes a problem when the redo log is already full and a large burst of writes arrives because it overwhelms the redo log and causes a stall. Is there a bug fix for this or a configuration change available to restore the old behaviour where everything gets flushed continuously and switches up a gear when the hwm is reached? Here are the screenshots comparing 10.5+ sawtooth pattern vs 10.4 and older versions.

2 1

[Maria-discuss] howto c-connect bindings
by juerg oehler 06 May '23

06 May '23

hi I'm working with|mariadb|and|c-connect|prepared statements and run into crashes executing them once a while. i so addressed my problem to stakeoverflow <https://stackoverflow.com/questions/76170354/mariadb-howto-c-connect-bindin…> hoping to get more information for binding programs data structure to db interface. i did get an answer. but this answer does not go inline with your documentation <https://mariadb.com/kb/en/mariadb-connectorc-data-structures/#mysql_bind>. i so do have the option to follow your documentation or use the answer and run into crashes in either way. you might ask why i'm not just going with your documentation? your documentation is far to thin to get a reliable idea how bind structure is used and i do get proofed this by crashes. second i played with <my_bool * is_nulll Pointer which indicates if column is NULL (not used for parameters)> for parameters and behavior was not as documented. this is my motivation to bring my problem to this mailing list hopping someone can point me to a documentation i can rely on. i found a short and useful documentation in mysql with permissible in and out types <https://dev.mysql.com/doc/c-api/8.0/en/c-api-prepared-statement-type-codes.…> and statement data structures <https://dev.mysql.com/doc/c-api/8.0/en/c-api-prepared-statement-data-struct…>. can i use these informations to build 2 test applications one focusing on parameter (attachment) one focusing on result? currently i implemented MariaPrepUpd.c using mariadb documentation. i felt like flying blind and ended up in crashes - once a while. do you think i could use mysql documentation for improving bindings? or do you have a better option? (i studied your bulk example <https://mariadb.com/kb/en/bulk-insert-row-wise-binding/>. i even was using this example as base for first try an hit the wall by using STMT_INDICATOR_NTS. i then learned my problem is not just the example using STMT_ATTR_ARRAY_SIZE = 1.) my goal is to have a version of MariaPrepUpd.c i can use for any application for iteration 1 to n. thank you for short answer. have a great day. juerg(=george) oehler

1 0

[Maria-discuss] mysql.innodb-table-stats-and mysql.innodb-index-stats are not updating properly
by ragul rangarajan 21 Apr '23

21 Apr '23

Hello all, I am facing a weird issue with my DB table where its Index and table stats not getting updated with values automatically in MariaDB 10.6.7 The table has more than a billion records. On querying the stats of the table, I could not see any stats for the table that begin automatically calculated until we do a mysql restart or ANALYZE TABLE. Once triggered we can able to see the index values are calculated and updated in this table 127.0.0.1:3307> SELECT * FROM mysql.innodb_table_stats ;+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+| DB | TableStats#P#p2023_04_11_13_00_00 | 2023-04-11 12:00:11 | 0 | 1 | 0 || DB | TableStats#P#p2023_04_11_14_00_00 | 2023-04-11 13:00:10 | 0 | 1 | 0 || DB | TableStats#P#p2023_04_11_15_00_00 | 2023-04-11 14:00:04 | 0 | 1 | 0 || DB | TableStats#P#p2023_04_11_16_00_00 | 2023-04-11 15:00:04 | 0 | 1 | 0 |+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+ 127.0.0.1:3307> SELECT * FROM mysql.innodb_index_stats;+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+| DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | n_diff_pfx01 | 0 | 1 | AgentId || DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | n_diff_pfx02 | 0 | 1 | AgentId,rackNr || DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index || DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | size | 1 | NULL | Number of pages in the index |+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+ As per the documentation https://mariadb.com/kb/en/innodb-persistent-statistics/, enabling *innodb_stats_auto_recalc* parameter should update the index value automatically which was not happening for me even though the changes are 10% of the row got affected within few days. The same was working for other DB table which is of lower load. Is there any way to debug why the calculation was not updated properly with values or any way to reduce the sample size? *Thanks and Regards,* Ragul R

2 2

[Maria-discuss] DELETE query hanging on Statistics (10.6.12)
by mariadb＠Biblestuph.com 14 Apr '23

14 Apr '23

With this table: CREATE TABLE t1 ( label varchar(100) NOT NULL, dt datetime NOT NULL, success tinyint(1) NOT NULL, PRIMARY KEY (label,dt), KEY dt (dt), KEY success (success,dt) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC I have a DELETE query that has been stuck in Statistics for nearly an hour now: DELETE MyDB.t1 FROM MyDB.t1 WHERE dt <= '2023-02-21 08:06:46' AND dt >= '2023-02-21 08:03:48' This statement runs several times per hour to trim the table (and has for years). It has run fine against my 10.6.12 DB (this is a downstream replica DB) for a couple weeks now(when I upgraded the replica); but suddenly today produced the hang. Any ideas why? The table has nearly 15 million rows; but the rows are more or less evenly spaced across datetimes. Also I find that I cannot kill the query using KILL 12345; the KILL statement just comes back immediately without reporting an error and the statement continues. Is there no way to kill this statement without crashing the DB? Again, this is a replica statement that is executing. I had planned to stop the query, execute an ANALYZE TABLE t1 and then try it again, but I don't seem to be able to kill the query. Thank you! Dan

3 5

[Maria-discuss] Privilege Question
by Scott Canaan 06 Apr '23

06 Apr '23

We are on MariaDB 10.5.18. There is a requirement to send all syslog data to a central syslog server. In the past, we did it using a login called ITS_READ. It has limited privs on purpose, but used to be able to execute the SET GLOBAL statements that we needed. Those statements are: SET GLOBAL server_audit_output_type=SYSLOG; SET GLOBAL server_audit_logging=1; SET GLOBAL server_audit_syslog_facility=LOG_LOCAL2; SET GLOBAL server_audit_events="connect,table,query_ddl,query_dcl"; Now, it doesn't have the privs needed to do that. In looking online, I found that there is a "new" privilege (in MySQL 8), called system_variables_admin that allows the user to execute SET GLOBAL statements. However, when I try to grant that privilege, I get an error: MariaDB [(none)]> grant system_variables_admin to 'ITS_READ'@'localhost'; ERROR 1959 (OP000): Invalid role specification `system_variables_admin` It seems that the only other option is to grant the SUPER privilege, which we don't want to do. How do I get around this, or do I just tell the sysadmins they can't have the syslog data? 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 3

[Maria-discuss] Disable validation for `DATETIME` columns
by William Edwards 03 Apr '23

03 Apr '23

Hi, Is it possible to disable validation for `DATETIME` columns? Context: A customer uses the Europe/Amsterdam session time zone. Therefore, they cannot insert dates during the hour in which DST starts and ends. Using the UTC session time zone would solve the issue. However, they also use auto-converting `TIMESTAMP` columns. Switching to a different session time zone would cause issues with those. Relaxing constraints with `sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES` does not suffice. With kind regards, William Edwards

2 3

[Maria-discuss] Column named 'row_number' gives error in 10.11
by Keith Edmunds 02 Apr '23

02 Apr '23

I have a table that includes an int column named row_number. This worked fine until I upgraded Debian from 11 to 12 which upgraded MariaDB from 10.5 to 10.11. I now get errors when referencing that column for updates. If I put backticks around the column name the update runs without error. Is this an expected behaviour change from 10.5 → 10.11? Example: CREATE DATABASE test; USE test; CREATE TABLE `kae` ( `id` int(11) NOT NULL, `row_number` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `kae` (`id`, `row_number`) VALUES (1, 0); UPDATE kae SET row_number=12 WHERE id=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'row_number=12 WHERE id=1' at line 1

4 8

[Maria-discuss] Mounting tmpdir via NFS
by Simon Avery 29 Mar '23

29 Mar '23

Hello, I have a question that I've been struggling to get a clear answer to, or even any indication someone has done this before, and would appreciate any experience or thoughts. Are there any known issues with defining tmpdir on multiple servers to point to a single NFS-mounted network share? Would each instance co-habit peacefully and not try to change any files from another instance? Why do I want to do this? I have around 80 Centos/Rocky vms which run Mariadb 10.6.x. Due to the nature of the work they do, some of them require a significant amount of tmpdir (up to 75Gb) relatively briefly (once or twice a day for a few minutes) This growth is not always predictable, so generally the first I know about it is when Mariadb reports lowspace and the query stops. We then grow the /tmp partition that is defined as tmpdir and if we're quick enough, the query completes, but often the job dies. Then that extra space stays locked away forever, even if that server never tries to use that much tmpdir again. This does not feel efficient and I am pondering better ways of doing this. One such thought would be to have a single HA NFS share on our network, mounted onto each VM as something like /mariadb-tempdir, and set $tmpdir to it. In my mind, this would make a better use of one large allocation of space used by many things often, rather than lots of individual small to large sized bits of space used by one thing, rarely. The chance of two or more servers filling that space feels small, but if it happened, one would wait until space was cleared - which might actually happen in this shared scenario when it never does in a single-server situation. In my particular world, we have fast storage and a fast network. Speed tests on large and tiny files over NFS give surprisingly similar performance to writing to local vm disks, so I'm not worried about the overhead of NFS and even if it was, slow might still be preferred to full. Thank you, Simon

3 5

[Maria-discuss] MariaDB Online-DDL
by Rhys.Campbell＠swisscom.com 17 Mar '23

17 Mar '23

Hello All, I'm back working with MariaDB again after a break of some years. While InnoDB Online-DDL had appeared when I was working with it, it was still sometimes a challenge to manage schema changes for very large tables. https://mariadb.com/kb/en/innodb-online-ddl-overview/ Can anyone summarize how InnoDB stacks up for Online-DDL now? Is it comparable to MSSQL (which I found to be pretty good for tables in the many tens of GB)? Are workarounds like pt-online-schema-change and blue/green deployments still required? Are there any specific areas to watch out for? Any feedback appreciated. Cheers, Rhys

2 1

[Maria-discuss] can not kill process
by Radisson97＠gmx.de 17 Mar '23

17 Mar '23

hi i have a strange effect a simple insert got stuck (why ?) i tried to kill but no success, it is even marked as "killed" (see below) 1398867 kpc localhost kpc 1716 Killed INSERT INTO i can kill other processes- my system: 10.5.15-MariaDB engine aria 5.10.0-14-amd64 any ideas what may the problem ? (no usefull entries in any log, as you expected ) re,

3 4