- discuss - lists.mariadb.org

[Maria-discuss] Clarification on Memory utilization of Mariadb
by ragul rangarajan 10 Nov '21

10 Nov '21

Hi Team, Need clarification on Memory utilization of Mariadb. I have a java application with MariaDB that runs on the same Rhel 7 server with physical memory(RAM) allocated as 12GB where I would see the normal utilization at the start as below. *sys> free -h* * total used free shared buff/cache available* *Mem: 11G 7.8G 1.6G 9.0M 2.2G 3.5G* *Swap: 0B 0B 0B* Over a period of time say >6month, I could see the RAM utilization is almost full, and available space is very less but still, the application continues to work fine without any issues. *sys> free -h* * total used free shared buff/cache available* *Mem: 11G 11G 134M 17M 411M 240M* *Swap: 0B 0B 0B* I don't see any OOM issue or high usage of RSS at the java application level but I could see MariaDB occupies huge memory.... apart from that overall application runs fine. To ensure this we observed memory utilization at the large-scale application servers where we deployed MariaDB and Java applications into different servers with 128GB each and could observe that the system with MariaDB uses full RAM size whereas the Java application consumption looks stable. *Server with Java application* sys> free total used free shared buff/cache available Mem: 128755 125171 606 118 2977 2694 Swap: 65535 9746 55789 *Server with MariaDB* sys> free total used free shared buff/cache available Mem: 128755 125171 606 118 2977 2694 Swap: 65535 9746 55789 *MariaDb conf:* innodb_buffer_pool_size=102G innodb_buffer_pool_instances=8 innodb_io_capacity=3000 innodb_log_buffer_size=8M innodb_log_file_size=2047MB innodb_log_files_in_group=2 Is this expected that MariaDB consumes overall memory and manages within RAM size.... regardless of buffer size configured? Do we need to optimize any configuration to control the MariaDB consumption apart from buffer pool size? Thanks and Regards, Ragul

3 3

[Maria-discuss] Troubleshooting Statement That Crashes MariaDB (mysqld got signal 11)
by Michael Caplan 04 Nov '21

04 Nov '21

Hello, I have a nothing fancy SQL statement that crashes MariaDB 10.3.31. The error log reports "mysqld got signal 11" with the below included mini core dump. The statement caused no issues with my prior install of 10.2.xx. With 10.3, the statement runs the majority of the time. The vast majority of the time. I have never dealt with such an issue with MariaDB, and am unsure where to start with troubleshooting this. Any tips? FYI, there is a corresponding bug recorded here: https://jira.mariadb.org/browse/MDEV-26835 Thanks, Mike 211014 16:13:53 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.3.31-MariaDB-1:10.3.31+maria~focal-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=587 max_threads=752 thread_count=81 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1784362 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f13e83b3768 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f1644e96dd8 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x32)[0x55d5a2f6f3a2] /usr/sbin/mysqld(handle_fatal_signal+0x55d)[0x55d5a2a46e8d] /lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7f27569fc3c0] /usr/sbin/mysqld(+0x978f4f)[0x55d5a2bdbf4f] /usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x50)[0x55d5a2a4c6d0] /usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3e)[0x55d5a2b7cb2e] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x18e)[0x55d5a28869be] /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xbee)[0x55d5a28b0dce] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x37)[0x55d5a28b1137] /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf2)[0x55d5a28b1282] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14b)[0x55d5a28b1bcb] /usr/sbin/mysqld(+0x5de9f1)[0x55d5a28419f1] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5414)[0x55d5a284f6e4] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1f3)[0x55d5a2852253] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x105d)[0x55d5a285462d] /usr/sbin/mysqld(_Z10do_commandP3THD+0x12d)[0x55d5a28565dd] /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x246)[0x55d5a293f186] /usr/sbin/mysqld(handle_one_connection+0x3f)[0x55d5a293f35f] /lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7f27569f0609] /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f2756915293] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f13e84a8b70): SELECT * FROM (( # # get postings from last statement and postings not on statements # SELECT a.*, COALESCE(ci.id, i.id) as invoice_id FROM ddx_lab_801317.postings a LEFT JOIN ddx_lab_801317.cases_invoices ci ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id LEFT JOIN ddx_lab_801317.invoices i ON a.ref_type = 'INVOICE' AND a.ref_id = i.id WHERE ( a.statement_date IS NULL OR a.statement_date = '2021-09-27' ) AND a.practice_id='19686' ORDER BY post_date ) UNION ( # # get postings that have remaining balances # SELECT DISTINCT a.*, COALESCE(ci.id, i.id) as invoice_id FROM ddx_lab_801317.postings a LEFT JOIN ddx_lab_801317.cases_invoices ci ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id LEFT JOIN ddx_lab_801317.invoices i ON a.ref_type = 'INVOICE' AND a.ref_id = i.id LEFT JOIN ddx_lab_801317.postings b ON a.ref_type = b.ref_type AND a.ref_id = b.ref_id AND a.id != b.id WHERE NOT (a.type = 'CREDIT' OR a.type = 'PAYMENT') AND ((SELECT SUM(b.amount)) IS NULL OR (SELECT SUM(a.amount)) + (SELECT SUM(b.amount)) > 0) AND a.practice_id='19686' GROUP BY a.id ORDER BY post_date ) UNION ( # # get partial payments and credits from older statements # SELECT a.*, COALESCE(ci.id, i.id) as invoice_id FROM ddx_lab_801317.postings a LEFT JOIN ddx_lab_801317.cases_invoices ci ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id LEFT JOIN ddx_lab_801317.invoices i ON a.ref_type = 'INVOICE' AND a.ref_id = i.id WHERE ( a.statement_date < '2021-09-27' ) AND (a.type='PAYMENT' OR a.type='CREDIT') AND a.practice_id='19686' )) AS t1 ORDER BY post_date ASC, invoice_id ASC Connection ID (thread ID): 21014956 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes unlimited unlimited processes Max open files 1048576 1048576 files Max locked memory unlimited unlimited bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 514974 514974 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core

2 2

[Maria-discuss] Incorrectly formatted XML files in mariadb-server sources
by Zuzana Miklankova 26 Oct '21

26 Oct '21

Hi, Fedora's rpminspect tool reports [1] some incorrectly formatted XMLs (most of them used for testsuite) within mariadb source files, namely they are: mysql-test/plugin/sphinx/sphinx/testdata.xml - undefined namespace mysql-test/std_data/loadxml.dat - different parser errors, seems to me like it is ill formed on purpose of the test mysql-test/plugin/connect/connect/std_data/nocs.xml - not proper utf-8 encoding mysql-test/std_data/loaddata/mdev9874.xml - not proper utf-8 encoding sql/share/mariadb/charsets/Index.xml - redefined attribute mysql-test/std_data/ldml/Index.xml - redefined attribute It does not seem (to me) that the ill-formatting breaks anything, and that in some cases it is desired. Specifically the loadxml.dat XML, where the test case counts with the form as it is, and Index.xml files, where modification to the XML format could change the meaning of the file. In other cases, it seems (to me) that the correction of the XMLs to a valid format could be possible. These are specifically the XMLs with encoding issues, where it should be sufficient to specify a used charset (ISO-8859-1 in both cases). This is also the case with the undefined namespace in testdata.xml, whose definition should be also possible. Would the correct approach to this issue be to suppress the rpminspect warnings, or should the specified XML files be modified to a valid XML form? Thanks for your opinions in advance. [1] https://osci-jenkins-1.ci.fedoraproject.org/job/fedora-ci/job/rpminspect-pi… Zuzana Miklánková Associate Software Engineer Red Hat <https://www.redhat.com> <https://www.redhat.com>

2 2

[Maria-discuss] MariaDB 10.4 shows a 2e308 cost on a optimization plan correctly preferred by 10.0
by David Strauss 25 Oct '21

25 Oct '21

I'm seeing a regression in query optimization after testing a database+query that was running well on MariaDB 10.0. The issue occurs on an installation of 10.4.18. Version 10.4 considers the plan that 10.0 used but dismisses it as having a cost of 2e308 [1]. The plan 10.4 chooses is highly non-optimal. Based on reviewing some source code, it looks like this sort of "max" cost should only result if summing/multiplying other costs would exceed that maximum. The usual steps of rebuilding tables and index statistics don't help, especially because the JSON output for the optimization plan already shows reasonable index use costs. (The 2e308 cost only starts appearing in a later section.) Because 10.0 lacks the same optimization tracing as 10.4, here is the EXPLAIN output for comparison [2]. Hopefully, there's just something simple amiss and posting here helps anyone else who encounters a 2e308 cost on query optimization plans. I tried to see if this may be fixed in 10.5+, but I haven't found much discussion online of this phenomenon. [1] https://gist.github.com/davidstrauss/becb03fa75ecac046cafe6f05da9f869#file-… [2] https://gist.github.com/davidstrauss/becb03fa75ecac046cafe6f05da9f869#file-…

1 0

[Maria-discuss] State of "alternative" engines and mariadb
by pslawek83 19 Oct '21

19 Oct '21

Hi Guys, what's the state of "alternative" engines (aria and rocks). AFAIR there were some plans to make aria much more advanced than myisam, transactional (transaction safe?) and more optimized for todays hardware. What's the status of RocksBD, basically on the mariadb website i can't find recent updates and most of sources on the internet are outdated. Is it 100% compatible with mariadb nowdays, is there a possibility to have "one-file-per-table" (one set of files) or at least per-database? If everything resides in shared space is there a possibility that one write error (eg. resulting from power loss) can make all rocks tables unreadable? Is it possible to efficiently drop eg. 100GB table partition if the table is using rocks? Last time i checked there were some issues with compaction for Rocks (some limit on DB size, which was rather small can't recall this currently). Is that resolved, and how much additional space is required to compact eg. 1tb database? How's the adoption and developement of both engines going? Are people using it instead of myisam/toku/inno? Is there a lot of effor put into making aria/rocks better? As i'll have to update many servers in the future what's generally the preferred way to go? Drop everything and just use innodb like Oracle did or move towards rocks/aria for everything which doesn't play with innodb well? Another interesting topic is that myisam is having performance issues because of spectre/meltdown mitigations and it seems to hit this engine the most (at least in 10.3, 10.4 as far as I recall). Are there some plans to optimize it for new CPUs (maybe it was already done) or all the effors are going into different storage engines and i should abandon myisam long term? Thanks

2 1

[Maria-discuss] Loading a backup created from 10.6.4 into newly installed 10.6.4 causes mysql.proc to appear corrupt
by Jeff Dyke 01 Oct '21

01 Oct '21

Failing process: Install mariadb from ppa using apt (happens through salt, fwiw) Download last night's backup from a 10.6.4 server, which is a replica from a galera cluster. run the script attached restore_mariabackup.sh Once this is done the database itself works, for our application, but any client trying to access anything that requires mysql.proc gets an error similar to: *Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: error : CorruptSep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: sys.x$schema_table_statistics* *Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: Error : Cannot load from mysql.proc. The table is probably corrupted * I just built two new servers, one that builds mariadb and then overwrites the data from a backup and one that just installs mariadb. The two different files base_install.proc.sql and backupedup.proc.sql. The error output from sudo mysql_upgrade on the server that received the backup is in mysql_upgrade.out There are more differences in the proc table if i dump the data as well, but i think much of it is driven by the utf8_mb4 rather than 3. Tried to keep this sucient and use attachments. This process has worked for me for until i upgraded to 10.6.4. Please ask anything you need. I think i've been using this similar process to build dev machines since about 10.4 Thanks Jeff

1 2

[Maria-discuss] A general question about performance of update command
by Lehrbass, Robert 27 Sep '21

27 Sep '21

Hi there, I'm new to MariaDB and I'm running tests on the update command. Server version 10.5.11. I'm finding that updates to a table with engine=columnstore are far slower than to a table with default engine=innodb. Obviously, there is no primary key in a table with engine=columnstore, whereas the innodb table does have a primary key. In my tests, for engine=columnstore, 10000 rows took 1995 seconds to be updated, but for engine=innodb, 10000 rows took only 7 seconds to transfer. The difference suggests columnstore takes about 285X longer than innodb. Is this to be expected? I don't need a lengthy answer, btw, just general guidance. Thanks very much, Rob

1 0

[Maria-discuss] DBSeeder Version 3.0.3
by Walter Weinmann 13 Sep '21

13 Sep '21

We are pleased to release version 3.0.3 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder) DBSeeder also supports the latest version of MariaDB Server available on Docker Hub (DBMS 10.6.4-focal) and Maven Repository (JDBC 2.7.4). [image: Perf_Snap_3.0.3_win10.png]

2 2

[Maria-discuss] AWS Key management plugin key rotation in replication
by Reinis Rozitis 30 Aug '21

30 Aug '21

Hello, I wanted to clarify - does the key rotation (with AWS KMS backend) work in replication scenario? As in do the slaves (somehow) get also notified and perform the re-encryption if the key is rotated (on master) in background or you have to use different key for each slave for rotation to work? In https://mariadb.com/kb/en/encryption-key-management/#key-rotation it wasn't mentioned. wbr rr

2 3

[Maria-discuss] Issues with Upgrading from MariaDB 10.2.22 to 10.5.12
by Michael Caplan 26 Aug '21

26 Aug '21

Hi there. I'm going through an upgrade process I have done before with earlier versions of mariaDB and Mysql, but running into an issue. My goal is to create a new slave including upgrade process from MariaDB 10.2.22 (serverOld) to 10.5.12 on a new server (serverNew) (as inspired by the brighter mind at https://www.stephenrlang.com/2016/08/setting-up-mysql-master-slave-replicat… ) 1. rsync /var/lib/mysql from serverOld to serverNew 2. On serverOld: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 3. rerun rsync from serverOld to serverNew 4. Release read lock from serverOld 5. Start mariadb on serverNew 6. run mysql_upgrade on serverNew 7. Celebrate and have a nap In actual practice step #5 failed numerous times before what I think is now an okay serverNew # Innodb settings between serverOld and serverNew #serverOld innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 48M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 128M #serverNew innodb-flush-method = O_DIRECT innodb-log-file-size = 48M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 128M The only difference is innodb-log-files-in-group setting is removed in serverNew, as it has been depricated and removed in 10.5 # Upgrade after crash? When attempting to start serverNew, it would fail with the following error: [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.2.22. [ERROR] InnoDB: Plugin initialization aborted with error Generic error the log file on serverOld showed no evidence of crashing. None the less, I restarted serverOld, and reran the process above. Still the same issue step #5. # Remove ib_logfile* files I then tried forcing the recreation of the redo logs by removing them and then starting up serverNew. This resulted in the following scary errors: [ERROR] InnoDB: Page [page id: space=0, page number=1984] log sequence number 285722576186 is in the future! Current system log sequence number 259573448177. [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. # innodb_fast_shutdown = 0 The next attempt started with serverOld being restarted and innodb_fast_shutdown = 0 being set beforehand. I reran the above steps and still got stuck on step #5 with the following error: [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.2.22. [ERROR] InnoDB: Plugin initialization aborted with error Generic error This time, however, when I removed the ib_logfile* files, we seemed to have an okay startup: [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 [Note] InnoDB: Completed initialization of buffer pool [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 [Note] InnoDB: New log file created, LSN=288830457728 [Note] InnoDB: 128 rollback segments are active. [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" [Note] InnoDB: Creating shared tablespace for temporary tables [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... [Note] InnoDB: File './ibtmp1' size is now 12 MB. [Note] InnoDB: 10.5.12 started; log sequence number 0; transaction id 3037161218 [Note] Plugin 'FEEDBACK' is disabled. [Note] Recovering after a crash using /var/lib/mysql/mysql-bin [Note] Starting crash recovery... [Note] Crash recovery finished. [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool [Note] Server socket created on IP: '::'. [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. [Note] Reading of all Master_info entries succeeded [Note] Added new Master_info '' to hash table [Note] /usr/sbin/mariadbd: ready for connections. Version: '10.5.12-MariaDB-1:10.5.12+maria~focal-log' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution Running mysql_upgrade completed without issue. I'm not feeling confident that this upgrade is actually okay. removing the ib_logfile* seems questionable as a requirement to get the upgrade done. Anyone have similar troubles? Recommendations on what my process should be? Thanks, Mike

3 3