Upgraded to 7.0 - AutoStat value missing

Help Query Tuning
1

After upgrading to MemSQL 7.0, we are not seeing the value of AUTOSTAT=TRUE or AUTOSTAT=FALSE when viewing the SHOW CREATE TABLE of tables.
This is even when the command to ENABLE AUTOSTATS was run against the table.

2

Can you post an example of the SHOW CREATE TABLE?

Here is what it looks like in my example:

show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
  /*!90618 , SHARD KEY () */ 
) /*!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE */ /*!90623 SQL_MODE='STRICT_ALL_TABLES' */ |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The relevant part is AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE at the end. In 6.x it showed AUTOSTATS_ENABLED={TRUE|FALSE}, now there are more different types of autostats with more granular settings which are shown here. You can find a description of these modes at SingleStoreDB Cloud · SingleStore Documentation

3

Those table attributes are the same that I was seeing, which was confusing because the script listed to update the AUTOSTATS for tables looks for the table setting AUTOSTATS_ENABLED=FALSE, and wasn’t being found after upgrading to 7.0.
if echo "$show_create_table" | grep -q "AUTOSTATS_ENABLED=FALSE"

4

Thanks for pointing that out, that part of the documentation is outdated and we’ll get it updated.

5

A quick fix is to change the grep from “AUTOSTATS_ENABLED=FALSE” to “AUTOSTATS_HISTOGRAM_MODE=OFF” which would work to detect tables that were upgraded from 6 to 7 and have not yet had autostats enabled.

6

Excellent, thank you