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 <[email protected]> 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 > > <[email protected]> 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:[email protected] > >> > >> _______________________________________________ > >> discuss mailing list -- [email protected] > >> To unsubscribe send an email to [email protected] > > > > > -- > Olivier Miquel > Administrateur Système et Réseau > Plate-forme technologique MRI > Tel : 06.50.19.27.43 > Mail:[email protected] > -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
