[PERFORM] App very unresponsive while performing simple update
Hi,Is Postgres supposed to be able to handle concurrent requests while doing large updates?This morning I was executing the following simple update statement that would affect 220,000 rows in my product table:update product set is_hungry = 'true' where date_modified > current_date - 10;But the application that accesses the product table for reading became very unresponsive while the update was happening.Is it just a matter of slow I/O? The CPU usage seemed very low (less than 5%) and iostat showed less than 1 MB / sec throughput.I was doing the update in psql.Are there any settings that I could tweak that would help with this sort of thing?Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com
Re: [PERFORM] App very unresponsive while performing simple update
Further to my issue, the update never did finish. I received the following message in psql:ssprod=# update product set is_hungry = 'true' where date_modified > current_date - 10;ERROR: deadlock detectedDETAIL: Process 18778 waits for ShareLock on transaction 711698780; blocked by process 15784.Process 15784 waits for ShareLock on transaction 711697098; blocked by process 18778.This is the second time I've tried to run this query without success.Would changing the isolation level to serializable in my psql session help with this?Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On May 28, 2006, at 3:37 AM, Brendan Duddridge wrote:Hi,Is Postgres supposed to be able to handle concurrent requests while doing large updates?This morning I was executing the following simple update statement that would affect 220,000 rows in my product table:update product set is_hungry = 'true' where date_modified > current_date - 10;But the application that accesses the product table for reading became very unresponsive while the update was happening.Is it just a matter of slow I/O? The CPU usage seemed very low (less than 5%) and iostat showed less than 1 MB / sec throughput.I was doing the update in psql.Are there any settings that I could tweak that would help with this sort of thing?Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com
Re: [PERFORM] App very unresponsive while performing simple update
Brendan Duddridge <[EMAIL PROTECTED]> writes: > Further to my issue, the update never did finish. I received the following > message in psql: > > ssprod=# update product set is_hungry = 'true' where date_modified > > current_date - 10; > ERROR: deadlock detected > DETAIL: Process 18778 waits for ShareLock on transaction 711698780; blocked > by process 15784. > Process 15784 waits for ShareLock on transaction 711697098; blocked by > process > 18778. What queries are those two processes executing? And what foreign keys do you have on the product table or elsewhere referring to the product table? And what indexes do you have on those columns? I think this indicates you have foreign keys causing the deadlock. One process is waiting until an update elsewhere finishes before modifying a record that other update refers to via a foreign key. But that other process is waiting similarly for the first one. Do you have any foreign keys in other tables referring to the product table? Do you have indexes on those other tables? The update needs to check those other tables to make sure there are no references to the records you're updating. If there's no index it has to do a sequential scan. To get a deadlock I think you would need another update running somewhere though. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] How can I make this query faster (resend)
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
To: "Cstdenis" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, May 22, 2006 8:20 AM
Subject: Re: [PERFORM] How can I make this query faster (resend)
> On Fri, May 19, 2006 at 03:56:49PM -0700, Cstdenis wrote:
> > (Its been a hour and I dont see my message on the list so I'm sending it
again. I've moved the queries and analyze out of the email incase it was
rejected because too long)
> >
> > query: http://pastebin.ca/57218
> >
> > In the pictures table all the ratings have a shared index
> >
> > CREATE INDEX idx_rating ON pictures USING btree (rating_nudity,
rating_violence, rating_sex, rating_racism, rating_spoilers, rating_yaoi,
rating_yuri, rating_profanity);
> >
> > and approved and date_submitted and user_id also have their own btree
indexes.
> >
> > In the picture_categories table pid and cat_id have their own btree
indices plus one together.
> >
> > Full table definition: http://pastebin.ca/57219
> >
> > the cat_id and rating values vary from query to query. The one listed
above took 54 seconds in a test run just now. Here is explain analyze:
http://pastebin.ca/57220
>
> pictures is the interesting table here. It looks like the planner would
> do better to choose something other than a nested loop on it. Try
> running EXPLAIN ANALYZE on the query with enable_nestloop=off and see
> what you get (you'll need to compare it to what you get with
> enable_nestloop on to see what the change is).
With enable_nestloop=off the same query as is explained further down in this
email took much longer 63 seconds insted of 6. It decided to do sequencial
scans on pictures and users with nested loop disabled.
Merge Join (cost=146329.63..146963.96 rows=231 width=66) (actual
time=61610.538..62749.176 rows=1305 loops=1)
Merge Cond: ("outer".user_id = "inner".user_id)
-> Sort (cost=123828.88..123829.46 rows=231 width=47) (actual
time=60445.367..60451.176 rows=1305 loops=1)
Sort Key: pictures.user_id
-> Hash Join (cost=634.36..123819.81 rows=231 width=47) (actual
time=128.088..60423.623 rows=1305 loops=1)
Hash Cond: ("outer".pid = "inner".pid)
-> Seq Scan on pictures (cost=0.00..121670.43 rows=302543
width=47) (actual time=0.210..58795.925 rows=291318 loops=1)
-> Hash (cost=633.78..633.78 rows=231 width=4) (actual
time=38.443..38.443 rows=1305 loops=1)
-> Bitmap Heap Scan on picture_categories
(cost=2.81..633.78 rows=231 width=4) (actual time=4.753..32.259 rows=1305
loops=1)
Recheck Cond: (cat_id = 182)
-> Bitmap Index Scan on
idx_picture_categories_cat_id (cost=0.00..2.81 rows=231 width=0) (actual
time=4.398..4.398 rows=1305 loops=1)
Index Cond: (cat_id = 182)
-> Sort (cost=22500.74..22816.79 rows=126418 width=23) (actual
time=1163.788..1505.104 rows=52214 loops=1)
Sort Key: users.user_id
-> Seq Scan on users (cost=0.00..11788.18 rows=126418 width=23)
(actual time=0.017..692.992 rows=54605 loops=1)
Total runtime: 62776.720 ms
> > Both pictures and picture categories have about 287,000 rows
> >
> > This query needs to run in under about a second or it kills my site by
clogging apache slots (apache maxes out at 256 and I can have several
hundred people on my site at a time). How can I make it run faster?
> >
> >
> > Server is a dual xeon with a gig of ram dedicated mostly to postgresql.
> > Here is the changed lines in my postgresql.conf:
http://pastebin.ca/57222
>
> I suspect the low work_mem may be why it's using a nested loop. In
> addition to the test above, it would be interesting to see what happens
> to the plan if you set work_mem to 1.
I moved to a more powerful server (2gb ram and mirrored scsi HDs) and upped
the work mem to 10mb. Its much faster now, however its still doing a nested
loop. (see also my reply to Markus Schaber)
Nested Loop (cost=2.81..3398.76 rows=231 width=66) (actual
time=14.946..5797.701 rows=1305 loops=1)
-> Nested Loop (cost=2.81..2022.71 rows=231 width=47) (actual
time=14.551..5181.042 rows=1305 loops=1)
-> Bitmap Heap Scan on picture_categories (cost=2.81..633.78
rows=231 width=4) (actual time=9.966..140.606 rows=1305 loops=1)
Recheck Cond: (cat_id = 182)
-> Bitmap Index Scan on idx_picture_categories_cat_id
(cost=0.00..2.81 rows=231 width=0) (actual time=9.720..9.720 rows=1305
loops=1)
Index Cond: (cat_id = 182)
-> Index Scan using pictures_pkey on pictures (cost=0.00..6.00
rows=1 width=47) (actual time=3.802..3.820 rows=1 loops=1305)
Index Cond: (pictures.pid = "outer".pid)
-> Index Scan using users_pkey on users (cost=0.00..5.94 rows=1
width=23) (actual time=0.095..0.100 rows=1 loops=1305)
Index Cond: ("outer".user_id = users.user_id)
Total runtime: 5812.238 ms
> To be honest, you're pushing things expecting a machine with only 1G to
> serve 300 a
Re: [PERFORM] App very unresponsive while performing simple update
Greg Stark <[EMAIL PROTECTED]> writes: > What queries are those two processes executing? And what foreign keys do you > have on the product table or elsewhere referring to the product table? And > what indexes do you have on those columns? And what PG version is this? Alvaro fixed the foreign-keys-take-exclusive-locks problem in 8.1 ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] App very unresponsive while performing simple update
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > What queries are those two processes executing? And what foreign keys do you > > have on the product table or elsewhere referring to the product table? And > > what indexes do you have on those columns? > > And what PG version is this? Alvaro fixed the > foreign-keys-take-exclusive-locks problem in 8.1 ... Except I don't think this is taking an exclusive lock at all. The original post had the deadlock detection fire on a SharedLock. I think the other process is also an update and is holding an exclusive lock while also trying to acquire a SharedLock for a foreign key column. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] App very unresponsive while performing simple update
Hi, Thanks for your replies. We are using PostgreSQL 8.1.3 on OS X Server. We do have foreign keys on other tables that reference the product table. Also, there will be updates going on at the same time as this update. When anyone clicks on a product details link, we issue an update statement to increment the click_count on the product. e.g. update product set click_count = click_count + 1; There are 1.2 million rows in this table and my update will affect 200,000 of them. We do have indexes on all foreign keys that reference the product table. Here's what our product table looks like: Table "public.product" Column|Type | Modifiers --+-+--- click_count | integer | date_created | timestamp without time zone | not null date_modified| timestamp without time zone | date_of_last_keyphrase_match | timestamp without time zone | ean | character varying(32) | gtin | character varying(32) | home_category_id | integer | is_active| character varying(5)| is_featured | character varying(5)| is_hungry| character varying(5)| isbn | character varying(32) | manufacturer_id | integer | media_for_clipboard_id | integer | media_for_detail_id | integer | media_for_thumbnail_id | integer | mpn | character varying(512) | product_id | integer | not null status_code | character varying(32) | unsps_code | bigint | upc | character varying(32) | riding_id| integer | name_en | character varying(512) | name_fr | character varying(512) | short_description_en | character varying(2048) | short_description_fr | character varying(2048) | long_description_en | text| long_description_fr | text| Indexes: "product_pk" PRIMARY KEY, btree (product_id) "product__active_status_idx" btree (is_active, status_code) "product__additional_0__idx" btree (riding_id) "product__date_created_idx" btree (date_created) "product__date_modified_idx" btree (date_modified) "product__date_of_last_keyphrase_match_idx" btree (date_of_last_keyphrase_match) "product__home_category_id_fk_idx" btree (home_category_id) "product__hungry_idx" btree (is_hungry) "product__lower_name_en_idx" btree (lower(name_en::text)) "product__lower_name_fr_idx" btree (lower(name_fr::text)) "product__manufacturer_id_fk_idx" btree (manufacturer_id) "product__manufacturer_id_mpn_idx" btree (manufacturer_id, mpn) "product__media_for_clipboard_id_fk_idx" btree (media_for_clipboard_id) "product__media_for_detail_id_fk_idx" btree (media_for_detail_id) "product__media_for_thumbnail_id_fk_idx" btree (media_for_thumbnail_id) "product__upc_idx" btree (upc) "product_additional_2__idx" btree (is_active, status_code) WHERE is_active::text = 'true'::text AND status_code::text = 'complete'::text Foreign-key constraints: "product_homecategory_fk" FOREIGN KEY (home_category_id) REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED "product_manufacturer_fk" FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(manufacturer_id) DEFERRABLE INITIALLY DEFERRED "product_mediaforclipboard_fk" FOREIGN KEY (media_for_clipboard_id) REFERENCES media(media_id) DEFERRABLE INITIALLY DEFERRED "product_mediafordetail_fk" FOREIGN KEY (media_for_detail_id) REFERENCES media(media_id) DEFERRABLE INITIALLY DEFERRED "product_mediaforthumbnail_fk" FOREIGN KEY (media_for_thumbnail_id) REFERENCES media(media_id) DEFERRABLE INITIALLY DEFERRED Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On May 28, 2006, at 10:04 AM, Tom Lane wrote: Greg Stark <[EMAIL PROTECTED]> writes: What queries are those two processes executing? And what foreign keys do you have on the product table or elsewhere referring to the product table? And what indexes do you have on those columns? And what PG version is this? Alvaro fixed the foreign-keys-take-exclusive-locks problem in 8.1 ... regards, tom lane ---(end of br
Re: [PERFORM] App very unresponsive while performing simple update
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> And what PG version is this? Alvaro fixed the >> foreign-keys-take-exclusive-locks problem in 8.1 ... > Except I don't think this is taking an exclusive lock at all. The original > post had the deadlock detection fire on a SharedLock. Yeah, but it was a ShareLock on a transaction ID, which is the trace of something doing XactLockTableWait, which is only done if we're blocking on a locked or updated-but-uncommitted row. Since Brendan says he's using 8.1, the FK theory is out, and I think what this probably is is a garden-variety deadlock on tuple updates, ie, two concurrent transactions tried to update the same tuples in different orders. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] App very unresponsive while performing simple update
Brendan Duddridge <[EMAIL PROTECTED]> writes: > We do have foreign keys on other tables that reference the product table. > Also, there will be updates going on at the same time as this update. When > anyone clicks on a product details link, we issue an update statement to > increment the click_count on the product. e.g. update product set click_count > = click_count + 1; You should realize this will produce a lot of garbage records and mean you'll have to be running vacuum very frequently. You might consider instead of updating the main table inserting into a separate clickstream table. That trades off not getting instantaneous live totals with isolating the maintenance headache in a single place. That table will grow large but you can prune it at your leisure without impacting query performance on your main tables. > There are 1.2 million rows in this table and my update will affect 200,000 > of them. > > We do have indexes on all foreign keys that reference the product table. Well I suppose you had an update running concurrently against one of CATEGORY, MANUFACTURER, or MEDIA. Do any of those tables have a reference back to the product table? Is it possible to have a record with a reference back to the same record that refers to it? I think you're seeing the problem because these foreign keys are all initially deferred. That means you can update both tables and then can't commit either one because it needs to obtain a shared lock on the other record which is already locked for the update. I'm not certain that making them not deferred would actually eliminate the deadlock. It might just make it less likely. The deferred foreign key checks may also be related to the performance complaints. In my experience they're quite fast but I wonder what happens when you do a large batch update and then need to perform a whole slew of deferred foreign key checks. More likely you were blocking on some lock. Until that other query holding that lock tries to commit Postgres won't actually detect a deadlock, it'll just sit waiting until the lock becomes available. Also, you have a lot of indexes here. That alone will make updates pretty slow. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] App very unresponsive while performing simple update
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > > Except I don't think this is taking an exclusive lock at all. The original > > post had the deadlock detection fire on a SharedLock. > > Yeah, but it was a ShareLock on a transaction ID, which is the trace > of something doing XactLockTableWait, which is only done if we're > blocking on a locked or updated-but-uncommitted row. Oops, didn't see this before I sent my last message. Brendan, in case it's not clear, in case of a conflict between my explanation and Tom's listen to Tom. :) -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
