This is my first post on this website. Please don’t hesitate to indicate me if there is any problem in my post.
I have a new issue when migrating from MariaDB 10.1 to MariaDB 10.6 on indexes with myisam tables that reduces the performance dramatically on SELECT requests after large INSERT (the large insert, about one million insert on a new table, reduces the efficiency of indexes created with the CREATE request).
The performance issue occurs on SELECT requests. It’s like there are no indexes. When I remove and recreate indexes on this myisam table, the performance issue disappears. On MariaDB 10.1 the problem does not exist (I don’t need to recreate indexes to maintain the performance good). This problem occurs on MariaDB 10.6 and above (I don’t know below, I don’t know how to downgrade mariadb without risk). This problem does not come from table conversion because I create new myisam table each day.
There is nothing special in the log files about this issue.
The MariaDB variables are the default variables of MariaDB, except on these variables: bulk_insert_buffer_size=100M ; default_storage_engine=MyISAM ; join_buffer_size=100M ; join_buffer_space_limit=52097152 ; key_buffer_size=4G ; max_allowed_packet=400M ; max_connections=80 ; max_heap_table_size=1500M ; multi_range_count=4000 ; myisam_use_mmap=1 ; myisam_sort_buffer_size=500M ; optimizer_switch=index_merge_sort_intersection=on,mrr=on,mrr_sort_keys=on,extended_keys=on ; query_cache_type=1 ; query_cache_size=1G ; query_cache_limit=90M ; query_cache_strip_comments=1 ; read_buffer_size=5M ; sort_buffer_size=40M ; sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ; symbolic-links=0 ; table_open_cache=400 ; thread_cache_size=25 ; tmp_table_size=1500M
It is like the INSERT does not update the indexes or does wrong update of indexes on MariaDB 10.6.
I don’t know how to continue my investigations. Maybe I'm wrong with something.
Do you have experienced such problem on MariaDB 10.6 or above that does not exists on MariaDB 10.1? Do you think about a change between MariaDB 10.1 and MariaDB 10.6 that can create this index inefficiency after large INSERT request (variable changes ...)?
Do you thing about something that corrupts indexes?
Regards
SELECT
query getting a sub-optimal plan, that most likely uses scans instead of seek operations. Adding to your Post an example query, and theEXPLAIN ANALYZE
before and after you load the 1 million records would help prove that out.mytable
ADD INDEXtmp
(id
); ALTER TABLEmytable
DROP INDEXtmp
; ) the MYI file has its size reduced, the problem is corrected and the performance becomes good, but this is a bit strange... Is what you said linked to this symptom?SELECT
query, and theEXPLAIN ANALYZE
before and after you load the 1 million records would help"