From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@postgresql.org
Subject: Re: db growing out of proportion
Date: Fri, 30 May 2003 22:50:02 -0600
Message-ID: <20030530225002.5750376f.Robert_Creager@LogicalChaos.org> (raw)
In-Reply-To: <17783.1054354286@sss.pgh.pa.us>
References: <Pine.LNX.4.44.0305301012040.22127-100000@RedDragon.Childs>
<8938.1054300299@sss.pgh.pa.us>
<20030530212101.37cef86f.Robert_Creager@LogicalChaos.org>
<17783.1054354286@sss.pgh.pa.us>
On Sat, 31 May 2003 00:11:26 -0400
Tom Lane <tgl@sss.pgh.pa.us> said something like:
>
> Cool ... but it's not immediately obvious which of these changes did the
> trick for you. What settings were you at before? And what's the
> details of the problem query?
>
> The first three settings you mention all seem like reasonable choices,
> but I'd be hesitant to recommend 64M sort_mem for general use (it won't
> take very many concurrent sorts to drive you into the ground...). So
> I'm interested to narrow down exactly what was the issue here.
>
> regards, tom lane
shared_buffers was 1024, now 8192
max_fsm_relations was 1000, now 10000
max_fsm_pages was 20000, now 100000
wal_buffers was 8, now 16
sort_mem was 1024, now 64000
vacuum_mem was 1024, now 64000
effective_cache_size was 1000, now 100000
I am in the process of reloading the dB, but obs_v and obs_i contain ~750000 records each. I'd be happy to play around with the settings if you would like to see the timing results. I'll also be able to get some explain analyze results tomorrow when finished reloading. Suggestions as to what values to change first?
There is a 'C' language trigger on the obs_v and obs_i tables which essentially combines the data from the the obs_? tables and updates the catalog table when the obs_? records are updated.
The query is:
UPDATE obs_v
SET mag = obs_v.imag + zp.zero_v + cg.color_v * (obs_v.imag - i.imag),
use = true
FROM color_group AS cg, zero_pair AS zp, obs_i AS i, files AS f
WHERE obs_v.star_id = i.star_id
AND obs_v.file_id = f.file_id
AND cg.group_id = f.group_id
AND f.group_id = $group_id
AND zp.pair_id = f.pair_id
which is called from a perl script (DBD::Pg - which sets $group_id), and the relevant tables are:
Table "public.obs_v"
Column | Type | Modifiers
---------+---------+------------------------------------------------
x | real | not null
y | real | not null
imag | real | not null
smag | real | not null
ra | real | not null
dec | real | not null
obs_id | integer | not null default nextval('"obs_id_seq"'::text)
file_id | integer |
use | boolean | default false
solve | boolean | default false
star_id | integer |
mag | real |
Indexes: obs_v_file_id_index btree (file_id),
obs_v_loc_index btree (ra, "dec"),
obs_v_obs_id_index btree (obs_id),
obs_v_star_id_index btree (star_id),
obs_v_use_index btree (use)
Foreign Key constraints: obs_v_files_constraint FOREIGN KEY (file_id) REFERENCES files(file_id) ON UPDATE NO ACTION ON DELETE CASCADE
Triggers: obs_v_trig
with obs_i being identical (inherited from same root table)
Table "public.color_group"
Column | Type | Modifiers
----------+---------+-----------
group_id | integer |
color_u | real | default 0
color_b | real | default 0
color_v | real | default 0
color_r | real | default 0
color_i | real | default 0
Indexes: color_group_group_id_index btree (group_id)
Foreign Key constraints: $1 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON UPDATE NO ACTION ON DELETE CASCADE
Table "public.zero_pair"
Column | Type | Modifiers
---------+---------+-----------
pair_id | integer | not null
zero_u | real | default 0
zero_b | real | default 0
zero_v | real | default 0
zero_r | real | default 0
zero_i | real | default 0
Indexes: zero_pair_pkey primary key btree (pair_id),
zero_pair_pair_id_index btree (pair_id)
Foreign Key constraints: $1 FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON UPDATE NO ACTION ON DELETE CASCADE
Table "public.files"
Column | Type | Modifiers
----------+--------------------------+-------------------------------------------------------
file_id | integer | not null default nextval('"files_file_id_seq"'::text)
group_id | integer |
pair_id | integer |
date | timestamp with time zone | not null
name | character varying | not null
ra_min | real | default 0
ra_max | real | default 0
dec_min | real | default 0
dec_max | real | default 0
Indexes: files_pkey primary key btree (file_id),
files_name_key unique btree (name),
files_id_index btree (file_id, group_id, pair_id),
files_range_index btree (ra_min, ra_max, dec_min, dec_max),
imported__file_id_idex btree (file_id)
Foreign Key constraints: $1 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON UPDATE NO ACTION ON DELETE CASCADE,
$2 FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON UPDATE NO ACTION ON DELETE CASCADE
Table "public.catalog"
Column | Type | Modifiers
------------------+------------------+-------------------------------------------------
star_id | integer | not null default nextval('"star_id_seq"'::text)
loc_count | integer | default 0
ra | real | not null
ra_sum | double precision | default 0
ra_sigma | real | default 0
ra_sum_square | double precision | default 0
dec | real | not null
dec_sum | double precision | default 0
dec_sigma | real | default 0
dec_sum_square | double precision | default 0
mag_u_count | integer | default 0
mag_u | real | default 99
mag_u_sum | double precision | default 0
mag_u_sigma | real | default 0
mag_u_sum_square | double precision | default 0
mag_b_count | integer | default 0
mag_b | real | default 99
mag_b_sum | double precision | default 0
mag_b_sigma | real | default 0
mag_b_sum_square | double precision | default 0
mag_v_count | integer | default 0
mag_v | real | default 99
mag_v_sum | double precision | default 0
mag_v_sigma | real | default 0
mag_v_sum_square | double precision | default 0
mag_r_count | integer | default 0
mag_r | real | default 99
mag_r_sum | double precision | default 0
mag_r_sigma | real | default 0
mag_r_sum_square | double precision | default 0
mag_i_count | integer | default 0
mag_i | real | default 99
mag_i_sum | double precision | default 0
mag_i_sigma | real | default 0
mag_i_sum_square | double precision | default 0
Indexes: catalog_pkey primary key btree (star_id),
catalog_ra_decl_index btree (ra, "dec"),
catalog_star_id_index btree (star_id)
--
O_
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: Robert_Creager@LogicalChaos.org, tgl@sss.pgh.pa.us, pgsql-bugs@postgresql.org
Subject: Re: db growing out of proportion
In-Reply-To: <20030530225002.5750376f.Robert_Creager@LogicalChaos.org>
References: <Pine.LNX.4.44.0305301012040.22127-100000@RedDragon.Childs> <8938.1054300299@sss.pgh.pa.us> <20030530212101.37cef86f.Robert_Creager@LogicalChaos.org> <17783.1054354286@sss.pgh.pa.us> <20030530225002.5750376f.Robert_Creager@LogicalChaos.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox