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];

Remarks

Optimizing 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 optimization - Regular - This optimization is started using the OPTIMIZE TABLE <table_name>; statement. Unlike automatic background optimization that only uses a single thread, this optimization uses multiple threads to complete the process as fast as possible. By using multiple threads, the performance of any current workloads may be negatively impacted. See Managing Columnstore Segments for details.
• Manual optimization - Full - This optimization is started using the OPTIMIZE TABLE <table_name> FULL; statement, and behaves similarly to a regular manual optimization. However, the FULL clause causes a single row segment group to be generated, which results in a sort of the entire table. Due to these additional steps, this optimization takes more time than others. The tradeoff is that fewer sorted runs are created, which results in improved segment elimination when filtering on a key. See the Managing Columnstore Segments section for more information.
• Manual optimization - Flush - This optimization is started using the OPTIMIZE TABLE <table_name> FLUSH; statement and will flush any rows in the rowstore-backed row segment group into one or more columnstore-backed row segment groups. This will allow queries to run faster by putting data into 100% columnar format. This is also a good option if your rowstore segment is taking up a lot of memory that you would like to free up for other purposes.

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 up to five times faster.
• The FLUSH flag is not a valid option for rowstore tables and will be ignored.
• 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!