Hi,
couldn’t find anywhere about the effect of transaction buffer greater than 0m
on the persistence of my data/transactions.
does it mean that on a DB with 16 partitions and 1m transaction buffer means I can LOSE 16mb of inserted data/updates if power failure/node crash? do I need transaction buffer 0m to be strongly consistent?
also, can I set it less than 1m?
Thanks in advance.
MemSQL 6.8 writes data to the log storage device (disk, SSD) asynchronously by default. Up to transaction_buffer bytes can be queued for writing per database partition. But although it is asynchronous, it is very fast. The background tasks writing the data work all the time. So the likelihood that the transaction buffer would be full at the time of a crash is very small. But when transactions ask to commit, by default, they don’t block waiting for data to get forced to disk.
In 6.8, you can set transaction_buffer to 0 go get synchronous durability, which means transactions wait for their log records to be forced to disk before their commit returns. With fast NVMe SSDs as the log device, setting transaction_buffer to 0 might slow throughput down only a little, like 10%. With slow SSDs, it might slow you down more, up to 50% or so, compared to using asynchronous durability (i.e. transaction_buffer > 0).
In 7.0, synchronous durability will be controlled in a different way (see SingleStoreDB Cloud · SingleStore Documentation) . And it will have minimal performance penalty compared with asynchronous durability.
Thanks for the detailed answer.
can’t wait for memsql 7!