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 <[email protected]> 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 <[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] > >> > > > -- > 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]
