Re: InnoDB and accesslog/ watchdog
Dries-
A patch for this? ALTER TABLE accesslog TYPE='InnoDB';
OK, maybe not a patch, but an altered file that we use as standard for all installs. For the record, we made the following tables InnoDB in Savannah: accesslog cache sessions watchdog We're also trying to be much better about our altering of Drupal code, so I insisted on a patch file (and a record of that file) without regard to the scope of the change. - Ken Rickard agentrickard
On 14 Aug 2006, at 18:46, Ken Rickard wrote:
For the record, we made the following tables InnoDB in Savannah:
accesslog cache sessions watchdog
Why those? How did you determine the set of tables? You should focus on the tables where there is lock contention ... and where concurrency matters. -- Dries Buytaert :: http://www.buytaert.net/
Dries Buytaert wrote:
On 14 Aug 2006, at 18:46, Ken Rickard wrote:
For the record, we made the following tables InnoDB in Savannah:
accesslog cache sessions watchdog
Why those? How did you determine the set of tables? You should focus on the tables where there is lock contention ... and where concurrency matters.
I can only guess why these tables were chosen: they have a lot of writes. Locking would only be an issue for the cache table. However, the tables can be subdivided again by tables which have a lot of reads too: sessions cache and those which are more or less write only: watchdog accesslog For the latter I advocate my merge table patch at http://drupal.org/node/78503 It would be nice if the postgresql people could investigate if this is usefull for them too. Some pointers I got in #postgresql: Partioning: http://www.postgresql.org/docs/current/static/ddl-partitioning.html Inheritance: http://www.postgresql.org/docs/current/static/ddl-inherit.html http://www.varlena.com/varlena/GeneralBits/98.php It would also be helpful if anybody could compare the use of InnoDB vs my patch. Cheers, Gerhard
On 15 Aug 2006, at 15:00, Gerhard Killesreiter wrote:
I can only guess why these tables were chosen: they have a lot of writes. Locking would only be an issue for the cache table.
And the variables table. Modules that frequently use variable_get()/variable_set() to store temporary results can bring your site to halt. variable_set() requires 4 SQL queries, and does a table lock that is likely to stall other Apache clients. :-)
For the latter I advocate my merge table patch at http://drupal.org/ node/78503
Did you actually benchmark that patch?
It would also be helpful if anybody could compare the use of InnoDB vs my patch.
I compared a InnoDB with a MyISAM cache table and noticed little or no performance gain. -- Dries Buytaert :: http://www.buytaert.net/
Dries Buytaert wrote:
On 15 Aug 2006, at 15:00, Gerhard Killesreiter wrote:
I can only guess why these tables were chosen: they have a lot of writes. Locking would only be an issue for the cache table.
And the variables table.
Modules that frequently use variable_get()/variable_set() to store temporary results can bring your site to halt. variable_set() requires 4 SQL queries, and does a table lock that is likely to stall other Apache clients. :-)
Who writes such modules? The only place that variable_set should be used is on admin pages.
For the latter I advocate my merge table patch at http://drupal.org/node/78503
Did you actually benchmark that patch?
My results were inconclusive. The only server where I could do a test with a large table is currently in need of a new setup. My main reason for writing this patch was the annoying behaviour of the statistics.module to switch off logging when the site is throttled. That totally invalidates the use of the module as a logging tool. IIRC the reason to throttle it was that we were afraid that the inserts on the (rather big) accesslog table would bring too much stress on the server if there are a lot of visitors. I think that if you want the logging to stop you should throttle the whole module. With my patch there won't be a reason to not change the throttling behaviour.
It would also be helpful if anybody could compare the use of InnoDB vs my patch.
I compared a InnoDB with a MyISAM cache table and noticed little or no performance gain.
I think that to notice any real difference (also wrt to my patch) you would need to set up a different test. The usual ab approach won't work here. In my opinion you should use siege or a similar tool to simulate a largish number of users traversing the site and set fire on the server. Under these circumstances any differences will be more apparent. The same is also true for my cache table split patch: Without running it on drupal.org I would never have been sure it is an improvement. Cheers, Gerhard
On Wed, 16 Aug 2006, Gerhard Killesreiter wrote:
Dries Buytaert wrote:
On 15 Aug 2006, at 15:00, Gerhard Killesreiter wrote:
I can only guess why these tables were chosen: they have a lot of writes. Locking would only be an issue for the cache table.
And the variables table.
Modules that frequently use variable_get()/variable_set() to store temporary results can bring your site to halt. variable_set() requires 4 SQL queries, and does a table lock that is likely to stall other Apache clients. :-)
Who writes such modules? The only place that variable_set should be used is on admin pages.
</lurk> Why not keep a global array of variables changed during the current request, and persist them during cleanup, instead of locking the table and persisting a single variable during each variable_set call? That would make doing multiple variable_sets in one request about as expensive as a single call to variable_set, as well as providing atomicity for multiple variables tweaked in one request. <lurk>
On Wed, 16 Aug 2006, Gerhard Killesreiter wrote:
Dries Buytaert wrote:
On 15 Aug 2006, at 15:00, Gerhard Killesreiter wrote:
I can only guess why these tables were chosen: they have a lot of writes. Locking would only be an issue for the cache table.
And the variables table.
Modules that frequently use variable_get()/variable_set() to store temporary results can bring your site to halt. variable_set() requires 4 SQL queries, and does a table lock that is likely to stall other Apache clients. :-)
Who writes such modules? The only place that variable_set should be used is on admin pages.
</lurk> Why not keep a global array of variables changed during the current request, and persist them during cleanup, instead of locking the table and persisting a single variable during each variable_set call?
That would make doing multiple variable_sets in one request about as expensive as a single call to variable_set, as well as providing atomicity for multiple variables tweaked in one request. <lurk>
Great idea, however, there is one drwaback, what if exit() is called before the variables are written?
johan@forngren.com wrote:
</lurk> Why not keep a global array of variables changed during the current request, and persist them during cleanup, instead of locking the table and persisting a single variable during each variable_set call?
That would make doing multiple variable_sets in one request about as expensive as a single call to variable_set, as well as providing atomicity for multiple variables tweaked in one request. <lurk>
Great idea, however, there is one drwaback, what if exit() is called before the variables are written?
Smart-ass answer: don't do that. But truly, one who is writing modules for Drupal should follow the Drupal API. If that API says never call exit(), then developers should not do that. Is it not already the case that calling exit() prevents hook_exit() and hook_footer() from being called, for instance?
Chris Johnson skrev:
johan@forngren.com wrote:
</lurk> Why not keep a global array of variables changed during the current request, and persist them during cleanup, instead of locking the table and persisting a single variable during each variable_set call?
That would make doing multiple variable_sets in one request about as expensive as a single call to variable_set, as well as providing atomicity for multiple variables tweaked in one request. <lurk>
Great idea, however, there is one drwaback, what if exit() is called before the variables are written?
Smart-ass answer: don't do that.
But truly, one who is writing modules for Drupal should follow the Drupal API. If that API says never call exit(), then developers should not do that.
Is it not already the case that calling exit() prevents hook_exit() and hook_footer() from being called, for instance?
Perheps we shoud add this to the coding standards then?
Johan Forngren skrev:
Chris Johnson skrev:
johan@forngren.com wrote:
</lurk> Why not keep a global array of variables changed during the current request, and persist them during cleanup, instead of locking the table and persisting a single variable during each variable_set call?
That would make doing multiple variable_sets in one request about as expensive as a single call to variable_set, as well as providing atomicity for multiple variables tweaked in one request. <lurk>
Great idea, however, there is one drwaback, what if exit() is called before the variables are written?
Smart-ass answer: don't do that.
But truly, one who is writing modules for Drupal should follow the Drupal API. If that API says never call exit(), then developers should not do that.
Is it not already the case that calling exit() prevents hook_exit() and hook_footer() from being called, for instance?
Perheps we shoud add this to the coding standards then?
Any progress on this? I _really_ like the approach...
participants (7)
-
Brandon Bergren -
Chris Johnson -
Dries Buytaert -
Gerhard Killesreiter -
Johan Forngren -
johan＠forngren.com
-
Ken Rickard