MemSQL uses a transaction buffer to handle workloads with bursts of writes that commit all at once. The transaction buffer will buffer write queries instead of queuing them, which accelerates the write workload. There is one transaction buffer per database. On leaves, database partitions are treated as databases, which means each leaf will have multiple transaction buffers.

The default size of each transaction buffer is 64 MB. This memory is reserved for each database, even if the database is empty. A rough calculation of memory used for transaction buffers in the cluster is shown below. This memory allocation can be seen in the result of SHOW STATUS EXTENDED as Alloc_durability_large.

Transaction buffer memory usage per leaf node = 64 MB * count(databases) * count(partitions per database)

The default transaction buffer size of 64 MB/database is a safe default for large clusters (i.e. leaf nodes with greater than 32-64 GB of RAM), but it can contribute to out-of-memory issues for smaller clusters (i.e. leaf nodes with less than 32 GB of RAM); therefore, you should reduce the transaction buffer size if your leaf nodes if you are running a small cluster.

It is especially important to reduce the transaction buffer for small clusters that have many databases and many partitions per database. This is true regardless of how much data is in each database, since the memory is reserved for accelerating writes even if the database is empty.

Calculating Transaction Buffer

Use the chart below to determine the appropriate size for your transaction buffer. If your workload involves bursts of writes, consider increasing these values. If there are many small databases in the cluster, consider reducing these values.

Updating the Transaction Buffer

The transaction buffer value is only applied when a node starts up, so changing the transaction buffer requires applying the change to all MemSQL nodes and then restarting them. This can be done by taking the entire cluster offline and performing the update, or by performing a rolling update while the cluster is online.

Offline Update

To update all of the nodes at once, first stop all of the nodes.

memsql-ops memsql-stop --all
****
Stopping cluster
Successfully stopped cluster

Update the transaction_buffer configuration value using the table above as a guideline.

memsql-ops memsql-update-config --key transaction_buffer --value 16m --all
****
Updating MemSQL configs
2019-02-10 06:42:26: Ja06eac [INFO] Changing config for MemSQL node 38CB9811550B91E8D9EFC355686328F79EF164EF on Agent A79f9df51c175447dabac6e436d845374 with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: J370268 [INFO] Changing config for MemSQL node 5A10D6DDD6665777EAFF5FB9F103C68D096BB62F on Agent A79f9df51c175447dabac6e436d845374 with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: Je61ba7 [INFO] Changing config for MemSQL node CFCEE8FD20C0ED41A2107146EADFD69FA30C44E3 on Agent A705d5fff862c4b5fa7c21a38367b48be with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: Jea0a96 [INFO] Changing config for MemSQL node 6D09DC011A7C121070C268A75EA04AD177522894 on Agent A78d1f036aae5459c912a811188487e8e with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: J87fbba [INFO] Changing config for MemSQL node F00E2487BA6FD13171817C347C3F508C1467FFED on Agent A705d5fff862c4b5fa7c21a38367b48be with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: Jd8ba5e [INFO] Changing config for MemSQL node EB837F771775C5F7D74CEA69F49E640740ADC2A7 on Agent A78d1f036aae5459c912a811188487e8e with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: J350975 [INFO] Changing config for MemSQL node 99A3CDBCE0E0E10670AC4519047B637015726506 on Agent Ad8aba4f302b14d59b5cbfa305cf23da4 with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: J0d40e0 [INFO] Changing config for MemSQL node F260D8A5E7BD6F64635DD5B78E7F11073CE42BB9 on Agent Ad8aba4f302b14d59b5cbfa305cf23da4 with values {"transaction_buffer":"16m"}
2019-02-10 06:42:27: Ja06eac [INFO] Successfully updated config for MemSQL node 38CB9811550B91E8D9EFC355686328F79EF164EF
2019-02-10 06:42:27: J370268 [INFO] Successfully updated config for MemSQL node 5A10D6DDD6665777EAFF5FB9F103C68D096BB62F
2019-02-10 06:42:27: Je61ba7 [INFO] Successfully updated config for MemSQL node CFCEE8FD20C0ED41A2107146EADFD69FA30C44E3
2019-02-10 06:42:27: Jea0a96 [INFO] Successfully updated config for MemSQL node 6D09DC011A7C121070C268A75EA04AD177522894
2019-02-10 06:42:27: Jd8ba5e [INFO] Successfully updated config for MemSQL node EB837F771775C5F7D74CEA69F49E640740ADC2A7
2019-02-10 06:42:27: J87fbba [INFO] Successfully updated config for MemSQL node F00E2487BA6FD13171817C347C3F508C1467FFED
2019-02-10 06:42:27: J350975 [INFO] Successfully updated config for MemSQL node 99A3CDBCE0E0E10670AC4519047B637015726506
2019-02-10 06:42:27: J0d40e0 [INFO] Successfully updated config for MemSQL node F260D8A5E7BD6F64635DD5B78E7F11073CE42BB9

memsql-ops memsql-start --all
****
Starting cluster
Successfully started cluster

To verify the value has been set correctly, connect to your cluster using a MySQL-compatible client, such as the built-in memsql client application that you can access from the terminal window, and view the transaction_buffer variable.

SHOW VARIABLES LIKE '%transaction_buffer%';
****
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| transaction_buffer | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

Rolling Update

If you require your cluster to be online during this operation, and your cluster has high availability enabled, set the transaction buffer through update-configas shown above, but restart the nodes following the steps outlined in Taking leaves offline without cluster downtime.

Post Update

After reducing the transaction buffer, monitor the workload. If writes start queuing or taking a long time to complete, your workload may benefit from a larger transaction buffer. If leaves do not have enough memory available for a larger transaction buffer, then consider allocating more memory per leaf.

Alternative: Consolidate Databases

If you have many small databases, consider combining them. This can be performed instead of, or in addition to reducing the transaction buffer.

For example consider the cluster below:

• Leaves: 4
• Memory per leaf: 32 GB
• CPU cores per leaf: 8
• Default partition count: 32
• Database count: 10

In this cluster, by default about 5 GB per leaf would be consumed simply for reserving memory for accelerating heavy write ingests. For such small leaves, the write load is likely also small and cannot make use of this large amount of memory. By adjusting the transaction buffer size and the count of databases, this memory consumption can be significantly reduced. See the chart below for examples:

Steps to consolidate databases:

1. Copy the tables into one or a few databases with an INSERT ... SELECT statement.
2. Switch the workload to write to the table in the new database.
3. Drop the old table.
4. Once all tables have been copied and dropped from the old database, drop the database.

Was this Article Helpful?

Thanks For Your Feedback!