Thread: PostgreSQL crashes with Qmail-SQL
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*. Would someone be able to take a look into this? ??? Regards and best wishes, Justin Clift -------- 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> > > > > It makes me wonder if the poor performance of PostgreSQL is still > > relevant, and I'm wondering if you tuned the memory size of the > > PostgreSQL database when you tested it. The default memory allocation > > gives really high CPU load and low performance, but this can be adjusted > > much easier now. > I didn't tune anything, but i'll redo my tests this weekend and play a little > with it ;) 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. ----------------------------------------------------------------------- Some people have told me they don't think a fat penguin really embodies the grace of Linux, which just tells me they have never seen a angry penguin charging at them in excess of 100mph. They'd be a lot more careful about what they say if they had -- Linus Torvalds
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. > -------- 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.
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 offwith 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 > > 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.
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 directoryIs the postmaster running locallyand accepting connectionon 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
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
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, Pennsylvania19026
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
Tom Lane wrote: > 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. Hard to imagine how X11 runs in such a case. Does it not go into /tmp/.X11_unix? -- 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, Pennsylvania19026
Justin Clift writes: > 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.... Deleting a socket file doesn't create havoc, it simply means you can't connect anymore. -- Peter Eisentraut peter_e@gmx.net
> > > 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? The system was running in single user mode when running the tests (i think i already mentioned that). This was done to prevent the results from being influenced by daily/hourly cronjobs such as logrotate. The symptom you're probably referring too is the daily run of 'tmpwatch': - since cron was disabled, tmpwatch didn't run - tmpwatch cleans empty directories and regular files. Sockets are leaved unchanged. > > 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. That's correct: qmail-getpw is called myriads of times: each time a mail needs to be delivered locally. I don't care about whether this favours one database or not: it's what happens on a busy mailserver. > > 2. The *well known* fact that PostgreSQL out of the box is > not configured for production was ignored. From http://qmail-sql.digibel.be/testing.html: The first testround uses the database-configurations as shipped by Red Hat: postgresql 7.1.3 and mysql 3.23.41. No additional performance tuning was performed. IMHO i didn't ignore the lack of tuning. And i DO want to tune it, but it seemed fair to me to start the test with the default configuration as shipped by RedHat and then investigate the results of different tunings. But the next tests were skipped since postgresql crashed. > > 3. Any possibility to track down the reasons for problems > was disabled. My first idea was to log connects,queries and disconnects: i imagine most database-admins turned logging on in their databases. With mysql it runs these things when you turn on 'log=/var/log/..'. I configured postgresql to log the same and ran 1 'qmail-getpw' testrun: - with logging: 112 seconds. - without logging: 32 seconds. I suspect this has to with the fact that i configured postgresql to log to syslog. Don't blame me for this: it's the way RedHat ships postgresql. To make things fair i disabled logging on both databases (mysql- performance is not affected by logging, but it doesn't use syslog). This also excluded vital debug-messages. Frankly: i didn't expect postgresql to crash but i'll turn them on a next time. > > 4. Instead of investigating what the problem is, PostgreSQL > was reported to *Crash*. Yes: it *crashed*. Since i disabled all debugging i cannot help you with investigating this problem. I hope i won't get the death penalty for this ;) > > It cannot get any more obvious. Please elaborate. Michael.
I said:
> 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.
On Linux (at least RH 7.2), the answer to what happens when the socket
file is removed is: nothing. Clients can't connect anymore, but the
postmaster gets no error indicating that anything is wrong. So it sits.
And that means that the 7.1-to-7.2 change I mentioned before is
relevant. In 7.1, the SIGCHLD signal handler blocked signals at its
beginning, and didn't think to unblock them on exit. So after servicing
one SIGCHLD interrupt, the postmaster would end up sitting at its
select() with signals blocked. Further SIGCHLDs would not get serviced
until the next spin around the outer loop re-enabled interrupts.
Normally, no big deal, but with no new connection requests coming in,
the postmaster wouldn't ever get around to wait()ing for its last few
children. (7.2 re-enables signals at exit from the handler, so I don't
think it will show this problem; and indeed I don't see any zombies
after "rm /tmp/.s.PGSQL.5432" during a run of Michael's benchmark
script with 7.2. Not incidentally, I do observe a complete lack of any
complaints out of the benchmark script; it keeps flailing along without
any sign that all its database connection attempts are failing.)
In short: all the reported facts can be explained by the theory that
*something* removed the socket file during that long test run.
regards, tom lane
Peter Eisentraut wrote: > > Justin Clift writes: > > > 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.... > > Deleting a socket file doesn't create havoc, it simply means you can't > connect anymore. Sorry Peter, I wasn't being entirely accurate, more describing that Something Not Wanted Happens. I'll try to be more accurate next time. :) + Justin > > -- > Peter Eisentraut peter_e@gmx.net -- "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
Michael Devogelaere wrote: > > > > 4. Instead of investigating what the problem is, PostgreSQL > > was reported to *Crash*. > Yes: it *crashed*. Since i disabled all debugging i cannot help you > with investigating this problem. I hope i won't get the death penalty > for this ;) > > > > It cannot get any more obvious. > Please elaborate. I hope you don't take any of my comments personal. Because they are not! It is just that I am tired and bored of these every so often repeated MySQL optimized "benchmarks". I see a clear difference between a database server process crash and a disabled service caused by misbehaving sysadmin scripts and/or bad service because of contra-optimized client behaviour. This is exactly the same style of reporting crashes or bad performace, the MySQL folks have practiced for years.I remember creating and dropping tables a couple thousand times, then VACUUM with a user that doesn't havepermissions to vacuum system catalogs, and report bad performance because the system cache got successfully screwedup ... there was even a comment in the script saying "this makes Postgres slow" ... haha. Other reported *crashes* have been core dumps of the test-scripts, because Postgres dealt with datums bigger than the perl-clientwas able to swallow ... well, the test driver just reported a crash, not exactly where and why, does that really matter? MySQL shows success and Postgres does not, that's what counts. The lowest level still accepted Transaction Processing Council Benchmark, TPC-C, can be implemented with a SUTusing MySQL. Do it using LAMP, if you want to learn what a database crash is ;-) There is a good reason why TPC has abandoned the TPC-1, TPC-A and TPC-B benchmarks. They are "too simple" to be of any meaning for benchmarking purposes these days. Yet all the stuff this huge crowd of MySQL-Lemmings is constantly babbling about is even more simple than that! They all have their reasons, the TPC members (basicallyall serious RDBMS vendors) on one side as well as the MySQL folks on the other. As a matter of fact, the MySQL folks are alone with their point of view that "beeing fastest on a single-table-select" is the most important criteria for a relational database management system. And as anothermatter of fact, Lemmings get what Lemmings deserve, 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
Hello again, I've rerun the tests and got: NOTICE: Message from PostgreSQL backend:The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.I have rolled back the current transaction and am going to terminate your database system connection and exit.Please reconnect to the database system and repeat your query. Nothing showed up in the logs however. This time i aborted the test after the first errormessages and could reconnect to the database ! I don't know if this has to do with the number of <defunct> postmasters: there only showed up 2 now and not a few screens as in the first crash. When typing 'ps aux' a few minutes later they were all gone ! Kind regards, Michael Devogelaere.
On Fri, Jan 25, 2002 at 10:20:01AM +0100, Michael Devogelaere wrote: > I've rerun the tests and got: > NOTICE: Message from PostgreSQL backend: > The Postmaster has informed me that some other backend died abnormally > and possibly corrupted shared memory. > I have rolled back the current transaction and am going to terminate > your database system connection and exit. > Please reconnect to the database system and repeat your query. > > Nothing showed up in the logs however. This time i aborted the test after the > first errormessages and could reconnect to the database ! I don't know if this > has to do with the number of <defunct> postmasters: there only showed up 2 now > and not a few screens as in the first crash. When typing 'ps aux' a few minutes > later they were all gone ! I know nothing about the reasons of your crash, if the message given above suffices to determine the reason and how to configure the database to get more reasonable log messages (actually about the last point I know something, but you can read it at * http://developer.postgresql.org/docs/postgres/install-procedure.html * http://developer.postgresql.org/docs/postgres/runtime-config.html#LOGGING so I won't repeat that stuff here), but I know that for scenarios like yours connection pooling was invented. Connection pooling avoids the creation of a new backend process for each single query. Did you ever try your test with connection pooling ? Real databases *require* connection pooling in such a case, MySQL or file systems don't. (Actually when considering to use MySQL, why not using ReiserFS. It's also kind of a database with hashs and all this stuff ;-) I know that another mail in this thread already mentioned connection pooling, but I don't remember any answer from your side. Maybe it's worth an attempt. -- Holger Krug hkrug@rationalizer.com
At 11:26 AM 1/25/02 +0100, Holger Krug wrote: >so I won't repeat that stuff here), but I know that for scenarios like >yours connection pooling was invented. Connection pooling avoids the >creation of a new backend process for each single query. Did you ever >try your test with connection pooling ? Real databases *require* >connection pooling in such a case, MySQL or file systems If the database crashes are not due to resource limits, connection pooling does not seem to be the real solution. If postgresql crashes after X concurrent backends are respawned Y times I figure something is wrong and should be fixed. Regards, Link.
On Fri, Jan 25, 2002 at 08:46:24PM +0800, Lincoln Yeoh wrote: > If the database crashes are not due to resource limits, connection pooling > does not seem to be the real solution. That's clear. But the other part of his problem (`bad performance') suffers from his setup (no connection pooling). -- Holger Krug hkrug@rationalizer.com
Lincoln Yeoh wrote: > At 11:26 AM 1/25/02 +0100, Holger Krug wrote: > >so I won't repeat that stuff here), but I know that for scenarios like > >yours connection pooling was invented. Connection pooling avoids the > >creation of a new backend process for each single query. Did you ever > >try your test with connection pooling ? Real databases *require* > >connection pooling in such a case, MySQL or file systems > > If the database crashes are not due to resource limits, connection pooling > does not seem to be the real solution. The crash he reported this time looks like a backend dumping core. I wonder how he killed the postmaster the last time and if he by doing it with -9 corrupted the database? The entire discussion is somehow pointless. Tell some Riksha- puller to compare his Riksha with this brand new Ferrari,and wait his comments after the test drive. He'll probably won't get the damn thing moving, and if, it'd bea hell of a ride, so he will tell you that his Riksha has a much better handling and the Ferrari *crashed*. 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
> > The crash he reported this time looks like a backend dumping > core. I wonder how he killed the postmaster the last time > and if he by doing it with -9 corrupted the database? He killed the database with -9, rebooted the machine, removed /var/lib/pgsql and created a new database from scratch. Then he populated the database again and ran the test again. > > The entire discussion is somehow pointless. Tell some Riksha- > puller to compare his Riksha with this brand new Ferrari, and > wait his comments after the test drive. He'll probably won't > get the damn thing moving, and if, it'd be a hell of a ride, > so he will tell you that his Riksha has a much better > handling and the Ferrari *crashed*. He doesn't have a Riksha or a Ferrari. But he would hate it if the doors fell of his Ferrari after opening and closing them a few times ;) Michael.
Michael Devogelaere wrote: > > > > The crash he reported this time looks like a backend dumping > > core. I wonder how he killed the postmaster the last time > > and if he by doing it with -9 corrupted the database? > He killed the database with -9, rebooted the machine, removed /var/lib/pgsql > and created a new database from scratch. Then he populated the database again > and ran the test again. So you did a new initdb? And then you got a core dump. Ewe, that doesn't seem right. The test case is still on your web page? I think I should download it and take a closer look. > He doesn't have a Riksha or a Ferrari. But he would hate it if the doors > fell of his Ferrari after opening and closing them a few times ;) That's the problem with Ferrari. What makes them expensive is that you need 3 cars. The Ferrari, a useful reliable one,and the car for the mechanic. 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
On Fri, 25 Jan 2002, Michael Devogelaere wrote: > Hello again, > > I've rerun the tests and got: > NOTICE: Message from PostgreSQL backend: > The Postmaster has informed me that some other backend died abnormally > and possibly corrupted shared memory. > I have rolled back the current transaction and am going to terminate > your database system connection and exit. > Please reconnect to the database system and repeat your query. > > Nothing showed up in the logs however. This time i aborted the test after the > first errormessages and could reconnect to the database ! I don't know if this > has to do with the number of <defunct> postmasters: there only showed up 2 now > and not a few screens as in the first crash. When typing 'ps aux' a few minutes > later they were all gone ! Hmm, did you get any core files in the database directory? And since I don't know what the test does and I'm not sure if locale is enabled in the default red hat package, as a low probability thing, you do have a version of glibc >= 2.2.3? As a side question, how far do you need to install qmail to use the test? I'd gotten the test files to run against a 7.1 server I set up, but didn't feel comfortable doing a full install of qmail if possible.
Michael Devogelaere <michael@digibel.be> writes:
>> The crash he reported this time looks like a backend dumping
>> core. I wonder how he killed the postmaster the last time
>> and if he by doing it with -9 corrupted the database?
> He killed the database with -9, rebooted the machine, removed /var/lib/pgsql
> and created a new database from scratch. Then he populated the database again
> and ran the test again.
In any case, the original report mentioned something that looked
suspiciously like a backend crash:
: 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.
The disappearing-socket-file theory explains most of this, but (AFAICS)
not the "unexpected EOF" message. That was why I was pressing Michael
for more details to begin with.
I've been running Michael's test script on an RH 7.2 box since yesterday
afternoon, but have yet to reproduce any failure (other than the
expected symptoms from manually removing the socket file).
regards, tom lane
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> As a side question, how far do you need to install qmail to use the test?
> I'd gotten the test files to run against a 7.1 server I set up, but didn't
> feel comfortable doing a full install of qmail if possible.
I didn't want to do that either. I managed to get things running by
unpacking the rpm, unpacking the qmail-1.03.tar.gz original sources,
applying the qmail-sql-0.19.patch (and not any of the other ones,
which might've been a mistake), then setting up the three needed config
files by hand:
$ cat sqltype.h
/* Uncomment to choose postgresql */
#define SQLTYPE PGSQLTYPE
/* Uncomment to choose mysql */
// #define SQLTYPE MYSQLTYPE
$ cat sql.headers
-I/usr/include/pgsql
$ cat sql.lib
-lpq
$
and then I was able to do "make qmail-getpw" which is the only
executable you need for the benchmark. Put that somewhere, copy
the sqlserver.sample file to where qmail-getpw will look for it [1]
and edit to taste, and you're set.
Caution: I wasted some time running "benchmarks" that proved only
to be exercising how fast the client could fail. qmail-getpw's
approach to error handling seems to be (a) don't bother testing for
very many error conditions (eg, it coredumps on an empty sqlserver
control file), and (b) if it does detect a failure, exiting with a
nonzero error code is a sufficient way of reporting it. Error messages
are for wimps, apparently. So don't bother running the querydb script
until you've made *sure* qmail-getpw is working. After running the
initdb script (watch out for name conflicts with ours!) you should get
$ ./qmail-getpw alias0 domain0
alias00existance_irrelevant_for_testing-alias00trash@devnull.biz$
if all is well. Lack of output means you have a problem.
regards, tom lane
[1] I didn't bother making the expected /var/qmail/control/sqlserver,
but just put the sqlserver file in subdirectory control of where I
was running the tests. Works fine since qmail-getpw doesn't check
whether its chdir("/var/qmail") succeeds.
[2] No, I don't think I'll be trusting my email to this thing real soon.
But I'd like to know whether it really is provoking a PG crash.
On Fri, 25 Jan 2002, Tom Lane wrote: > [2] No, I don't think I'll be trusting my email to this thing real soon. > But I'd like to know whether it really is provoking a PG crash. One thing I've missed in this thread is where qmail-sql is. Since I run nothing but qmail it's already installed on all of my machines and I just happen to have a 7.2b5 system pretty much sitting idle. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Fri, Jan 25, 2002 at 12:30:17PM -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I didn't want to do that either. I managed to get things running by > unpacking the rpm, unpacking the qmail-1.03.tar.gz original sources, > applying the qmail-sql-0.19.patch (and not any of the other ones, > which might've been a mistake), then setting up the three needed config > files by hand: I run qmail without any of the patches and it works just fine. There could be something about the sql patch that requires some other patch, but hopefully that would have been mentioned in the patch. > Caution: I wasted some time running "benchmarks" that proved only > to be exercising how fast the client could fail. qmail-getpw's > approach to error handling seems to be (a) don't bother testing for > very many error conditions (eg, it coredumps on an empty sqlserver > control file), and (b) if it does detect a failure, exiting with a This is probably something caused by the patch. DJB's stuff doesn't coredump. > nonzero error code is a sufficient way of reporting it. Error messages > are for wimps, apparently. So don't bother running the querydb script > until you've made *sure* qmail-getpw is working. After running the > initdb script (watch out for name conflicts with ours!) you should get This (modifying qmail-getpw) seems like a poor way to do things. You could probably do the same thing using a script that queries the database and writes output that can be processed by unpatched qmail-pw2u (if you want some stuff to come from /etc/passwd) and qmail-newu. I use these (but not the sql stuff) since only a couple of accounts should get email and I have a couple of lists set up as subusers of my normal account to simplify maintainance (i.e. the list files are owned by me instead of their own accounts). The idea of using a database to help handle local delivery is also bad. cdb is much faster for typical kinds of usage (users change MUCH less often than email arrives). It would make a lot more sense to keep the authoritative information in a database and resync the cdb information after a change.
> Caution: I wasted some time running "benchmarks" that proved only > to be exercising how fast the client could fail. qmail-getpw's > approach to error handling seems to be (a) don't bother testing for > very many error conditions (eg, it coredumps on an empty sqlserver > control file), and (b) if it does detect a failure, exiting with a > nonzero error code is a sufficient way of reporting it. Error messages > are for wimps, apparently. (b) is part of the qmail strategy - qmail is implemented as a set of independent processes with different owners and rights and they communicate problems through standard exit codes. We can agree that it should be more forthcoming with meaningful help for people setting up the system, but it can't just write an message to STDOUT because its caller has probably already set up a pipe to another process - any error message would normally find itself inserted into the mail queue! (a), in contrast, is definitely not normal for qmail and is the type of thing that should be fixed... even if all it does after detecting a problem is return with a nonzero error code. :-) Bear
Bear Giles <bear@coyotesong.com> writes: > > Caution: I wasted some time running "benchmarks" that proved only > > to be exercising how fast the client could fail. qmail-getpw's > > approach to error handling seems to be (a) don't bother testing for > > very many error conditions (eg, it coredumps on an empty sqlserver > > control file), and (b) if it does detect a failure, exiting with a > > nonzero error code is a sufficient way of reporting it. Error messages > > are for wimps, apparently. > > (b) is part of the qmail strategy - qmail is implemented as a set > of independent processes with different owners and rights and they > communicate problems through standard exit codes. > > We can agree that it should be more forthcoming with meaningful > help for people setting up the system, but it can't just write an > message to STDOUT because its caller has probably already set up > a pipe to another process - any error message would normally find > itself inserted into the mail queue! Gaah. Has djb ever heard of syslog(3)? Or is that too insecure for him? -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Doug McNaught <doug@wireboard.com> writes:
> Bear Giles <bear@coyotesong.com> writes:
>> We can agree that it should be more forthcoming with meaningful
>> help for people setting up the system, but it can't just write an
>> message to STDOUT because its caller has probably already set up
>> a pipe to another process - any error message would normally find
>> itself inserted into the mail queue!
> Gaah. Has djb ever heard of syslog(3)?
Or stderr? There's a good reason why Unix has both stdout and stderr
as part of the standard process model. stderr is for human-readable
error messages. In a noninteractive situation you can send it to
/dev/null, if you don't believe in logging; but when a human is running
a program it's polite to say something on stderr before going belly-up.
regards, tom lane
On Fri, Jan 25, 2002 at 11:01:23AM -0700, Bear Giles <bear@coyotesong.com> wrote: > > (a), in contrast, is definitely not normal for qmail and is the type > of thing that should be fixed... even if all it does after detecting > a problem is return with a nonzero error code. :-) This was probably caused by an error in the patch rather than the original code. Unless they have a very high rate of change for accounts, it seems that it would have been better to not use the database directly for account information. When stuff changed in the database, the cdb file could be rebuilt using stock qmail tools.
On 1 Feb 2002, Doug McNaught wrote: > Bear Giles <bear@coyotesong.com> writes: > > > > Caution: I wasted some time running "benchmarks" that proved only > > > to be exercising how fast the client could fail. qmail-getpw's > > > approach to error handling seems to be (a) don't bother testing for > > > very many error conditions (eg, it coredumps on an empty sqlserver > > > control file), and (b) if it does detect a failure, exiting with a > > > nonzero error code is a sufficient way of reporting it. Error messages > > > are for wimps, apparently. > > > > (b) is part of the qmail strategy - qmail is implemented as a set > > of independent processes with different owners and rights and they > > communicate problems through standard exit codes. > > > > We can agree that it should be more forthcoming with meaningful > > help for people setting up the system, but it can't just write an > > message to STDOUT because its caller has probably already set up > > a pipe to another process - any error message would normally find > > itself inserted into the mail queue! > > Gaah. Has djb ever heard of syslog(3)? Or is that too insecure for > him? Insecure and slow. Check out multilog which is part of daemontools. http://cr.yp.to/daemontools/multilog.html Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Fri, Feb 01, 2002 at 01:58:33PM -0500, Doug McNaught <doug@wireboard.com> wrote: > Bear Giles <bear@coyotesong.com> writes: > > Gaah. Has djb ever heard of syslog(3)? Or is that too insecure for > him? Its called multilog and he feels it is much better than syslog. Instead of using the network to send data around, a pipe is used. multilog also does the log rotation (with configurable sizes) so that you don't have to worry about that.
Vince Vielhaber <vev@michvhf.com> writes: >> Gaah. Has djb ever heard of syslog(3)? Or is that too insecure for >> him? > Insecure and slow. Check out multilog which is part of daemontools. > http://cr.yp.to/daemontools/multilog.html Hmm, should we (do we?) have a pointer to this in our docs somewhere? I know I wrote a section of the Admin Guide that just handwaves about where to get log-rotation scripts. regards, tom lane
On Fri, 1 Feb 2002, Tom Lane wrote: > Vince Vielhaber <vev@michvhf.com> writes: > >> Gaah. Has djb ever heard of syslog(3)? Or is that too insecure for > >> him? > > > Insecure and slow. Check out multilog which is part of daemontools. > > http://cr.yp.to/daemontools/multilog.html > > Hmm, should we (do we?) have a pointer to this in our docs somewhere? > I know I wrote a section of the Admin Guide that just handwaves about > where to get log-rotation scripts. I don't believe so. Lemme look and see where it might fit best. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================