pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedadd column with default value is very slow
8+ messages / 4 participants
[nested] [flat]
* add column with default value is very slow
@ 2012-09-11 13:20 AI Rumman <rummandba@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: AI Rumman @ 2012-09-11 13:20 UTC (permalink / raw)
To: pgsql-performance
I have a table as follows:
\d entity
Table "public.entity"
Column | Type | Modifiers
--------------+-----------------------------+--------------------
crmid | integer | not null
smcreatorid | integer | not null default 0
smownerid | integer | not null default 0
modifiedby | integer | not null default 0
setype | character varying(30) | not null
description | text |
createdtime | timestamp without time zone | not null
modifiedtime | timestamp without time zone | not null
viewedtime | timestamp without time zone |
status | character varying(50) |
version | integer | not null default 0
presence | integer | default 1
deleted | integer | not null default 0
Indexes:
"entity_pkey" PRIMARY KEY, btree (crmid)
"entity_createdtime_idx" btree (createdtime)
"entity_modifiedby_idx" btree (modifiedby)
"entity_modifiedtime_idx" btree (modifiedtime)
"entity_setype_idx" btree (setype) WHERE deleted = 0
"entity_smcreatorid_idx" btree (smcreatorid)
"entity_smownerid_idx" btree (smownerid)
"ftx_en_entity_description" gin (to_tsvector('vcrm_en'::regconfig,
for_fts(description)))
"entity_deleted_idx" btree (deleted)
Referenced by:
TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid)
REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "servicecontracts" CONSTRAINT "fk_1_servicecontracts" FOREIGN KEY
(servicecontractsid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "vantage_cc2entity" CONSTRAINT "fk_vantage_cc2entity_entity"
FOREIGN KEY (crm_id) REFERENCES entity(crmid) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE "vantage_emails_optout_history" CONSTRAINT
"fk_vantage_emails_optout_history_crmid" FOREIGN KEY (crmid) REFERENCES
entity(crmid) ON DELETE CASCADE
TABLE "vantage_emails_optout_history" CONSTRAINT
"fk_vantage_emails_optout_history_emailid" FOREIGN KEY (emailid) REFERENCES
entity(crmid) ON DELETE CASCADE
I execued the query:
ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
The db is stuck. The enity table has 2064740 records;
Watching locks:
select
pg_stat_activity.datname,pg_class.relname,pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,10),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;
datname | relname | mode
| granted | usename | substr | query_start |
age | procpid
-------------------+-------------------------------------+---------------------+---------+----------+------------+-------------------------------+-----------------+---------
db_test | entity_modifiedtime_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | | ExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | entity_modifiedby_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity_createdtime_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity | ShareLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | entity | AccessExclusiveLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | | ExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | entity_pkey | AccessExclusiveLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | | ShareLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | ftx_en_entity_description | AccessExclusiveLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | | AccessShareLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | entity_smcreatorid_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity_smownerid_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity_setype_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
Any idea for the db stuck?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 13:41 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Albe Laurenz @ 2012-09-11 13:41 UTC (permalink / raw)
To: AI Rumman *EXTERN* <rummandba@gmail.com>; pgsql-performance
AI Rumman wrote:
> I execued the query:
> ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
>
> The db is stuck. The enity table has 2064740 records;
>
> Watching locks:
[all locks are granted]
> Any idea for the db stuck?
To add the column, PostgreSQL has to modify all rows in the table.
But then 2064740 records is not very much, so it shouldn't take forever.
Do you see processor or I/O activity?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 13:44 hubert depesz lubaczewski <depesz@depesz.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2012-09-11 13:44 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
On Tue, Sep 11, 2012 at 07:20:28PM +0600, AI Rumman wrote:
> I have a table as follows:
> I execued the query:
> ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
>
> The db is stuck. The enity table has 2064740 records;
such alter table has to rewrite whole table. So it will take a while
> Watching locks:
output of this was perfectly unreadable, because your email client
wrapped lines at some random places.
In future - please put such dumps on some paste site, or just attach it
to mail, and not copy/paste them to body of message.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 13:55 AI Rumman <rummandba@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 8+ messages in thread
From: AI Rumman @ 2012-09-11 13:55 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-performance
I added the excel file for locks data.
I was surprised to see that while I was updating a single column value for
all records in a tables, all indexes are locked by the server.
On Tue, Sep 11, 2012 at 7:44 PM, hubert depesz lubaczewski <
depesz@depesz.com> wrote:
> On Tue, Sep 11, 2012 at 07:20:28PM +0600, AI Rumman wrote:
> > I have a table as follows:
> > I execued the query:
> > ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
> >
> > The db is stuck. The enity table has 2064740 records;
>
> such alter table has to rewrite whole table. So it will take a while
>
> > Watching locks:
>
> output of this was perfectly unreadable, because your email client
> wrapped lines at some random places.
>
> In future - please put such dumps on some paste site, or just attach it
> to mail, and not copy/paste them to body of message.
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact
> with it.
>
> http://depesz.com/
>
Attachments:
pglocks.xls (application/vnd.ms-excel, 21.5K) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 13:59 hubert depesz lubaczewski <depesz@depesz.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2012-09-11 13:59 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote:
> I added the excel file for locks data.
well, it worked, but why didn't you just make it text file, in notepad or
something like this?
> I was surprised to see that while I was updating a single column value for
> all records in a tables, all indexes are locked by the server.
alter table is not locked (At least looking at the pg_locks data you
showed).
this means - it just takes long time.
Please do:
select pg_total_relation_size('entity');
to see how much data it has to rewrite.
for future - just don't do alter table, with default, and not null.
doing it via add column; set default; batch-backfill data, set not null
will take longer but will be done with much shorter locks.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 14:04 AI Rumman <rummandba@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 8+ messages in thread
From: AI Rumman @ 2012-09-11 14:04 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-performance
Table size is 1186 MB.
I split the command in three steps as you said, but the result same during
the update operation.
One more thing, I have just restored the db from dump and analyzed it and
I am using Postgresql 9.1 with 3 GB Ram with dual core machine.
On Tue, Sep 11, 2012 at 7:59 PM, hubert depesz lubaczewski <
depesz@depesz.com> wrote:
> On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote:
> > I added the excel file for locks data.
>
> well, it worked, but why didn't you just make it text file, in notepad or
> something like this?
>
> > I was surprised to see that while I was updating a single column value
> for
> > all records in a tables, all indexes are locked by the server.
>
> alter table is not locked (At least looking at the pg_locks data you
> showed).
>
> this means - it just takes long time.
>
> Please do:
> select pg_total_relation_size('entity');
> to see how much data it has to rewrite.
>
> for future - just don't do alter table, with default, and not null.
> doing it via add column; set default; batch-backfill data, set not null
> will take longer but will be done with much shorter locks.
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact
> with it.
>
> http://depesz.com/
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 14:05 hubert depesz lubaczewski <depesz@depesz.com>
parent: AI Rumman <rummandba@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: hubert depesz lubaczewski @ 2012-09-11 14:05 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
On Tue, Sep 11, 2012 at 08:04:06PM +0600, AI Rumman wrote:
> Table size is 1186 MB.
if it takes long, it just means that your IO is slow.
> I split the command in three steps as you said, but the result same during
> the update operation.
three? I was showing four steps, and one of them is usually consisting
hundreds, if not thousands, of queries.
> One more thing, I have just restored the db from dump and analyzed it and
> I am using Postgresql 9.1 with 3 GB Ram with dual core machine.
so it looks like your IO channel is slow.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: add column with default value is very slow
@ 2012-09-11 14:07 Andrew Dunstan <andrew@dunslane.net>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Andrew Dunstan @ 2012-09-11 14:07 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: depesz@depesz.com; pgsql-performance
On 09/11/2012 09:55 AM, AI Rumman wrote:
> I added the excel file for locks data.
> I was surprised to see that while I was updating a single column value
> for all records in a tables, all indexes are locked by the server.
Any ALTER TABLE command locks the whole table in ACCESS EXCLUSIVE mode,
indexes included. See the description of ACCESS EXCLUSIVE lock at
<http://www.postgresql.org/docs/current/static/explicit-locking.html;
cheers
andrew
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-09-11 14:07 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-11 13:20 add column with default value is very slow AI Rumman <rummandba@gmail.com>
2012-09-11 13:41 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-11 13:44 ` hubert depesz lubaczewski <depesz@depesz.com>
2012-09-11 13:55 ` AI Rumman <rummandba@gmail.com>
2012-09-11 13:59 ` hubert depesz lubaczewski <depesz@depesz.com>
2012-09-11 14:04 ` AI Rumman <rummandba@gmail.com>
2012-09-11 14:05 ` hubert depesz lubaczewski <depesz@depesz.com>
2012-09-11 14:07 ` Andrew Dunstan <andrew@dunslane.net>
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