What does fragmentation on Rowstore Table mean?
When a node starts up, it recovers data in memory. This means rowstore tables are loaded into memory, in order of the primary key. For new data that is written, it will be written to new pages of memory. The index will account for this, but the data will not be located in memory together with the rest of the similar rows. As data is deleted, previous areas of memory will be freed up. However, if they are very small then new data may not be able to be written into the slots that were opened up so they may have to be written to new pages of memory. Over time if there are many small writes and deletes, it can cause fragmentation of memory use, where there are many tiny gaps that are allocated but are not actually written with data. Both restarting a leaf, as well as running OPTIMIZE TABLE on a rowstore table will get the table loaded back in order, compactly. This frees up the memory, by defragmenting it.
Please check for more information on that process and resolution.
If you see memory fragmentation increase quickly, consider running OPTIMIZE TABLE periodically on rowstore tables during your lowest traffic time. This may be needed if the workload is highly transactional and there are many tables and leaves that are typically running for long periods of time (for months) without a restart. However please consider the two points below if you find this necessary:
MemSQL supports online OPTIMIZE TABLE, which means that you can read and write while the table is being optimized. OPTIMIZE TABLE on a sharded table is always executed online. Note that the online OPTIMIZE TABLE will not begin optimizing the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before optimizing the table and ensures consistency of results from queries on the table since the time of execution of OPTIMIZE TABLE. As soon as the in-progress reads and writes are complete and the OPTIMIZE TABLE command begins optimizing the table, new reads and writes will proceed as normal. This blocking period usually lasts on the order of milliseconds.
If you are running frequent OPTIMIZE TABLE statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since it blocks other queries from starting while it waits for the completion of long-running queries.
There is a way to gauge memory fragmentation by querying information_schema.table_statistics, and looking for excessive MEMORY_USE compared to the ROWS.
Rowstore table memory fragmentation would show up as excessive MEMORY_USE (compared to the ROWS) in information_schema.table_statistics, and fragmentation on variable-allocated memory shows up in SHOW STATUS EXTENDED in the Alloc_variable_XXX outputs, this can be found in the cluster report.