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 < [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.43Mail:[email protected] > > _______________________________________________ > discuss mailing list -- [email protected] > To unsubscribe send an email to [email protected] >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
