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 update the transaction_buffer configuration value using the table above as a guideline.

memsql-admin update-config --key transaction_buffer --value 16m --all
****
Toolbox is about to run 'memsqlctl update-config --key transaction_buffer --value 16m' on the following nodes:
    - On host node1:
      + E6EA453C7D9A8E35704C15F2B1EA52D82726EF57
      + DEE597764651DC8CB6381AD5D07FCA7F3B284B72
      + 98E6EA281C3723C035B22FB35108AA47359B7362
      + A1618FC9B1A07CF773B28035E283E376D31AD9A1

Would you like to continue? [y/N]: y
✓ Updated configuration on node1
Operation completed successfully

Then restart the nodes.

memsql-admin restart-node --all
****
memsql-admin restart-node --all
Toolbox is about to perform the following actions:
  · Restart all nodes in the cluster

Would you like to continue? [y/N]: y
✓ Stopped Master node on node1 (1/1)
✓ Successfully stopped Master node on 1 host
✓ Stopped Master node
✓ Stopped Aggregator nodes on node1 (1/1)
✓ Successfully stopped Aggregator nodes on 1 host
✓ Stopped Aggregator node
✓ Stopped Leaf nodes on node1 (1/1)
✓ Successfully stopped Leaf nodes on 1 host
✓ Stopped Leaf nodes
✓ Started Leaf nodes on node1 (1/1)
✓ Successfully started Leaf nodes on 1 host
✓ Successfully connected to Leaf nodes
✓ Started Aggregator nodes on node1 (1/1)
✓ Successfully started Aggregator nodes on 1 host
✓ Successfully connected to Aggregator node
✓ Started Master node on node1 (1/1)
✓ Successfully started Master node on 1 host
✓ Successfully connected to Master node
Operation completed successfully

To verify the value has been set correctly, connect to your cluster using a MySQL-compatible client, such as the one in memsql-client, 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!