Re: db growing out of proportion

On Sat, 31 May 2003 00:11:26 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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_