This is more or less a D7 backport for the reason that I'm currently running daily into timeout issues in update.php and in other places on a very big site (~150.000 nodes). The main reason for this is the cache_clear_all() function that is using an very unperformant way to clear the tables with db_query("DELETE FROM {". $table ."}"); if a wildcard * is used. This is mostly the reason if we do a full cache clear.
The patch attached heavily speeds up the cache clear process by not deleting every single item and do a truncate on the whole table. My cache_filter table had 150.000 entries today and you can count yourself that this *must* run into a PHP timeout and heavy DB stress!
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | core_cache_clear_all_truncate_fix-D5.patch | 603 bytes | hass |
| #1 | core_cache_clear_all_truncate_fix2-D6.patch | 1.34 KB | hass |
| core_cache_clear_all_truncate_fix-D6.patch | 606 bytes | hass |
Comments
Comment #1
hass commentedFound another place in update.module that could better use TRUNCATE.
Comment #2
hass commentedNot that I care much about D5 any longer, but here is a patch if you'd like to commit.
Comment #3
hass commentedComment #4
hass commented~2 seconds for clearing all monster caches... this is such a great boost - you can really feel it - everywhere a full cache clear is executed - and this happens often in Drupal modules!
This patch is sooo trivial... I could simply set it RTBC myself... this are backports of #452936: Use db_truncate() in cache_clear_all()
Comment #5
gábor hojtsy1. Let's verify this works in the supported database engines. Since TRUNCATE is not part of any SQL standard. http://www.postgresql.org/docs/8.1/static/sql-truncate.html looks to support it but we need to look back to as much in versions as we support.
2. Fixes go first to Drupal 7.
3. Please do not RTBC your own patches.
Comment #6
hass commentedThis has already been implemented in D7 some months ago (see #4)... with
db_truncate()andTRUNCATE TABLE {table}works 100% with PgSQL and MySQL - only SQLite requires some special handling. Therefore we are fine in D6 and D5 to use it as is.Comment #7
gábor hojtsyThat it works in D7 databases doe not mean it works in D6 databases. D6 and D5 has different DB requirements, and we need to check according to that.
Comment #8
hass commentedOk, looking though the database servers documentation:
MySQL 4.1: http://dev.mysql.com/doc/refman/4.1/en/truncate-table.html looks working.
PgSQL 7.4: http://www.postgresql.org/docs/7.4/interactive/sql-truncate.html looks also working.
Do we need to test every version up to the current to verify?
Comment #9
damien tournoud commentedTRUNCATE is supported by both MySQL and PostgreSQL on versions we support on D6. Looks good to go.
But @hass: never *ever* mark as RTBC your own patches again.
Comment #10
hass commented*Sry*
Comment #11
gábor hojtsyCommitted to Drupal 6, thanks.