Re: 121+ million record table perf problems

cyber-postgres(at)midnightfantasy(dot)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/