Re: [PERFORM] Problem with query, server totally unresponsive
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 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
Re: [PERFORM] Problem with query, server totally unresponsive
From: [EMAIL PROTECTED] [mailto:pgsql-performance- [EMAIL PROTECTED] 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: 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. 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/ProductList.asp?Category=34N=200034+5 0001157+1302820275+1051007392Submit=ENE Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Problem with query, server totally unresponsive
On Fri, Mar 24, 2006 at 08:46:54AM -0600, Dave Dutcher wrote: 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. 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 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
[PERFORM] Problem with query, server totally unresponsive
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_transactions = 5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of
shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
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_array(text);
CREATE FUNCTION hydra.join_aggregate(text, text) RETURNS text
AS 'select $1 || ''|'' || $2'
LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION hydra.join_aggregate_to_array(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_array
);
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'::regclass)
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)::timestamp(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
