Re: Serious Crash last Friday - Mailing list pgsql-general

not only lookups, but also inserts, updates and deletions, of course...

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Bertin, Philippe" <philippe.bertin@barco.com>
To: "Henrik Steffen" <steffen@city-map.de>
Sent: Thursday, June 20, 2002 11:33 AM
Subject: RE: [GENERAL] Serious Crash last Friday


Hello,

If you're only having lookups, I still doubt that this kind of problem would
be able to occur (however, never be surprised of anything :)  How you can
see the current ID, I can't tell, but I think that was also in the same
text. I suggest you have a look into the official PostgreSQL- documentation,
probably the Admin's guide, or the User's guide, because I think it was
there I saw this explanation.

Kind regards,

Philippe.

> -----Original Message-----
> From: Henrik Steffen [SMTP:steffen@city-map.de]
> Sent: donderdag 20 juni 2002 11:29
> To: Bertin, Philippe
> Subject: Re: [GENERAL] Serious Crash last Friday
>
> Hi,
>
> thanks for this piece of information. Do you know, where and how I can see
> which XID is the current? From this I could tell if there more than 2M
> transactions
> a day.
>
> I don't use any triggers. But on our heavily loaded website, there are
> about
> 60.000 pageviews a day, and each page view triggers maybe 10 - 15 database
> lookups.
> so we might get rather close to 2 M a day on certain days.
>
> Mit freundlichem Gruß
>
> Henrik Steffen
> Geschäftsführer
>
> top concepts Internetmarketing GmbH
> Am Steinkamp 7 - D-21684 Stade - Germany
> --------------------------------------------------------
> http://www.topconcepts.com          Tel. +49 4141 991230
> mail: steffen@topconcepts.com       Fax. +49 4141 991233
> --------------------------------------------------------
> 24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
> --------------------------------------------------------
> System-Partner gesucht: http://www.franchise.city-map.de
> --------------------------------------------------------
> Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> --------------------------------------------------------
>
> ----- Original Message -----
> From: "Bertin, Philippe" <philippe.bertin@barco.com>
> To: "Henrik Steffen" <steffen@city-map.de>
> Sent: Thursday, June 20, 2002 10:52 AM
> Subject: RE: [GENERAL] Serious Crash last Friday
>
>
> Henrik,
>
> Not that I know so much about it (I don't even remember if it's really
> called 'XID'- raparound). But it comes to something like : every action on
> the database gets an ID. This ID is contained in 32 bits, so 4M; only half
> of the range can be used, so 2M. Every 2M actions, the ID wraps around. If
> that happens, your data may get corrupt. A vacuum analyze resets these
> ID's
> ...
>
> I don't remember where I read this. I'll look around as from when I have
> time, and mail you back the reference for it. I think it'll be around this
> evening (Belgian time) when I'll find the time for doing so ... Maybe
> Martijn could give you some hints ?
>
> However, if you indicate that you're doing a vacuum analyze on a daily
> basis, I doubt that that can be the reason (unless you would have a lot of
> triggers on your tables that increment the number of actions exponentially
> by recursively calling each other ?)
>
> Kind regards,
>
> Philippe Bertin.
>
> > -----Original Message-----
> > From: Henrik Steffen [SMTP:steffen@city-map.de]
> > Sent: donderdag 20 juni 2002 9:34
> > To: Bertin, Philippe
> > Cc: pg
> > Subject: Re: [GENERAL] Serious Crash last Friday
> >
> >
> > Dear Philippe,
> >
> > I have always been using the VACUUM ANALYZE on a daily basis.
> >
> > I recently changed this cronjob into "VACUUM FULL ANALYZE" - which
> > didn't help either.
> >
> > What seems to be helpful is, to drop all user indexes and recreate them
> > on a daily basis (before, I did this only on a weekly basis once every
> > sunday)
> >
> > What exactly is XID wraparound? How can I make sure I am using it? Is it
> > anything
> > I shouldn't use? Or should I change anything to make my system run more
> > stable?
> >
> > Any help highly appreciated
> >
> > thanks
> >
> >
> > Mit freundlichem Gruß
> >
> > Henrik Steffen
> > Geschäftsführer
> >
> > top concepts Internetmarketing GmbH
> > Am Steinkamp 7 - D-21684 Stade - Germany
> > --------------------------------------------------------
> > http://www.topconcepts.com          Tel. +49 4141 991230
> > mail: steffen@topconcepts.com       Fax. +49 4141 991233
> > --------------------------------------------------------
> > 24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
> > --------------------------------------------------------
> > System-Partner gesucht: http://www.franchise.city-map.de
> > --------------------------------------------------------
> > Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> > --------------------------------------------------------
> >
> > ----- Original Message -----
> > From: "Bertin, Philippe" <philippe.bertin@barco.com>
> > To: "Henrik Steffen" <steffen@city-map.de>
> > Sent: Thursday, June 20, 2002 9:06 AM
> > Subject: RE: [GENERAL] Serious Crash last Friday
> >
> >
> > Henrik,
> >
> > I think it was Martijn who asked at a certain point if you made sure
> there
> > was no ... (XID ?) wraparound. As it's a heavily used database, couldn't
> > this be the cause ? i.e., did you try since then to regularly perform
> the
> > vacuum analyze (as I see this, on a daily basis) ?
> >
> > Regards,
> >
> > Philippe Bertin
> > Software Development Engineer Avionics
> > ---------------------------------------------------
> >
> > > -----Original Message-----
> > > From: Henrik Steffen [SMTP:steffen@city-map.de]
> > > Sent: donderdag 20 juni 2002 8:54
> > > To: pg
> > > Subject: Re: [GENERAL] Serious Crash last Friday
> > >
> > >
> > > Hello all,
> > >
> > > unfortunately I didn't get a really helping answer from the list yet.
> > >
> > > Additionally yesterday night there was again a problem with some
> > SELECTs:
> > >
> > > 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.
> > > DB-Error in /web/intern.city-map.de/www/vertrieb/wiedervorlage.pl Code
> > 7:
> > >  server closed the connection unexpectedly
> > > This probably means the server terminated abnormally
> > > before or while processing the request.
> > >
> > > Command was:
> > > SELECT name
> > > FROM regionen
> > > WHERE region='0119';
> > >  at /web/pm/CityMap/Abfragen.pm line 135
> > >
> > > This is really annoying.
> > >
> > > When I noticed it this morning, I dropped all indexes and recreated
> > them.
> > > Then I ran a VACUUM FULL VERBOSE ANALYZE - afterwards the same query
> > > worked
> > > properly again.
> > >
> > > I have now created a cronjob that will drop and recreate all indexes
> on
> > a
> > > daily basis.
> > >
> > > But shouldn't this be unnecessary ?
> > >
> > >
> > >
> > > Mit freundlichem Gruß
> > >
> > > Henrik Steffen
> > > Geschäftsführer
> > >
> > > top concepts Internetmarketing GmbH
> > > Am Steinkamp 7 - D-21684 Stade - Germany
> > > --------------------------------------------------------
> > > http://www.topconcepts.com          Tel. +49 4141 991230
> > > mail: steffen@topconcepts.com       Fax. +49 4141 991233
> > > --------------------------------------------------------
> > > 24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
> > > --------------------------------------------------------
> > > System-Partner gesucht: http://www.franchise.city-map.de
> > > --------------------------------------------------------
> > > Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> > > --------------------------------------------------------
> > >
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html