Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.26
-
Linux Centos 7.x
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).