Update problem on large table - Mailing list pgsql-performance
| From | felix |
|---|---|
| Subject | Update problem on large table |
| Date | |
| Msg-id | AANLkTinhH2qtjANkwiNcvr2CRa0H8UZYmQHVqkHX_Xqs@mail.gmail.com Whole thread Raw |
| Responses |
Re: Update problem on large table
|
| List | pgsql-performance |
Hello,
I have a very large table that I'm not too fond of. I'm revising the design now.
Up until now its been insert only, storing tracking codes from incoming webtraffic.
It has 8m rows
It appears to insert fine, but simple updates using psql are hanging.
update ONLY traffic_tracking2010 set src_content_type_id = 90 where id = 90322;
I am also now trying to remove the constraints, this also hangs.
alter table traffic_tracking2010 drop constraint traffic_tracking2010_src_content_type_id_fkey;
thanks in advance for any advice.
Table "public.traffic_tracking2010"
Column | Type | Modifiers
---------------------+--------------------------+-------------------------------------------------------------------
id | integer | not null default nextval('traffic_tracking2010_id_seq'::regclass)
action_time | timestamp with time zone | not null
user_id | integer |
content_type_id | integer |
object_id | integer |
action_type | smallint | not null
src_type | smallint |
src_content_type_id | integer |
src_object_id | integer |
http_referrer | character varying(100) |
search_term | character varying(50) |
remote_addr | inet | not null
Indexes:
"traffic_tracking2010_pkey" PRIMARY KEY, btree (id)
"traffic_tracking2010_content_type_id" btree (content_type_id)
"traffic_tracking2010_src_content_type_id" btree (src_content_type_id)
"traffic_tracking2010_user_id" btree (user_id)
Foreign-key constraints:
"traffic_tracking2010_content_type_id_fkey" FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED
"traffic_tracking2010_src_content_type_id_fkey" FOREIGN KEY (src_content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED
"traffic_tracking2010_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
This is generated by Django's ORM.
The hang may be do having other clients connected, though I have tried doing the update when I know all tracking inserts are stopped.
But the other client (the webapp) is still connected.
based on this:
ns=> ANALYZE traffic_tracking2010;
ANALYZE
ns=> SELECT relpages, reltuples FROM pg_class WHERE relname = 'traffic_tracking2010';
relpages | reltuples
----------+-------------
99037 | 8.38355e+06
and I did vacuum it
vacuum verbose traffic_tracking2010;
INFO: vacuuming "public.traffic_tracking2010"
INFO: scanned index "traffic_tracking2010_pkey" to remove 1057 row versions
DETAIL: CPU 0.09s/0.37u sec elapsed 10.70 sec.
INFO: scanned index "traffic_tracking2010_user_id" to remove 1057 row versions
DETAIL: CPU 0.12s/0.30u sec elapsed 13.53 sec.
INFO: scanned index "traffic_tracking2010_content_type_id" to remove 1057 row versions
DETAIL: CPU 0.11s/0.28u sec elapsed 13.99 sec.
INFO: scanned index "traffic_tracking2010_src_content_type_id" to remove 1057 row versions
DETAIL: CPU 0.09s/0.26u sec elapsed 15.57 sec.
INFO: "traffic_tracking2010": removed 1057 row versions in 535 pages
DETAIL: CPU 0.01s/0.02u sec elapsed 2.83 sec.
INFO: index "traffic_tracking2010_pkey" now contains 8315147 row versions in 22787 pages
DETAIL: 1057 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "traffic_tracking2010_user_id" now contains 8315147 row versions in 29006 pages
DETAIL: 1057 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "traffic_tracking2010_content_type_id" now contains 8315147 row versions in 28980 pages
DETAIL: 1057 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "traffic_tracking2010_src_content_type_id" now contains 8315147 row versions in 28978 pages
DETAIL: 1057 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "traffic_tracking2010": found 336 removable, 8315147 nonremovable row versions in 99035 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
25953 pages contain useful free space.
0 pages are entirely empty.
CPU 0.78s/1.49u sec elapsed 100.43 sec.
INFO: vacuuming "pg_toast.pg_toast_165961"
INFO: index "pg_toast_165961_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: "pg_toast_165961": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
pgsql-performance by date: