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.
OPTIMIZE TABLE table_name [FULL | FLUSH];
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
FULLclause 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 TABLEphysically 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.
FLUSHflag is not a valid option for rowstore tables and will be ignored.
FULLflag is not a valid option for rowstore tables; the syntax is accepted but it will be ignored by MemSQL.