[development] Drupal could be 10x times faster with correct indexing (no flame war)

[Jean-Michel Pouré]

Le lundi 30 juin 2008 à 15:35 -0500, David Timothy Strauss a écrit :
> My reply may have been overly blunt, but it's insulting to have
> someone post to our development list promising a 10x performance boost
> from methods we've clearly considered and implemented. Development
> work and discussion surrounding database indexes is easy to find with
> a quick search of Drupal.org.

I don't want to start a flame war. 

First, I have to apoligize because it seems that some indexes are
missing in my PostgreSQL schema, whereas indexes are set in the MySQL
schema. There are some missing indexes, but very few. As proposed, I
will try to submit patches. This will not change the overall performance
by 10x.

Secondly, my Apache installation lacked a maximum PostgreSQL pconnect
number of connections. My fault, because I migrated recently and forgot
to set a maximum number of pconnect. When using Drupal without cache,
attackers were able to create a large number of database sockets,
because Drupal runs multiple SQL commands. After 10 minutes my dedicated
3Gb RAM server was down and I was a little bit surprised. It seems that
Drupal should always be installed with normal cache option. Otherwize,
too many SQL commands are sent. Sometimes, I could notice that Drupal
would send 5 times the same SELECT on user command.

Third, I looked at the shouts module and discovered there was no
indexing at all. The attackers only needed to call this module
repetedly. A lot of modules use a SELECT to query a table and then
explore each subsequent node sending a SELECT on each node. This is a
clear overhead and can be used to attack a Drupal site. Looking at the
Feeded, it seems to work this way too, except that it is correctly
indexed. But this is the Drupal way to explore nodes I believe. I don't
know if theses SELECTs can be replaced with LEFT JOINS.

Fourth, sorry I didn't mean to attack Drupal developers. Drupal is far
too important for me to annoy you.

I will do my best to submit patches about the few SQL commands that need
indexing. Forgot this silly 10x times faster title. Drupal could be much
faster if using LEF JOINs and indexes when appropriate, but this would
not be 10 times.

Kind regards,
Jean-Michel