ColumnStore is very slow in inserting single rows but fast to insert multiple rows. This is especially a problem in a master-slave setup where ColumnStore is is used on the slave for some of the tables in the master.
What we are trying to solve:
- Speed up inserts to ColumnStore to help ColumnStore on a slave to keep up
- The solution will also speed up single or few row insert's.
- Cache should be totally invisible for the normal user
- Cache usage should be crash safe. In no case will we loose any committed
- Cache should be able to hold data from thousands of independent insert
transactions as the master may have done each insert in a separate
transaction. The transactions can be intermixed with other transactions
for other tables.
Assumptions for ColumnStore usage:
- ColumnStore is most often used on a slave
- Lots of inserts, few updates and deletes.
- Occasional selects (much fewer than inserts)
Note that the above requirements require us to store the inserted data on a
persistent medium at commits.
- In this draft we are suggesting to use MyISAM because it's trivial to
have watermarks in MyISAM dynamic row format that tells us what is the
last committed transaction, even after a crash. This is not possible
with CVS. We can also not use MEMORY tables as it would not allow us
to meet the requirements.
- We may choose to instead use Aria instead of MyISAM. However even if
Aria would be used, we would not use crash safe tables as the overhead
for storing undo's for inserts is notable and not needed.
- The MyISAM table will not use any indexes, which make inserts very fast.
We need two syncs per commit (one for committing the data and one for
committing the water mark). All row data will be cached by the file
system, there is no internal cache.
- A new table handler, COLUMNSTORE_CACHE, that inheritens from
ColumnStore and overrides the following methods:
- Create table
- Creates table in MyISAM and then an identical table in ColumnStore
- Insert row into MyISAM
- ha_delete_row and ha_update
- Flush committed rows in cache as a separate transaction.
- Copy all not committed rows to ColumnStore.
- Run rest of transaction as normal transaction (slow but safe).
- In case of read lock, flush insert cache to ColumnStore up to
- Call columnstore::external_lock
- Inserts will limited to one transaction at a time. This can be crash safe
in MyISAM. In case of crash all not committed rows can trivially be
- Will not be 100% crash safe in the case where we crash exactly after
copying data but before we have time to truncate the cache
(very small window)
- In cache of flush, wait until currently running transaction using
ColumnStore has ended and then flush everything at once and truncate cache.
- Assumption is that there are no update and delete's in a transaction that
starts with an insert. (Most common case)
- Add handling of delete and update in a transaction that also used inserts
according to 'Solution'.
3) Add support for batched inserts without cpimport to ColumnStore.
This will speed up flushing of insert cache, LOAD DATA and INSERT SELECT.
- Add 'XA' kind of security around 'copy to ColumnStore, commit and truncate
MyISAM. This will make the cache totally crash safe.
- Add an interface for ColumnStore engine to ask for current transaction id
and after crash be able to ask if the id was committed.
- Add crash recovery to ensure that an interrupted the copy was fully done
and, if not done, redo it.
- Allow multiple inserts at same time.
- In case of flush, block new inserts, wait until old transactions has
committed and move all rows to ColumnStore at once.
- Don't truncate the table as part of copy. Only add a watermark in the
MyISAM table what is copied. This allows us to do copying of data while
other transactions are doing inserts.
- Truncate can be done when there is no inserts since last copy or
when the cached files has reached some size limit.
- Add a timer that flushes committed transactions from cache once in a while
(once per hour?)
- Add logic to know when inserts are idle and flush in idle time.
- FLUSH TABLE table_name on a cached table should wait until current
transactions has ended and flush all data to ColumnStore.
- Option to flush data to ColumnStore on server shutdown