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

ANALYZE TABLE far slower and collects wrong optimizer statistics

    XMLWordPrintable

    Details

      Description

      The relevant database used for long time without such issues under a few revisions of 10.3.x.
      It has 5 innodb tables, 3 of which have around 300k-500k rows 5-7 cols each the others are very small. The large tables contain all int/doubles.

      Larger table of 500k rows is:
      `ID1` int(10) unsigned NOT NULL,
      `ID2` mediumint(5) unsigned NOT NULL,
      `ID3` int(10) unsigned NOT NULL,
      `ID4` int(10) unsigned NOT NULL,
      `Value` double NOT NULL,
      Partitioned on ID1 (only one that is partitioned)

      On 10.3.26 (10.3.27 as same behaviour):
      Running ANALYZE TABLE for all tables (inc above) takes over 4 minutes vs 2-3 secs when running same on same db and same machine on 10.3.22. After it completes, it makes certain join queries (joining 4 tables in the db incl above) to become 10-0x slower, as the optimizer chooses wrong path per EXPLAIN order (compared to 10.3.22)

      Restarting the db on 10.3.22, running ANALYZE on it (taking 2-3 secs), then stopping it and restarting on 10.3.27, makes the query fast again. But gets very slow after another ANALYZE TABLE.

      This issue may be present since 10.3.23; can try and provide you info.

      This makes it impossible for us to upgrade.

      Also, can ANALYZE TABLE, for large tables, be made to run on a portion of the table as per given parameter? Seems it now is extermely slow for large tables, prohibitive to run for a 24x7 env (where we now run a few times per day).

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            technomagos@gmail.com Tech Magos
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration