Isotopp just pointed me to the blog of Ronald Bradford, a MySQL consultant, writing about a Drupal installation and some performance observations.
He observes a large table "watchdog":
Presently, volume and content is of a low volume, but expecting to ramp up. I do however find 90% of disk volume in one table called ‘watchdog’;
Well, yes, the watchdog table logs system messages. No big surprise so far, I guess. So, turning off database logging (Administer -> Site building -> Modules -> turn off Database logging module) or reduce the maximum number of entries in the database log at Administer -> Site Configuration -> Logging and alerts -> Database Logging will reduce the size of the watchdog table.
Robert continues with some "page not found" errors. Of course these have to be fixed somewhere, either by editing the content or fixing theme related bugs. Not really a Drupal issue, I guess.
Then he mentions repeated SQL queries:
Looking at just 1 random second of SQL logging shows 1200+ SELECT statements.
355 are SELECT changed FROM node
There is plenty of information regarding monitoring the Slow Queries in MySQL, but I have also promoted that’s it not the slow queries that ultimately slow a system down, but the 1000’s of repeating fast queries.
MySQL of course has the Query Cache to assist, but this is a course grade solution, and a high volume read/write environment this is meaningless.
There is a clear need for either a application level caching, or a database redesign to pull rather then poll this information, however without more in depth review of Drupal I can not make any judgment calls.
Drupal offers some kind of application level caching at Administer -> Site configuration -> Performance, so turning it on might already help, although I'm not sure whether that minimizes the SQL queries or to what degree.
Anyway, there are some more Drupal performance tips here:
Hope this helps... :-)