Is it recommended to schedule ANALYZE queries on all tables?

We just experienced a huge speed improvement (x16 times) by running ANALYZE on a few tables after debugging some queries with unusual plans using the VISUAL EXPLAIN feature in MemSQL Studio.

The huge impact was probably caused by a larger data movement/regeneration yesterday, but it made us wonder if we should schedule some ANALYZE queries to always keep the statistics up-to-date.

The documentation mentions some automatic statistics collections, but strongly recommends executing ANALYZE queries manually.

We do mostly have rowstores but also a single columnstore with histograms.

I guess we would benefit from a daily/weekly statistics collection, but wonders why this isn’t happening automatically by MemSQL. The only real draw appears to be plan invalidation, which probably isn’t the worst drawback since you gave us the INTERPRET_FIRST option.

Is this the right direction to follow, or am I missing something?

For MemSQL 6.8 and earlier, I would recommend running ANALYZE after large loads or updates, and also nightly or weekly to account for changes after continuous updates.

MemSQL 7.0 will have fully-automatic statistics gathering on all table types, so the large majority of users will never have to run ANALYZE again.

Great news regarding 7.0 - thanks for sharing.

We’ll follow your recommendations with a combination of scheduled nightly and weekly executions until 7.0 is released.

Regarding the same point, is there a way to run analyze on all tables in a database instead of going at each table level.

We just wrote a quick procedure a few days ago that accepts the name of the database as a parameter.

  1. It hits the information_schema.Tables
  2. grabs the lists of tables - "select table_name from information_schema.tables where table_schema = ‘ABC’ "
  3. then loops through an execute immediate of "Analyze table XYZ "

It’s not handling the updating of the individual column histograms, but it accomplishes a quick analyze on a schedule.

1 Like

@sproksell

Would you mind sharing your procedure to the community :)?