Hello,
I am experiencing major performance problems when upgrading our version
of mariadb from debian 10 ( mariadb 10.3 ) to debian 12 ( mariadb 10.5 ).
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.
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,
______________
_______
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)
--
Olivier Miquel
Administrateur Système et Réseau
Plate-forme technologique MRI
Tel : 06.50.19.27.43
Mail:[email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
