How many queries can be used in a single MySQL or MariaDB transaction

Question

How many queries can be used in a single MySQL/MariaDB transaction?

Is there any limit for the number of queries used in a transaction? Currently I have tested with a large ZF1 project, a transaction with 175,000+ queries, and its working fine!

The transaction will be executed once in a month, but the number of queries can be increased in future.

Is there any limit to the number of queries, or any performance issue when increasing the volume of transaction?

$db = Zend_Db_Table_Abstract::getDefaultAdapter();

// begin database transaction
$db->beginTransaction();

try {
    // here comes the bulk processing with insert, update and delete queries
    $this->monthlyBatchProcessing();

    // commit all changes
    $db->commit();
} catch (Exception $ex) {

    // rollback changes upon exception
    $db->rollBack();

    throw $ex;
}

Answer 1

See the manual page for innodb_log_buffer_size. The setting is typically defaulted to 8M or 16M.

See your current size:

SHOW VARIABLES LIKE "%innodb_log_buffer_size%";

It can be set into the GB range. The downside, it takes away from system memory. The upside is that more can be squeezed into it before it is flushed to disk. So disk I/O is postponed and done in larger blocks versus more frequent smaller chunks.

A higher value will deliver better performance, no different than a large file copy performing substantially better than the same data size but over 100 to 1000 files. Granted that is a major over-simplification for it. The transactions still have the overhead of the innodb log itself to deal with.

To see the number of times the log buffer was deemed to be too small ("the number of times that buffer was flushed before proceeding"):

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';