Impact of large write transactions on cluster performance and stability

[SQLCaesar]

Hello Percona Community,

We would like to describe and understand the impact of large writes (megabytes of data or thousands of rows) submitted as a single transaction, which could severely impact cluster performance and stability.

How do these large writes impact the cluster and its replication mechanism, and which status variables and logs are worth checking?

Are there any tuning parameters that can make the cluster more robust to prevent from suffering the impact of such large writes?

Thank you in advance for your contribution!

[CTutte]

Hi SQLCaesar,

Large transactions are bad for multiple reasons. Just to name a few:

• For PXC , there is a size limitation and will impact performance and certification Percona XtraDB Cluster Limitations - Percona XtraDB Cluster
• For regular replication, a transaction with a long execution time will cause brief replication spikes
• Rollbacks are more expensive the longer the transaction
• Larger transaction will require more locks
• HLL (history list length) and number of undo pages created and maintained will grow the bigger the transaction. https://www.percona.com/blog/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/

HLL (in SHOW ENGINE INNODB STATUS) is a good metric to check

SHOW PROCESSLIST; can also show long lived executions

You should keep transaction size to be low , split large transactions into smaller chunks and commit every now and then rather than executing all in a single transaction trying to eat more than you can chew

Regards

[SQLCaesar]

Thank you so much @CTutte for your explanation and links provided. Now this topic seems to be much clearer to me.