mariadb – What are the risks of upgrading DB major version in replica and then promoting it as master?

Recently AWS decided to force update all MariaDB RDS under 10.3, and I am using AWS and my RDS are using MariaDB 10.1. We are trying to minimize the downtime and any risk of data out of sync.

Our plan is to create a replica and upgrade it from 10.1 to 10.5. After that we will let it to consume binary log from old non-upgraded RDS then promoting it as Master and point our website to the newly created and upgrade RDS.

Assume we will have continuous stream of data coming into the RDS, what are the risk of steps above? Is the new RDS able to get all binary log genereated during the upgrade time? We estimated there will be ~ 1.5 hours to finish upgrading from 10.1 to 10.5

Thank you for helping

mariadb – Galera cluster slightly slower than single database?

I recently set up a MariaDB galera cluster for our production. I used sysbench to benchmark the cluster against the old database which is on a single server.

On my PRD Galera Cluster I got the following results:

SQL statistics:
    queries performed:
        read:                            3914980
        write:                           0
        other:                           782996
        total:                           4697976
    transactions:                        391498 (1304.77 per sec.)
    queries:                             4697976 (15657.22 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0492s
    total number of events:              391498

Latency (ms):
         min:                                    5.37
         avg:                                   12.26
         max:                                   66.20
         95th percentile:                       15.83
         sum:                              4798745.23

Threads fairness:
    events (avg/stddev):           24468.6250/414.77
    execution time (avg/stddev):   299.9216/0.01

Meanwhile our old single database production got this results:

SQL statistics:
    queries performed:
        read:                            5306060
        write:                           0
        other:                           1061212
        total:                           6367272
    transactions:                        530606 (1768.51 per sec.)
    queries:                             6367272 (21222.18 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0266s
    total number of events:              530606

Latency (ms):
         min:                                    3.87
         avg:                                    9.04
         max:                                   59.99
         95th percentile:                       12.08
         sum:                              4798278.00

Threads fairness:
    events (avg/stddev):           33162.8750/440.14
    execution time (avg/stddev):   299.8924/0.01

Now I’m wondering why does the cluster operate a bit slower than the single database? They have the same specs: Quadcore CPU, 32GB RAM and vm.swappiness=1. Here’s my cluster configuration (same across 3 servers) and is using HAProxy to load balance between 3 servers:

max_connections = 3000

wsrep_slave_threads=4
innodb_lock_wait_timeout=8000
innodb_io_capacity=2000
innodb_buffer_pool_size=25G
innodb_buffer_pool_instances=25
innodb_log_buffer_size=256M
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
innodb_flush_method = O_DIRECT_NO_FSYNC

innodb_read_io_threads=8
innodb_write_io_threads=4

thread_handling = pool-of-threads
thread_stack = 192K
thread_cache_size = 4
thread_pool_size = 8
thread_pool_oversubscribe = 3

wsrep_provider_options="gcache.size=10G; gcache.page_size=10G"

I used sysbench on a spare server, does the latency between servers also affect the outputs? I would appreciate any inputs, thank you.

Problem with a foreign key referencing a primary key which is also part of an unique key (MariaDB)

I created a database using MariaDb. I have the following author table (with id as primary key), which is extended by two other tables, author_personal and author_corporate, representing two different kinds of author and having different fields:

CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_type` char(1) NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  UNIQUE KEY `author_UN` (`id`,`author_type`) USING BTREE,
  CONSTRAINT `author_CHECK_type` CHECK (`author_type` in ('P','C'))
);

CREATE TABLE `author_personal` (
  `id` int(10) unsigned NOT NULL,
  `surname` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  -- other fields
  `author_type` char(1) GENERATED ALWAYS AS ('P') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_personal_FK` (`id`,`author_type`),
  CONSTRAINT `author_personal_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

CREATE TABLE `author_corporate` (
  `id` int(10) unsigned NOT NULL,
  `corporate_name` varchar(50) DEFAULT NULL,
  `corporate_acronym` varchar(5) DEFAULT NULL,
  `author_type` char(1) GENERATED ALWAYS AS ('C') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_corporate_FK` (`id`,`author_type`),
  CONSTRAINT `author_corporate_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

Although author.id would be enough, I decided to create an UNIQUE KEY (id, author_type) to be referenced by the foreign key in the two other tables, so that it would be impossible to reference from author_personal an author which is not flagged as P, and from author_corporate an author which is not flagged as C.

The problem rises when I want to reference author using the primary key, like in this table:

CREATE TABLE `work_authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  KEY `work_authors_FK` (`work_id`),
  KEY `work_authors_FK_author` (`author_id`) USING BTREE,
  CONSTRAINT `work_authors_FK` FOREIGN KEY (`work_id`) REFERENCES `work` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `work_authors_FK_author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON UPDATE CASCADE
);
    

Both DBeaver and PhpMyAdmin think that work_authors_FK_author references author_UN instead of the actual primary key (author.id).

DBeaver Foreign Key description

This means that in DBeaver I am not able to click on the values in work_authors.author_id and open the referenced record in authors because I get the following error:

Entity (davide_library.author) association (work_authors_FK_author) columns differs from referenced constraint (author_UN) (1<>2)

I created the foreign keys using DBeaver. Although I selected PRIMARY as the unique key, in the list of foreign keys it always show author_UN as the referenced object. I don’t know if this behavior depends on MariaDB or DBeaver.

My question is:

Is there any way to explicitily reference in the DDL the primary key instead of the unique key?

or, alternatively:

Is there a different way, other than the UNUQUE constraint, to check that author_personal references only authors flagged with P, and the same for author_corporate?

JOIN bug in MariaDB 10.2

This is less a question than just a “beware.” Thankfully MariaDB 10.2 is an old version, but it does some funky things when joining to nested derived tables. I updated my database from MariaDB 10.0.35 to 10.2.12 and this bug popped up. I tested the same setup and query on 10.0.35, 10.2.12, and 10.4.17 and got different results.

Here’s the setup:

CREATE TABLE `users`
(
    `id`       int AUTO_INCREMENT PRIMARY KEY,
    `username` varchar(128) NULL
) CHARSET = `utf8`;

INSERT INTO `users` (`id`, `username`)
VALUES (6171, 'user01'),
       (6172, 'user02'),
       (6880, 'user03'),
       (7184, 'user04'),
       (7185, 'user05'),
       (7186, 'user06'),
       (7187, 'user07'),
       (7188, 'user08'),
       (7190, 'user09'),
       (7874, 'user10'),
       (9307, 'user11'),
       (9585, 'user12'),
       (12918, 'user13');

CREATE TABLE `addons_users`
(
    `id`         int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`    int        NULL,
    `addon_id`   int        NULL,
    `active`     tinyint(1) NULL,
    `start_date` datetime   NULL,
    `end_date`   datetime   NULL
) CHARSET = `utf8`;

INSERT INTO `addons_users` (`id`,
                            `user_id`,
                            `addon_id`,
                            `active`,
                            `start_date`,
                            `end_date`)
VALUES (623, 7185, 1, 0, '2018-10-19 22:56:01', NULL),
       (624, 7185, 1, 0, '2019-10-19 22:56:01', NULL),
       (625, 7185, 1, 1, '2020-10-19 22:56:01', NULL),
       (626, 7186, 1, 0, '2020-04-19 22:56:01', NULL);

Here’s the query:

SELECT `users`.`id`, `users`.`username`, `addons_users`.`active`
FROM `users`
         LEFT JOIN (SELECT *
                    FROM `addons_users`
                    WHERE `id` IN (SELECT MAX(`id`) AS `id`
                                   FROM `addons_users`
                                   GROUP BY `user_id`)) AS `addons_users_unique_current`
                   ON `addons_users_unique_current`.`user_id` = `users`.`id`;

And here are the results:

MariaDB 10.0.35                 MariaDB 10.2.12                MariaDB 10.4.17
+-------+----------+--------+   +------+----------+--------+   +-------+----------+--------+
| id    | username | active |   | id   | username | active |   | id    | username | active |
+-------+----------+--------+   +------+----------+--------+   +-------+----------+--------+
|  7185 | user05   |      1 |   | 7185 | user05   |      1 |   |  7185 | user05   |      1 |
|  7186 | user06   |      0 |   | 7186 | user06   |      0 |   |  7186 | user06   |      0 |
|  6171 | user01   |   NULL |   +------+----------+--------+   |  6171 | user01   |   NULL |
|  6172 | user02   |   NULL |                                  |  6172 | user02   |   NULL |
|  6880 | user03   |   NULL |                                  |  6880 | user03   |   NULL |
|  7184 | user04   |   NULL |                                  |  7184 | user04   |   NULL |
|  7187 | user07   |   NULL |                                  |  7187 | user07   |   NULL |
|  7188 | user08   |   NULL |                                  |  7188 | user08   |   NULL |
|  7190 | user09   |   NULL |                                  |  7190 | user09   |   NULL |
|  7874 | user10   |   NULL |                                  |  7874 | user10   |   NULL |
|  9307 | user11   |   NULL |                                  |  9307 | user11   |   NULL |
|  9585 | user12   |   NULL |                                  |  9585 | user12   |   NULL |
| 12918 | user13   |   NULL |                                  | 12918 | user13   |   NULL |
+-------+----------+--------+                                  +-------+----------+--------+

As you can see, 10.2 treats the LEFT JOIN like an INNER JOIN.

mariadb – Get rows above and below a certain row, based on two criteria SQL (with SUM)

Say I have a table like so:

+----------+---------+------+---------------------+
|student_no|level_id |points|      timestamp      |
+----------+---------+------+---------------------+
|     4    |    1    |  70  | 2021-01-14 21:50:38 |
|     3    |    2    |  90  | 2021-01-12 15:38:0  |
|     1    |    1    |  20  | 2021-01-14 13:10:12 |
|     5    |    1    |  50  | 2021-01-13 12:32:11 |
|     7    |    1    |  50  | 2021-01-14 17:15:20 |
|     8    |    1    |  55  | 2021-01-14 09:20:00 |
|    10    |    2    |  99  | 2021-01-15 10:50:38 |
|     2    |    1    |  45  | 2021-01-15 10:50:38 |
+----------+---------+------+---------------------+

What I want to do is find the total points for each person (student_no), and show 5 of these rows in a table, with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order – with highest at the top). This will be like a score board but only showing the user’s total points (over all levels) with the two above and two below. So because points could be equal, the timestamp column will also need to be used – so if two scores are equal, then the first person to get the score is shown above the other person.

I have tried this below but it is not outputting what I need.

SELECT 
    student_no, SUM(points)
FROM
    (
    (SELECT 
        student_no, SUM(points), 1 orderby
    FROM student_points a
    HAVING
        SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
    ORDER BY SUM(points) ASC LIMIT 3) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 2 orderby
    FROM student_points a
    WHERE student_no = 40204123) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 3 orderby
    FROM student_points a
    HAVING
        SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
            AND student_no <> 40204123
    ORDER BY SUM(points) DESC LIMIT 3)
    ) t1
ORDER BY orderby ASC , SUM(points) DESC

This is a dbfiddle of what I am trying:
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=5ada81241513c9a0be0b6c95ad0f2947

debian – 2 Apache instances running. Can both access mariaDB?

I have 2 Apache2 instances running on my server ( Debian.)

One called Apache2 (ports 80,443,4443)
One called apache2-suiteCRM (port 8443)

The second instance is created to install suiteCRM and the problem there is that I get an error “can not load DB manager”. I can run the install and all preliminary checks are ok, I have setup a database and a user with all privileges. But one way or another it seems that this Apache instance can not access the mariaDB database. My only guess is that it is because it runs on a second instance.

Is there some simple code to check if my server on port 8443 can read and write into mariaDB?

Thanks

performance – mariadb: Aborted connection .. Got timeout reading communication packets

What is the typical cause of warnings such as this? They appear periodically, sometimes multiple times per day then not for a day or so.

2021-01-08 13:20:46 203939 (Warning) Aborted connection 203939 to db: ‘lsv’ user: ‘finder’ host: ‘23.227.111.186’ (Got timeout reading communication packets)

This database server is only queried by a few hosts, and it seems to happen with all hosts and all databases on the host. This server is connected by a 1gbit link to the Internet as well as a 10gbit local link to a web server.

This is a mariadb-10.4.17 server on fedora33 with a 5.9.16 kernel and 128GB of RAM. It’s the only function of this box. It’s been happening for quite some time. It doesn’t seem to matter How do I troubleshoot this? Could this be a networking problem?

I would appreciate any ideas you might have. Here is the contents of the my.cnf.

# cat my.cnf |grep -Ev '^$|^#'
(client)
port            = 3306
socket          = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

(mysqld)
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections=600
replicate_do_db='txrepdb'
replicate_do_db='sqlgrey'
replicate_do_db='sbclient'
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 512M
join_buffer_size = 2M 
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
query_cache_size = 0
query_cache_type = 0
relay_log_space_limit = 500M
relay_log_purge = 1
log-slave-updates = 1
local_infile = OFF
binlog_format = ROW
max_heap_table_size = 1024M 
tmp_table_size = 1024M 
performance_schema=ON
performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON
relay-log=havoc-relay-bin
log_bin                 = /var/log/mariadb/mysql-bin.log
expire_logs_days        = 2
max_binlog_size         = 500M
plugin_load=server_audit=server_audit.so
plugin_load_add = query_response_time
server_audit_events=connect,query
server_audit_file_path                  = /var/log/mariadb/server_audit.log
server_audit_file_rotate_size           = 1G
server_audit_file_rotations             = 1
slow-query-log = 1
slow-query-log-file = /var/log/mariadb/mariadb-slow.log
long_query_time = 1
log_error = /var/log/mariadb/mariadb-error.log
binlog_format=mixed
server-id       = 590
report-host=havoc.example.com
innodb_data_home_dir = /var/lib/mysql
innodb_defragment=1
innodb_file_per_table
innodb_data_file_path = ibdata1:10M:autoextend:max:500M
innodb_buffer_pool_size=60G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 50
innodb_buffer_pool_instances = 40
open_files_limit=30000  # from 1222 for ~ 50% of planned ulimit -a Open Files of 65536
innodb_open_files=10000  # from 512 to match table_open_cache
innodb_log_buffer_size=64M  # from 8M for ~ 30 minutes log buffered in RAM
innodb_page_cleaners=15  # from 4 to expedite page cleaning
innodb_purge_threads=15  # from 4 to expedite purge processing
innodb_write_io_threads=64  # from 4 to expedite multi core write processing SE5666 Rolando
innodb_read_io_threads=64  # from 4 to expedite multi core read processing SE5666 9/12/11
read_rnd_buffer_size=262144  # from 4M to reduce handler_read_rnd_next of 124,386 RPS
innodb_io_capacity=2100  # from 1100 to allow higher SSD iops
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every SECOND
max_connect_errors=10
table_open_cache=10000  # from 512 to reduce opened_tables RPS of 1
read_buffer_size=1572864 # from 1M to reduce handler_read_next of 32,317 RPS
table_definition_cache=10000  # from 400 to reduce opened table_definitions RPS of 1
log_slow_verbosity=explain  # from nothing or ADD ,explain to enhance SLOW QUERY log
query_prealloc_size=32768 # from 24K to reduce CPU malloc frequency
query_alloc_block_size=32768 # from 16K to reduce CPU malloc frequency
transaction_prealloc_size=32768 # from 4K to reduce CPU malloc frequency
transaction_alloc_block_size=32768 # from 8K to reduce CPU malloc frequency
innodb_fast_shutdown=0
aria_pagecache_division_limit=50  # from 100 for WARM blocks percentage
aria_pagecache_age_threshold=900
innodb_adaptive_max_sleep_delay=20000  # from 150000 ms (15 sec to 2 sec) delay when busy
innodb_flushing_avg_loops=5  # from 30 to minimize innodb_buffer_pool_pages_dirty count
max_seeks_for_key=64  # from ~ 4 Billion to conserve CPU
max_write_lock_count=16  # from ~ 4 Billion to allow RD after nn lck requests
optimizer_search_depth=0  # from 62 to allow OPTIMIZER autocalc of reasonable limit
innodb_print_all_deadlocks=ON  # from OFF to log event in error log for DAILY awareness
wait_timeout=7200
innodb_flush_neighbors=0 # from ON to conserve CPU cycles when you have SSD/NVME
interactive_timeout=7200
innodb_buffer_pool_dump_pct=90  # from 25 to minimize WARM time on STOP / START or RESTART
innodb_fill_factor=93
innodb_read_ahead_threshold=8  # from 56 to reduce delays by ReaDing next EXTENT earlier
sort_buffer_size=1572864 # from 1M to reduce sort_merge_passes RPS of 1
innodb_stats_sample_pages=32  # from 8 for optimizer to use more accurate cardinality
min_examined_row_limit=1  # from 0 to reduce clutter in slow query log
query_cache_limit=0  # from 2M to conserve RAM because your QC is OFF, as it should be.
query_cache_min_res_unit=512  # from 4096 to increase QC capacity, if EVER used

(mysqldump)
quick
max_allowed_packet = 16M

(mysql)
no-auto-rehash
default-character-set = utf8mb4

(myisamchk)
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

(mysqlhotcopy)
interactive-timeout

performance – mariadb: Aborted connection .. Got timeout r eading communication packets

What is the typical cause of warnings such as this? They appear periodically, sometimes multiple times per day then not for a day or so.

2021-01-08 13:20:46 203939 (Warning) Aborted connection 203939 to db: ‘lsv’ user: ‘finder’ host: ‘23.227.111.186’ (Got timeout reading communication packets)

This database server is only queried by a few hosts, and it seems to happen with all hosts and all databases on the host. This server is connected by a 1gbit link to the Internet as well as a 10gbit local link to a web server.

This is a mariadb-10.4.17 server on fedora33 with a 5.9.16 kernel and 128GB of RAM. It’s the only function of this box. It’s been happening for quite some time. It doesn’t seem to matter How do I troubleshoot this? Could this be a networking problem?

I would appreciate any ideas you might have. Here is the contents of the my.cnf.

# cat my.cnf |grep -Ev '^$|^#'
(client)
port            = 3306
socket          = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

(mysqld)
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections=600
replicate_do_db='txrepdb'
replicate_do_db='sqlgrey'
replicate_do_db='sbclient'
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 512M
join_buffer_size = 2M 
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
query_cache_size = 0
query_cache_type = 0
relay_log_space_limit = 500M
relay_log_purge = 1
log-slave-updates = 1
local_infile = OFF
binlog_format = ROW
max_heap_table_size = 1024M 
tmp_table_size = 1024M 
performance_schema=ON
performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON
relay-log=havoc-relay-bin
log_bin                 = /var/log/mariadb/mysql-bin.log
expire_logs_days        = 2
max_binlog_size         = 500M
plugin_load=server_audit=server_audit.so
plugin_load_add = query_response_time
server_audit_events=connect,query
server_audit_file_path                  = /var/log/mariadb/server_audit.log
server_audit_file_rotate_size           = 1G
server_audit_file_rotations             = 1
slow-query-log = 1
slow-query-log-file = /var/log/mariadb/mariadb-slow.log
long_query_time = 1
log_error = /var/log/mariadb/mariadb-error.log
binlog_format=mixed
server-id       = 590
report-host=havoc.example.com
innodb_data_home_dir = /var/lib/mysql
innodb_defragment=1
innodb_file_per_table
innodb_data_file_path = ibdata1:10M:autoextend:max:500M
innodb_buffer_pool_size=60G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 50
innodb_buffer_pool_instances = 40
open_files_limit=30000  # from 1222 for ~ 50% of planned ulimit -a Open Files of 65536
innodb_open_files=10000  # from 512 to match table_open_cache
innodb_log_buffer_size=64M  # from 8M for ~ 30 minutes log buffered in RAM
innodb_page_cleaners=15  # from 4 to expedite page cleaning
innodb_purge_threads=15  # from 4 to expedite purge processing
innodb_write_io_threads=64  # from 4 to expedite multi core write processing SE5666 Rolando
innodb_read_io_threads=64  # from 4 to expedite multi core read processing SE5666 9/12/11
read_rnd_buffer_size=262144  # from 4M to reduce handler_read_rnd_next of 124,386 RPS
innodb_io_capacity=2100  # from 1100 to allow higher SSD iops
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every SECOND
max_connect_errors=10
table_open_cache=10000  # from 512 to reduce opened_tables RPS of 1
read_buffer_size=1572864 # from 1M to reduce handler_read_next of 32,317 RPS
table_definition_cache=10000  # from 400 to reduce opened table_definitions RPS of 1
log_slow_verbosity=explain  # from nothing or ADD ,explain to enhance SLOW QUERY log
query_prealloc_size=32768 # from 24K to reduce CPU malloc frequency
query_alloc_block_size=32768 # from 16K to reduce CPU malloc frequency
transaction_prealloc_size=32768 # from 4K to reduce CPU malloc frequency
transaction_alloc_block_size=32768 # from 8K to reduce CPU malloc frequency
innodb_fast_shutdown=0
aria_pagecache_division_limit=50  # from 100 for WARM blocks percentage
aria_pagecache_age_threshold=900
innodb_adaptive_max_sleep_delay=20000  # from 150000 ms (15 sec to 2 sec) delay when busy
innodb_flushing_avg_loops=5  # from 30 to minimize innodb_buffer_pool_pages_dirty count
max_seeks_for_key=64  # from ~ 4 Billion to conserve CPU
max_write_lock_count=16  # from ~ 4 Billion to allow RD after nn lck requests
optimizer_search_depth=0  # from 62 to allow OPTIMIZER autocalc of reasonable limit
innodb_print_all_deadlocks=ON  # from OFF to log event in error log for DAILY awareness
wait_timeout=7200
innodb_flush_neighbors=0 # from ON to conserve CPU cycles when you have SSD/NVME
interactive_timeout=7200
innodb_buffer_pool_dump_pct=90  # from 25 to minimize WARM time on STOP / START or RESTART
innodb_fill_factor=93
innodb_read_ahead_threshold=8  # from 56 to reduce delays by ReaDing next EXTENT earlier
sort_buffer_size=1572864 # from 1M to reduce sort_merge_passes RPS of 1
innodb_stats_sample_pages=32  # from 8 for optimizer to use more accurate cardinality
min_examined_row_limit=1  # from 0 to reduce clutter in slow query log
query_cache_limit=0  # from 2M to conserve RAM because your QC is OFF, as it should be.
query_cache_min_res_unit=512  # from 4096 to increase QC capacity, if EVER used

(mysqldump)
quick
max_allowed_packet = 16M

(mysql)
no-auto-rehash
default-character-set = utf8mb4

(myisamchk)
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

(mysqlhotcopy)
interactive-timeout

Backup live mariadb

I want to take a dump from a live mariadb database, but I need during the backup process to stop any kind of insert or update so as my backup to be the latest data image from my database.

Is this possible and how ?

mysql – MariaDB InnoDB does not rollback on timeout

I have the ff. sample queries in this sequence:

  1. START TRANSACTION
  2. UPDATE
  3. INSERT
  4. COMMIT

Query #2 is timing out but I noticed that query #3 is not rolled back.

I am also trying to manually rollback by verifying (querying the table) if the updates went through before I commit. But when I do that, I get an explicit: General error: 2006 MySQL server has gone away error.

My questions:

  1. I have innodb_rollback_on_timeout set to ON, isn’t query #3 supposed to be rolled back?

  2. Since I can’t even manually roll back, what options do I have?

Note: This is on mariadb-10.4.17