Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24697

Slow queries after ALTERation of fieldlength until ANALYZE TABLE

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.13
    • None
    • Optimizer, Query Cache, Server
    • None
    • Centos 7 x64

    Description

      Hey,

      recently we came across some weird problem. We changed the fieldlength of a bunch of tables from 45 to 255. After this the time for one query with a JOIN did at least take 10 times longer. Before the time was half or max a second. After we got consistently to 12 seconds.

      After some time we did a ANALYZE TABLE and it worked as before. We also could reproduce this behavoiur when we changed the fieldlength again.
      Shouldn't a ANALYZE TABLE be done automatically after altering tables?

      Every check that we did told us that everything was fine. E.g. CHECK TABLE, SHOW TABLE STATUS, SHOW ERRORS, SHOW WARNINGS. Also mysqlcheck at the database did not throw any suspicous messages.

      We have read that under some circumstances the question optimizer could pick/arrange a wrong query based on used table statistics and thus leading to performance issues. As in our case it was not to much traffic at the table and shouldn't have caused such a problem.

      Could that be a BUG or is it maybe a known or already solved problem? Maybe we are missing one bit of information.

      Here is the ALTER statement for the one table which had this problem when JOINed:

      # nothing too special
      ALTER TABLE `customerdb`.`niederlassung` CHANGE `sniederlassungbez` `sniederlassungbez` VARCHAR(255) CHARSET utf8 COLLATE utf8_general_ci NOT NULL;
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            avalor Andreas Hering
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.