Have you tried changing innodb_flush_method from 'O_DIRECT' to 'fsync'? Also is query cache enabled in new version? ( query_cache_size, query_cache_type ) These results you pasted look like 10.3.39 is just serving from query cache, the increase is just too large... may be that you're just querying a set of fairly stable data and querying it from database each time instead of just returning data from cache - is overloading the server. Dnia 30 marca 2025 20:39 Karl Levik via discuss <[email protected]> napisał(a): 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." mariadb.com 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.43 Mail:[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]
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
