TYPO3 Core

This doesn't work as the behavior of DELETE is different from TRUNCATE, TRUNCATE will also reset the auto increment sequence, DELETE won't. This could produce unexpected results.

What unexpected results might occur because Autoindex is reset to zero?

We are talking about clearing the cache?

you didn't mention that...

Firsts, we need to check how dbal works with this. Secondly, truncate is identical to delete in speed for innodb tables.

Yes, interesting:

"TRUNCATE tbl_name is mapped to DELETE FROM tbl_name for InnoDB and doesn't reset the AUTO_INCREMENT counter."

Anyway, not all tables are InnoDB nor automatically converted to InnoDB when upgrading from older installation.

DELETE FROM cache_pages takes ages comparing to TRUNCATE cache_pages. Obviously it is extremely slower even for InnoDB.

Every time before deleting FE cache from admin panel, I have to do TRUNCATE manually, because cache_pages is 15 GB.

We have the same problem: Clearing the fe cache from within the backend takes several minutes and reduces page delivery performance, truncating the cache_* tables directly takes less than a second.

TRUNCATE on InnoDB is no longer mapped to DELETE since MySQL 5.0.3 (except if foreign keys reference to the table, which isn't the case for cache_hash AFAIK).

This means TRUNCATE is really faster. Much faster.

I would really like to see the patch applied.

But: If I get the patch right, it changes the behaviour for all tables since it is in a global function. If you really want to be sure you don't break anything, add a whitelist of truncate'able tables to the function.

This ones seems no to be a difficult one to solve and at the same time it could speed up cache clearing for big sites.

One site I have been working with is almost going down when clearing the page cache, since DELETE FROM cache_pages takes forever and locks the table.

Since TRUNCATE no longer is mapped to DELETE in InnoDB it seems even more needed to have this one changed.

Hi Ingo,

Please have a look at this one.

I have seen a TYPO3 website go down completely, because of cleaning the cache or updating the TS code. The website have around 20.000 pages and because of extensions with parameters, the cache_pages and cache_pagecontent have around 40.000 records each. Doing the delete is sooooo slow, and because of wait time for the DELETE, the queue in those tables gets enorm and MySQL crashes more or less hard. As a temporary work-around we run a TRUNCATE on the 3 cache_tables and the we click the backend button.

Thanks,

Lars

We are going to have a new caching framework in TYPO3 v 4.3. I am not sure that this issue still applies.

the new framework uses TRUNCATE

Lars, you should use InnoDB for cache tables anyway. If you have such long delays it means you use MyISAM, which is bad because of rea/write locks.

Closing the issue because it is no longer relevant