Op maandag 05 december 2005 07:42, schreef Dries Buytaert:

On 05 Dec 2005, at 04:29, David K Norman wrote:

...

Has it ever been discussed to run a query to optimize some of the more active tables in MySQL during cron.php runs? For example 'OPTIMIZE TABLE watchdog;'

OPTIMIZE TABLE is a MySQL-ism; it's not ANSI SQL.

MySQL.inc is not included in the module hooks AFAIK. so adding a hook_cron to mysql.inc is not an option. This could be added in a conditional structure to dba.module Ber

On Mon, Dec 05, 2005 at 09:19:18AM +0100, B?r Kessels wrote:

Op maandag 05 december 2005 07:42, schreef Dries Buytaert:

...

On 05 Dec 2005, at 04:29, David K Norman wrote:

...

Has it ever been discussed to run a query to optimize some of the more active tables in MySQL during cron.php runs? For example 'OPTIMIZE TABLE watchdog;'

OPTIMIZE TABLE is a MySQL-ism; it's not ANSI SQL.

MySQL.inc is not included in the module hooks AFAIK. so adding a hook_cron to mysql.inc is not an option.

This could be added in a conditional structure to dba.module

There are some task that should/could be done automatically by drupal core itself. Besides OPTIMIZE TABLE it'd be nice to making backups (database mainly, but maybe files too?) and for PostgreSQL running VACUUM (http://www.postgresql.org/docs/current/static/maintenance.html) There are some issues to remember with VACUUM: - there's autovacuum in 8.1 so we would need to check if it's enabled - vacuuming could be done by host. I don't know if hosts do vacuuming or not - it should be sheduled at low usage time - we should make sure not to vacuum the same database from two different sites using that database. Those issues would require at least some page with settings were you could enable/disable vaccuming and shedule it's time and frequency. -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!

A big -1 on having backup and optimisation stuff in core. A big +1 on putting that in contributed maintainance or database modules.

You have a point there. Maybe we should consider some convention of how, if at all, to extend apis, for example database wrappers, from contrib modules. We can see things like that being used already, although not very widespread. ---------- For example a module providing db_optimise() with a default catch all code, if possible. db_optimise_mysql() and db_optimise_pg() in other separate modules. There is merit in being able to use the api namespaces for such extensions, rather putting them in their own module naming convention. I know it is not in the SQL standards, but such a thing can be a useful extension. ---------- Cheers, Vlado

On Mon, Dec 05, 2005 at 10:30:15AM +0100, B?r Kessels wrote:

...

A big -1 on having backup and optimisation stuff in core. A big +1 on putting that in contributed maintainance or database modules.

Piotr Krukowiecki wrote:

I think backup stuff is essential element of any advanced system and should be in core.

Also, VACUMM in PostgreSQL not only optimizes but also marks deleted rows for reuse. If the database is not vacuumed it will grow infinitely.

I am opposed to adding this kind of thing to core. When I first read the suggestion, I thought, "yeah, why not use Drupal's cron to improve Drupal's performance by making sure the database tables are optimized?" Then I read the responses and thought about the problems. What is Drupal core? If it's a CMS or application framework, it's not the right place to put system and database administration tasks. It will conflict with current best practices for managing those tasks. Worst of all, it dilutes the strengths of Drupal by trying to make it a one-tool-fits-all solution, while bloating the code with features many people will never use. Should you optimize, vacuum and integrity check your database tables? Yes. But do it from the database administration level. Do it via system cron or other such method. The same is true of backups. They don't belong in Drupal core and are best done from the operating system level. Should these features be available in a non-core module? Sure. If it helps some people or some sites, it's worth doing. Is there a problem with doing backups or database maintenance that would benefit from being able to signal Drupal in some way to, say, display a "site temporarily unavailable" or "site temporarily read only" message, and then behave appropriately? Maybe. If so, providing an API or hooks that outside processes could call might be appropriate in core. ..chrisxj

2005/12/5, Khalid B <kb@2bits.com>:

-1 for having OPTIMIZE or VACUUM in core.

Writing a module to do this is fairly easy.

It can even be added to the dba module as an optional setting (how often to run optimize/vacuum and autodetect the database type to run the correct one).

great, this would be nice :) -- vi is a real WYSIWYG editor: you see text, you get text.

2005/12/5, Bèr Kessels <ber@webschuur.com>:

IMO this is not something that must be built into core. In a contrib: fine, but certainly not in core. I for one, running a flock of sites on multisite want to maintain my backups / optimisations and that stuff in crontab. Having to circumvent built-in backups, that otherwise would break my server backup system would be a hell for me.

A big -1 on having backup and optimisation stuff in core. A big +1 on putting that in contributed maintainance or database modules.

I agree. having such thing in core will down my server for similar reasons. -- vi is a real WYSIWYG editor: you see text, you get text.

...

...

Has it ever been discussed to run a query to optimize some of the more active tables in MySQL during cron.php runs? For example 'OPTIMIZE TABLE watchdog;'

OPTIMIZE TABLE is a MySQL-ism; it's not ANSI SQL. That's true, but from the currently summorted databases postgress carries out automatic optimisations, mysql accepts OPTIMIZE.

Can't we make an db_optimize() abstraction function, with mysql backend using OPTIMIZE TABLE, the default other doing nothing, and if more backends are added they will be resposible for doing something else than nothing.

This could be added in a conditional structure to dba.module maybe. Vlado