dbase restore

Bradley Brown <bradley@segrestfarms.com> writes:

When I try to restore th dbase I get the following on screen:

"...NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'countries_pkey' for table 'countries' pqReadData() -- backend closed
the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
connection to server was lost".

Hmm, could you provide a debugger backtrace from the core file that
(hopefully) was left by the crashing backend?

Also, you might try running the dump script with debug_print_query
turned on, so that the log will show exactly what query triggers
the crash.

Or, you could just update to PG 7.2 and see if the problem goes away ;-)
7.0 is quite a ways back (especially if it's really 7.0 and not
7.0.something).

regards, tom lane

I'm running PostgreSQL 7.1.3 on Red Hat Linux 7.1 and believe there is a
problem with my PostgreSQL server. I have a PHP application on a separate
server accessing the PostgreSQL server. The PostgreSQL server seems to be
getting hammered, as even simple queries on indexed columns are taking
FOREVER. When I run top, here I normally see at least 50 entries similar to
these for postmaster:

19336 postgres 9 0 92960 90M 92028 S 0.0 9.0 0:18 postmaster
19341 postgres 9 0 87996 85M 87140 S 0.0 8.5 0:09 postmaster
19355 postgres 9 0 87984 85M 87112 S 11.6 8.5 0:09 postmaster
19337 postgres 9 0 87952 85M 87092 S 0.0 8.5 0:09 postmaster

The server info from top reads:

57 processes: 54 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 54.3% user, 3.7% system, 0.0% nice, 41.8% idle
Mem: 1028908K av, 346760K used, 682148K free, 93812K shrd, 8640K
buff
Swap: 1048536K av, 0K used, 1048536K free 131796K
cached

My postgresql.conf has the following changes/additions to it that change
from the default:

max_connections = 512
sort_mem = 1024
shared_buffers = 12800

I've run a vacuum on all the tables and still see the same slow responses
from the database. Does anyone have any suggestions and/or pointers?

Thank you,

Joshua Hoover

On Wed, Mar 20, 2002 at 06:04:17PM -0500, Joshua Hoover wrote:

I'm running PostgreSQL 7.1.3 on Red Hat Linux 7.1 and believe there is a
problem with my PostgreSQL server. I have a PHP application on a separate
server accessing the PostgreSQL server. The PostgreSQL server seems to be
getting hammered, as even simple queries on indexed columns are taking
FOREVER. When I run top, here I normally see at least 50 entries similar to
these for postmaster:

19336 postgres 9 0 92960 90M 92028 S 0.0 9.0 0:18 postmaster
19341 postgres 9 0 87996 85M 87140 S 0.0 8.5 0:09 postmaster
19355 postgres 9 0 87984 85M 87112 S 11.6 8.5 0:09 postmaster
19337 postgres 9 0 87952 85M 87092 S 0.0 8.5 0:09 postmaster

90MB per process? wow. Can you look in the server logs to see which query is
taking all the time?

I know PHP has persistant and non-persistant connections. I don't know what
criteria it uses to determine when the connection can be closed. Do you know
what your server is doing?

Lastly, is there a lot of disk activity?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Joshua Hoover wrote:

I'm running PostgreSQL 7.1.3 on Red Hat Linux 7.1 and believe there is a
problem with my PostgreSQL server. I have a PHP application on a separate
server accessing the PostgreSQL server. The PostgreSQL server seems to be
getting hammered, as even simple queries on indexed columns are taking
FOREVER. When I run top, here I normally see at least 50 entries similar to
these for postmaster:

19336 postgres 9 0 92960 90M 92028 S 0.0 9.0 0:18 postmaster
19341 postgres 9 0 87996 85M 87140 S 0.0 8.5 0:09 postmaster
19355 postgres 9 0 87984 85M 87112 S 11.6 8.5 0:09 postmaster
19337 postgres 9 0 87952 85M 87092 S 0.0 8.5 0:09 postmaster

Looks pretty good to me, assuming it's a top(1) output as I
see it on my RedHAT 7.1 system.

Nearly all of the virtual memory of the processes is shared
memory and everything seems to be sucked in (the 90M etc.
column, resident set size).

The server info from top reads:

57 processes: 54 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 54.3% user, 3.7% system, 0.0% nice, 41.8% idle
Mem: 1028908K av, 346760K used, 682148K free, 93812K shrd, 8640K
buff
Swap: 1048536K av, 0K used, 1048536K free 131796K
cached

Confirms my above statement, 0K used Swap, so that server has
plenty of unused RAM.

My postgresql.conf has the following changes/additions to it that change
from the default:

max_connections = 512
sort_mem = 1024
shared_buffers = 12800

1 Gig of physical RAM on a dedicated database server (that's
what you describe at the top, since your PHP app runs on a
different system, so PostgreSQL is the only thing here,
right?).

Why don't you make a serious amount of that memory available
for shared buffers? I would start with 65536 (1/2 GB). Don't
forget to adjust SHMMAX during boot!

I've run a vacuum on all the tables and still see the same slow responses
from the database. Does anyone have any suggestions and/or pointers?

Did you run VACUUM only or did you also allow it to ANALYZE
the tables? This is the main pointer, but don't miss the
above optimization hints.

Thank you,

You're welcome.

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 Thu, 21 Mar 2002, Martijn van Oosterhout wrote:

On Wed, Mar 20, 2002 at 06:04:17PM -0500, Joshua Hoover wrote:

I'm running PostgreSQL 7.1.3 on Red Hat Linux 7.1 and believe there is a
problem with my PostgreSQL server. I have a PHP application on a separate
server accessing the PostgreSQL server. The PostgreSQL server seems to be
getting hammered, as even simple queries on indexed columns are taking
FOREVER. When I run top, here I normally see at least 50 entries similar to
these for postmaster:

19336 postgres 9 0 92960 90M 92028 S 0.0 9.0 0:18 postmaster
19341 postgres 9 0 87996 85M 87140 S 0.0 8.5 0:09 postmaster
19355 postgres 9 0 87984 85M 87112 S 11.6 8.5 0:09 postmaster
19337 postgres 9 0 87952 85M 87092 S 0.0 8.5 0:09 postmaster

90MB per process? wow. Can you look in the server logs to see which query is
taking all the time?

I can't help with the problem but is 90MB such a shock? I can get towards that
just by running something like:

SELECT * FROM big_table
WHERE time > 'sometime'
AND time < 'someothertime'
AND name IN ('first', 'second', 'third', 'fourth', 'fifth')
ORDER BY time

Indeed I got blase about running such a thing and rather than the backend
instance dying the last time it froze my kernel. I haven't done it again.

BTW, the killer bit was the fifth name, up to that point things got large but
stayed within capabilities of the machine. I tried all I could think of to get
limits applied to the backend processes (short of editing and recompiling from
source) but nothing worked. There wasn't any change when switching from a IN
test to a string of ORs.

(6.5.1 I think postgres, since upgraded to 7.2 on FreeBSD 3.3-STABLE)

Why am I saying this? No idea. Just not sure why a 90MB footprint for a DB
backend would be so shocking.

Nigel J.Andrews
Logictree Systems Limited

On Thu, Mar 21, 2002 at 02:10:08PM +0000, Nigel J. Andrews wrote:

On Thu, 21 Mar 2002, Martijn van Oosterhout wrote:

90MB per process? wow. Can you look in the server logs to see which query is
taking all the time?

I can't help with the problem but is 90MB such a shock? I can get towards that
just by running something like:

We have tables here running into the hundreds of megabytes and if a backend
process goes over 40MB, it generally means a bug in the query.

SELECT * FROM big_table
WHERE time > 'sometime'
AND time < 'someothertime'
AND name IN ('first', 'second', 'third', 'fourth', 'fifth')
ORDER BY time

Indeed I got blase about running such a thing and rather than the backend
instance dying the last time it froze my kernel. I haven't done it again.

That basically means you gave the backend enough memory to hang the machine.
I think that means you went too far.

Given the above query, do you have any form of index on "time"? How big is
your table? If the backend process is using a lot of memory, that's
generally an indication that it's doing a sort on a large resultset. An
index can solve that problem.

From here it becomes hard to suggest anything without an indication of the
EXPLAIN output of a query. Sometimes with proper jiggling, a 10 second query
can become a sub-second one.

BTW, the killer bit was the fifth name, up to that point things got large
but stayed within capabilities of the machine. I tried all I could think
of to get limits applied to the backend processes (short of editing and
recompiling from source) but nothing worked. There wasn't any change when
switching from a IN test to a string of ORs.

I beleive the parser converts the IN to ORs anyway.

(6.5.1 I think postgres, since upgraded to 7.2 on FreeBSD 3.3-STABLE)

Why am I saying this? No idea. Just not sure why a 90MB footprint for a DB
backend would be so shocking.

I think as someone else pointed out, it's probably all shared memory any and
so may not be a problem. That doesn't solve your basic problem though.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Thu, Mar 21, 2002 at 02:10:08PM +0000, Nigel J. Andrews wrote:

(6.5.1 I think postgres, since upgraded to 7.2 on FreeBSD 3.3-STABLE)

Why am I saying this? No idea. Just not sure why a 90MB footprint for a DB
backend would be so shocking.

I think as someone else pointed out, it's probably all shared memory any and
so may not be a problem. That doesn't solve your basic problem though.

Yeah, the number reported by ps should be viewed with suspicion until
you know for certain whether it counts the shared memory segment or not.
(In my experience, on some platforms it does and on some it doesn't.)

However, I think the real issue here is probably just 6.5's well known
problems with intra-query memory leaks. If Nigel can reproduce the
difficulty on 7.2 then I'd be more interested in looking into it...

regards, tom lane

On Thu, 21 Mar 2002, Tom Lane wrote:

Martijn van Oosterhout <kleptog@svana.org> writes:

On Thu, Mar 21, 2002 at 02:10:08PM +0000, Nigel J. Andrews wrote:

(6.5.1 I think postgres, since upgraded to 7.2 on FreeBSD 3.3-STABLE)

Why am I saying this? No idea. Just not sure why a 90MB footprint for a DB
backend would be so shocking.

I think as someone else pointed out, it's probably all shared memory any and
so may not be a problem. That doesn't solve your basic problem though.

Yeah, the number reported by ps should be viewed with suspicion until
you know for certain whether it counts the shared memory segment or not.
(In my experience, on some platforms it does and on some it doesn't.)

That I considered somewhat immaterial since the process was using the
memory. That's what normally killed the process, using up all available memory
including swap and still requiring more.

However, I think the real issue here is probably just 6.5's well known
problems with intra-query memory leaks. If Nigel can reproduce the
difficulty on 7.2 then I'd be more interested in looking into it...

Good point, I hadn't tried it since the upgrade becuase that wasn't why I
upgraded (don't worry I've got a _long_ post on that subject waiting to be
sent), I tightened up limits for the generation of the SQL string in the
application before then. However, I have just tried it with 7 poster_names
listed and top never reported even 8MB for the postgres footprint. I won't give
the EXPLAIN output because it's not interesting and it would almost be an
overlap with the contents of my long, pending post.

FWIW, the table has >1 million rows and the list of names I just gave the query
includes some of the highest volume posters, including the top one with 55,000
rows in the table. There is an index on the poster_name and one on the time
columns.

Thanks for the comments, I didn't even know about the 6.5 memory leak.

Nigel Andrews
Logictree Systems Limited

On Fri, Mar 22, 2002 at 01:11:11AM +0000, Nigel J. Andrews wrote:

Good point, I hadn't tried it since the upgrade becuase that wasn't why I
upgraded (don't worry I've got a _long_ post on that subject waiting to be
sent), I tightened up limits for the generation of the SQL string in the
application before then. However, I have just tried it with 7 poster_names
listed and top never reported even 8MB for the postgres footprint. I won't give
the EXPLAIN output because it's not interesting and it would almost be an
overlap with the contents of my long, pending post.

Hmm, with something that matches most of the table, it would likely choose a
sequential scan which can take quite a while over a large table.

FWIW, the table has >1 million rows and the list of names I just gave the query
includes some of the highest volume posters, including the top one with 55,000
rows in the table. There is an index on the poster_name and one on the time
columns.

If you're selecting on one column (poster_name) and sorting on another
(time) it may help to have an index on both (time,poster_name) since that
avoids the sort step. (I hope 7.2 estimates sort costs better than earlier
versions).

However, if you really want the whole output (rather than say the first 100
lines) and that really involves trawling a majority of the table, then you
are simply bound by disk transfer speed.

Alternativly, using a cursor may allow you to start outputting data before
the query has finished.

Thanks for the comments, I didn't even know about the 6.5 memory leak.

Earlier versions of postgres tended to leak a lot within queries (not
between queries). 7.0 fixed most of them but still some issues with
functions and I don't beleive it's a problem anymore.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

2002-03-22 16:44:28 [383] DEBUG: SIInsertDataEntry: table is 70% full,
signaling postmaster
2002-03-22 16:44:28 [383] NOTICE: RegisterSharedInvalid: SI buffer
overflow
2002-03-22 16:44:28 [315] NOTICE: InvalidateSharedInvalid: cache state
reset
2002-03-22 16:44:28 [279] NOTICE: InvalidateSharedInvalid: cache state
reset
2002-03-22 16:44:28 [278] NOTICE: InvalidateSharedInvalid: cache state
reset
2002-03-22 16:44:28 [277] NOTICE: InvalidateSharedInvalid: cache state
reset
2002-03-22 16:44:28 [276] NOTICE: InvalidateSharedInvalid: cache state
reset

This is happening once a rather large batch process commits ie a PLPGSQL
function from within cron.

I have had a good look through groups and most of the time it seems to be
related to a vacuum.

In this circumstance it is causing a peer process job that useses
persistant connections to receive a SIGPIPE signal.

Can some one please advise us can we stop the table filling? Or can we stop
the SIGPIPE?

Thanks

Andrew Bartley

"Andrew Bartley" <abartley@evolvosystems.com> writes:

2002-03-22 16:44:28 [383] DEBUG: SIInsertDataEntry: table is 70% full,
signaling postmaster
2002-03-22 16:44:28 [383] NOTICE: RegisterSharedInvalid: SI buffer
overflow
2002-03-22 16:44:28 [315] NOTICE: InvalidateSharedInvalid: cache state
reset

AFAIK these notices are completely harmless (and they have in fact been
downgraded to DEBUG level in 7.2). SI buffer overrun is an expected
condition when a large number of system catalog updates are committed
at once.

In this circumstance it is causing a peer process job that useses
persistant connections to receive a SIGPIPE signal.

I think the SIGPIPE has little or no direct connection to the SI buffer
overruns --- what *exactly* are you doing and what are you seeing?

regards, tom lane

On Fri, 22 Mar 2002, Martijn van Oosterhout wrote:

On Fri, Mar 22, 2002 at 01:11:11AM +0000, Nigel J. Andrews wrote:

Good point, I hadn't tried it since the upgrade becuase that wasn't why I
upgraded (don't worry I've got a _long_ post on that subject waiting to be
sent), ...
[sniped]
...
the EXPLAIN output because it's not interesting and it would almost be
an overlap with the contents of my long, pending post.

Hmm, with something that matches most of the table, it would likely choose a
sequential scan which can take quite a while over a large table

Yep, doing a seqscan does take a while and I can see why it does one most of
the time. This is the subject of my long, pending post, which is no longer
pending as I have recently sent it to the list.

[sniped]

If you're selecting on one column (poster_name) and sorting on another
(time) it may help to have an index on both (time,poster_name) since that
avoids the sort step. (I hope 7.2 estimates sort costs better than earlier
versions).

Thank you, it didn't occur to me that the two column index would ease the
sorting. Would the order of the columns specified in the index creation be
significant?

However, if you really want the whole output (rather than say the first 100
lines) and that really involves trawling a majority of the table, then you
are simply bound by disk transfer speed.

I have coded the application to use all the data, caching the pages (it's a web
site) not requested but which use the results of the query, eg. pages 2 to xxx
of the results when page 1 has been requested by the user. I'm happy this way
since the query hit is taken only once instead of several dozen times and I
have a fancy caching system that fits in nicely with data update frequency. In
fact, that was one of the driving forces behind my fancy caching system. The
I/O is a definitely a bottleneck. There's almost continuous disk activity with
a change in it's 'character' marking the switch from backend query processing
to page cache filling.

Alternativly, using a cursor may allow you to start outputting data before
the query has finished.

At the moment the application just issues the query and waits for the results.
I had considered changing this more in order to reduce the memory usage within
the client than as a speeding up means. I think such a change makes the split
between content and presentation harder to maintain though, possible but
harder, or shall we say more sophisticated.

--
Nigel J. Andrews
Logictree Systems Limited

On Fri, Mar 22, 2002 at 02:09:16PM +0000, Nigel J. Andrews wrote:

If you're selecting on one column (poster_name) and sorting on another
(time) it may help to have an index on both (time,poster_name) since that
avoids the sort step. (I hope 7.2 estimates sort costs better than earlier
versions).

Thank you, it didn't occur to me that the two column index would ease the
sorting. Would the order of the columns specified in the index creation be
significant?

Yes. If you build an index on the columns (a,b), the index becomes a tree
where each value of a is ordered. At each node there is a subtree with each
value of b in ordered format. So if you do a sequential scan on an index it
comes out ordered by a then b.

Ofcourse, after identifying a tuple in the index, the database then has to
go back to the main table to check that it's valid in the current
transaction (and to get the data ofcourse). (There is a good reason
somewhere why transaction information is not stored within the index but I
don't remember it right now).

So it comes down to a comparison between index scan over most of the table
vs. sequential scan + sort of whole table. I'm not sure which would win...

HTH,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/