Problem

The Zf_errorlog table in our database was originally created using the MyISAM storage engine, based on the assumption that it would offer better performance for our logging needs. However, this decision has led to scalability issues over time. Since MyISAM does not support row-level locking, high volumes of inserts and concurrent reads have begun to cause table-level locks, resulting in significant delays and blocked queries once the table exceeded around one million records.

Solution

To resolve this, we are migrating the Zf_errorlog table to the InnoDB storage engine, which provides row-level locking and better concurrency support. A new empty table is created with the same structure but using the InnoDB engine. The existing log data is bulk-copied into this new table, and a fulltext index is added to the message column to maintain search functionality.

After the initial data transfer, the tables are swapped in a short maintenance window by renaming them. Any log entries that were added to the original table after the first copy are then transferred into the new table, excluding the primary key (id) to ensure no conflicts occur with the new table’s auto-increment logic. This approach allows for a seamless transition with minimal downtime and ensures long-term performance and stability of the logging system.