PostgreSQL Performance - Problem with query, server totally unresponsive

Author

Problem with query, server totally unresponsive

Bendik Rognlien Johansen

2006-03-23, 7:46 am

Hello, I have a big problem with one of my databases. When i run my
query, after a few minutes, the postmaster shows 99% mem i top, and
the server becomes totally unresponsive.

I get this message when I try to cancel the query:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

This works fine on a different machine with the same database
settings and about 30% less records. The other machine is running
PostgreSQL 8.0.3
The troubled one is running 8.1.2

Any help is greatly appreciated!

Thanks

The machine has 2x Intel dual core processors (3GHz) and 2 Gigs of ram.

#-----------------------------------------------------------------------
----
# RESOURCE USAGE (except WAL)
#-----------------------------------------------------------------------
----

# - Memory -

shared_buffers = 8192 # min 16 or
max_connections*2, 8KB each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transa
ctions = 5 # can be 0 or more
# note: increasing max_prepared_transac
tions costs ~600 bytes of
shared memory
# per transaction slot, plus lock space (see max_locks_per_transa
ction).
work_mem = 4096 # min 64, size in KB
maintenance_work_mem
= 262144 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB

My query:

SELECT r.id AS id, max(r.name) AS name, max(companyid) AS companyid,
max(extract(epoch from r.updated)) as r_updated, hydra.join(co.value)
AS contacts, hydra.join(ad.postalsite) AS postalsites FROM records r
LEFT OUTER JOIN contacts co ON(r.id = co.record AND co.type IN
(1,11,101,3)) LEFT OUTER JOIN addresses ad ON(r.id = ad.record) WHERE
r.original IS NULL GROUP BY r.id;

The hydra.join function
-- Aggregates a column to an array

DROP FUNCTION hydra. join_aggregate(text,
text) CASCADE;
DROP FUNCTION hydra. join_aggregate_to_ar
ray(text);

CREATE FUNCTION hydra. join_aggregate(text,
text) RETURNS text
AS 'select $1 || ''|'' || $2'
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION hydra. join_aggregate_to_ar
ray(text) RETURNS text[]
AS 'SELECT string_to_array($1, ''|'')'
LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE hydra.join (
BASETYPE = text
,SFUNC = hydra.join_aggregate
,STYPE = text
,FINALFUNC = hydra. join_aggregate_to_ar
ray
);

Tables:
records: 757278 rows
contacts: 2256253 rows
addresses: 741536 rows

Explain:

QUERY PLAN
------------------------------------------------------------------------
-----------------------------
GroupAggregate (cost=636575.63..738618.40 rows=757278 width=75)
-> Merge Left Join (cost=636575.63..694469.65 rows=1681120
width=75)
Merge Cond: ("outer".id = "inner".record)
-> Merge Left Join (cost=523248.93..552247.54
rows=1681120 width=63)
Merge Cond: ("outer".id = "inner".record)
-> Sort (cost=164044.73..165937.93 rows=757278
width=48)
Sort Key: r.id
-> Seq Scan on records r (cost=0.00..19134.78
rows=757278 width=48)
Filter: (original IS NULL)
-> Sort (cost=359204.20..363407.00 rows=1681120
width=19)
Sort Key: co.record
-> Seq Scan on contacts co
(cost=0.00..73438.06 rows=1681120 width=19)
Filter: (("type" = 1) OR ("type" = 11) OR
("type" = 101) OR ("type" = 3))
-> Sort (cost=113326.70..115180.54 rows=741536 width=16)
Sort Key: ad.record
-> Seq Scan on addresses ad (cost=0.00..20801.36
rows=741536 width=16)
(16 rows)

se_companies=# \d records;
Table "public.records"
Column | Type |
Modifiers
-----------------+--------------------------
+------------------------------------------------------
id | integer | not null default nextval
('records_id_seq'::r
egclass)
companyid | character varying(16) | default ''::character
varying
categories | integer[] |
nace | integer[] |
name | character varying(255) | default ''::character
varying
updated | timestamp with time zone | default
('now'::text)::times
tamp(6) with time zone
updater | integer |
owner | integer |
loaner | integer |
info | text |
original | integer |
active | boolean | default true
categoryquality | integer | not null default 0
searchwords | character varying(128)[] |
priority | integer |
categorized | timestamp with time zone |
infopage | boolean |
national | boolean |
password | character varying(32) |
login | boolean |
deleted | boolean | not null default false
reference | integer[] |
nuinfo | text |
brands | integer[] |
volatile | boolean | not null default false
Indexes:
"records_pkey" PRIMARY KEY, btree (id) CLUSTER
"original_is_null" btree (original) WHERE original IS NULL
" records_category_rdt
ree_idx" gist (categories)
" records_categoryqual
ity_idx" btree (categoryquality)
" records_lower_name_i
dx" btree (lower(name::text))
" records_original_idx
" btree (original)
"records_owner" btree ("owner")
"records_updated_idx" btree (updated)
Foreign-key constraints:
" records_original_fke
y" FOREIGN KEY (original) REFERENCES records
(id)

se_companies=# \d contacts;
Table "public.contacts"
Column | Type | Modifiers
-------------+------------------------
+-------------------------------------------------------
id | integer | not null default nextval
('contacts_id_seq'::
regclass)
record | integer |
type | integer |
value | character varying(128) |
description | character varying(255) |
priority | integer |
itescotype | integer |
original | integer |
source | integer |
reference | character varying(32) |
quality | integer |
deleted | boolean | not null default false
searchable | boolean | not null default true
visible | boolean | not null default true
Indexes:
"contacts_pkey" PRIMARY KEY, btree (id)
" contacts_original_id
x" btree (original)
" contacts_quality_idx
" btree (quality)
"contacts_record_idx" btree (record)
" contacts_source_refe
rence_idx" btree (source, reference)
"contacts_value_idx" btree (value)
Foreign-key constraints:
" contacts_original_fk
ey" FOREIGN KEY (original) REFERENCES
contacts(id)

se_companies=# \d addresses;
Table "public.addresses"
Column | Type |
Modifiers
--------------+--------------------------
+--------------------------------------------------------
id | integer | not null default nextval
('addresses_id_seq':
:regclass)
record | integer |
address | character varying(128) |
extra | character varying(32) |
postalcode | character varying(16) |
postalsite | character varying(64) |
description | character varying(255) |
position | point |
uncertainty | integer | default 99999999
priority | integer |
type | integer |
place | character varying(64) |
floor | integer |
side | character varying(8) |
housename | character varying(64) |
original | integer |
source | integer |
reference | character varying(64) |
quality | integer |
deleted | boolean | not null default false
searchable | boolean | not null default true
visible | boolean | not null default true
municipality | integer |
map | boolean | not null default true
geocoded | timestamp with time zone | default now()
Indexes:
"addresses_pkey" PRIMARY KEY, btree (id)
" addresses_lower_addr
ess_postalcode" btree (lower
(address::text), lower(postalcode::te
xt))
" addresses_original_i
dx" btree (original)
" addresses_record_idx
" btree (record)
" addresses_source_ref
erence_idx" btree (source, reference)
Foreign-key constraints:
" addresses_original_f
key" FOREIGN KEY (original) REFERENCES
addresses(id)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby

2006-03-24, 7:43 am

On Thu, Mar 23, 2006 at 01:12:08PM +0100, Bendik Rognlien Johansen wrote:
> Hello, I have a big problem with one of my databases. When i run my
> query, after a few minutes, the postmaster shows 99% mem i top, and
> the server becomes totally unresponsive.

You've got a bunch of sorts going on; could you be pushing the machine
into swapping?

> I get this message when I try to cancel the query:
>
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

Did you send a kill of some kind to the backend?

> The machine has 2x Intel dual core processors (3GHz) and 2 Gigs of ram.

Unless I missed some big news recently, no such CPU exists.
Hyperthreading is absolutely not the same as dual core, and many people
have found that it's best to disable hyperthreading on database servers.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

http://archives.postgresql.org

Dave Dutcher

2006-03-24, 9:32 am

> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Jim C. Nasby
> Subject: Re: [PERFORM] Problem with query, server totally unresponsive
>
> On Thu, Mar 23, 2006 at 01:12:08PM +0100, Bendik Rognlien Johansen
wrote:
>
> You've got a bunch of sorts going on; could you be pushing the machine
> into swapping?
>
>
> Did you send a kill of some kind to the backend?
>
ram.[color=darkred]
>
> Unless I missed some big news recently, no such CPU exists.
> Hyperthreading is absolutely not the same as dual core, and many
people
> have found that it's best to disable hyperthreading on database
servers.

Maybe I'm confused by the marketing, but I think those CPUs do exist.
According to New Egg the Pentium D 830 and the Pentium D 930 both are
dual core Pentiums that run at 3Ghz. It also specifically says these
processors don't support hyper threading, so I believe they really have
two cores. Maybe you are thinking he was talking about a 3Ghz Core
Duo.

http://www.newegg.com/Product/Produ...&N=2000340000+5
0001157+1302820275+1
051007392&Submit=ENE

Dave

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jim C. Nasby

2006-03-24, 9:32 am

On Fri, Mar 24, 2006 at 08:46:54AM -0600, Dave Dutcher wrote:
> ram.
> people
> servers.
>
> Maybe I'm confused by the marketing, but I think those CPUs do exist.
> According to New Egg the Pentium D 830 and the Pentium D 930 both are
> dual core Pentiums that run at 3Ghz. It also specifically says these
> processors don't support hyper threading, so I believe they really have
> two cores. Maybe you are thinking he was talking about a 3Ghz Core
> Duo.

A quick google shows I'm just behind the times; Intel does have true
dual-core CPUs now.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links