Performs optimizations on the storage of rows in a table.

Optimizing Columnstore Tables

This topic assumes familiarity with the concepts of columnstore table layout, including segments, row segment groups, and background mergers. For more on these topics, see Managing Columnstore Segments.

MemSQL automatically runs optimization routines on tables, but they can also be started manually.

Syntax

OPTIMIZE TABLE table_name [FULL | FLUSH]

Arguments

The types of optimization performed by OPTIMIZE TABLE are discussed below.

• Without any arguments, OPTIMIZE TABLE runs a Manual merge.
• With the FLUSH argument, OPTIMIZE TABLE runs a Manual flush. It does not run a Manual merge like OPTIMIZE TABLE.
• With the FULL argument, OPTIMIZE TABLE runs both a Manual merge - Full and a Manual flush (in MemSQL 6.0 and later). In MemSQL 5.8 and earlier, it runs only a Manual merge - Full.

Remarks

There are a few types of optimization performed on columnstore tables:

• Automatic background optimization - MemSQL has background threads that automatically optimize columnstore indexes as needed. These optimizations are run in a single thread to minimize impact to concurrent query workloads. The background optimization will attempt to keep the number of row segment groups low, but will not attempt to create a single row segment group due to the cost of this operation.
• Manual merge - The OPTIMIZE TABLE table_name statement runs a foreground optimization which uses multiple threads to complete the process as fast as possible, unlike automatic background optimization which only uses a single thread. As a result, this may negatively impact the performance of other concurrent workloads.
  • Manual merge - Full - With the FULL option, the OPTIMIZE TABLE command sorts the entire table, creating a single sorted row segment group. This is more expensive and therefore takes longer than a regular manual optimization. The potential benefit is that because it sorts the entire table into a single sorted row segment group instead of a small number of them, read queries may perform faster. However, this benefit diminishes as writes are made to the table. See the Managing Columnstore Segments section for more information.
• Manual flush - This OPTIMIZE TABLE table_name FLUSH statement will flush any rows in the in-memory rowstore-format row segment group into one or more columnstore-format row segment groups. Rows in the in-memory rowstore-format group are automatically flushed to the columnstore format in the background, so this command is generally not necessary. Some potential benefits are that this may be helpful if your rowstore segment is taking up a lot of memory that you would like to free up for other purposes, and putting all data into the columnar format may allow queries to run faster.

See Managing Columnstore Segments for details.

Optimizing Rowstore Tables

For rowstore tables, OPTIMIZE TABLE physically sorts the data in memory by the primary key and optimizes the organization of the table’s internal structures, so primary key table scans will execute significantly faster. However, this benefit diminishes as writes are made to the table. It also triggers garbage collection on the table.

Syntax

OPTIMIZE TABLE table_name

Arguments

• The FLUSH flag is not a valid option for rowstore tables.
• The FULL flag is not a valid option for rowstore tables; the syntax is accepted but it will be ignored by MemSQL.

Was this Article Helpful?

Thanks For Your Feedback!