On Linux Slackware PCs, I have a 'Source' database on one PC, and a 'Replica' on the other. On the Source end (11.4.7-MariaDB) I've programmed ID=1. On the Replica end (10.11.6-MariaDB), I set ID=11 and set the Source IP. I've started them, and they seem to like each other, as the Show replica status reports everything as Ok, no errors, comms ok, etc... BUT, when I create a new table on the 'Source' machine, nothing happens on the 'Replica' - no new table is created. And, after I manually create the table on the Replica, and I insert a record on the Source, no data transfer happens either. No pointers are incremented, seemingly no data is transferred. And no errors seem to be appearing in the logs. Here are the status reports for Source and Replica. I dispair, I consulted AI, and after several sessions, AI was in the same state as I was... repeatedly asking me to insert new records at the Source and checking at the other end, maybe hoping something would repair itself. Also suggesting extra complications such as adding SSH, Gtid and other optionals. What can I do to get the show on the road? I've reinstalled MariaDB at the Replica. I'm somewhat reticent in changing the DB at the Source, as it's in use. I've checked permissions on files and directories... Last session ended at 04:30 AM this morning. **source** status:

show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000020
        Position: 328
    Binlog_Do_DB: homeorptemp, farma1, test_replication
Binlog_Ignore_DB: 
1 row in set (0.000 sec)

**replica** status:

slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.102
                   Master_User: my_repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000020
           Read_Master_Log_Pos: 328
                Relay_Log_File: mysql-relay-bin.000007
                 Relay_Log_Pos: 627
         Relay_Master_Log_File: mysql-bin.000020
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 328
               Relay_Log_Space: 1235
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 1
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
          Replicate_Rewrite_DB: 
1 row in set (0.001 sec)

**EDIT** my.cnf at the replica:

[client]
[mysqld]
port                    = 3306
socket                  = /var/run/mysql/mysql.sock
skip-external-locking
bind-address            = 0.0.0.0
key_buffer_size         = 256M
max_allowed_packet      = 32M
table_open_cache        = 1024
sort_buffer_size        = 2M
net_buffer_length       = 8K
read_buffer_size        = 512K
read_rnd_buffer_size    = 1M
skip-networking         = OFF
server-id               = 11
log-bin                 = mysql-bin
binlog_format           = mixed
innodb_buffer_pool_size = 512M
innodb_log_file_size    = 256M
read_only               = OFF
log_warnings		= 1
log_slave_updates 	= ON

[mysqldump]
quick
max_allowed_packet     = 32M

[mysql]
no-auto-rehash

[myisamchk]

show processlist;
+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command   | Time  | State                                                  | Info             | Progress |
+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+
|  5 | system user |           | NULL | Slave_IO  | 17725 | Waiting for master to send event                       | NULL             |    0.000 |
|  6 | system user |           | NULL | Slave_SQL | 17724 | Slave has read all relay log; waiting for more updates | NULL             |    0.000 |
|  8 | root        | localhost | NULL | Query     |     0 | starting                                               | show processlist |    0.000 |
+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+
3 rows in set (0.000 sec)

I inherited an issue where a MariaDB Galera Cluster has only one out of 3 nodes working. Node1: up and running in production and is accepting reads and writes Node2: fresh install it just need to sync with node 1 Node3: fresh install it just need to sync with node 1 If I run service mysql start on Node2/Node3 it changes the state of wsrep_local_state_comment from Synced to Donor/Desynced and a SST is triggered but I will have a lot of downtime. I have a 2TB of databases and it's going to take about 5-7 hours for a newly added node to sync, and I can't afford the downtime (as an idea, during the day I have 1GB/minute of binlogs and at night about 4GB/hour of binlogs). I take a db backup every night with Percona XtraBackup and I know how to restore it on a fresh server. I have galera.cache set at 32GB. Is there a way of restoring the other 2 nodes without triggering an SST? I was thinking of restoring the backup of Node1 to Node2 maybe it will trigger an IST.

/var/log/system.log says (over and over)

Oct 13 19:34:01 Data-Server com.apple.xpc.launchd (com.mariadb.server): Service exited with abnormal code: 1
Oct 13 19:34:01 Data-Server com.apple.xpc.launchd (com.mariadb.server): Service only ran for 0 seconds. Pushing respawn out by 10 seconds.

/usr/local/var/mysql/Data-Server.local.err says (once recently, repeated a number of times well before the crash)

2020-10-13  2:44:25 20019181 [Warning] Aborted connection 20019181 to db: 'EcoReality' user: 'root' host: '10.1.2.2' (Got timeout reading communication packets)

First thing I did was to shutdown the launchctl entry, to keep it from constantly restarting. # launchctl unload /Library/LaunchDaemons/com.mariadb.server.plist Then I tried invoking mysqld manually:

# sudo  /usr/local/bin/mysqld -u mysql
2020-10-13 20:46:09 0 [Note] /usr/local/bin/mysqld (mysqld 10.4.6-MariaDB) starting as process 2364 ...
2020-10-13 20:46:09 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-13 20:46:09 0 [Note] InnoDB: Uses event mutexes
2020-10-13 20:46:09 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-13 20:46:09 0 [Note] InnoDB: Number of pools: 1
2020-10-13 20:46:09 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-10-13 20:46:09 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-10-13 20:46:09 0 [Note] InnoDB: Completed initialization of buffer pool
2020-10-13 20:46:09 0 [ERROR] InnoDB: Invalid log block checksum. block: 81635496 checkpoint no: 2609153 expected: 296846624 found: 3735928559
2020-10-13 20:46:09 0 [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 41797373564 between the checkpoint 41797373564 and the end 41797373440.
2020-10-13 20:46:09 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-10-13 20:46:09 0 [Note] InnoDB: Starting shutdown...
2020-10-13 20:46:09 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-10-13 20:46:09 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-13 20:46:09 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-10-13 20:46:09 0 [Note] CONNECT: Version 1.06.0009 January 27, 2019
2020-10-13 20:46:09 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2020-10-13 20:46:09 0 [ERROR] Aborting

So now I'm a bit stumped at the lack of diagnostic messages. Is there any way to coax more info out of mysqld when it goes down? Or should I just start incrementing innodb_force_recovery until something interesting happens?

I'm running MariaDB 11.2 in Docker Swarm in Ubuntu 20.04 and its memory usage keeps growing until database is killed by OOM killer. According to MySQLTuner, max memory should be around 7.1GB, but after 1-2 days memory usage goes up to 30GB. So when new task is started on server and there are not enough memory, the OOM killer select mariadbd and kills it. Server: - Ubuntu 20.04.1 (5.4.0-169-generic Kernel) - 64 GB RAM - Docker 24.0.6 - MariaDB 11.2.3 (in container) - Also Apache 2 (in container) Here's docker-compose.yaml:

version: "3.8"
services:
    database:
        image: mariadb:11.2
        deploy:
            restart_policy:
                condition: any
        networks:
            - database
        ports:
            - "3306:3306"
        ulimits:
            memlock: 2097152 # 2MB
        volumes:
            - "db_data:/var/lib/mysql"
            - "db_log:/var/log/mysql"
            - "/etc/localtime:/etc/localtime:ro"
            - "/etc/timezone:/etc/timezone:ro"
            - "./my.cnf:/etc/mysql/my.cnf"

networks:
    database:
        driver: overlay
        attachable: true
        name: database
...

Here's my.cnf:

[server]
table_definition_cache = 2048
innodb_buffer_pool_size = 6G
innodb_log_file_size = 1536M
innodb_log_buffer_size = 256M

log_error=/var/log/mysql/error.log

[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/mariadb.conf.d/
!includedir /etc/mysql/conf.d/

*Includes are from my.cnf template.* Content of /etc/mysql/mariadb.conf.d/:

# 05-skipcache.cnf:
[mariadb]
host-cache-size=0
skip-name-resolve

# 50-server.cnf:
[mariadbd]
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr

expire_logs_days        = 10

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

Directory /etc/mysql/conf.d/ is empty. ## What I tried? ### Update database I had same problem with 10.6. version. So I tried upgrading to 11.2. but that didn't fix the problem. ### Performence schema I enabled performence schema with memory instruments.

...
performance_schema = ON

performance-schema-instrument='memory/%=COUNTED'
...

Sum from sys.x$memory_global_by_current_bytes is around 7.1GB. But memory used by mariadbd process is much higher. ### Incresse memlock in container to 2MB I found warning in mysql/error.log:

2024-02-11 21:09:42 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOMEM: try larger memory locked limit, ulimit -l, or https://mariadb.com/kb/en/systemd/#configuring-limitmemlock  under systemd (262144 bytes required)
2024-02-11 21:09:42 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF

So I increesed memory lock limit to 2MB. Warning is gone, but memory problem still persists. --- ## Update 1 I had to reduce innodb_buffer_pool_size to 1GB to reduce the number of crashes. I also adjusted innodb_log_file_size to 256MB. MySQLTuner output when mariadb takes up 20GB of RAM (total system usage 48GB - 70%):

...

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 21h 59m 30s (142M q [564.348 qps], 336K conn, TX: 531G, RX: 107G)
[--] Reads / Writes: 80% / 20%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 2.0G
[--] Other process memory: 0B
[--] Total buffers: 1.5G global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.6G (2.59% of installed RAM)
[OK] Maximum possible memory usage: 2.0G (3.11% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (612/142M)
[OK] Highest usage of available connections: 25% (38/151)
[OK] Aborted connections: 0.00% (0/336034)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (113 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 1341
[OK] Temporary tables created on disk: 4% (16K on disk / 358K total)
[OK] Thread cache hit rate: 99% (38 created / 336K connections)
[OK] Table cache hit rate: 99% (143M hits / 143M requests)
[OK] table_definition_cache (2048) is greater than number of tables (441)
[OK] Open file limit used: 0% (32/32K)
[OK] Table locks acquired immediately: 100% (468 immediate / 468 locks)

...

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :128.0M
[--]  +-- KB Used Size :23.3M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 1.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 1.0G / 34.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 256.0M * 1/1.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 64 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.34% (42353558185 hits / 42633926388 total)
[OK] InnoDB Write Log efficiency: 91.45% (130133891 hits / 142298603 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12164712 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/2.6M
[OK] Aria pagecache hit rate: 99.9% (507M cached / 385K reads)

...

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    You are using an unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size 
    Performance schema should be activated for better diagnostics
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    performance_schema=ON
    innodb_buffer_pool_size (>= 34.4G) if possible.

--- ## Update 2 I found warning in log file:

[Warn] [Entrypoint]: /sys/fs/cgroup/pids:/docker/
11:net_cls,net_prio:/docker/
10:cpuset:/docker/
9:memory:/docker/
8:cpu,cpuacct:/docker/
7:hugetlb:/docker/
6:freezer:/docker/
5:devices:/docker/
4:blkio:/docker/
3:rdma:/docker/
2:perf_event:/docker/
1:name=systemd:/docker/
0::/docker//memory.pressure not writable, functionality unavailable to MariaDB

We a running an instance of Apache syncope with around 130k users (ca. 33k with active or pending state). When searching for users by attributes we see long query times often over 50 seconds. One example query might be

select * from user_search_attr 
where schema_id = 'familyName' 
and stringvalue = 'Bergmann'

user_search_attr is a view defined as follows:

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW user_search_attr AS
select
    u.id AS any_id,
    attrs.schema_id AS schema_id,
    attrs.booleanvalue AS booleanvalue,
    attrs.datevalue AS datevalue,
    attrs.doublevalue AS doublevalue,
    attrs.longvalue AS longvalue,
    attrs.stringvalue AS stringvalue
from
    (SyncopeUser u
left join JSON_TABLE(coalesce(u.plainAttrs, '[]'), '$[*]' 
   COLUMNS (schema_id varchar(255) PATH '$.schema', 
   NESTED PATH '$.values[*]' 
   COLUMNS (booleanvalue int(11) PATH '$.booleanValue', 
            datevalue varchar(32) PATH '$.dateValue', 
            doublevalue double PATH '$.doubleValue', 
            longvalue bigint(20) PATH '$.longValue', 
            stringvalue varchar(255) PATH '$.stringValue'))) attrs on
    (1 = 1))
where
    attrs.schema_id is not null
    and (attrs.booleanvalue is not null
        or attrs.datevalue is not null
        or attrs.doublevalue is not null
        or attrs.longvalue is not null
        or attrs.stringvalue is not null);

As you can see the data comes from the table SyncopeUser which is defined as follows:

CREATE TABLE SyncopeUser (
  id varchar(36) NOT NULL,
  creationContext varchar(255) DEFAULT NULL,
  creationDate datetime(3) DEFAULT NULL,
  creator varchar(255) DEFAULT NULL,
  lastChangeContext varchar(255) DEFAULT NULL,
  lastChangeDate datetime(3) DEFAULT NULL,
  lastModifier varchar(255) DEFAULT NULL,
  status varchar(255) DEFAULT NULL,
  changePwdDate datetime(3) DEFAULT NULL,
  cipherAlgorithm varchar(20) DEFAULT NULL,
  failedLogins int(11) DEFAULT NULL,
  lastLoginDate datetime(3) DEFAULT NULL,
  mustChangePassword int(11) DEFAULT NULL,
  password varchar(255) DEFAULT NULL,
  passwordHistory text DEFAULT NULL,
  securityAnswer varchar(255) DEFAULT NULL,
  suspended int(11) DEFAULT NULL,
  token text DEFAULT NULL,
  tokenExpireTime datetime(3) DEFAULT NULL,
  username varchar(255) DEFAULT NULL,
  plainAttrs longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
              DEFAULT NULL CHECK (json_valid(plainAttrs)),
  REALM_ID varchar(36) DEFAULT NULL,
  SECURITYQUESTION_ID varchar(36) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY U_SYNCPSR_USERNAME (username),
  UNIQUE KEY SyncopeUser_username (username),
  KEY SECURITYQUESTION_ID (SECURITYQUESTION_ID),
  KEY SyncopeUser_realm_id (REALM_ID),
  CONSTRAINT SyncopeUser_ibfk_1 FOREIGN KEY (REALM_ID) 
           REFERENCES Realm (id),
  CONSTRAINT SyncopeUser_ibfk_2 FOREIGN KEY (SECURITYQUESTION_ID)
           REFERENCES SecurityQuestion (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

The relevant column plainAttrs contains the users attibutes as JSON string. One example might be [{"uniqueValue":{"stringValue":"123456789"},"schema":"lockSystemId"},{"values":[{"stringValue":"Bergmann"}],"schema":"familyName"}]. The following indexes are defined on SyncopeUser:

Table      |Non_unique|Key_name            |Seq_in_index|Column_name        |Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Ignored|
-----------+----------+--------------------+------------+-------------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+
SyncopeUser|         0|PRIMARY             |           1|id                 |A        |     149635|        |      |    |BTREE     |       |             |NO     |
SyncopeUser|         0|U_SYNCPSR_USERNAME  |           1|username           |A        |     149635|        |      |YES |BTREE     |       |             |NO     |
SyncopeUser|         0|SyncopeUser_username|           1|username           |A        |     149635|        |      |YES |BTREE     |       |             |NO     |
SyncopeUser|         1|SECURITYQUESTION_ID |           1|SECURITYQUESTION_ID|A        |          1|        |      |YES |BTREE     |       |             |NO     |
SyncopeUser|         1|SyncopeUser_realm_id|           1|REALM_ID           |A        |          1|        |      |YES |BTREE     |       |             |NO     |

As most normal users have over 15 attributes the user_search_attr view contains over 2M rows. The servers are VMs with currently 8 GB of RAM and 6 CPUs but this can be changed. We are currently running version 11.4.7 of MariaDB in a galera cluster of 3 Servers. Syncope is only connected to one of the servers as this cluster is also used for other applications and we don't want to influnce these other applications. Here are some of the IMHO relevant configuration options:

[mysqld]
aria_pagecache_buffer_size = 32M
basedir = /usr
bind-address = ::
binlog_format = ROW
character_set_server = utf8
collation_server = utf8_general_ci
innodb_adaptive_hash_index = OFF
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 4096M
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16MB
innodb_log_file_size = 512M
key_buffer_size = 1M
log-error = /var/log/mariadb/mariadb.log
log_queries_not_using_indexes = OFF
long_query_time = 4.0
max_allowed_packet = 128M
max_binlog_size = 100M
max_connections = 400
max_heap_table_size = 256M
performance_schema = ON
query_cache_limit = 16M
query_cache_size = 0
query_cache_type = OFF
skip-external-locking
skip-name-resolve = 1
slow_query_log = 0
slow_query_log_file = /var/log/mariadb/slow.log
sort_buffer_size = 4M

table_definition_cache = 800
table_open_cache = 1000
thread_cache_size = 8
thread_stack = 256K
tmp_table_size = 256M
wsrep_on = ON
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so

As most queries only search for active or pending users one option that I have not tried is indexing or partitioning the table for the status column. Currently we are not in the position of changing the design of the database or even switching to another IDM sollution. We hope to find optimazion potential on the DB layer or maybe some improvements on some queries.

I've been working with database collations of late. I generally don't like ambiguous queries such as SHOW COLLATION so I eventually found the source via the following query: SELECT * FROM information_schema.COLLATIONS ORDER BY CHARACTER_SET_NAME ASC, COLLATION_NAME ASC; I now have the option to update the collation to uca1400_ai_ci however the associated character set value is NULL. Why is the uca1400_ai_ci NULL and what, if any considerations should I have while updating from the character set utf8mb4 and collation utf8mb4_unicode_520_ci?

I have an InnoDB full-text table that serves the Ajax-powered search box at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site and amalgamates them all into one FT table for searching. To give users the best experience (IMHO) I take whatever their input is, clean certain characters out of it (all full-text modifiers, for example), and then prepend every term with + and append them all with *. So a search for "stack overflow" becomes +stack* +overflow* The column that I'm searching on the FT table is small, with a typical character length of 30 characters. Event names, people's names, geographical locations, that sort of thing. Not huge passages of text. It works, but queries take on the order of 1 second to be returned. **EDIT: just after posting I've rebuilt the index and it's down to 0.4 seconds now - but I'd still like to improve it, if possible.** How could I change that to 0.1 seconds, or is that a pipe dream? My server is a dual Xeon with 16 cores/32 threads and 128GB of memory. I serve a million pages or so each month, and rarely see server load above 1-2, with plenty of spare memory. I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or MySQL restart only takes 30 seconds or so), and if that would help? Or maybe MySQL is already holding it in memory - how can I check? I'm happy with the query itself, I don't think there's much that I can improve about it, but I know very little about how to maximize server potential through configuration. FWIW SELECT VERSION() gives me 10.3.20-MariaDB-log.

I have 2 users, one with ALL grants, another with SELECT only. User 1 can see the database, not user 2, why? Read-only user: MariaDB [(none)]> SHOW GRANTS FOR 'readonly'@'%'; +------------------------------------------------------------------------------------------------------+ | Grants for readonly@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO readonly@% IDENTIFIED BY PASSWORD '*854D73C4F9D2019568DEA092DA192405FAD90F77' | | GRANT SELECT ON database.* TO readonly@% | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) # mysql -u readonly -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.001 sec) Full-grants user: MariaDB [(none)]> SHOW GRANTS FOR 'fullgrants'@'%'; +--------------------------------------------------------------------------------------------------------+ | Grants for fullgrants@% | +--------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO fullgrants@% IDENTIFIED BY PASSWORD '*72A473061F48961B5B0EB890CFEA4B9698A569E9' | | GRANT ALL PRIVILEGES ON database.* TO fullgrants@% | +--------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) # mysql -u fullgrants -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | database | +--------------------+ 2 rows in set (0.002 sec)

I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorized into groups. The events have a start and end timestamp and a process group identifier. The table structure is as follows (InnoDB, MariaDB 10): **Table Name: measurements** CREATE TABLE measurements ( row_id int(11) NOT NULL AUTO_INCREMENT, process_name varchar(100) COLLATE utf8_bin NOT NULL, process_id int(11) NOT NULL, process_group_id tinyint(4) NOT NULL, measurement_1 float NOT NULL, measurement_2 float NOT NULL, measurement_3 float NOT NULL, measurement_4 float NOT NULL, start_timestamp int(11) NOT NULL, end_timestamp int(11) NOT NULL, PRIMARY KEY (row_id), KEY process_group_id (process_group_id, start_timestamp,end_timestamp), KEY process_id (process_id) ) ENGINE=InnoDB AUTO_INCREMENT=7294932 DEFAULT CHARSET=utf8 COLLATE=utf8_bin I'm designing a query to obtain the sum of measurements 1,2,3 & 4 for all processes running within a group at a particular point in time so that the app can express each measurement for a specific process as a percentage of the total measurements in the group at that time. The start and end times of processes within a group are not synchronized and they are of variable length. So for a process running in Group 5, at timestamp 1431388800 SELECT SUM(measurement_1), SUM(measurement_2), SUM(measurement_3), SUM(measurement_4) FROM measurements WHERE process_group_id = 5 AND 1431388800 BETWEEN start_timestamp AND end_timestamp This query runs, but takes around 0.5s. The table has 8m records and grows by about 30,000 a day. I have an index on process_group_id, start_timestamp, end_timestamp. However, the query does not appear to use anything but the process_group_id part of the index. I created an additional index on process_group_id alone to check this, and once created EXPLAIN showed it using this index. After some searching, I saw a suggestion to modify the query and add an ORDER BY clause. Having done this the query is accelerated to around 0.06s and it seems to use the full index. However, I'm unsure as to why: SELECT process_group_id, SUM(measurement_1), SUM(measurement_2), SUM(measurement_3), SUM(measurement_4) FROM measurements WHERE process_group_id = 5 AND 1431388800 BETWEEN start_timestamp AND end_timestamp ORDER BY process_group_id ASC With 30,000 new records a day that requires their shares to be calculated, 0.06s is still not particularly fast. Is there a better way of structuring either the table or designing the query to get a few orders of magnitude quicker, or is a query which matches on one column and then a range query on two others always going to be fairly slow to run?

We have an issue with a database of ours on RDS running MariaDB 10.4.8 on a t3-large instance type. For the past week or so, it's been sporadically inaccessible with no errors I can find. During the minute or two that it's down I can't run 'show process list' it just hangs. It fixes itself and is back working within a few minutes. When it comes back it looks like it has a lot of connections and queries to catch up on. Changed params from default: ft_min_word_len 1 innodb_buffer_pool_size {DBInstanceClassMemory*17/20} innodb_ft_enable_stopword 1 innodb_ft_min_token_size 1 log_bin_trust_function_creators 1 max_statement_time 30 sql_mode NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ## Freeable Memory: ## Swap space: ## Performance Insights: If there any way to find out what's causing it. Is it not enough free memory? What's a normal threshold to be running to?

When I run mysqld it fails as follows:- > 2025-07-31 14:47:29 0 [Warning] Can't create test file 'C:\Program > Files\MariaDB 11.4\data\AcerVero.lower-test' (Errcode: 13 "Permission > denied") C:\Program Files\MariaDB 11.4\bin\mysqld: Can't create file > 'C:\Program Files\MariaDB 11.4\data\AcerVero.err' (errno: 13 > "Permission denied") > >2025-07-31 14:47:29 0 [Note] Starting MariaDB > 11.4.5-MariaDB source revision 0771110266ff5c04216af4bf1243c65f8c67ccf4 server_uid > MhQsFfBB48q7y378l6y8KZMY7gs= as process 29644 > >2025-07-31 14:47:29 0 > [ERROR] mysqld: Can't create/write to file '.\ddl_recovery.log' > (Errcode: 13 "Permission denied") > >2025-07-31 14:47:29 0 [ERROR] > DDL_LOG: Failed to create ddl log file: .\ddl_recovery.log > >2025-07-31 14:47:29 0 [ERROR] Aborting I've tried running it from a different drive, but that made no difference. I am logged into Windows 11 as an administrator. What am I doing wrong? Thanks in advance, Steve

I'm new to Percona XtraBackup. I've been trying to perform a full backup stream from my local ( *test server around 600GB* ) to remote server. I just have some questions and I need guides, and I think this is the best place. I have this command, which I executed in my local innobackupex --user=user --password=password --stream=tar /which/directory/ | pigz | ssh user@10.11.12.13 "cat - > /mybackup/backup.tar.gz" My questions are : - **My log scan is not changing / increasing** >> log scanned up to (270477048535) >> log scanned up to (270477048535) >> log scanned up to (270477048535) >> log scanned up to (270477048535) >> log scanned up to (270477048535) I've read a comment before and someone says log scan will not increase if no one is using the database. ( Yes, no one is using the database )  - **It's been running for a while.** I've tried to use xtrabackup to a local test server with around 1.7TB and finished in just a few hours. Is this because I'm using stream that's why it is slow? What is the purpose of "/which/directory/" in my command? Is it going to store the file in /which/directory/ first and then transfer to my remote server ? Why do I have to specify a directory? - **No created file on my local server /which/directory/ and to my remote server /mybackup/.** Am I doing something wrong ? Is there a much easier way to perform this? My only goal is to backup my local database to a remote server, I'm doing this stream because I don't have enough disk space to store my backup locally. I'm using MariaDB 5.5 and Percona XtraBackup 2.2

*Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.* The important part of the schema shows /*Database: mamlocal*/ ---------------------- /*Table Information*/ --------------------- Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary ------------------------------------ ------ ------- ---------- ------ -------------- ----------- --------------- ------------ --------- -------------- ------------------- ------------------- ---------- ----------------- -------- -------------- ----------------------------------------------------------- ------------------ ----------- ... member InnoDB 10 Dynamic 706 162 114688 0 49152 0 4528 2020-08-17 14:48:55 2021-04-06 10:27:53 (NULL) utf8_general_ci (NULL) 0 N ... and the table member is created using... CREATE TABLE member ( member_id int(11) NOT NULL AUTO_INCREMENT, member_title varchar(10) DEFAULT NULL, member_fn_1 varchar(50) DEFAULT NULL, member_sn varchar(50) DEFAULT NULL, ... other fields PRIMARY KEY (member_id) ) These statements each produce the expected results. SELECT * FROM information_schema.tables WHERE table_schema = 'mamlocal' AND table_name = 'member'; SELECT * FROM information_schema.tables WHERE table_name = 'member'; SELECT member_id FROM member SELECT member_id FROM mamlocal.member but these produce Error Code: 1109 Unknown table 'member' in field list UPDATE member SET member_id = member_id ; UPDATE mamlocal.member SET member_id = member_id *(The real update does something useful but I simplified it to try to find out where the error was)* Can anyone help me to identify why? I have looked at these other questions but still cannot see what is wrong Qn1 Qn2 Qn3 Here is an SQLfiddle showing the same code working correctly in MySQL, as I would expect and I have used similar code in many other places without problems to update table member. Using the same code to update another table works ok. **More experiments showing that table member is there.** This code... INSERT INTO member () VALUES (); UPDATE member SET member_id = member_id ; ... gives me the message 2 queries executed, 1 success, 1 errors, 0 warnings Query: insert into member () values () 1 row(s) affected Execution Time : 0.002 sec Transfer Time : 0.001 sec Total Time : 0.004 sec ----------------------------------------------------------- Query: UPDATE member SET member_id = member_id Error Code: 1109 Unknown table 'member' in field list Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.001 sec

I have configured a MariaDB Galera cluster with three nodes. The master node and one slave-master node is up and running, but the third node just won't run. The system specs and cluster config are the same on all three nodes (except changing the wsrep_node_address and the wsrep_node_name on each node). Here is the config for the nodes (wsrep_node_address and the wsrep_node_name are unique on each node): [galera] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 query_cache_size=0 query_cache_type=0 bind-address=0.0.0.0 datadir=/var/lib/mysql innodb_log_file_size=100M innodb_file_per_table innodb_flush_log_at_trx_commit=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113" wsrep_cluster_name='galera_cluster' wsrep_node_address='192.168.9.113' wsrep_node_name='george-db4' wsrep_sst_method=xtrabackup wsrep_sst_auth=username:password It seems like MariaDb does not like the config wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113" When I comment it out MariaDB restarts just fine. When I uncomment it, it just displays this message: Starting MySQL................................... ERROR! ERROR! Failed to restart server. When I change the wsrep_cluster_address to just wsrep_cluster_address="gcomm:// MariaDb runs with no problem. So I am not sure why it is having problems with assigning the IP address (even if it works fine on the other two nodes). I have tried emptying the galera.cache file, I have tried re-installing the cluster, I have tried rebooting the machine etc. and nothing works so far. I even truncated the log file /var/lib/mysql/db1.err with the command truncate logfile --size 0 (now I cannot figure out how to get my log file back with space as I realized I would need the log file).

I am playing around with a MariaDB Galera Master Master Cluster and I have some issues which I do not understand. [mysqld] bind-address=0.0.0.0 [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so binlog_format=ROW innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 query_cache_size=0 query_cache_type=0 default_storage_engine=InnoDB innodb_log_file_size=100M innodb_file_per_table innodb_flush_log_at_trx_commit=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://" wsrep_cluster_name='galera_cluster' wsrep_node_address='192.168.2.67' wsrep_node_name='KVM-1' wsrep_sst_method=rsync wsrep_sst_auth=repl_user:PASS On the other nodes [mysqld] bind-address=0.0.0.0 [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so binlog_format=ROW innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 query_cache_size=0 query_cache_type=0 default_storage_engine=InnoDB innodb_log_file_size=100M innodb_file_per_table innodb_flush_log_at_trx_commit=2 wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://IP_ADDR_MAIN_NODE,OTHER_NODES_ADDRS" wsrep_cluster_name='galera_cluster' wsrep_node_address='192.168.2.68' wsrep_node_name='KVM-2' wsrep_sst_method=rsync wsrep_sst_auth=repl_user:PASS when i login to mysql and query for SHOW STATUS LIKE 'wsrep_cluster_size'; Empty set (0.00 sec) Am i missing anything?

After migrating from MariaDB 5.5 to 10.0, I noticed an error message mentioning a table named piwik/#sql-1526_3a. From there I started googling and landed on the [innodb troubleshooting docs](https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html#innodb-orphan-temporary-tables) Indeed, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; gives the following result: MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+--------+----------------+------+--------+-------+ | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE | +----------+--------+----------------+------+--------+-------+ | 323 | piwik | #sql-1526_3a | 1 | 11 | 320 | | 265 | piwik | #sql-5305_92b0 | 1 | 24 | 262 | | 321 | piwik | #sql-7920_48 | 1 | 24 | 318 | +----------+--------+----------------+------+--------+-------+ I tried to follow MySQL troubleshooting instructions and did: MariaDB [(none)]> use piwik Database changed MariaDB [piwik]> drop table #mysql50##sql-1526_3a; ERROR 1051 (42S02): Unknown table '#mysql50##sql-1526_3a' From there, I read more blog posts and stack exchange answers and noticed that there are no #sql-1526_3a.idb and #sql-1526_3a.frm files in my /var/lib/mysql/piwik/ folder. I then tried the following: $ cd /var/lib/mysql/piwik $ cp piwik_user.ibd #sql-1526_3a.ibd $ cp piwik_user.frm #sql-1526_3a.frm and restarted MariaDB. Then I tried droping the table again: MariaDB [piwik]> drop table #mysql50##sql-1526_3a; Database changed Query OK, 0 rows affected (0.00sec) Horray?!! Well, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; still lists the #sql-1526_3a table. On disk, the .frm file got deleted but the .ibd file remains. So? Does someone know how I can remove these orphan tables for real? --- **EDIT** It seems to me these trailing temporary tables are "referenced" from /var/lib/mysql/ibdata1 before I shrunk it and enabled innodb_file_per_table = 1.

I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I dump the 5 tables used in my query and load them into a new DB, the query runs fine using the index field_department_target_id. But once I analyze that table (or MariaDB does an internal stats rebuild) the query stops using that index and uses the PRIMARY index. I don't understand why it does this, and the query is MUCH slower using PRIMARY (90-120 seconds vs under 1/10th of a second.) Does anyone know how I can diagnose why the planner is picking the wrong index? Or why ANALYZEing the table makes it break (I would think that would make things better) The query is dynamically generated by Drupal so I can't just go into the code and add FORCE INDEX. Here's the explain for the fast result: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE file_managed ref uri,status,file_type status 1 const 7403 Using where; Using temporary; Using filesort 1 SIMPLE field_data_field_department ref PRIMARY,entity_type,deleted,entity_id PRIMARY 391 const,drupal_authoring.file_managed.fid,const 1 Using where 1 SIMPLE taxonomy_term_data_field_data_field_department eq_ref PRIMARY PRIMARY 4 drupal_authoring.field_data_field_department.field_department_target_id 1 Using where; Using index 1 SIMPLE taxonomy_term_data_field_data_field_department__field_data_field_department ref PRIMARY,entity_type,deleted,field_department_target_id field_department_target_id 390 drupal_authoring.taxonomy_term_data_field_data_field_department.tid,const 15 Using where; Using index 1 SIMPLE file_usage ref PRIMARY,fid_count,fid_module fid_count 4 drupal_authoring.file_managed.fid 1 Using index And for the slow query: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE file_managed ref uri,status,file_type file_type 152 const 7592 Using index condition; Using where; Using temporary; Using filesort 1 SIMPLE field_data_field_department ref PRIMARY,entity_type,deleted,entity_id PRIMARY 391 const,drupal_authoring.file_managed.fid,const 1 Using where 1 SIMPLE taxonomy_term_data_field_data_field_department eq_ref PRIMARY PRIMARY 4 drupal_authoring.field_data_field_department.field_department_target_id 1 Using where; Using index 1 SIMPLE taxonomy_term_data_field_data_field_department__field_data_field_department ref PRIMARY,entity_type,deleted,field_department_target_id PRIMARY 386 const 7985 Using where 1 SIMPLE file_usage ref PRIMARY,fid_count,fid_module fid_count 4 drupal_authoring.file_managed.fid 1 Using index And here is the output of SHOW INDEXES Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment field_data_field_department 0 PRIMARY 1 entity_type A 4 NULL NULL BTREE field_data_field_department 0 PRIMARY 2 entity_id A 15742 NULL NULL BTREE field_data_field_department 0 PRIMARY 3 deleted A 15742 NULL NULL BTREE field_data_field_department 0 PRIMARY 4 delta A 15742 NULL NULL BTREE field_data_field_department 0 PRIMARY 5 language A 15742 NULL NULL BTREE field_data_field_department 1 entity_type 1 entity_type A 4 NULL NULL BTREE field_data_field_department 1 bundle 1 bundle A 24 NULL NULL BTREE field_data_field_department 1 deleted 1 deleted A 2 NULL NULL BTREE field_data_field_department 1 entity_id 1 entity_id A 15742 NULL NULL BTREE field_data_field_department 1 revision_id 1 revision_id A 15742 NULL NULL YES BTREE field_data_field_department 1 language 1 language A 2 NULL NULL BTREE field_data_field_department 1 field_department_target_id 1 field_department_target_id A 715 NULL NULL BTREE Any ideas? Even if I can just do something to stop MariaDB from analyzing the table that would help. I can dump and reload it as a temp fix, but that only lasts for a little while.

This is a simple Update Join that updates only about 100 rows: Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020' This takes about 30 seconds to run, despite the fact that: - This query updates the same 100 rows and takes milliseconds to run: Update A SET active = 1 WHERE date > '2020' - The join condition is fast, this query does the same join and takes less than a second SELECT * FROM A INNER JOIN B using(id) WHERE A.date > '2020' - The field active not part of any index - Table A has an index on (id, date), and table B has an index on id. I tried putting the where condition in the join (using on date > '2020') but it didn't help. I'm absolutely stumped why this takes so long. Any help is appreciated.

I'm very new to Galera Cluster and is exploring a potential setup with reasonable resiliency to node failure and network failure. Looking at the very bottom part of this documentation , the **Weighted Quorum for a Primary and Secondary Site Scenario** is quite promising. For ease of reading, I've extracted the setup from the document as follows: > When configuring quorum weights for primary and secondary sites, use > the following pattern: > > Primary Site: > node1: pc.weight = 2 > node2: pc.weight = 2 > > Secondary Site: > node3: pc.weight = 1 > node4: pc.weight = 1 > > Under this pattern, some nodes are located at the primary site while > others are at the secondary site. In the event that the secondary site > goes down or if network connectivity is lost between the sites, the > nodes at the primary site remain the Primary Component. Additionally, > either node1 or node2 can crash without the rest of the nodes becoming > non-primary components. But there seems to be two drawbacks: 1. If there are two failed nodes and one of them happened to be on the primary site, the quorum will be <= 50% and the remaining two nodes will become non-primary components. 2. Despite pc.weight is a dynamic option that can be changed while the server is running, flipping between primary site and secondary site requires modification on all nodes, which is a bit troublesome. So I've come up with another idea in mind - leave the weight as 1 for all nodes, and in the primary site add a Galera Arbitrator. In this case: - The primary site will remain the Primary Component on network issue, just like the original setup. - The cluster still functions even if two nodes failed. - Flipping between primary and secondary site just require a move of the Galera Arbitrator. May I know if there's anything wrong with my idea, or if there will be any practical difficulties? Appreciate if you can share your thoughts with me.

I am not a database guru. But here's the matter with which I'd like some help. I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs. After updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup: # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION bind-address = 127.0.0.1 # skip-name-resolve=1 query_cache_size=0 query_cache_type=0 local-infile=0 innodb_buffer_pool_size=1024M query_cache_size=64M performance_schema=ON tmp_table_size=96M max_heap_table_size=96M unix_socket=OFF table_definition_cache=2400 key_buffer_size=1024M innodb_buffer_pool_size=3G innodb_log_file_size=750M max_connections=300 !includedir /etc/mysql/releem.conf.d Several days have passed, and I have run the MySQL Tuner. I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions: We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See MySQL: Welcome (specially the conclusions at the bottom of the page). When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 64M) join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 96M) max_heap_table_size (> 96M) table_definition_cache (2400) > 3113 or -1 (autosizing if supported) key_buffer_size (~ 195M) innodb_buffer_pool_size (>= 3.3G) if possible. NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled. Thanks for any further assistance. I am obviously not a database guru.