mysql merge tables and partitioning
nice article at http://sebastian-bergmann.de/archives/655-Partitioning-with-Dates-in-MySQL-5.... it describes mysql's new partitioning feature which seems like a good idea for our accesslog table. seems like drupal doesn't even have to know about the partitions, apart from system.install. if you haven't looked into it, access logging is a bad performance bottleneck in drupal. if you want a fast site, i recommend disabling statistics.module entirely. if your DBMS isn't mysql 5.1+ this would not apply to your site. thats obvious, but i'm quite certain someone will complain that this is mysql only and 5.1_ only and all that. yeah, we know. move on.
Hi Moshe, Killes and m3avrck did loads of work in the area of speedups for heavy-write tables like watchdog and accesslog. Their work nearly made it into 5.1, but it was too late. The functionality will be going into 6.x. What killes found was merge tables. This will work on all Drupal supported MySQL versions. For those from PostgreSQL ilk, we also will get a speed improvement. Killes requested I look into it and sure enough there is table partitioning in our wonderful DBMS for all Drupal supported versions. Yay for us! :) For more information about the patches and how it was done see: http://drupal.org/node/78503 Cheers, -- Sammy Spets Synerger Pty Ltd http://synerger.com On 31-Jan-07 22:50, Moshe Weitzman wrote:
nice article at http://sebastian-bergmann.de/archives/655-Partitioning-with-Dates-in-MySQL-5.... it describes mysql's new partitioning feature which seems like a good idea for our accesslog table. seems like drupal doesn't even have to know about the partitions, apart from system.install.
if you haven't looked into it, access logging is a bad performance bottleneck in drupal. if you want a fast site, i recommend disabling statistics.module entirely.
if your DBMS isn't mysql 5.1+ this would not apply to your site. thats obvious, but i'm quite certain someone will complain that this is mysql only and 5.1_ only and all that. yeah, we know. move on.
Clarification. The functionality is targetting 6.x but there is still the hurdle of acceptance. Place comments into the issue post below if you support the inclusion (we'll need to get better benchmarks most likely). Cheers, -- Sammy Spets Synerger Pty Ltd http://synerger.com On 01-Feb-07 23:09, Sammy Spets wrote:
Hi Moshe,
Killes and m3avrck did loads of work in the area of speedups for heavy-write tables like watchdog and accesslog. Their work nearly made it into 5.1, but it was too late. The functionality will be going into 6.x.
What killes found was merge tables. This will work on all Drupal supported MySQL versions.
For those from PostgreSQL ilk, we also will get a speed improvement. Killes requested I look into it and sure enough there is table partitioning in our wonderful DBMS for all Drupal supported versions. Yay for us! :)
For more information about the patches and how it was done see: http://drupal.org/node/78503
Cheers,
-- Sammy Spets Synerger Pty Ltd http://synerger.com
On 31-Jan-07 22:50, Moshe Weitzman wrote:
nice article at http://sebastian-bergmann.de/archives/655-Partitioning-with-Dates-in-MySQL-5.... it describes mysql's new partitioning feature which seems like a good idea for our accesslog table. seems like drupal doesn't even have to know about the partitions, apart from system.install.
if you haven't looked into it, access logging is a bad performance bottleneck in drupal. if you want a fast site, i recommend disabling statistics.module entirely.
if your DBMS isn't mysql 5.1+ this would not apply to your site. thats obvious, but i'm quite certain someone will complain that this is mysql only and 5.1_ only and all that. yeah, we know. move on.
On 01 Feb 2007, at 13:09, Sammy Spets wrote:
Killes and m3avrck did loads of work in the area of speedups for heavy-write tables like watchdog and accesslog. Their work nearly made it into 5.1, but it was too late. The functionality will be going into 6.x.
What killes found was merge tables. This will work on all Drupal supported MySQL versions.
I talked to a MySQL employee and merge tables will probably disappear. They are deprecated by the partitioning.
For more information about the patches and how it was done see: http://drupal.org/node/78503
-- Dries Buytaert :: http://www.buytaert.net/
Dries Buytaert wrote:
On 01 Feb 2007, at 13:09, Sammy Spets wrote:
Killes and m3avrck did loads of work in the area of speedups for heavy-write tables like watchdog and accesslog. Their work nearly made it into 5.1, but it was too late. The functionality will be going into 6.x.
What killes found was merge tables. This will work on all Drupal supported MySQL versions.
I talked to a MySQL employee and merge tables will probably disappear. They are deprecated by the partitioning.
Well, that means the _might_ disappear in MySQL 5.2 or whatever next version. I don't see a reason to not use them until then. The patch accounts for the fact that an install might not have merge tables, btw. Cheers, Gerhard
What killes found was merge tables. This will work on all Drupal supported MySQL versions.
I talked to a MySQL employee and merge tables will probably disappear. They are deprecated by the partitioning.
Yes, but we will require only MySQL 4.1 for Drupal 6 (EOL: 2006-12-31). MySQL 5 EOL is not yet known but it's safe to presume that Drupal 7 will run on MySQL 5. So, if it's "only" for the next two years, I vote on the MERGE tables. And, it may be three years, anyways.
What about using ARCHIVE table instead of MERGE. It's basically optimized for watchdog style data. http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html To me, it doesn't make sense to spend time making the db schema more complicated and relying on a table type that is already deprecated (and implemented only by one database). (Didn't people want a cleaner db design for the next release?). If you really want this type of behaviour for large log tables, think about working on it from a partitioning point of view which is supported by more db's (and will be more future-proof). -Rowan
Rowan Kerr wrote:
What about using ARCHIVE table instead of MERGE. It's basically optimized for watchdog style data.
http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html
I don't recall right now why I didn't go with Archive, but there was a reason to it. Looking at http://drupal.org/node/78503 the reason was probably that archive isn't in the default mysql install whereas merge seems to be.
To me, it doesn't make sense to spend time making the db schema more complicated and relying on a table type that is already deprecated (and
The deprecation isn't in any way official and would only play a role in the future. Like about in three years, when we develop Drupal 10 or 11.
implemented only by one database).
That's not true, PgSQL has a similar thing which my patch uses, thanks to Sammy's work.
(Didn't people want a cleaner db design for the next release?).
Didn't see an issue for this. Of course we always want our Db design to be as clean as possible, but when it comes to performance improvements I am more than just a little bit inclined to make amends.
If you really want this type of behaviour for large log tables, think about working on it from a partitioning point of view which is supported by more db's (and will be more future-proof).
But unusable in the here and now. I mainly worked on the patch because drupal.org was brought to a crawl by - among other things - slow inserts into big log tables. Drupal.org still runs on MySQL 4.0 (but we are in the process of changing that, but not to 5.1). Cheers, Gerhard
This sounds like a good case for an abstraction. -----Original Message----- From: development-bounces@drupal.org [mailto:development-bounces@drupal.org] On Behalf Of Rowan Kerr Sent: Thursday, February 01, 2007 10:30 AM To: development@drupal.org Subject: Re: [development] mysql merge tables and partitioning What about using ARCHIVE table instead of MERGE. It's basically optimized for watchdog style data. http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html To me, it doesn't make sense to spend time making the db schema more complicated and relying on a table type that is already deprecated (and implemented only by one database). (Didn't people want a cleaner db design for the next release?). If you really want this type of behaviour for large log tables, think about working on it from a partitioning point of view which is supported by more db's (and will be more future-proof). -Rowan -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.17.19/663 - Release Date: 2/1/2007 2:28 PM
participants (7)
-
Dries Buytaert -
Gerhard Killesreiter -
Karoly Negyesi -
Moshe Weitzman -
Rowan Kerr -
Sammy Spets -
Walt Daniels