pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMassive table (500M rows) update nightmare
30+ messages / 14 participants
[nested] [flat]
* Massive table (500M rows) update nightmare
@ 2010-01-07 07:17 Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 07:49 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-07 13:14 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-08 11:19 ` Re: Massive table (500M rows) update nightmare Eduardo Morras <emorras@s21sec.com>
0 siblings, 4 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-07 07:17 UTC (permalink / raw)
To: pgsql-performance
Our DB has an audit table which is 500M rows and growing. (FYI the objects
being audited are grouped semantically, not individual field values).
Recently we wanted to add a new feature and we altered the table to add a
new column. We are backfilling this varchar(255) column by writing a TCL
script to page through the rows (where every update is a UPDATE ... WHERE id
>= x AND id < x+10 and a commit is performed after every 1000 updates
statement, i.e. every 10000 rows.)
We have 10 columns, six of which are indexed. Rough calculations suggest
that this will take two to three weeks to complete on an 8-core CPU with
more than enough memory.
As a ballpark estimate - is this sort of performance for an 500M updates
what one would expect of PG given the table structure (detailed below) or
should I dig deeper to look for performance issues?
As always, thanks!
Carlo
Table/index structure:
CREATE TABLE mdx_core.audit_impt
(
audit_impt_id serial NOT NULL,
impt_session integer,
impt_version character varying(255),
impt_name character varying(255),
impt_id integer,
target_table character varying(255),
target_id integer,
target_op character varying(10),
note text,
source_table character varying(255),
CONSTRAINT audit_impt_pkey PRIMARY KEY (audit_impt_id)
)
CREATE INDEX audit_impt_impt_id_idx
ON mdx_core.audit_impt
USING btree
(impt_id);
CREATE INDEX audit_impt_impt_name
ON mdx_core.audit_impt
USING btree
(impt_name, impt_version);
CREATE INDEX audit_impt_session_idx
ON mdx_core.audit_impt
USING btree
(impt_session);
CREATE INDEX audit_impt_source_table
ON mdx_core.audit_impt
USING btree
(source_table);
CREATE INDEX audit_impt_target_id_idx
ON mdx_core.audit_impt
USING btree
(target_id, audit_impt_id);
CREATE INDEX audit_impt_target_table_idx
ON mdx_core.audit_impt
USING btree
(target_table, target_id, audit_impt_id);
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-07 07:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-07 17:49 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
3 siblings, 1 reply; 30+ messages in thread
From: Scott Marlowe @ 2010-01-07 07:49 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
On Thu, Jan 7, 2010 at 12:17 AM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
> Our DB has an audit table which is 500M rows and growing. (FYI the objects
> being audited are grouped semantically, not individual field values).
>
> Recently we wanted to add a new feature and we altered the table to add a
> new column. We are backfilling this varchar(255) column by writing a TCL
> script to page through the rows (where every update is a UPDATE ... WHERE id
>>= x AND id < x+10 and a commit is performed after every 1000 updates
> statement, i.e. every 10000 rows.)
>
> We have 10 columns, six of which are indexed. Rough calculations suggest
> that this will take two to three weeks to complete on an 8-core CPU with
> more than enough memory.
>
> As a ballpark estimate - is this sort of performance for an 500M updates
> what one would expect of PG given the table structure (detailed below) or
> should I dig deeper to look for performance issues?
Got an explain analyze of the delete query?
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 07:49 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-01-07 17:49 ` Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 0 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-07 17:49 UTC (permalink / raw)
To: pgsql-performance
> Got an explain analyze of the delete query?
UPDATE mdx_core.audit_impt
SET source_table = 'mdx_import.'||impt_name
WHERE audit_impt_id >= 319400001 AND audit_impt_id <= 319400010
AND coalesce(source_table, '') = ''
Index Scan using audit_impt_pkey on audit_impt (cost=0.00..92.63 rows=1
width=608) (actual time=0.081..0.244 rows=10 loops=1)
Index Cond: ((audit_impt_id >= 319400001) AND (audit_impt_id <=
319400010))
Filter: ((COALESCE(source_table, ''::character varying))::text = ''::text)
Total runtime: 372.141 ms
Hard to tell how reliable these numbers are, because the caches are likely
spun up for the WHERE clause - in particular, SELECT queries have been run
to test whether the rows actually qualify for the update.
The coalesce may be slowing things down slightly, but is a necessary evil.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-07 13:14 ` Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 15:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-07 17:56 ` Re: Massive table (500M rows) update nightmare Greg Sabino Mullane <greg@turnstep.com>
2010-01-07 17:56 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
3 siblings, 3 replies; 30+ messages in thread
From: Leo Mannhart @ 2010-01-07 13:14 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
Carlo Stonebanks wrote:
> Our DB has an audit table which is 500M rows and growing. (FYI the
> objects being audited are grouped semantically, not individual field
> values).
>
> Recently we wanted to add a new feature and we altered the table to add
> a new column. We are backfilling this varchar(255) column by writing a
> TCL script to page through the rows (where every update is a UPDATE ...
> WHERE id >= x AND id < x+10 and a commit is performed after every 1000
> updates statement, i.e. every 10000 rows.)
>
> We have 10 columns, six of which are indexed. Rough calculations suggest
> that this will take two to three weeks to complete on an 8-core CPU with
> more than enough memory.
>
> As a ballpark estimate - is this sort of performance for an 500M updates
> what one would expect of PG given the table structure (detailed below)
> or should I dig deeper to look for performance issues?
>
> As always, thanks!
>
> Carlo
>
If it is possible to lock this audit table exclusively (may be during
off peak hours) I would look into
- create new_audit_table as select col1, col2, col3 ... col9,
'new_col_value' from old_audit_table;
- create all indexes
- drop old_audit_table
- rename new_audit_table to old_audit_table
That is probably the fasted method you can do, even if you have to join
the "new_col_value" from an extra helper-table with the correspondig id.
Remeber, databases are born to join.
You could also try to just update the whole table in one go, it is
probably faster than you expect.
just a thought
Leo
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 13:14 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
@ 2010-01-07 15:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-07 15:47 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
2 siblings, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-01-07 15:18 UTC (permalink / raw)
To: Leo Mannhart <leo.mannhart@beecom.ch>; Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
Leo Mannhart <leo.mannhart@beecom.ch> wrote:
> You could also try to just update the whole table in one go, it is
> probably faster than you expect.
That would, of course, bloat the table and indexes horribly. One
advantage of the incremental approach is that there is a chance for
autovacuum or scheduled vacuums to make space available for re-use
by subsequent updates.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 13:14 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 15:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-07 15:47 ` Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 16:38 ` Re: Massive table (500M rows) update nightmare Ludwik Dylag <ldylag@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Leo Mannhart @ 2010-01-07 15:47 UTC (permalink / raw)
To: pgsql-performance
Kevin Grittner wrote:
> Leo Mannhart <leo.mannhart@beecom.ch> wrote:
>
>> You could also try to just update the whole table in one go, it is
>> probably faster than you expect.
>
> That would, of course, bloat the table and indexes horribly. One
> advantage of the incremental approach is that there is a chance for
> autovacuum or scheduled vacuums to make space available for re-use
> by subsequent updates.
>
> -Kevin
>
ouch...
thanks for correcting this.
... and forgive an old man coming from Oracle ;)
Leo
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 13:14 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 15:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-07 15:47 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
@ 2010-01-07 16:38 ` Ludwik Dylag <ldylag@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Ludwik Dylag @ 2010-01-07 16:38 UTC (permalink / raw)
To: Leo Mannhart <leo.mannhart@beecom.ch>; +Cc: pgsql-performance
I would suggest:
1. turn off autovacuum
1a. ewentually tune db for better performace for this kind of operation
(cant not help here)
2. restart database
3. drop all indexes
4. update
5. vacuum full table
6. create indexes
7. turn on autovacuum
Ludwik
2010/1/7 Leo Mannhart <leo.mannhart@beecom.ch>
> Kevin Grittner wrote:
> > Leo Mannhart <leo.mannhart@beecom.ch> wrote:
> >
> >> You could also try to just update the whole table in one go, it is
> >> probably faster than you expect.
> >
> > That would, of course, bloat the table and indexes horribly. One
> > advantage of the incremental approach is that there is a chance for
> > autovacuum or scheduled vacuums to make space available for re-use
> > by subsequent updates.
> >
> > -Kevin
> >
>
> ouch...
> thanks for correcting this.
> ... and forgive an old man coming from Oracle ;)
>
> Leo
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Ludwik Dyląg
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 13:14 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
@ 2010-01-07 17:56 ` Greg Sabino Mullane <greg@turnstep.com>
2 siblings, 0 replies; 30+ messages in thread
From: Greg Sabino Mullane @ 2010-01-07 17:56 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> If it is possible to lock this audit table exclusively (may be during
> off peak hours) I would look into
> - create new_audit_table as select col1, col2, col3 ... col9,
> 'new_col_value' from old_audit_table;
> - create all indexes
> - drop old_audit_table
> - rename new_audit_table to old_audit_table
This is a good approach, but you don't necessarily have to exclusively
lock the table. Only allowing reads would be enough, or you could
install a trigger to keep track of which rows were updated. Then
the process becomes:
1. Create trigger on the old table to store changed pks
2. Create new_audit_table as select col1, col2, col3 ... col9,
'new_col_value' from old_audit_table;
3. Create all indexes on the new table
4. Stop your app from writing to the old table
5. COPY over the rows that have changed
6. Rename the old table to something else (for safety)
7. Rename the new table to the real name
8. Drop the old table when all is good
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201001071253
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAktGIC0ACgkQvJuQZxSWSshEAQCfRT3PsQyWCOBXGW1XRAB814df
pJUAoMuAJoOKho39opoHq/d1J9NprGlH
=htaE
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 13:14 ` Re: Massive table (500M rows) update nightmare Leo Mannhart <leo.mannhart@beecom.ch>
@ 2010-01-07 17:56 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2 siblings, 0 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-07 17:56 UTC (permalink / raw)
To: pgsql-performance
> If it is possible to lock this audit table exclusively (may be during
> off peak hours) I would look into
> - create new_audit_table as select col1, col2, col3 ... col9,
> 'new_col_value' from old_audit_table;
> - create all indexes
> - drop old_audit_table
> - rename new_audit_table to old_audit_table
>
> That is probably the fasted method you can do, even if you have to join
> the "new_col_value" from an extra helper-table with the correspondig id.
> Remeber, databases are born to join.
>
This has all been done before - the production team was crippled while they
waited for this and the SECOND the table was available again, they jumped on
it - even though it meant recreating the bare minimum of the indexes.
> You could also try to just update the whole table in one go, it is
> probably faster than you expect.
Possibly, but with such a large table you have no idea of the progress, you
cannot interrupt it without rolling back everything. Worse, you have
applications stalling and users wanting to know what is going on - is the OS
and the DB/MVCC trashing while it does internal maintenance? Have you
reached some sort of deadlock condition that you can't see because the
server status is not helpful with so many uncommitted pending updates?
And of course, there is the file bloat.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-07 21:05 ` marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
3 siblings, 1 reply; 30+ messages in thread
From: marcin mank @ 2010-01-07 21:05 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
> every update is a UPDATE ... WHERE id
>>= x AND id < x+10 and a commit is performed after every 1000 updates
> statement, i.e. every 10000 rows.
What is the rationale behind this? How about doing 10k rows in 1
update, and committing every time?
You could try making the condition on the ctid column, to not have to
use the index on ID, and process the rows in physical order. First
make sure that newly inserted production data has the correct value in
the new column, and add 'where new_column is null' to the conditions.
But I have never tried this, use at Your own risk.
Greetings
Marcin Mank
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
@ 2010-01-07 21:48 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-07 21:48 UTC (permalink / raw)
To: pgsql-performance
> What is the rationale behind this? How about doing 10k rows in 1
> update, and committing every time?
When we did 10K updates, the application would sometimes appear to have
frozen, and we were concerned that there was a deadlock condition because of
the number of locked rows. While we may have the patience to sit around and
wait five minutes to see if the update would continue, we couldn't risk
having other applications appear frozen if that was the case. In fact, there
is no reason for any user or application to write to the same records we are
writing to - but the audit table is read frequently. We are not explicitly
locking anything, or writing any additional code to arbitrate the lcoking
model anywhere -it's all default UPDATE and SELECT syntax.
Doing the updates in smaller chunks resolved these apparent freezes - or,
more specifically, when the application DID freeze, it didn't do it for more
than 30 seconds. In all likelyhood, this is the OS and the DB thrashing.
We have since modified the updates to process 1000 rows at a time with a
commit every 10 pages. Just this morning, though, the IS manager asked me to
stop the backfill because of the load affect on other processes.
> You could try making the condition on the ctid column, to not have to
> use the index on ID, and process the rows in physical order.
An interesting idea, if I can confirm that the performance problem is
because of the WHERE clause, not the UPDATE.
>'where new_column is null' to the conditions.
Already being done, albeit with a coalesce(val, '') = '' - it's quite
possible that this is hurting the WHERE clause; the EXPLAIN shows the table
using the pkey and then filtering on the COALESCE as one would expect.
Carlo
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 00:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
1 sibling, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-01-08 00:18 UTC (permalink / raw)
To: pgsql-performance; Carlo Stonebanks <stonec.register@sympatico.ca>
"Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
> An interesting idea, if I can confirm that the performance problem
> is because of the WHERE clause, not the UPDATE.
If you could show EXPLAIN ANALYZE output for one iteration, with
related queries and maybe more info on the environment, it would
take most of the guesswork out of things.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-08 06:02 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 14:11 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-08 06:02 UTC (permalink / raw)
To: pgsql-performance
Already done in an earlier post, Kevin - I have included it again below. As
you can see, it's pretty well wqhat you would expect, index scan plus a
filter.
One note: updates where no rows qualify run appreciably faster than the ones
that do. That is, the update itself appears to be consuming a good deal of
the processing time. This may be due to the 6 indexes.
UPDATE mdx_core.audit_impt
SET source_table = 'mdx_import.'||impt_name
WHERE audit_impt_id >= 319400001 AND audit_impt_id <= 319400010
AND coalesce(source_table, '') = ''
Index Scan using audit_impt_pkey on audit_impt (cost=0.00..92.63 rows=1
width=608) (actual time=0.081..0.244 rows=10 loops=1)
Index Cond: ((audit_impt_id >= 319400001) AND (audit_impt_id <=
319400010))
Filter: ((COALESCE(source_table, ''::character varying))::text = ''::text)
Total runtime: 372.141 ms
""Kevin Grittner"" <Kevin.Grittner@wicourts.gov> wrote in message
news:4B462563020000250002DFA3@gw.wicourts.gov...
> "Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
>
>> An interesting idea, if I can confirm that the performance problem
>> is because of the WHERE clause, not the UPDATE.
>
> If you could show EXPLAIN ANALYZE output for one iteration, with
> related queries and maybe more info on the environment, it would
> take most of the guesswork out of things.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 14:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 17:38 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-01-08 14:11 UTC (permalink / raw)
To: pgsql-performance; Carlo Stonebanks <stonec.register@sympatico.ca>
"Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
> Already done in an earlier post
Perhaps I misunderstood; I thought that post mentioned that the plan
was one statement in an iteration, and that the cache would have
been primed by a previous query checking whether there were any rows
to update. If that was the case, it might be worthwhile to look at
the entire flow of an iteration.
Also, if you ever responded with version and configuration
information, I missed it. The solution to parts of what you
describe would be different in different versions. In particular,
you might be able to solve checkpoint-related lockup issues and then
improve performance by using bigger batches. Right now I would be
guessing at what might work for you.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 14:11 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-08 17:38 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 18:08 ` Re: Massive table (500M rows) update nightmare Kenneth Marshall <ktm@rice.edu>
2010-01-08 18:12 ` Re: Massive table (500M rows) update nightmare Greg Smith <greg@2ndquadrant.com>
2010-01-08 18:12 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 3 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-08 17:38 UTC (permalink / raw)
To: pgsql-performance
>I thought that post mentioned that the plan
> was one statement in an iteration, and that the cache would have
> been primed by a previous query checking whether there were any rows
> to update. If that was the case, it might be worthwhile to look at
> the entire flow of an iteration.
This is the only SQL query in the code in question - the rest of the code
manages the looping and commit. The code was copied to PgAdminIII and values
written in for the WHERE clause. In order for me to validate that rows would
have been updated, I had to run a SELECT with the same WHERE clause in
PgAdminIII first to see how many rows would have qualified. But this was for
testing purposes only. The SELECT statement does not exist in the code. The
vast majority of the rows that will be processed will be updated as this is
a backfill to synch the old rows with the values being filled into new
columns now being inserted.
> Also, if you ever responded with version and configuration
> information, I missed it.
This is hosted on a new server the client set up so I am waiting for the
exact OS and hardware config. PG Version is PostgreSQL 8.3.6, compiled by
Visual C++ build 1400, OS appears to be Windows 2003 x64 Server.
More than anything, I am more concerned with the long-term use of the
system. This particular challenge with the 500M row update is one thing, but
I am concerned about the exceptional effort required to do this. Is it
REALLY this exceptional to want to update 500M rows of data in this day and
age? Or is the fact that we are considering dumping and restoring and
dropping indexes, etc to do all an early warning that we don't have a
solution that is scaled to the problem?
Config data follows (I am assuming commented values which I did not include
are defaulted).
Carlo
autovacuum = on
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 250
autovacuum_naptime = 1min
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 500
bgwriter_lru_maxpages = 100
checkpoint_segments = 64
checkpoint_warning = 290
datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
log_destination = 'stderr'
log_line_prefix = '%t '
logging_collector = on
maintenance_work_mem = 16MB
max_connections = 200
max_fsm_pages = 204800
max_locks_per_transaction = 128
port = 5432
shared_buffers = 500MB
vacuum_cost_delay = 100
work_mem = 512MB
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 14:11 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 17:38 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 18:08 ` Kenneth Marshall <ktm@rice.edu>
2 siblings, 0 replies; 30+ messages in thread
From: Kenneth Marshall @ 2010-01-08 18:08 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
On Fri, Jan 08, 2010 at 12:38:46PM -0500, Carlo Stonebanks wrote:
>> I thought that post mentioned that the plan
>> was one statement in an iteration, and that the cache would have
>> been primed by a previous query checking whether there were any rows
>> to update. If that was the case, it might be worthwhile to look at
>> the entire flow of an iteration.
>
> This is the only SQL query in the code in question - the rest of the code
> manages the looping and commit. The code was copied to PgAdminIII and
> values written in for the WHERE clause. In order for me to validate that
> rows would have been updated, I had to run a SELECT with the same WHERE
> clause in PgAdminIII first to see how many rows would have qualified. But
> this was for testing purposes only. The SELECT statement does not exist in
> the code. The vast majority of the rows that will be processed will be
> updated as this is a backfill to synch the old rows with the values being
> filled into new columns now being inserted.
>
>> Also, if you ever responded with version and configuration
>> information, I missed it.
>
> This is hosted on a new server the client set up so I am waiting for the
> exact OS and hardware config. PG Version is PostgreSQL 8.3.6, compiled by
> Visual C++ build 1400, OS appears to be Windows 2003 x64 Server.
>
> More than anything, I am more concerned with the long-term use of the
> system. This particular challenge with the 500M row update is one thing,
> but I am concerned about the exceptional effort required to do this. Is it
> REALLY this exceptional to want to update 500M rows of data in this day and
> age? Or is the fact that we are considering dumping and restoring and
> dropping indexes, etc to do all an early warning that we don't have a
> solution that is scaled to the problem?
>
> Config data follows (I am assuming commented values which I did not include
> are defaulted).
>
> Carlo
>
Hi Carlo,
It all boils down to resource management and constraints. For small
problems relative to the amount of system resources available, little
effort is needed to have satisfactory performance. As the problems
consume more and more of the total resource capacity, you will need
to know more and more in depth about the workings of every piece of
the system to wring the most possible performance out of the system.
Some of the discussions on this topic have covered a smattering of
details that will become increasingly important as your system scales
and determine whether or not it will scale. Many times the need for
updates on such a massive scale do point to normalization problems.
My two cents.
Cheers,
Ken
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 14:11 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 17:38 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 18:12 ` Greg Smith <greg@2ndquadrant.com>
2 siblings, 0 replies; 30+ messages in thread
From: Greg Smith @ 2010-01-08 18:12 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
Carlo Stonebanks wrote:
> This is hosted on a new server the client set up so I am waiting for
> the exact OS and hardware config. PG Version is PostgreSQL 8.3.6,
> compiled by Visual C++ build 1400, OS appears to be Windows 2003 x64
> Server.
>
> More than anything, I am more concerned with the long-term use of the
> system. This particular challenge with the 500M row update is one
> thing, but I am concerned about the exceptional effort required to do
> this. Is it REALLY this exceptional to want to update 500M rows of
> data in this day and age? Or is the fact that we are considering
> dumping and restoring and dropping indexes, etc to do all an early
> warning that we don't have a solution that is scaled to the problem?
It's certainly not common or easy to handle. If someone told me I had
to make that task well perform well and the tools at hand were anything
other than a largish UNIX-ish server with a properly designed disk
subsystem, I'd tell them it's unlikely to work well. An UPDATE is the
most intensive single operation you can do in PostgreSQL; the full
lifecycle of executing it requires:
-Creating a whole new row
-Updating all the indexes to point to the new row
-Marking the original row dead
-Pruning the original row out of the database once it's no longer
visible anywhere (VACUUM)
As a rule, if you can't fit a large chunk of the indexes involved in
shared_buffers on your system, this is probably going to have terrible
performance. And you're on a platform where that's very hard to do,
even if there's a lot of RAM around. It sounds like your system spends
all its time swapping index blocks in and out of the database buffer
cache here. That suggests there's a design problem here either with
those indexes (they're too big) or with the provisioned
hardware/software combination (needs more RAM, faster disks, or a
platform where large amounts of RAM work better).
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 14:11 ` Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 17:38 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 18:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 0 replies; 30+ messages in thread
From: Kevin Grittner @ 2010-01-08 18:12 UTC (permalink / raw)
To: pgsql-performance; Carlo Stonebanks <stonec.register@sympatico.ca>
"Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
> In order for me to validate that rows would have been updated, I
> had to run a SELECT with the same WHERE clause in PgAdminIII first
> to see how many rows would have qualified. But this was for
> testing purposes only. The SELECT statement does not exist in the
> code.
OK, I did misunderstand your earlier post. Got it now, I think.
> This is hosted on a new server the client set up so I am waiting
> for the exact OS and hardware config. PG Version is PostgreSQL
> 8.3.6, compiled by Visual C++ build 1400, OS appears to be Windows
> 2003 x64 Server.
That might provide more clues, when you get it.
> bgwriter_lru_maxpages = 100
With the large database size and the apparent checkpoint-related
delays, I would make that more aggressive. Getting dirty pages to
the OS cache reduces how much physical I/O needs to happen during
checkpoint. We use this on our large databases:
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
Boosting your checkpoint_completion_target along with or instead of
a more aggressive background writer might also help.
> max_fsm_pages = 204800
This looks suspiciously low for the size of your database. If you
do a VACUUM VERBOSE (for the database), what do the last few lines
show?
> work_mem = 512MB
That's OK only if you are sure you don't have a lot of connections
requesting that much RAM at one time, or you could drive yourself
into swapping.
I hope this helps.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 01:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 06:14 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 19:20 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
1 sibling, 2 replies; 30+ messages in thread
From: Scott Marlowe @ 2010-01-08 01:40 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
On Thu, Jan 7, 2010 at 2:48 PM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
> Doing the updates in smaller chunks resolved these apparent freezes - or,
> more specifically, when the application DID freeze, it didn't do it for more
> than 30 seconds. In all likelyhood, this is the OS and the DB thrashing.
It might well be checkpoints. Have you tried cranking up checkpoint
segments to something like 100 or more and seeing how it behaves then?
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-01-08 06:14 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 06:21 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-08 06:14 UTC (permalink / raw)
To: pgsql-performance
> It might well be checkpoints. Have you tried cranking up checkpoint
> segments to something like 100 or more and seeing how it behaves then?
No I haven't, althugh it certainly make sense - watching the process run,
you get this sense that the system occaisionally pauses to take a deep, long
breath before returning to work frantically ;D
Checkpoint_segments are currently set to 64. The DB is large and is on a
constant state of receiving single-row updates as multiple ETL and
refinement processes run continuously.
Would you expect going to 100 or more to make an appreciable difference, or
should I be more aggressive?
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 06:14 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 06:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 17:06 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 1 reply; 30+ messages in thread
From: Scott Marlowe @ 2010-01-08 06:21 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
On Thu, Jan 7, 2010 at 11:14 PM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
>> It might well be checkpoints. Have you tried cranking up checkpoint
>> segments to something like 100 or more and seeing how it behaves then?
>
> No I haven't, althugh it certainly make sense - watching the process run,
> you get this sense that the system occaisionally pauses to take a deep, long
> breath before returning to work frantically ;D
>
> Checkpoint_segments are currently set to 64. The DB is large and is on a
> constant state of receiving single-row updates as multiple ETL and
> refinement processes run continuously.
>
> Would you expect going to 100 or more to make an appreciable difference, or
> should I be more aggressive?
If you're already at 64 then not much. Probably wouldn't hurt to
crank it up more and delay the checkpoints as much as possible during
these updates. 64 segments is already 1024M. If you're changing a
lot more data than that in a single update / insert then cranking them
up more might help.
What you might need to do is to change your completion target to
something closer to 100% since it's likely that most of the updates /
inserts are not happening to the same rows over and over, but to
different rows for each one, the closer you can get to 100% completed
before the next checkpoint the better. This will cause some more IO
to happen, but will even it out more (hopefully) so that you don't get
checkpoint spikes. Look into the checkpoint logging options so you
can monitor how they're affecting system performance.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 06:14 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 06:21 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-01-08 17:06 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 17:21 ` Re: Massive table (500M rows) update nightmare Andreas Kretschmer <akretschmer@spamfence.net>
2010-01-09 08:15 ` Re: Massive table (500M rows) update nightmare Pierre Frédéric Caillaud <lists@peufeu.com>
0 siblings, 2 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-08 17:06 UTC (permalink / raw)
To: pgsql-performance
> crank it up more and delay the checkpoints as much as possible during
> these updates. 64 segments is already 1024M.
We have 425M rows, total table size is 78GB, so we can imagine a worst case
UPDATE write is less than 200 bytes * number of rows specified in the update
(is that logic correct?).
Inerestingly, the total index size is 148GB, twice that of the table, which
may be an indication of where the performance bottleneck is.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 06:14 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 06:21 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 17:06 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 17:21 ` Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 30+ messages in thread
From: Andreas Kretschmer @ 2010-01-08 17:21 UTC (permalink / raw)
To: pgsql-performance
Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> Inerestingly, the total index size is 148GB, twice that of the table,
> which may be an indication of where the performance bottleneck is.
Maybe a sign for massive index-bloat?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 06:14 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 06:21 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 17:06 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-09 08:15 ` Pierre Frédéric Caillaud <lists@peufeu.com>
1 sibling, 0 replies; 30+ messages in thread
From: Pierre Frédéric Caillaud @ 2010-01-09 08:15 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance
>> crank it up more and delay the checkpoints as much as possible during
>> these updates. 64 segments is already 1024M.
>
> We have 425M rows, total table size is 78GB, so we can imagine a worst
> case UPDATE write is less than 200 bytes * number of rows specified in
> the update (is that logic correct?).
There is also the WAL : all these updates need to be logged, which doubles
the UPDATE write throughput. Perhaps you're WAL-bound (every 16MB segment
needs fsyncing), and tuning of fsync= and wal_buffers, or a faster WAL
disk could help ? (I don't remember your config).
> Inerestingly, the total index size is 148GB, twice that of the table,
> which may be an indication of where the performance bottleneck is.
Index updates can create random I/O (suppose you have a btree on a rather
random column)...
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-01-14 19:20 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 21:19 ` Re: Massive table (500M rows) update nightmare Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-14 19:20 UTC (permalink / raw)
To: pgsql-performance
Guys, I want to thank you for all of the advice - my client has just made a
surprise announcement that he would like to set start from scratch with a
new server, so I am afraid that all of this great advice has to be seen in
the context of whatever decision is made on that. I am out there,
hat-in-hand, looking for advice under the PERFORM post: "New server to
improve performance on our large and busy DB - advice?"
Thanks again!
Carlo
"Scott Marlowe" <scott.marlowe@gmail.com> wrote in message
news:dcc563d11001071740q572cdae2re410788fe790dbf6@mail.gmail.com...
> On Thu, Jan 7, 2010 at 2:48 PM, Carlo Stonebanks
> <stonec.register@sympatico.ca> wrote:
>> Doing the updates in smaller chunks resolved these apparent freezes - or,
>> more specifically, when the application DID freeze, it didn't do it for
>> more
>> than 30 seconds. In all likelyhood, this is the OS and the DB thrashing.
>
> It might well be checkpoints. Have you tried cranking up checkpoint
> segments to something like 100 or more and seeing how it behaves then?
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-14 19:20 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-14 21:19 ` Craig James <craig_james@emolecules.com>
2010-01-14 22:05 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 1 reply; 30+ messages in thread
From: Craig James @ 2010-01-14 21:19 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
Carlo Stonebanks wrote:
> Guys, I want to thank you for all of the advice - my client has just
> made a surprise announcement that he would like to set start from
> scratch with a new server, so I am afraid that all of this great advice
> has to be seen in the context of whatever decision is made on that. I am
> out there, hat-in-hand, looking for advice under the PERFORM post: "New
> server to improve performance on our large and busy DB - advice?"
You might start this as a new topic with a relevant title, and reiterate your database requirements. Otherwise it will get submerged as just a footnote to your original question. It's really nice to be able to quickly find the new-equipment discussions.
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` Re: Massive table (500M rows) update nightmare marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 01:40 ` Re: Massive table (500M rows) update nightmare Scott Marlowe <scott.marlowe@gmail.com>
2010-01-14 19:20 ` Re: Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 21:19 ` Re: Massive table (500M rows) update nightmare Craig James <craig_james@emolecules.com>
@ 2010-01-14 22:05 ` Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 0 replies; 30+ messages in thread
From: Carlo Stonebanks @ 2010-01-14 22:05 UTC (permalink / raw)
To: pgsql-performance
My bad - I thought I had, so it has been re-posted with a (v2) disclaimer in
the title... like THAT will stop the flaming! <g>
Thanks for your patience!
"Craig James" <craig_james@emolecules.com> wrote in message
news:4B4F8A49.7010906@emolecules.com...
> Carlo Stonebanks wrote:
>> Guys, I want to thank you for all of the advice - my client has just made
>> a surprise announcement that he would like to set start from scratch with
>> a new server, so I am afraid that all of this great advice has to be seen
>> in the context of whatever decision is made on that. I am out there,
>> hat-in-hand, looking for advice under the PERFORM post: "New server to
>> improve performance on our large and busy DB - advice?"
>
> You might start this as a new topic with a relevant title, and reiterate
> your database requirements. Otherwise it will get submerged as just a
> footnote to your original question. It's really nice to be able to
> quickly find the new-equipment discussions.
>
> Craig
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-08 11:19 ` Eduardo Morras <emorras@s21sec.com>
3 siblings, 0 replies; 30+ messages in thread
From: Eduardo Morras @ 2010-01-08 11:19 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
At 08:17 07/01/2010, Carlo Stonebanks wrote:
>Our DB has an audit table which is 500M rows and growing. (FYI the objects being audited are grouped semantically, not individual field values).
>
>Recently we wanted to add a new feature and we altered the table to add a new column. We are backfilling this varchar(255) column by writing a TCL script to page through the rows (where every update is a UPDATE ... WHERE id >= x AND id < x+10 and a commit is performed after every 1000 updates statement, i.e. every 10000 rows.)
>
>We have 10 columns, six of which are indexed. Rough calculations suggest that this will take two to three weeks to complete on an 8-core CPU with more than enough memory.
>
>As a ballpark estimate - is this sort of performance for an 500M updates what one would expect of PG given the table structure (detailed below) or should I dig deeper to look for performance issues?
>
>As always, thanks!
>
>Carlo
You can dump the table with pg_dumpand get a file like this (only a few columns, not all of them). Note that the last line, has the data in TSV (Tab Separate Format) plus a LF.
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE mdx_core.audit_impt
(
audit_impt_id serial NOT NULL,
impt_session integer,
impt_version character varying(255),
}
COPY mdx_core.audit_impt (audit_impt_id, impt_session, impt_version) FROM stdin;
1 1 tateti
2 32 coll
You can add a new column hacking it, just adding the new column to the schema, the name in the copy statement and a tabulator+data at the end of the line (before the LF). Note that the table name is different from the original.
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE mdx_core.audit_impt2
(
audit_impt_id serial NOT NULL,
impt_session integer,
impt_version character varying(255),
source_table character varying(255)
}
COPY mdx_core.audit_impt2 (audit_impt_id, impt_session, impt_version, source_table) FROM stdin;
1 1 tateti tentown
1 32 coll krof
After this, add indexes, constraints as usual.
HTH
--------------------------------
Eduardo Morrás González
Dept. I+D+i e-Crime Vigilancia Digital
S21sec Labs
Tlf: +34 902 222 521
Móvil: +34 555 555 555
www.s21sec.com, blog.s21sec.com
Salvo que se indique lo contrario, esta información es CONFIDENCIAL y
contiene datos de carácter personal que han de ser tratados conforme a la
legislación vigente en materia de protección de datos. Si usted no es
destinatario original de este mensaje, le comunicamos que no está autorizado
a revisar, reenviar, distribuir, copiar o imprimir la información en él
contenida y le rogamos que proceda a borrarlo de sus sistemas.
Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO
informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen
datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta
honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula
bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko.
Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea.
Antes de imprimir este mensaje valora si verdaderamente es necesario. De
esta forma contribuimos a la preservación del Medio Ambiente.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
@ 2010-01-07 16:57 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-07 17:46 ` Re: Massive table (500M rows) update nightmare Kevin Kempter <kevink@consistentstate.com>
0 siblings, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-01-07 16:57 UTC (permalink / raw)
To: leo.mannhart@beecom.ch; ldylag@gmail.com; +Cc: pgsql-performance
Ludwik Dylag <ldylag@gmail.com> wrote:
> I would suggest:
> 1. turn off autovacuum
> 1a. ewentually tune db for better performace for this kind of
> operation (cant not help here)
> 2. restart database
> 3. drop all indexes
> 4. update
> 5. vacuum full table
> 6. create indexes
> 7. turn on autovacuum
I've only ever attempted something like that with a few tens of
millions of rows. I gave up on waiting for the VACUUM FULL step
after a few days.
I some scheduled down time is acceptable (with "some" kind of hard
to estimate accurately) the best bet would be to add the column with
the USING clause to fill in the value. (I think that would cause a
table rewrite; if not, then add something to the ALTER TABLE which
would.) My impression is that the OP would rather stretch out the
implementation than to suffer down time, which can certainly be a
valid call.
If that is the goal, then the real question is whether there's a way
to tune the incremental updates to speed that phase. Carlo, what
version of PostgreSQL is this? Can you show us the results of an
EXPLAIN ANALYZE for the run of one iteration of the UPDATE?
Information on the OS, hardware, PostgreSQL build configuration, and
the contents of postgresql.conf (excluding all comments) could help
us spot possible techniques to speed this up.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Massive table (500M rows) update nightmare
2010-01-07 16:57 Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-07 17:46 ` Kevin Kempter <kevink@consistentstate.com>
0 siblings, 0 replies; 30+ messages in thread
From: Kevin Kempter @ 2010-01-07 17:46 UTC (permalink / raw)
To: pgsql-performance
On Thursday 07 January 2010 09:57:59 Kevin Grittner wrote:
> Ludwik Dylag <ldylag@gmail.com> wrote:
> > I would suggest:
> > 1. turn off autovacuum
> > 1a. ewentually tune db for better performace for this kind of
> > operation (cant not help here)
> > 2. restart database
> > 3. drop all indexes
> > 4. update
> > 5. vacuum full table
> > 6. create indexes
> > 7. turn on autovacuum
>
> I've only ever attempted something like that with a few tens of
> millions of rows. I gave up on waiting for the VACUUM FULL step
> after a few days.
>
> I some scheduled down time is acceptable (with "some" kind of hard
> to estimate accurately) the best bet would be to add the column with
> the USING clause to fill in the value. (I think that would cause a
> table rewrite; if not, then add something to the ALTER TABLE which
> would.) My impression is that the OP would rather stretch out the
> implementation than to suffer down time, which can certainly be a
> valid call.
>
> If that is the goal, then the real question is whether there's a way
> to tune the incremental updates to speed that phase. Carlo, what
> version of PostgreSQL is this? Can you show us the results of an
> EXPLAIN ANALYZE for the run of one iteration of the UPDATE?
> Information on the OS, hardware, PostgreSQL build configuration, and
> the contents of postgresql.conf (excluding all comments) could help
> us spot possible techniques to speed this up.
>
> -Kevin
>
If you can come up with an effective method of tracking updates/deletes/inserts
such as a trigger that writes the PK to a separate table upon any inserts,
updates or deletes to the table you could do something like this:
1) create new table (no indexes) with the structure you want the table to have
at the end of the process (i.e. the post-altered state) [new_tab]
2) create the insert,update,delete triggers mentioned above on the existing
table [curr_tab] and write all the PK id's that change into a 3rd table
[changed_keys]
3) kick off a process that simply does a select from curr_tab into new_tab and
populates/back-fills the new column as part of the query
4) let it run as long as it takes
5) once it's complete do this:
Create the all the indexes on the new_tab
BEGIN;
LOCK TABLE curr_tab;
DELETE from new_tab
where pk_id in (select distinct pk_id from changed_keys);
INSERT into new_tab
select * from curr_tab
where curr_tab.pk_id in (select distinct pk_id from changed_keys);
ALTER TABLE curr_tab RENAME to old_tab;
ALTER TABLE new_tab RENAME to curr_tab;
COMMIT;
Also you might want to consider partitioning this table in the process...
Once you're confident you no longer need the old table [old_tab] you can drop
it
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2010-01-14 22:05 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-07 07:17 Massive table (500M rows) update nightmare Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 07:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-07 17:49 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 13:14 ` Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 15:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-07 15:47 ` Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-07 16:38 ` Ludwik Dylag <ldylag@gmail.com>
2010-01-07 17:56 ` Greg Sabino Mullane <greg@turnstep.com>
2010-01-07 17:56 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-07 21:05 ` marcin mank <marcin.mank@gmail.com>
2010-01-07 21:48 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 00:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 06:02 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 14:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 17:38 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 18:08 ` Kenneth Marshall <ktm@rice.edu>
2010-01-08 18:12 ` Greg Smith <greg@2ndquadrant.com>
2010-01-08 18:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-08 01:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 06:14 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 06:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-08 17:06 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 17:21 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-01-09 08:15 ` Pierre Frédéric Caillaud <lists@peufeu.com>
2010-01-14 19:20 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 21:19 ` Craig James <craig_james@emolecules.com>
2010-01-14 22:05 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-08 11:19 ` Eduardo Morras <emorras@s21sec.com>
2010-01-07 16:57 Re: Massive table (500M rows) update nightmare Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-07 17:46 ` Kevin Kempter <kevink@consistentstate.com>
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