Troubleshooting major performance issue.
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:olivier.miquel@mri.cnrs.fr
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan. On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Hello, Thanks for your answer. In this case the slowdown is occuring with simple select queries on a table. No join. How could this be possible between two versions? O. Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
Not sure if I got this right but in one case you do SELECT COUNT(*) FROM transactions; which is fast because it bypasses IMHU (in my humble understanding) the table scan and uses table metadata to get number of rows which is fast. and in the other you do SELECT COUNT(recipient) FROM transactions; If you want to count recipients and not transactions you need to use COUNT(DISTINCT recipient) and add an INDEX to recipient to be fastish otherwise you go a full table scan as the EXPLAIN demonstrates. Hope that helps. Vassilis On 3/24/25 6:15 PM, Olivier Miquel via discuss wrote:
Hello,
Thanks for your answer.
In this case the slowdown is occuring with simple select queries on a table. No join.
How could this be possible between two versions?
O.
Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
So the problem query is: ``` select count(recipient) from transactions; ``` ? Can you share the output of: ``` SHOW CREATE TABLE transactions; ``` ? It could be the difference between cold caches and hot caches. Are the tables InnoDB on both versions? Is innodb_buffer_pool_size the same? Can you run this twice, and report back the second timing? ``` select SQL_NO_CACHE count(recipient) from transactions; ``` On Mon, 24 Mar 2025 at 18:15, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Hello,
Thanks for your answer.
In this case the slowdown is occuring with simple select queries on a table. No join.
How could this be possible between two versions?
O.
Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Yes, this is two innoDB tables on both versions. The problem occurs when i am working with the transaction table. I had reported the "select count(recipient) from transactions;" request as an example. Again, thanks for your help. It is much appreciated. Here's the outputs of the requests you have required : Mariadb 10.3 : 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.11 : 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 Le 24/03/2025 à 17:24, Gordan Bobic a écrit :
So the problem query is: ``` select count(recipient) from transactions; ``` ?
Can you share the output of: ``` SHOW CREATE TABLE transactions; ``` ?
It could be the difference between cold caches and hot caches. Are the tables InnoDB on both versions? Is innodb_buffer_pool_size the same?
Can you run this twice, and report back the second timing? ``` select SQL_NO_CACHE count(recipient) from transactions; ```
On Mon, 24 Mar 2025 at 18:15, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Hello,
Thanks for your answer.
In this case the slowdown is occuring with simple select queries on a table. No join.
How could this be possible between two versions?
O.
Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
It sounds like what you really need is an index on transactions(recipient). I think you switched the labels, your last message implies 0.6s for 10.11 and 4.5s for 10.3. On Mon, 24 Mar 2025 at 18:45, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Yes, this is two innoDB tables on both versions.
The problem occurs when i am working with the transaction table. I had reported the "select count(recipient) from transactions;" request as an example.
Again, thanks for your help. It is much appreciated.
Here's the outputs of the requests you have required :
Mariadb 10.3 :
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.11 :
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
Le 24/03/2025 à 17:24, Gordan Bobic a écrit :
So the problem query is: ``` select count(recipient) from transactions; ``` ?
Can you share the output of: ``` SHOW CREATE TABLE transactions; ``` ?
It could be the difference between cold caches and hot caches. Are the tables InnoDB on both versions? Is innodb_buffer_pool_size the same?
Can you run this twice, and report back the second timing? ``` select SQL_NO_CACHE count(recipient) from transactions; ```
On Mon, 24 Mar 2025 at 18:15, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Hello,
Thanks for your answer.
In this case the slowdown is occuring with simple select queries on a table. No join.
How could this be possible between two versions?
O.
Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Yes you're right! Sorry, i had swapped the two results. It is way slower with 10.11. I could add an index on recipient, but why this is so faster with 10.3 without any indexes ? I'm guessing that there's something i missed here. Le 24/03/2025 à 17:51, Gordan Bobic a écrit :
It sounds like what you really need is an index on transactions(recipient). I think you switched the labels, your last message implies 0.6s for 10.11 and 4.5s for 10.3.
On Mon, 24 Mar 2025 at 18:45, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Yes, this is two innoDB tables on both versions.
The problem occurs when i am working with the transaction table. I had reported the "select count(recipient) from transactions;" request as an example.
Again, thanks for your help. It is much appreciated.
Here's the outputs of the requests you have required :
Mariadb 10.3 :
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.11 :
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
Le 24/03/2025 à 17:24, Gordan Bobic a écrit :
So the problem query is: ``` select count(recipient) from transactions; ``` ?
Can you share the output of: ``` SHOW CREATE TABLE transactions; ``` ?
It could be the difference between cold caches and hot caches. Are the tables InnoDB on both versions? Is innodb_buffer_pool_size the same?
Can you run this twice, and report back the second timing? ``` select SQL_NO_CACHE count(recipient) from transactions; ```
On Mon, 24 Mar 2025 at 18:15, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Hello,
Thanks for your answer.
In this case the slowdown is occuring with simple select queries on a table. No join.
How could this be possible between two versions?
O.
Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
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:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org -- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
On Mon, 24 Mar 2025 at 18:57, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Yes you're right! Sorry, i had swapped the two results. It is way slower with 10.11.
I could add an index on recipient, but why this is so faster with 10.3 without any indexes ?
What do you get from: ANALYZE select count(recipient) from transactions;
I'm guessing that there's something i missed here.
Same disks, same file system? -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Hello Gordan, Yes this is same disk, same filesystem. Only the mariadb engine version differs. Here are the outputs required. What does mean this rows value difference in the output ? Many thanks, O. 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 | | +------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+ Le 24/03/2025 à 18:30, Gordan Bobic a écrit :
On Mon, 24 Mar 2025 at 18:57, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Yes you're right! Sorry, i had swapped the two results. It is way slower with 10.11.
I could add an index on recipient, but why this is so faster with 10.3 without any indexes ?
What do you get from: A
I'm guessing that there's something i missed here. Same disks, same file system?
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
rows is heuristic estimate of how many rows will need to be read. r_rows is the number of rows actually read. Since the ANALYZE outputs are close enough that they don't explain the difference, this is probably going to require some deeper debugging. Consider filing a performance bug on MariaDB MDEV JIRA board. On Tue, 25 Mar 2025 at 10:48, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Hello Gordan,
Yes this is same disk, same filesystem. Only the mariadb engine version differs.
Here are the outputs required.
What does mean this rows value difference in the output ?
Many thanks,
O.
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 | | +------+-------------+--------------+------+---------------+------+---------+------+---------+------------+----------+------------+-------+
Le 24/03/2025 à 18:30, Gordan Bobic a écrit :
On Mon, 24 Mar 2025 at 18:57, Olivier Miquel <olivier.miquel@mri.cnrs.fr> wrote:
Yes you're right! Sorry, i had swapped the two results. It is way slower with 10.11.
I could add an index on recipient, but why this is so faster with 10.3 without any indexes ?
What do you get from: A
I'm guessing that there's something i missed here. Same disks, same file system?
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Not sure if this could help, but you might want to check out the following sentence from the MariaDB KB regarding the InnoDB buffer pool: "Be aware that before MariaDB 10.4.4 the total memory allocated is about 10% more than the specified size as extra space is also reserved for control structures and buffers." https://mariadb.com/kb/en/innodb-buffer-pool/ So presumably you will need to increase your value for innodb_buffer_pool_size in MariaDB 10.11 to get the same actual size of buffer pool and similar performance as you have in 10.3. Karl On Mon, 24 Mar 2025 at 15:16, Olivier Miquel via discuss < discuss@lists.mariadb.org> wrote:
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.43Mail:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
participants (4)
-
Gordan Bobic -
Karl Levik -
Olivier Miquel -
Vassilis Virvilis