Re: 121+ million record table perf problems - Mailing list pgsql-performance

cyber-postgres@midnightfantasy.com wrote:
> I need some help on recommendations to solve a perf problem.
>
> I've got a table with ~121 million records in it.  Select count on it
> currently takes ~45 minutes, and an update to the table to set a value
> on one of the columns I finally killed after it ran 17 hours and had
> still not completed.  Queries into the table are butt slow, and

Scanning 121 million rows is going to be slow even on 16 disks.

>
> System:   SUSE LINUX 10.0 (X86-64)
> Postgresql:   PostgreSQL 8.2.1
> Index type:   btree

You really should be running 8.2.4.

>
> A select count took ~48 minutes before I made some changes to the
> postgresql.conf, going from default values to these:
> shared_buffers = 24MB

This could be increased.

> work_mem = 256MB
> maintenance_work_mem = 512MB
> random_page_cost = 100
> stats_start_collector = off
> stats_row_level = off
>
> As a test I am trying to do an update on state using the following queries:
> update res set state=5001;

You are updating 121 million rows, that takes a lot of time considering
you are actually (at a very low level) marking 121 million rows dead and
inserting 121 million more.

> The update query that started this all I had to kill after 17hours.  It
> should have updated all 121+ million records.  That brought my select
> count down to 19 minutes, but still a far cry from acceptable.

Not quite sure what you would considerable acceptable based on what you
are trying to do.


Sincerely,

Joshua D. Drake



>
> Here is the schema for the table giving me problems:
>
> CREATE TABLE res
> (
>   res_id integer NOT NULL DEFAULT nextval('result_id_seq'::regclass),
>   res_client_id integer NOT NULL,
>   "time" real DEFAULT 0,
>   error integer DEFAULT 0,
>   md5 character(32) DEFAULT 0,
>   res_tc_id integer NOT NULL,
>   state smallint DEFAULT 0,
>   priority smallint,
>   rval integer,
>   speed real,
>   audit real,
>   date timestamp with time zone,
>   gold_result_id integer,
>   CONSTRAINT result_pkey PRIMARY KEY (res_id),
>   CONSTRAINT unique_res UNIQUE (res_client_id, res_tc_id)
> )
> WITHOUT OIDS;
> ALTER TABLE res OWNER TO postgres;
>
> CREATE INDEX index_audit
>   ON res
>   USING btree
>   (audit);
>
> CREATE INDEX index_event
>   ON res
>   USING btree
>   (error);
>
> CREATE INDEX index_priority
>   ON res
>   USING btree
>   (priority);
>
> CREATE INDEX index_rval
>   ON res
>   USING btree
>   (rval);
>
> CREATE INDEX index_speed
>   ON res
>   USING btree
>   (speed);
>
> CREATE INDEX index_state
>   ON res
>   USING btree
>   (state);
>
> CREATE INDEX index_tc_id
>   ON res
>   USING btree
>   (res_tc_id);
>
> CREATE INDEX index_time
>   ON res
>   USING btree
>   ("time");
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/