Adjusting fsm values was Re: [BUGS] db growing out of proportion - Mailing list pgsql-performance

Hey Tom,

Sorry for the long delay.  I'd been having mail trouble, and your and
postgresql mail servers were bouncing me because Starband (my ISP)
doesn't setup a full DNS entry for their clients.  I'm now relaying
through another host.

I'm posting to the performance list, as it seems more appropriate there.

The results were not as clear cut as I would of thought.  If either
fsm_relations, fsm_pages or sort_mem were dropped to their original
values, the queries went from 3 hours to not completing 9/15 sets after
13 hours. When the shared buffers were reverted, the set completed in 12
hours.

I didn't capture any explains for the problem settings, but will be
happy to do so if you would like to see some of the results (if they are
different).  I'm almost caught up with importing new data (too much rain
around here to take new data), and can explain away this weekend.

Cheers,
Rob

On Fri, 30 May 2003 22:50:02 -0600
Robert Creager <Robert_Creager@LogicalChaos.org> said something like:

> 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_
>


--
O_