From: Joshua D. Drake <jd@commandprompt.com>
To: cyber-postgres@midnightfantasy.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 121+ million record table perf problems
Date: Fri, 18 May 2007 11:51:04 -0700
Message-ID: <464DF598.20706@commandprompt.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
References: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, cyber-postgres@midnightfantasy.com
Subject: Re: 121+ million record table perf problems
In-Reply-To: <464DF598.20706@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox