- Development - lists.drupal.org

One-to-one tables considered harmful
by David Strauss 07 Jun '07

07 Jun '07

== What's bad about the current approach == The arguments for splitting table into one-to-one relationships are: * Improving performance on systems that can only use table-level locking * Minimizing invalidations of the query cache These are not good reasons considering their impacts. First, I'd like to adopt a development philosophy on the Drupal project: screw scalability if the database running Drupal doesn't support row-level locking. Sites using only table-level locks are doomed to scale poorly anyway because of the over-aggressive locking. We won't be able to prevent that disaster with the tiny improvements in lock granularity afforded by splitting tables into one-to-one table pairs. Drupal should still run on hosts allowing only table-level locks. We just need to stop weighing their unique performance concerns so heavily. Second, the query cache is a luxury, not a key part of database design. A site needs to run fast without the query cache because -- at some point -- the database will need to be restarted, which will dump the query cache. If the site can't survive without the query cache, it will enter a cycle of crashing/overloading the server before it can populate the cache. The query cache provides minimal support for the large variety of queries hitting the system. For example, a cached tracker query for me does nothing to help a cached track query for you. The query cache only works after the first run. If a query takes 45 seconds to run, the page load is in jeopardy. A failed page load to populate the query cache is unacceptable. Minimizing invalidations to the query cache by splitting tables yields far worse performance overall because we can't have keys span the tables. This will be discussed in detail in the next section. If we rely on the query cache, we're also being hypocrites with our stance on the first reason because many low-end hosts don't run the query cache. Either we care about scalability on low-end configurations or we don't. I'm suggesting "don't," but if people truly do want to care about low-end scalability, we can't use the query cache argument. == Why avoiding one-to-one joins is a good idea == Many of the slowest queries in Drupal (as seen on Drupal.org) are products of the following query plan: 1. Join the two one-to-one tables, maybe after a WHERE restriction on one. This creates a temporary table. 2. Finish processing the WHERE restrictions on the temporary table. 3. Filesort the results to meet ORDER BY criteria. An example of such a query would be finding all published nodes in order of most recent comments. Another would be finding all published nodes in order of most views today. These are extremely common queries in Drupal. The problem is caused by an insurmountable restriction in MySQL (and probably PostgreSQL): we cannot create a key that spans tables. Because we cannot create such a key, any query with WHERE or ORDER BY criteria spanning two one-to-one tables is doomed to a temp table and a filesort. To have these queries run without temp tables and filesorts, we need to have a key that has all WHERE criteria as a prefix and then has the ORDER BY criteria left over. The greatest paradox to me is that many of the worst one-to-one offenders are denormalized tables designed to improve performance. Because these denormalized tables contain calculated values that many queries use for WHERE and ORDER BY, the JOINs with them inevitably sink the query into temp table/filesort hell. == Where one-to-one is good == It's fine to have one-to-one tables if one table is only used in the JOIN and doesn't have WHERE and ORDER BY restrictions on it. For example, the one-to-one tables created by many content types that JOIN with {node_revisions} are great. You simply INNER JOIN {node_revisions} with the content type's table at little performance penalty. For this same reason, there's not much argument for denormalizing user names into other tables unless we're allowing people to sort on them. Even the forums don't allow sorting by user name. A simple JOIN from {blah}.uid to {users}.uid places almost no burden on the query. == What I'm doing about it == I'm working on a patch: Merge {node_comment_statistics} and {node_counter} into {node} http://drupal.org/node/148849 It also makes more intelligent choices for which calculated values to manage, which removes GREATEST() from numerous queries.

21 60

Should I submit a new module?
by Richard Morse 06 Jun '07

06 Jun '07

Hi! I found a recent need to be able to force clear the caches, but didn't want to install devel.module. I created a small module that just provides a menu item for clearing caches. (The scenario is that I have a page which is generated by the php filter; it wasn't being updated for anonymous users due to caching.) Is this something that other people would find useful? I would probably have the menu item be optional and use hook_form_alter to put a button on the performance settings page. If others are interested, I'll create a project on drupal.org and release it, but I wasn't certain if something already did this, or if people felt it just wasn't worth bothering with. Thanks, Ricky The information transmitted in this electronic communication is intended only for the person or entity to whom it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this information in error, please contact the Compliance HelpLine at 800-856-1983 and properly dispose of this information.

6 9

Need consensus on what "activity" means
by David Strauss 06 Jun '07

06 Jun '07

I'm working on a patch that refactors node_comment_statistics into node and performs many other query optimizations. One is that I'm tracking node "activity" as a combined timestamp for the node and the node's comments. Does activity include: 1. The node creation timestamp or the latest comment timestamp 2. #1 plus the node update timestamp Normally, I wouldn't email the dev list about a specific patch, but I need broad feedback because the combined "activity" timestamp will order forum topic and tracker results. The patch: http://drupal.org/node/148849

2 1

3 Languages in one drupal site - common forum
by Srimal Jayawardena 06 Jun '07

06 Jun '07

Hi I need to have a drupal site which supports 3 languages where all pages must have versions representing each of the 3 languages. However the discussion forum should be common to all 3 languages. How is this possible in drupal? Is there a specific module to handle this? Thanks in advance. Srimal.

5 4

New story submit fails on table prefix
by Tim Beelen 06 Jun '07

06 Jun '07

I'm not quite sure if this is the correct mailing list, but, I've ran into a bit of a problem: When submitting a new story in the 6 beta (checked out about 30 minutes ago) it returns: user warning: Table 'test.node' doesn't exist query: UPDATE node SET vid = 2 WHERE nid = 2 in /var/www/html/drupal/includes/database.mysqli.inc on line 154. I think it has to do with the fact that I'm using a table prefix and that test.node should actually be test.os_node. But I could be mistaking. Tim

2 1

Re: [development] Go PHP 5, Go!
by Michelle Cox 06 Jun '07

06 Jun '07

On 6/6/2007 12:25:02 AM, Larry Garfield (larry(a)garfieldtech.com) wrote: > It is our believe that this will allow web hosts a reason to upgrade and > the I don't know if you're looking for feedback as mundane as typos, but that should be "belief". While I'm on the sentence, "allow web hosts" sounds kind of funny. I think "give" would be a better word. Michelle

2 1

Problems making a proper patch with new failes and new directory
by Stefan Nagtegaal 06 Jun '07

06 Jun '07

First of all, I'm not sure I wrote this message to the proper list. Perhaps it should be on the support list, but as there are *a lot* of really good developers on this list, I think my question is more in place here than there. If not, I apoligize for that.. the main reason for my question is that I'm trying to make a proper patch for the following issue: http://drupal.org/node/140932 - Improve image-api: Image toolkits should be implemented as modules The problem is that the patch should add a directory 'gd', and place the new files (gd.info and gd.module) inside it. According to http://drupal.org/patch/create (Creating patches) it is possible to add new files by having the '-N' flag in your cvs diff command and adding the /newfile.inc/0/New file// lines in the 'CVS/ Entries' file. Is this possible with new to add directories? And how should I do that? Thank you for your time and help! Kind regards, Stefan Nagtegaal

2 1

Re: [development] Should I submit a new module?
by Karen Stevenson 06 Jun '07

06 Jun '07

I think something like this should really be in core because: 1) It's not really a big enough item to justify an entire module as a solution 2) It's desired/needed in nearly any setup I submitted an issue on that a while back at http://drupal.org/node/128635. Since I didn't provide a patch it has not gotten any attention and I don't have time to create a patch right now because of my father's illness, but perhaps someone else would like to follow up on it? Karen ----- Original Message ---- From: Michelle Cox <mcox(a)charter.net> To: development(a)drupal.org Sent: Tuesday, June 5, 2007 9:23:56 PM Subject: Re: [development] Should I submit a new module? I like the snippit idea. I don't want to install another module but a snippit would be nice. I currently go in the db and empty the table manually because I've had too many problems with keeping devel installed and only install it when I really need it. Michelle On 6/5/2007 1:58:03 PM, Peter Wolanin (pwolanin(a)gmail.com) wrote: > For such "mini" modules, I created a section of the snippets handbook > a while back: > > http://drupal.org/node/70903 > > To me, > it's not worth cluttering the download page and maintaining > something that's > only a few lines of code. However, they are about as > useful as a page snippet and can provide a basis for others to > customize. > > -Peter

1 0

movable type repositions
by Gabor Hojtsy 06 Jun '07

06 Jun '07

Hi, Seems like Movabletype (a Perl based blogging platform that was king before Wordpress) tries to get back the momentum which was lost when some licensing issues arised a few years back and many people migrated to Wordpress. They have a good looking redesign of the admin interface and a GPL based open source model for the code, as well as a new plugin architecture. They are aiming to get through the "blogging app" limitation (which many customizers cut through anyway) and become a more versatile CMS with social features (starting with OpenID of course :) http://www.readwriteweb.com/archives/movable_type_40.php http://www.movabletype.com/mt4/ Gabor

2 1

Re: [development] Should I submit a new module?
by Michelle Cox 06 Jun '07

06 Jun '07

I like the snippit idea. I don't want to install another module but a snippit would be nice. I currently go in the db and empty the table manually because I've had too many problems with keeping devel installed and only install it when I really need it. Michelle On 6/5/2007 1:58:03 PM, Peter Wolanin (pwolanin(a)gmail.com) wrote: > For such "mini" modules, I created a section of the snippets handbook > a while back: > > http://drupal.org/node/70903 > > To me, > it's not worth cluttering the download page and maintaining > something that's > only a few lines of code. However, they are about as > useful as a page snippet and can provide a basis for others to > customize. > > -Peter

1 0