optimize table in cron
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;'
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. -- Dries Buytaert :: http://www.buytaert.net/
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
2005/12/5, Bèr Kessels <ber@webschuur.com>:
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.
+1 David : all good dev trying to make a portable app must try to be closer to the standart and open to a wide env. if possible...
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
+2 Bèr : this doesn't go against the previous point and help much...
Ber
-- vi is a real WYSIWYG editor: you see text, you get text.
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!
Hi
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)
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. Ber
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.
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. -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
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
-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).
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.
On Monday 05 December 2005 12:16, Chris Johnson wrote:
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.
I concur. -1 for database backups or optimizations in core +1 for an optional module to help novices or those who don't have access to do it as a part of regular sysadmin (and I agree that "dba" is a good module to add this feature, rather than building an additional module) -- ------------------------------------------------------------------------------- Scott Courtney Drupal user name: "syscrusher" http://drupal.org/user/9184 scott at 4th dot com Drupal projects: http://drupal.org/project/user/9184 Sandbox: http://cvs.drupal.org/viewcvs/drupal/contributions/sandbox/syscrusher
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.
On 12/5/05 4:30 AM, Bèr Kessels wrote:
Hi
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)
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.
A big -1 from me as well for this in core. My take on it goes something like this: anyone running a site (or group of sites) large enough where regular optimizations are necessary is likely going to put something in place that better suits their environment... and those that aren't aware of this already (either that table optimizations could/should happen) likely aren't running a site where it's as big an issue. or something. I too certainly don't wanna have to spend time working around it. -- James Walker :: http://walkah.net/ :: xmpp:walkah@walkah.net
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
participants (10)
-
Bèr Kessels -
Chris Johnson -
David K Norman -
Dries Buytaert -
Gildas COTOMALE -
James Walker -
Khalid B -
piotr@mallorn.ii.uj.edu.pl -
Syscrusher -
vlado