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

mariadb-upgrade / check tables for upgrade should update table stats

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • N/A
    • Upgrades
    • None

    Description

      From Cecilia Lovlia, updating table statistics should be part of the upgrade process.

      After an upgrade its always hoped queries will be better. Running around to find analyze table is an effective downtime while you are in a place where a downgrade is hard. Lets simplify this for users.

      upgrade mechanism used:

      1. uninstall
      2. change repo
      3. install
      4. mariadb-upgrade

      Attachments

        Activity

          I don't think updating statistics is part of the upgrade. The purpose of mariadb-upgade to modify the metadata (and data if needed) to work correctly in the new server version.

          serg Sergei Golubchik added a comment - I don't think updating statistics is part of the upgrade. The purpose of mariadb-upgade to modify the metadata (and data if needed) to work correctly in the new server version.

          My response from the zulipchat conversation:

          Normally Analyze table should not be needed when upgrading and statistics from previous versions should work "the same" for newer versions.
          There is however one thing that may be different for 11.0, that would be good to get verified.
          The mysql.table_stats table contains the number of rows of the table when the last analyze table was run.
          If that number is very wrong (for example if one runs analyze table on an empty table), it may be possible that 11.0 acts worse than earlier MariaDB versions as it does more precise calculations of costs and if the number of rows are very different from the real value, it may affect 11.0 worse. (This is just a hypothesis).
          I am considering to add code to not rely on mysql.table_stats for any engine that can give a reasonable value for the number of rows in the table (InnoDB, MyISAM, Aria etc).
          @Cecilia Lovlia do you happen to have a backup of the database before you did the upgrade ?
          If yes, could you check the values in mysql.table_stats for the tables that gave you problems and see if the value for "cardinality" did match the number of rows in the corresponding table?
          Another thing you could do to check if analyze was needed is to remove the table from mysql.column_stats, mysql.index_stats and mysql.table_stats and do 'flush tables'.
          If the query is still fast, then your problem was that some statistics data in the above tables where out of date.
          If not, then there is some other issue with 11.0 optimizer that I would like to be able to investigate more.

          monty Michael Widenius added a comment - My response from the zulipchat conversation: Normally Analyze table should not be needed when upgrading and statistics from previous versions should work "the same" for newer versions. There is however one thing that may be different for 11.0, that would be good to get verified. The mysql.table_stats table contains the number of rows of the table when the last analyze table was run. If that number is very wrong (for example if one runs analyze table on an empty table), it may be possible that 11.0 acts worse than earlier MariaDB versions as it does more precise calculations of costs and if the number of rows are very different from the real value, it may affect 11.0 worse. (This is just a hypothesis). I am considering to add code to not rely on mysql.table_stats for any engine that can give a reasonable value for the number of rows in the table (InnoDB, MyISAM, Aria etc). @Cecilia Lovlia do you happen to have a backup of the database before you did the upgrade ? If yes, could you check the values in mysql.table_stats for the tables that gave you problems and see if the value for "cardinality" did match the number of rows in the corresponding table? Another thing you could do to check if analyze was needed is to remove the table from mysql.column_stats, mysql.index_stats and mysql.table_stats and do 'flush tables'. If the query is still fast, then your problem was that some statistics data in the above tables where out of date. If not, then there is some other issue with 11.0 optimizer that I would like to be able to investigate more.

          People

            Unassigned Unassigned
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.