2

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

Improve this question
5
  • You might be running into some sort of outdated statistics (or similar type of) issue, which is resulting in your SELECT query getting a sub-optimal plan, that most likely uses scans instead of seek operations. Adding to your Post an example query, and the EXPLAIN ANALYZE before and after you load the 1 million records would help prove that out.
    – J.D.
    Jun 15 at 12:13
  • Thank you for this comment. Can you describe it a bit more? I am not sure of what you said. The main symptom is that when I create a new index and I remove it (ALTER TABLE mytable ADD INDEX tmp (id); ALTER TABLE mytable DROP INDEX tmp; ) 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?
    – Tom Tom
    Jun 15 at 12:43
  • I don't fully follow your last statement. But please add the information I requested to your Post and that should make it clearer to solve your problem. Dropping and re-adding the index may be causing updated statistics which is why the problem gets fixed, but there's simpler ways to accomplish that. But first let's confirm the problem, if you could please add the information my previous comment referenced.
    – J.D.
    Jun 15 at 12:47
  • Can you describe more precisely what do you need? About the simpler ways to update statistics, what are you thinking about? Because I tried on my table optimize, repair, flush, check, analyze and checksum, but these requests just improve a bit the performance (5%, this is not good), but this is noting comparing to a request like "ALTER TABLE mytable ADD INDEX tmp (id); ALTER TABLE mytable DROP INDEX tmp;" that increases performance by about 30000% (this last request really corrects the problem one for all on a table, even hours after, even with one million INSERT after, but this is too tricky)
    – Tom Tom
    Jun 15 at 13:03
  • "an example SELECT query, and the EXPLAIN ANALYZE before and after you load the 1 million records would help"
    – J.D.
    Jun 15 at 13:15

1 Answer 1

Reset to default
0

The problem is solved by creating btree indexes separately from the CREATE requests.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.