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
System: SUSE LINUX 10.0 (X86-64)
Postgresql: PostgreSQL 8.2.1
Index type: btree
A select count took ~48 minutes before I made some changes to the
postgresql.conf, going from default values to these:
shared_buffers = 24MB
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;
select count(resid) from res;
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.
The system has 2GB of RAM (more is alreads on order), but doesn't seem to
show problems in TOP with running away with RAM. If anything, I don't
think it's using enough as I only see about 6 processes using 26-27 MB
each) and is running on a single disk (guess I will likely have to at the
minimum go to a RAID1). Workload will primarily be comprised of queries
against the indicies (thus why so many of them) and updates to a single
record from about 10 clients where that one records will have md5, state,
rval, speed, audit, and date columns updated. Those updates don't seem to
be a problem, and are generally processed in bulk of 500 to 5000 at a
time.
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
