So long and thanks for all the fish

Thank you to each of you who helped me with my PostgreSQL and
Solaris7 troubles.  A special thank you goes out to Marc Fournier,
Tatsuo Ishii and especially Tom Lane, without whom I would have lost
what is left of my mind.

After much research, it was determined that the primary cause of my
distress was incomplete transactions.  Coding in perl and using
DBI/DBD seems to encapsulate every command (including SELECTs) in a
transaction.  Apparently we were abandoning the transactions,
leaving the database to rollback, but not until after holding the
lock for a very long time.  Given the lock-happy behavior of the
current release, this made the database basically unusable under any
load.  As a matter of fact, a single insert amid dozens of queries
could throw the backend so far out to lunch that it never recovers
and had to be restarted.  We closed those holes and even tried every
other access method available in DBI/DBD.  Unfortunately, in a
benchmark, MySQL still outperformed PostgreSQL by such:

One Minute trial, 60 readers, one writer:
Each reader attempts to retrieve ten rows:            mysql        pgsql
# of inserts performed  19,000    26
# of random seek reads  4,000        7

Not to say this was the only error.  We still saw lots and lots of
this behavior:

Feb 28 13:17:19 constantinople POSTMASTER:
Feb 28 13:17:19 constantinople POSTMASTER: FATAL: s_lock(ef68004d)
at
spin.c:125, stuck spinlock. Aborting.

Which leads me to think there are still some aberrant IPC issues
lurking in Solaris7.  I'm not sure if any of you are aware, but Sun
went back to sockets and away from streams in the 2.6-2.7 upgrade.
This fact alone could count for some of the behavior we have seen.
When we reproduced our problem on Linux, we did not see any of the
stuck spinlock messages.

I know the new locking methods will improve this greatly, but for
what we hope will be one of the busiest portals on the Internet,
speed is very important.  We have taken our database portion of the
site down in order to port to MySQL.  <Sigh>  A sad move on my part,
now that I know PostgreSQL so well.

Thank you all again, be well.  See you all on the flip side...

DwD
--
Daryl W. Dunbar
VP of Engineering/Chief Technology Officer
http://www.com, Where the Web Begins!
mailto:daryl@www.com