PostgreSQL crashes with Qmail-SQL

On Thu, 24 Jan 2002, Justin Clift wrote:

Michael Devogelaere, the guy who writes the QMail-SQL package has
recently started moving away from using PostgreSQL to MySQL.

In his experience, MySQL works better. Now, he's just tested them over
the weekend, and what he reports is that PostgreSQL *crashes*. Doesn't
just go slow, but *crashes*.

Would someone be able to take a look into this?

I went to the page mentioned below and wanted to try grabbing the test
scripts to see if I could replicate the crash, but got 404's for the
testscripts.tgz link on the page. The speed doesn't surprise me as much
as the crash does.

Stephan Szabo wrote:

On Thu, 24 Jan 2002, Justin Clift wrote:

Michael Devogelaere, the guy who writes the QMail-SQL package has
recently started moving away from using PostgreSQL to MySQL.

In his experience, MySQL works better. Now, he's just tested them over
the weekend, and what he reports is that PostgreSQL *crashes*. Doesn't
just go slow, but *crashes*.

Would someone be able to take a look into this?

I went to the page mentioned below and wanted to try grabbing the test
scripts to see if I could replicate the crash, but got 404's for the
testscripts.tgz link on the page. The speed doesn't surprise me as much
as the crash does.

As the doc says, all done totally untuned. And CRASH by
itself doesn't say anything. A little more precise would be
good.

Other than that, once again one of these mostly read only
scenarios with simple queries where it is well known that a
real database cannot compete.

Jan

-------- Original Message --------
Subject: Re: Qmail-SQL
Date: Wed, 23 Jan 2002 15:17:12 +0100
From: Michael Devogelaere <michael@digibel.be>
To: Justin Clift <justin@postgresql.org>
References: <3C48F4B9.AC03E251@postgresql.org>
<20020119122728.A9661@digibel.be>

Ok. I worked a bit on it this weekend and put the results on
http://qmail-sql.digibel.be/testing.html. I'm very sorry but
postgresql was between 3 and 4 times slower than mysql and didn't
survive all tests.

Kind regards,
Michael Devogelaere.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Justin Clift wrote:

Hi guys,

Michael Devogelaere, the guy who writes the QMail-SQL package has
recently started moving away from using PostgreSQL to MySQL.

In his experience, MySQL works better. Now, he's just tested them over
the weekend, and what he reports is that PostgreSQL *crashes*. Doesn't
just go slow, but *crashes*.

I tried to grab his scripts but both his "here" links return a 404 Not
Found.

One interesting point ... he does:

* A failing query for the user.
* A query for the alias-user.
* A query for alias-username in the dotqmails-table.
* A query for alias-default in the dotqmails-table.

Four queries that most likely can be done with a single query ... gotta
wonder about these MySQL-trained wunderkinds and their ability to write
decent queries.

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

On Wed, Jan 23, 2002 at 04:24:17PM -0800, Stephan Szabo wrote:

On Thu, 24 Jan 2002, Justin Clift wrote:

Michael Devogelaere, the guy who writes the QMail-SQL package has
recently started moving away from using PostgreSQL to MySQL.

That's not entirely true: i'm still using PostgreSQL and i don't even
consider moving to MySQL, since i'm using a more complicated database which
doesn't run on MySQL. But - sorry to tell it - in my opinion, MySQL is a
lot faster for simple queries if one needs to connect/disconnect frequently.
I suspect that PostgreSQL connects quite slowly and therefore performs
bad in this kind of tests.

Would someone be able to take a look into this?

I went to the page mentioned below and wanted to try grabbing the test
scripts to see if I could replicate the crash, but got 404's for the
testscripts.tgz link on the page. The speed doesn't surprise me as much
as the crash does.

The link is fixed now.

Kind regards,
Michael Devogelaere.

As the doc says, all done totally untuned. And CRASH by
itself doesn't say anything. A little more precise would be
good.

Ok: the client reported something like:
"Unexpected EOF from PostgreSQL-backend". When looking with ps aux, i noted
that all postmaster-childs where <defunct>. I couldn't connect anymore with
psql (i aborted the test and no other processes tried to access the database
since my machine was in single user mode). After killing the master process and
restarting, the database worked fine.

Other than that, once again one of these mostly read only
scenarios with simple queries where it is well known that a
real database cannot compete.

True: i planned two tests. One big read-only test and then another which would
add simulation of pop-logins. After a successful pop-login the field
'lastlogin' is updated. But i didn't run that test since postgresql already
failed the simple read-only test.

Kind regards,
Michael Devogelaere.

Michael Devogelaere wrote:

But - sorry to tell it - in my opinion, MySQL is a

lot faster for simple queries if one needs to connect/disconnect frequently.
I suspect that PostgreSQL connects quite slowly and therefore performs
bad in this kind of tests.

Sure, this is known. Serious applications pool persistent connections,
though, making it a non-issue for many of us.

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

Michael Devogelaere wrote:

As the doc says, all done totally untuned. And CRASH by
itself doesn't say anything. A little more precise would be
good.

Ok: the client reported something like:
"Unexpected EOF from PostgreSQL-backend". When looking with ps aux, i noted
that all postmaster-childs where <defunct>. I couldn't connect anymore with
psql (i aborted the test and no other processes tried to access the database
since my machine was in single user mode). After killing the master process and
restarting, the database worked fine.

Looks like leftover or not fast enough reaped old connections
that fill up all possible backend slots (default max 32).
Persistent connections is definitely something that
PostgreSQL likes.

Other than that, once again one of these mostly read only
scenarios with simple queries where it is well known that a
real database cannot compete.

True: i planned two tests. One big read-only test and then another which would
add simulation of pop-logins. After a successful pop-login the field
'lastlogin' is updated. But i didn't run that test since postgresql already
failed the simple read-only test.

As said, "simple read-only" is not really something you want
a full featured RDBMS for. Maybe you are better off with a
simple and stupid system on the feature level of gdbm or
MySql.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Jan Wieck wrote:

<snip>

As said, "simple read-only" is not really something you want
a full featured RDBMS for. Maybe you are better off with a
simple and stupid system on the feature level of gdbm or
MySql.

Jan

I'd agree with this on the query-level functionality... but...

Michael, does Qmail-SQL *store* the email in the database? (haven't
checked)

If so, there's no way I'd want new customer inquiries or other
*important* email stored in a system which didn't know how to fully
recover if the server crashes.

Imagine... 200,000 customer emails in a busy MySQL 3.23.x database, and
the UPS power cuts off.

Erk...

+ Justin

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Fri, Jan 25, 2002 at 03:48:40AM +1100, Justin Clift wrote:

Jan Wieck wrote:

<snip>

As said, "simple read-only" is not really something you want
a full featured RDBMS for. Maybe you are better off with a
simple and stupid system on the feature level of gdbm or
MySql.

Jan

I'd agree with this on the query-level functionality... but...

Michael, does Qmail-SQL *store* the email in the database? (haven't
checked)

No way ;) Only the user/authentication-management moved to the database.

If so, there's no way I'd want new customer inquiries or other
*important* email stored in a system which didn't know how to fully
recover if the server crashes.

Neither do i. But i know there exists a patch (qmail seems to consist merely
of patches) which stores the mail in a database. Maybe you can use this to
"save" helpdesk-calls ;)

Regards,
Michael.

After reading about the patch, it seems that the database is only used for
virtualhost lookups and password/account verification -- it never mentioned
doing any more than that... I've just read over the docs once though, so no
one take my word as law yet.

I'm going to install this later and play with it as I've been looking for a
solution like this for a while (though I'm a postfix user, I'd gladly switch
if this patch works!). I'll see if I can get PG to crash with it and
investigate further..

Just in theory, I don't even trust MySQL to store my usernames and
passwords, I've seen it take a dive too many times to use it for much of
anything... They've released several versions since I last used it but it
was a lot less stable for me than older 6.X versions of PG when the load got
a little high...

If the patch just does a few simple queries, I'd think something along the
lines of mSQL might be nice (though I've never used it, I've heard some nice
things about it for tiny databases).. PG's feature set is grossly underused
for applications like this... If I do use it I'll probably install another
copy of PG and turn down the sort mem and such to get a little better
scalability -- spawning a new PG process every time someone checks their
mail is going to cost me dearly with the way my PG is setup now..

Well, we'll see how it goes.

-Mitch

Michael Devogelaere <michael@digibel.be> writes:

Ok: the client reported something like:
"Unexpected EOF from PostgreSQL-backend".

What showed up in the postmaster log when this happened? I would like
*exact* error message texts, not approximations.

When looking with ps aux, i noted
that all postmaster-childs where <defunct>. I couldn't connect anymore with
psql

What happened when you tried to connect with psql? Again, exact, not
approximate.

It sounds like the postmaster got into a state where it was not
responding to SIGCHLD signals. We fixed one possible cause of that
between 7.1 and 7.2, but without a more concrete report I have no way to
know if you saw the same problem or a different one. I'd have expected
connection attempts to unwedge the postmaster in any case.

regards, tom lane

On Thu, Jan 24, 2002 at 01:11:39PM -0500, Tom Lane wrote:

Michael Devogelaere <michael@digibel.be> writes:

Ok: the client reported something like:
"Unexpected EOF from PostgreSQL-backend".

What showed up in the postmaster log when this happened? I would like
*exact* error message texts, not approximations.

Nothing. I disabled all logging since the database responded too slowly
with logging turned on. So i cannot help you on this.

When looking with ps aux, i noted
that all postmaster-childs where <defunct>. I couldn't connect anymore with
psql

What happened when you tried to connect with psql? Again, exact, not
approximate.

psql: connectDBStart() -- connect() failed: No such file or directory
Is the postmaster running locally
and accepting connection on Unix socket ...

Kind regards,
Michael Devogelaere.

Michael Devogelaere <michael@digibel.be> writes:

On Thu, Jan 24, 2002 at 01:11:39PM -0500, Tom Lane wrote:

What showed up in the postmaster log when this happened? I would like
*exact* error message texts, not approximations.

Nothing. I disabled all logging since the database responded too slowly
with logging turned on. So i cannot help you on this.

If you're not going to be cooperative, then I don't see how you expect
us to fix the problem.

FWIW, I don't believe for a moment that /dev/null'ing the postmaster log
improves performance measurably. I've done plenty of profiling in my
time, and never seen any indication that it's an issue; at least not at
the default verbosity level.

What happened when you tried to connect with psql? Again, exact, not
approximate.

psql: connectDBStart() -- connect() failed: No such file or directory
Is the postmaster running locally
and accepting connection on Unix socket ...

No such file?? Hard to believe that that could happen while the
postmaster was still running. Unless something else had decided to
delete the socket file from /tmp. The postmaster certainly would not
do it.

regards, tom lane

Tom Lane wrote:

Michael Devogelaere <michael@digibel.be> writes:

On Thu, Jan 24, 2002 at 01:11:39PM -0500, Tom Lane wrote:

What showed up in the postmaster log when this happened? I would like
*exact* error message texts, not approximations.

Nothing. I disabled all logging since the database responded too slowly
with logging turned on. So i cannot help you on this.

If you're not going to be cooperative, then I don't see how you expect
us to fix the problem.

FWIW, I don't believe for a moment that /dev/null'ing the postmaster log
improves performance measurably. I've done plenty of profiling in my
time, and never seen any indication that it's an issue; at least not at
the default verbosity level.

What happened when you tried to connect with psql? Again, exact, not
approximate.

psql: connectDBStart() -- connect() failed: No such file or directory
Is the postmaster running locally
and accepting connection on Unix socket ...

No such file?? Hard to believe that that could happen while the
postmaster was still running. Unless something else had decided to
delete the socket file from /tmp. The postmaster certainly would not
do it.

Haven't there been some over enthusiastic cleanup scripts in
some Linux distro's, that removed the socket from /tmp
because of it's age?

Anyway, so in summary:

1. The test case was the *well known* MySQL favorite suite;
Simple one-table read-only access with myriads of
connect's.

2. The *well known* fact that PostgreSQL out of the box is
not configured for production was ignored.

3. Any possibility to track down the reasons for problems
was disabled.

4. Instead of investigating what the problem is, PostgreSQL
was reported to *Crash*.

It cannot get any more obvious.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Hi Tom,

Tom Lane wrote:

<snip>

If you're not going to be cooperative, then I don't see how you expect
us to fix the problem.

Hey, lets not start a war here!

Michael's been using PostgreSQL for a while, but hasn't had a chance to
really get into it. When he makes a mistake like this, it's not because
of evil intentions!

<snip>

No such file?? Hard to believe that that could happen while the
postmaster was still running. Unless something else had decided to
delete the socket file from /tmp. The postmaster certainly would not
do it.

This provides an interesting lead. There's at least one linux
distribution which does this. Part of the cron'd maintenance scripts
delete all the files in /tmp, and therefore play havoc....

But, I don't remember which distribution, although I don't think it was
RedHat.

???

Regards and best wishes,

Justin Clift

regards, tom lane

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Justin Clift <justin@postgresql.org> writes:

No such file?? Hard to believe that that could happen while the
postmaster was still running. Unless something else had decided to
delete the socket file from /tmp. The postmaster certainly would not
do it.

This provides an interesting lead. There's at least one linux
distribution which does this. Part of the cron'd maintenance scripts
delete all the files in /tmp, and therefore play havoc....

Yeah, I do recall that some versions had a tmp-scrubber that didn't make
any exception for socket files. But it's kind of a big coincidence to
assume that would happen just while Michael was running his benchmark.
Not sure I credit it.

regards, tom lane

Tom Lane wrote:

Justin Clift <justin@postgresql.org> writes:

No such file?? Hard to believe that that could happen while the
postmaster was still running. Unless something else had decided to
delete the socket file from /tmp. The postmaster certainly would not
do it.

This provides an interesting lead. There's at least one linux
distribution which does this. Part of the cron'd maintenance scripts
delete all the files in /tmp, and therefore play havoc....

Yeah, I do recall that some versions had a tmp-scrubber that didn't make
any exception for socket files. But it's kind of a big coincidence to
assume that would happen just while Michael was running his benchmark.
Not sure I credit it.

We added some PostgreSQL code to touch the socket file during
checkpoints, and I thought that was in 7.1.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I said:

Yeah, I do recall that some versions had a tmp-scrubber that didn't make
any exception for socket files. But it's kind of a big coincidence to
assume that would happen just while Michael was running his benchmark.

... or maybe not. I just looked back at Michael's benchmark page and
observed that the extrapolated time to complete the run in question was
over 24 hours (and the first two parts of the script would've taken
more than 12). If he'd left the machine alone for a couple of days
while the script ran, maybe it's credible that a /tmp-scrubber did its
thing meanwhile.

That still leaves us with all the defunct postmaster children to explain
though. Hmm. I wonder exactly what the postmaster does when someone
forcibly removes its socket file... probably system-dependent, but I
could certainly believe getting into a busy-wait loop of select/accept.
That doesn't look like it should prevent SIGCHLD from getting noticed,
though.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

We added some PostgreSQL code to touch the socket file during
checkpoints, and I thought that was in 7.1.

You're thinking about the socket lock file, which is a plain file.

The problem with socket files is that the file mod time usually doesn't
change even when it's in active use. That's why things like
/tmp-scrubbers need to make an exception for socket files.

regards, tom lane