[development] drupal on postgresql benchmark

[Earnie Boyd]

Quoting Larry Garfield <larry at garfieldtech.com>:

> On Monday 26 November 2007, Earnie Boyd wrote:
>
>> >> Incidentally, there are lots of places where Drupal could use
>> >> transactions when they're available.  user_add and node_save would
>> >> both be a lot more DB-crash-resistant, for starters.
>> >>
>> >> jh
>> >
>> > The problem is that transaction support is not universal.  In MySQL,
>> > for example, if you roll back a transaction it will roll back but
>> > throw a warning on any MyISAM tables that were affected, so unless
>> > you're using no MyISAM tables the rollback is not actually complete
>> > or atomic.  If you're on shared hosting, 95% chance you're on MyISAM.
>>
>> If the choice is to use InnoDB then the port should default to InnoDB.
>> Another port can be used for a MyISAM default.
>
> I have no idea what you're talking about. :-)  MySQL can be run quite happily
> with some tables InnoDB, some MyISAM.  You can also run a Master/slave
> configuration with the Master InnoDB and the slaves MyISAM (or vice versa,
> although why you'd want to I have no idea).  That means sometimes a
> transaction may not rollback properly, and other times it will.  We can't
> have "two Drupals", one that uses transactions and one that doesn't (if
> that's what you mean by port, since TCP port wouldn't make any sense in this
> context).
>

My point is, if you want InnoDB for transactions then don't allow 
MyISAM tables within the the DB to prevent your point that a 
transaction may not rollback properly.  I understand the MyISAM and 
InnoDB coexist within MySQL table structures but allowing it to happen 
is a different matter.

> Our choices are:
>
> 1) Don't use transactions.
>

Covered in point 3.

> 2) Use transactions and silently ignore it when a rollback doesn't actually
> roll back, and/or file a watchdog entry but otherwise don't do anything.
>

If transactions are used they should only be used for the 
insert/update/delete methods.  The read only selects for display 
purposes should not be a transaction because there would be nothing to 
roll back.

> 3) Allow the user to explicitly flag if a connection should use transactions,
> defaulting no, and if not then starting a transaction has no effect and
> neither does rolling back or committing.
>

This might keep the transaction nay sayers happy.

> 4) Don't support database configurations that don't fully support
> transactions.
>

As you say, not an option.

> #4 is not an option, naturally.  #1 seems like a waste, but it is what we do
> now.  That leaves #2 and #3 as alternatives.  I am not entirely sure which
> route is least lame at the moment. :-)
>

As I said, if transactions are allowed then the entire database needs 
to support it, not just some tables.  BTW, when I converted my tables 
from MyISAM to InnoDB the display of the nodes happen quicker.  I don't 
have supporting data yet but I plan to do just that.  Of course there 
are no transactions in the current code base so they are not even a 
factor.  Drupal version is 5.3.

Earnie -- http://for-my-kids.com/
-- http://give-me-an-offer.com/