[drupal-devel] prune tables

[Gerhard Killesreiter]

On Wed, 11 May 2005, Dries Buytaert wrote:

>
> Would it make sense to change the "Discard watchdog/access log entries
> older than" settings to use the number of SQL table rows, rather than
> the number of days?
>
> The problem?  Again, at drupal.org, I had these set to "3 days" which I
> felt was short enough for the tables not to grow unwieldy.  It wasn't
> until this week that I found out -- through extensive profiling -- that
> 3 days was too long.
>
> The point? Unless you are a Drupal developer, you won't profile your
> site.  This means, most users have no clue about how much data is kept
> track of, or why their site becomes slower and slower.
>
> The question? How can we make it so that this doesn't happen?  To me,
> having an option "Number of records to keep" rather than "Discard
> records older than" makes sense, because (i) it gives me control over
> the maximum number of table rows (ii) without having to worry about
> traffic bursts or a growing number of visitors (the cost is more likely
> to be constant over time).  It gives me a clue about the
> performance/resource ramifications of certain features.  Having a
> similar setting for the sessions- and history-table would make sense
> too.
>
> Would this degrade or improve usability?  Suggestions?

I am not too fond of the idea. Admins don't want statistics over number of
sql data rows but over time.

What I'd rather see is more than one watchdog / accesslog table.

There would be a small "current" table. When it has reached maybe 500
entries, those get moved to a big backup table and the current table gets
pruned. This could be done by cron. This would require locking, I think.

Which advantages does that have?

- We work with a small table on normal page views. Inserts are fast.
- We still have all the data we want on admin pages.

Cheers,
	Gerhard