Why is Truncating a Table So Slow?

It's no secret that truncating a table can be a slow process. This is because changes are recorded in binary logs and DML triggers are executed. It's faster than deleting the existing table and creating a new one, but it still takes time. The DELETE statement acquires row-level locks, while TRUNCATE TABLE locks the data pages before deleting the data.

From documents, TRUNCATE deletes the rows from the disk immediately, while DELETE will only mark them as dirty and wait for VACUUM. This can make a big difference in performance. When we look at the log usage percentage when the data in the TRUNCATE statement is deleted, it's clear that TRUNCATE uses fewer resources than the DELETE statement. To avoid slow truncation performance, it's important to create a smaller cache dedicated to the specific (partitioned) object.

Using TRUNCATE TABLE in an InnoDB table requires complete table locking because TRUNCATE TABLE is DDL (data definition language) and not DML (data manipulation). This means that one of the slaves may not receive any readings while performing TRUNCATE TABLE if it is a dedicated slave and the process he reads from that slave is inactive. To prevent this from happening again in the future, it's important to create a new table in a locally managed table space with a uniform extension size of 10 MB or more. It's also important to note that TRUNCATE will not activate the DELETE trigger, even though the DELETE statement will trigger the DELETE trigger.

If there are any uncommitted transactions in user_engagements, it could also contain a TRUNCATE TABLE. TRUNCATE TABLE locks the entire table to remove data from a table; therefore, this command also uses less transaction space than DELETE. TRUNCATE transactions can be undone in database engines such as SQL Server and PostgreSQL, but not in MySQL and Oracle. This error can occur when you DROP or TRUNCATE a table and it has a large buffer pool, there may be a server crash or a crash of several seconds, causing the table to become unresponsive.

So why is truncating a table so slow? It's because changes are recorded in binary logs and DML triggers are executed. The DELETE statement acquires row-level locks, while TRUNCATE TABLE locks the data pages before deleting the data. It also uses fewer resources than the DELETE statement in all respects and requires complete table locking because it is DDL (data definition language) and not DML (data manipulation). To prevent slow truncation performance, it's important to create a smaller cache dedicated to the specific (partitioned) object and create a new table in a locally managed table space with a uniform extension size of 10 MB or more.

It's also important to note that TRUNCATE will not activate the DELETE trigger.