Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.11
-
None
-
None
-
None
-
Debian 12
Description
Hello,
I am experiencing major performance problems when upgrading our version of mariadb from debian 10 ( mariadb 10.3 ) to an up to date debian 12 ( mariadb 10.11 ).
It's especially when accessing a table with about 2 million records that requests are much slower ( 4.7 seconds vs 0.001 s ) to the point of making our application unusable. It happens also with simple select queries (no join) .
I've observed this between two versions of mariadb, each with default settings in Debian.
I've tried changing the settings several times, but nothing seems to help.
Do you have any idea why this big difference in performance and how to fix it?
Thanks for your help.
Some outputs to highlight the problem :
Mariadb 10.11 :
|
|
|
MariaDB [cfms_accounting]> select SQL_NO_CACHE count(recipient) from transactions;
|
+------------------+
|
| count(recipient) |
|
+------------------+
|
| 419 |
|
+------------------+
|
1 row in set (4,470 sec)
|
|
|
MariaDB [cfms_accounting]> select SQL_NO_CACHE count(recipient) from transactions;
|
+------------------+
|
| count(recipient) |
|
+------------------+
|
| 419 |
|
+------------------+
|
1 row in set (4,354 sec)
|
|
|
MariaDB 10.3 :
|
|
|
MariaDB [cfms_accounting]> select SQL_NO_CACHE count(recipient) from transactions;
|
+------------------+
|
| count(recipient) |
|
+------------------+
|
| 419 |
|
+------------------+
|
1 row in set (0,724 sec)
|
|
|
MariaDB [cfms_accounting]> select SQL_NO_CACHE count(recipient) from transactions;
|
+------------------+
|
| count(recipient) |
|
+------------------+
|
| 419 |
|
+------------------+
|
1 row in set (0,671 sec)
|
|
|
SHOW CREATE TABLE transactions;
|
|
|
CREATE TABLE `transactions` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`reference` varchar(255) NOT NULL,
|
`recipient` varchar(255) DEFAULT NULL,
|
`sender` varchar(255) DEFAULT NULL,
|
`sender_reference` varchar(255) DEFAULT NULL,
|
`amount` double(15,8) NOT NULL,
|
`amount_type` varchar(255) NOT NULL,
|
`old_balance` double(15,8) NOT NULL,
|
`new_balance` double(15,8) NOT NULL,
|
`remark` text DEFAULT NULL,
|
`order` varchar(255) DEFAULT NULL,
|
`form_state` text DEFAULT NULL,
|
`creator_user_id` int(10) unsigned NOT NULL,
|
`accounting_group_id` int(10) unsigned NOT NULL,
|
`working_group_id` int(10) unsigned DEFAULT NULL,
|
`resource_id` int(10) unsigned DEFAULT NULL,
|
`transaction_type_id` int(10) unsigned NOT NULL,
|
`start_time` timestamp NOT NULL,
|
`end_time` timestamp NOT NULL,
|
`created_at` timestamp NOT NULL,
|
`updated_at` timestamp NOT NULL,
|
`deleted_at` timestamp NULL DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `transactions_reference_unique` (`reference`),
|
KEY `transactions_creator_user_id_foreign` (`creator_user_id`),
|
KEY `transactions_accounting_group_id_foreign` (`accounting_group_id`),
|
KEY `transactions_working_group_id_foreign` (`working_group_id`),
|
KEY `transactions_transaction_type_id_foreign` (`transaction_type_id`),
|
KEY `transactions_resource_id_foreign` (`resource_id`),
|
KEY `end_time` (`end_time`),
|
KEY `deleted_at` (`deleted_at`)
|
) ENGINE=InnoDB AUTO_INCREMENT=2848536 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
|
|
|
|
|
|
|
Mariadb 10.3 :
|
|
|
MariaDB [cfms_accounting]> ANALYZE select count(recipient) from transactions;
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
|
| 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 1779448 | 1912563.00 | 100.00 | 100.00 | |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
|
|
|
Mariadb 10.11 :
|
|
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
|
| 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 1942730 | 1912563.00 | 100.00 | 100.00 | |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
|
|
|
|
|
|
MariaDB [cfms_accounting]> DESCRIBE transactions;
|
+---------------------+------------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+---------------------+------------------+------+-----+---------+----------------+
|
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
|
| reference | varchar(255) | NO | UNI | NULL | |
|
| recipient | varchar(255) | YES | | NULL | |
|
| sender | varchar(255) | YES | | NULL | |
|
| sender_reference | varchar(255) | YES | | NULL | |
|
| amount | double(15,8) | NO | | NULL | |
|
| amount_type | varchar(255) | NO | | NULL | |
|
| old_balance | double(15,8) | NO | | NULL | |
|
| new_balance | double(15,8) | NO | | NULL | |
|
| remark | text | YES | | NULL | |
|
| order | varchar(255) | YES | | NULL | |
|
| form_state | text | YES | | NULL | |
|
| creator_user_id | int(10) unsigned | NO | MUL | NULL | |
|
| accounting_group_id | int(10) unsigned | NO | MUL | NULL | |
|
| working_group_id | int(10) unsigned | YES | MUL | NULL | |
|
| resource_id | int(10) unsigned | YES | MUL | NULL | |
|
| transaction_type_id | int(10) unsigned | NO | MUL | NULL | |
|
| start_time | timestamp | NO | | NULL | |
|
| end_time | timestamp | NO | MUL | NULL | |
|
| created_at | timestamp | NO | | NULL | |
|
| updated_at | timestamp | NO | | NULL | |
|
| deleted_at | timestamp | YES | MUL | NULL | |
|
+---------------------+------------------+------+-----+---------+----------------+
|
|
|
|
|
|
|
MariaDB [cfms_accounting]> ANALYZE TABLE transactions;
|
+------------------------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+------------------------------+---------+----------+----------+
|
| cfms_accounting.transactions | analyze | status | OK |
|
+------------------------------+---------+----------+----------+
|
1 row in set (0,107 sec)
|
|
|
|
|
|
|
Server version: 10.3.39-MariaDB-0+deb10u2 Debian 10
|
|
|
|
|
MariaDB [cfms_accounting]> SELECT count(*) from transactions;
|
+----------+
|
| count(*) |
|
+----------+
|
| 1912563 |
|
+----------+
|
1 row in set (0,616 sec)
|
|
|
MariaDB [cfms_accounting]> EXPLAIN select count(recipient) from transactions;
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|
| 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 1942730 | |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|
1 row in set (0,002 sec)
|
|
|
|
|
MariaDB [cfms_accounting]> select count(recipient) from transactions;
|
+------------------+
|
| count(recipient) |
|
+------------------+
|
| 419 |
|
+------------------+
|
1 row in set (0,001 sec)
|
|
|
Server version: 10.11.11-MariaDB-0+deb12u1 Debian 12
|
|
|
MariaDB [cfms_accounting]> ANALYZE TABLE transactions;
|
+------------------------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+------------------------------+---------+----------+----------+
|
| cfms_accounting.transactions | analyze | status | OK |
|
+------------------------------+---------+----------+----------+
|
1 row in set (0,058 sec)
|
|
|
|
|
MariaDB [cfms_accounting]> select count(*) from transactions;
|
+----------+
|
| count(*) |
|
+----------+
|
| 1912563 |
|
+----------+
|
1 row in set (0,333 sec)
|
|
|
MariaDB [cfms_accounting]> EXPLAIN select count(recipient) from transactions;
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|
| 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 1779448 | |
|
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|
1 row in set (0,002 sec)
|
|
|
|
|
|
|
|
|
MariaDB [cfms_accounting]> select count(recipient) from transactions;
|
+------------------+
|
| count(recipient) |
|
+------------------+
|
| 419 |
|
+------------------+
|
1 row in set (4,778 sec)
|
|