Обсуждение: catalog corruption causes

Поиск
Список
Период
Сортировка

catalog corruption causes

От
"Burgholzer, Robert (DEQ)"
Дата:
I am trying to get my head around what causes catalog corruption.  I
have posted before with regard to recovering from corruptions (if that
is what indeed happened to me), and was given much help.

Does anyone know why a database catalog will get corrupted?  As I
mentioned previously, my db involves considerable use of temporary
tables created by php-psql connections.  Other than that, I don't know
of too much that is "odd" about my use (misuse) of the database.

Thanks,
r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer@deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/


Re: catalog corruption causes

От
"Kevin Grittner"
Дата:
"Burgholzer, Robert (DEQ)" <Robert.Burgholzer@deq.virginia.gov>
wrote:

> I am trying to get my head around what causes catalog corruption.

From what I've seen many are caused by things outside of PostgreSQL
-- like bad disk, bad drivers, OS bugs, running on network drives
which aren't reliable, write caches without battery back-up,
anti-virus software, etc.

Another common cause is using faster-but-unreliable PostgreSQL
configuration settings like fsync=off or full_page_writes=off.  If
that's couple with a kill -9 or a crash of hardware or OS, you can
get corruption.

Then there's the possibility of PostgreSQL bugs.  Make sure you use
a recent minor release of whatever major release you're on, so you
get the benefit of bug fixes.  And I recommend staying away from
VACUUM FULL -- among the many other reasons there are to avoid it,
it seems to have more than it's share of odd corner cases where
things can go wrong.

Less likely, but still possible, is that there's some malicious
element involved.  Keep your security tight to minimize the risk of
that.

-Kevin

Re: catalog corruption causes

От
"Burgholzer, Robert (DEQ)"
Дата:
Thanks Kevin, that gives me more than enough things to pepper my
sysadmin with. :)

r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer@deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/


Re: catalog corruption causes

От
Scott Marlowe
Дата:
On Fri, Aug 6, 2010 at 12:28 PM, Burgholzer, Robert (DEQ)
<Robert.Burgholzer@deq.virginia.gov> wrote:
> I am trying to get my head around what causes catalog corruption.  I
> have posted before with regard to recovering from corruptions (if that
> is what indeed happened to me), and was given much help.
>
> Does anyone know why a database catalog will get corrupted?  As I
> mentioned previously, my db involves considerable use of temporary
> tables created by php-psql connections.  Other than that, I don't know
> of too much that is "odd" about my use (misuse) of the database.

Two most common causes are bad memory / hard drives / cpu and a
machine that doesn't fsync properly crashing and losing part of a
write to the disks.

memtest86+ will give you an idea if your hardware (cpu / mem) are
stable and reliable.  SMART can tell you if your hard drives are
acting up.  pgbench can tell you if your system is lying about fsync
(a single SATA drive shouldn't be able to do more than a few hundred
tps).

Re: catalog corruption causes

От
Scott Marlowe
Дата:
On Fri, Aug 6, 2010 at 12:28 PM, Burgholzer, Robert (DEQ)
<Robert.Burgholzer@deq.virginia.gov> wrote:
> I am trying to get my head around what causes catalog corruption.  I
> have posted before with regard to recovering from corruptions (if that
> is what indeed happened to me), and was given much help.
>
> Does anyone know why a database catalog will get corrupted?  As I
> mentioned previously, my db involves considerable use of temporary
> tables created by php-psql connections.  Other than that, I don't know
> of too much that is "odd" about my use (misuse) of the database.

Note that it's also possible you've found some uncommon corner case
with pgsql code.  If you have tested memory / cpu and can consistently
get corrupted catalogs by running a particular sequence of commands
against pgsql over and over then definitely report it and provide a
test case.  Guidance on reporting problems here:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

High-water Mark for number of sessions/connections reached in Postgres

От
"Tomeh, Husam"
Дата:
I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres
database.Is there a view or command that can provide this information.  The pg_stat_database shows the current number
ofconnections, but not the high-water mark a database had reached. 

Thanks in advance.

      Husam
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.

Thank you.
******************************************************************************************
CLLD


Re: High-water Mark for number of sessions/connections reached in Postgres

От
"Kevin Grittner"
Дата:
"Tomeh, Husam" <HTomeh@corelogic.com> wrote:

> I was wondering if we can query/obtain the high-water mark of
> number of sessions or connections reached in a Postgres database.

I'm not aware of anything like that, although it seems as though it
would have obvious uses in database administration.  If you had such
a thing, would you want the ability to reset it?

-Kevin

Re: catalog corruption causes

От
"Burgholzer, Robert (DEQ)"
Дата:
Thanks Scott,
I will look into these testing programs and file a bug if I have in fact
found a special occurrence.  I suspect that there might be some issues
with our postgres install, as the initial install was via a yum, and I
recall that our sysadmin had some difficulty removing it in order to do
a compile from source install.  We have also experienced one or two
cases of strange hard drive behavior, so this gives us some avenues to
explore.

r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer@deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/


Re: High-water Mark for number of sessions/connections reached in Postgres

От
Scott Marlowe
Дата:
On Fri, Aug 6, 2010 at 6:19 PM, Tomeh, Husam <HTomeh@corelogic.com> wrote:
> I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres
database.Is there a view or command that can provide this information.  The pg_stat_database shows the current number
ofconnections, but not the high-water mark a database had reached. 

It's a pretty easy thing to approximate with a shell script.

while true;do ps ax|grep postgres:|grep -v grep|wc -l ;sleep 10;done >
connects.log &

then just tail the connects.log file.  It's a dirty hack and it'll be
a few counts over due to counting the postmaster and a few other
processes, but it'll give you a good idea of what your system is
doing.  Add a date in there if you need to know the time it was
happening.

Re: High-water Mark for number of sessions/connections reached in Postgres

От
Scott Marlowe
Дата:
If you want a date stamp, you can change the ps ax stuff to look like this:

date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc -l

On Mon, Aug 9, 2010 at 4:21 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Aug 6, 2010 at 6:19 PM, Tomeh, Husam <HTomeh@corelogic.com> wrote:
>> I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a
Postgresdatabase. Is there a view or command that can provide this information.  The pg_stat_database shows the current
numberof connections, but not the high-water mark a database had reached. 
>
> It's a pretty easy thing to approximate with a shell script.
>
> while true;do ps ax|grep postgres:|grep -v grep|wc -l ;sleep 10;done >
> connects.log &
>
> then just tail the connects.log file.  It's a dirty hack and it'll be
> a few counts over due to counting the postmaster and a few other
> processes, but it'll give you a good idea of what your system is
> doing.  Add a date in there if you need to know the time it was
> happening.
>



--
To understand recursion, one must first understand recursion.

Re: High-water Mark for number of sessions/connections reached in Postgres

От
Alvaro Herrera
Дата:
Excerpts from Scott Marlowe's message of lun ago 09 18:29:58 -0400 2010:
> If you want a date stamp, you can change the ps ax stuff to look like this:
>
> date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc -l

FWIW the "grep" business is best solved by ps itself, something like
ps ax -C postgres

date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax -C  postgres|wc -l

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: High-water Mark for number of sessions/connections reached in Postgres

От
"Tomeh, Husam"
Дата:
Thanks for these workarounds I have something similar implemented. It would've been nice to have Postgres maintain the
high-watermark inside the database in a pg view such as pg_stat_database. This is useful from a capacity perspective
wherethat can be monitored to alert DBAs when a threshold is reached. Other DB engines has such feature built in such
asOracle. Perhaps something to be on Postgres TO DO  list soon  :)
 

Regards,
      Husam

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Alvaro Herrera
Sent: Monday, August 09, 2010 3:39 PM
To: Scott Marlowe
Cc: Tomeh, Husam; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] High-water Mark for number of sessions/connections reached in Postgres

Excerpts from Scott Marlowe's message of lun ago 09 18:29:58 -0400 2010:
> If you want a date stamp, you can change the ps ax stuff to look like this:
> 
> date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc -l

FWIW the "grep" business is best solved by ps itself, something like 
ps ax -C postgres

date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax -C  postgres|wc -l

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.

Thank you.
******************************************************************************************
CLLD

Re: High-water Mark for number of sessions/connections reached in Postgres

От
Rosser Schwarz
Дата:
On Mon, Aug 9, 2010 at 5:33 PM, Tomeh, Husam <HTomeh@corelogic.com> wrote:
> This is useful from a capacity perspective where that can be monitored to alert DBAs when a threshold is reached.

If alerting based on connection counts is your concern, you might get
some benefit from the Bucardo project's check_postgres.pl script.  One
of the tests it performs is a backend count.  It works with any
monitoring package that speaks NRPE, so you can issue alerts based on
either a percentage of your max_connections setting, or on the actual
backend count, as indicated by the arguments with which it's invoked.

It would also be fairly straightforward to store whatever value the
test returns in a db table, log file, Cacti/other RRD-based tools, or
whatever else strikes your fancy, for historical and trending
analysis.

<http://bucardo.org/check_postgres/>

rls

--
:wq