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

Performance problems due to incorrect table statistics after database imports

    XMLWordPrintable

Details

    Description

      We are seeing big performance problems for databases that were dumped (mysqldump) and then restored in an empty database. The query plans are sometimes completely inefficient and the 'ANALYZE FORMAT = JSON' list a low number of rows in some tables while there are for example 100k rows present ("rows": 1, "r_rows": 99924). This results in complete table scans and queries that normally take ~10ms, now take over 100s to complete.

      Doing an analyze table for all tables in the database (or the ones used in the query) fixes the problem. We are now implementing a cron job that does (mariadb-check --analyze --all-databases) every evening, but that is not an ideal situation.

      I think the problem is that the innodb_table_stats table statistics are not (correctly) updated during the bulk insert of sql data.

      We did not have this problem (or at least did not notice it) in MariaDB 10.4.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              stijn Stijn
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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