Re: [PERFORM] Spurious failure to obtain row lock possible in PG 9.1?
Hi there, > henk de wit wrote: > > I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS > > I'm "pretty" sure there's really no other process that has the lock, > as I'm the only one on a test DB. > > If I execute the query immediately again, it does succeed in obtaining > the lock. I can however not > > reproduce this via e.g. PGAdmin. > > > There must be at least a second database connection that holds > locks on the objects you need. > Look in pg_stat_activity if you see other connections. > > It is probably a race condition of some kind. It indeed most likely was, but not exactly the kind of race condition I had in mind. I was (wrongfully) thinking that a "... for update nowait" lock, would only not wait for other "... for update nowait" locks. However, as it turned out it also immediately returns with the error code if there's a kind of transitive "normal" lock related to a plain insert or update elsewhere (plain = without a 'for update' clause). As I was the only one on the Database, I was pretty sure there was no other "... for update nowait" query executing, but there *was* another parallel insert of a row that had a foreign key to the entry in the table I was trying to lock explicitly. That insert caused the lock in the other query to immediately fail. To me this was quite unexpected, but that's probably just me. What I thus actually need from PG is a "nowaitforupdate" or such thing; e.g. if there's a normal insert going on with a FK that happens to reference that row, it's okay to wait. The only thing I don't want to wait for is explicit locks that are hold by application code. I've worked around the issue by creating a separate table called "customer_lock" without any foreign keys from it or to it. It's used exclusively for obtaining those explicit locks. It violates the relational model a bit, but it does work. Thanks for your help!
[PERFORM] deadlock_timeout affect on performance
Hi all, I have a question about the deadlock_timeout in regards to performance. Right now we have this timeout set at its default of 1s. My understanding of it is that this means that every 1 second the server will check for deadlocks. What I am wondering is how much of a performance improvement we would expect to get if this was raised to 30 seconds? Is it negligible or could it be a substantial performance improvement on a busy system? We very rarely have deadlocks and waiting 30 seconds to discover one doesn't seem too bad. Thank you.
Re: [PERFORM] hardware advice
> > From: M. D. >To: pgsql-performance@postgresql.org >Sent: Friday, 28 September 2012, 18:33 >Subject: Re: [PERFORM] hardware advice > >On 09/28/2012 09:57 AM, David Boreham wrote: >> On 9/28/2012 9:46 AM, Craig James wrote: >>> Your best warranty would be to have the confidence to do your own >>> repairs, and to have the parts on hand. I'd seriously consider >>> putting your own system together. Maybe go to a few sites with >>> pre-configured machines and see what parts they use. Order those, >>> screw the thing together yourself, and put a spare of each critical >>> part on your shelf. >>> >> This is what I did for years, but after taking my old parts collection to >> the landfill a few times, realized I may as well just buy N+1 machines and >> keep zero spares on the shelf. That way I get a spare machine available for >> use immediately, and I know the parts are working (parts on the shelf may be >> defective). If something breaks, I use the spare machine until the >> replacement parts arrive. >> >> Note in addition that a warranty can be extremely useful in certain >> organizations as a vehicle of blame avoidance (this may be its primary >> purpose in fact). If I buy a bunch of machines that turn out to have buggy >> NICs, well that's my fault and I can kick myself since I own the company, >> stay up late into the night reading kernel code, and buy new NICs. If I have >> an evil Dilbertian boss, then well...I'd be seriously thinking about buying >> Dell boxes in order to blame Dell rather than myself, and be able to say >> "everything is warrantied" if badness goes down. Just saying... >> >I'm kinda in the latter shoes. Dell is the only thing that is trusted in my >organisation. If I would build my own, I would be fully blamed for anything >going wrong in the next 3 years. Thanks everyone for your input. Now my final >choice will be if my budget allows for the latest and fastest, else I'm going >for the x5690. I don't have hundreds of users, so I think the x5690 should do >a pretty good job handling the load. > > Having plenty experience with Dell I'd urge you reconsider. All the Dell servers we've had have arrived hideously misconfigured, and tech support gets you nowhere. Once we've rejigged the hardware ourselves, maybe replacing a part or two they've performed okay. Reliability has been okay, however one of our newer R910s recently all of a sudden went dead to the world; no prior symptoms showing in our hardware and software monitoring, no errors in the os logs, nothing in the dell drac logs. After a hard reset it's back up as if nothing happened, and it's an issue I'm none the wiser to the cause. Not good piece of mind. Look around and find another vendor, even if your company has to pay more for you to have that blame avoidance. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] deadlock_timeout affect on performance
On 01.10.2012 19:49, pg noob wrote: Hi all, I have a question about the deadlock_timeout in regards to performance. Right now we have this timeout set at its default of 1s. My understanding of it is that this means that every 1 second the server will check for deadlocks. Not quite. It means that when a backend gets blocked, waiting on a lock, it will check for deadlocks after waiting for 1 second. When no backend is waiting for a lock, there are no deadlock checks regardless of deadlock_timeout. What I am wondering is how much of a performance improvement we would expect to get if this was raised to 30 seconds? Is it negligible or could it be a substantial performance improvement on a busy system? We very rarely have deadlocks and waiting 30 seconds to discover one doesn't seem too bad. It's almost certainly negligible. If you regularly have deadlocks, it might even better for performance to make the timeout shorter than 1 s, so that deadlocks are detected earlier, and backends will spend less time deadlocked, and more time doing real work. Although I doubt it will make any meaningful difference either way. - Heikki -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice - opinions about HP?
> From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Glyn Astill > Sent: Tuesday, October 02, 2012 4:21 AM > To: M. D.; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] hardware advice > >> From: M. D. >> To: pgsql-performance@postgresql.org >> Sent: Friday, 28 September 2012, 18:33 >> Subject: Re: [PERFORM] hardware advice >> >> On 09/28/2012 09:57 AM, David Boreham wrote: >>> On 9/28/2012 9:46 AM, Craig James wrote: Your best warranty would be to have the confidence to do your own repairs, and to have the parts on hand. I'd seriously consider putting your own system together. Maybe go to a few sites with pre-configured machines and see what parts they use. Order those, screw the thing together yourself, and put a spare of each critical part on your shelf. >>> This is what I did for years, but after taking my old parts collection to the landfill a few times, realized I may as well just buy N+1 machines and keep zero spares on the shelf. That way I get a spare machine available for use immediately, and I know the parts are working (parts on the shelf may be defective). If something breaks, I use the spare machine until the replacement parts arrive. >>> >>> Note in addition that a warranty can be extremely useful in certain organizations as a vehicle of blame avoidance (this may be its primary purpose in fact). If I buy a bunch of machines that turn out to have buggy NICs, well that's my fault and I can kick myself since I own the company, stay up late into the night reading kernel code, and buy new NICs. If I have an evil Dilbertian boss, then well...I'd be seriously thinking about buying Dell boxes in order to blame Dell rather than myself, and be able to say "everything is warrantied" if badness goes down. Just saying... >>> >>I'm kinda in the latter shoes. Dell is the only thing that is trusted in my organisation. If I would build my own, I would be fully blamed for anything going wrong in the next 3 years. Thanks everyone for your input. Now my final choice will be if my budget allows for the latest and fastest, else I'm going for the x5690. I don't have hundreds of users, so I think the x5690 should do a pretty good job handling the load. >> >> > > Having plenty experience with Dell I'd urge you reconsider. All the Dell servers > we've had have arrived hideously misconfigured, and tech support gets you > nowhere. Once we've rejigged the hardware ourselves, maybe replacing a > part or two they've performed okay. > > Reliability has been okay, however one of our newer R910s recently all > of a sudden went dead to the world; no prior symptoms showing in our > hardware and software monitoring, no errors in the os logs, nothing in > the dell drac logs. After a hard reset it's back up as if nothing > happened, and it's an issue I'm none the wiser to the cause. Not good > piece of mind. > > Look around and find another vendor, even if your company has to pay > more for you to have that blame avoidance. We're currently using Dell and have had enough problems to think about switching. What about HP? Dan Franklin
Re: [PERFORM] Inserts in 'big' table slowing down the database
On 10/01/2012 07:15 PM, Stefan Keller wrote: Any ideas? Partitioning? Yes. Make sure you have a good column to partition on. Tables this large are just bad performers in general, and heaven forbid you ever have to perform maintenance on them. We had a table that size, and simply creating an index could take upwards of two hours. If you can't archive any of the table contents, partitioning may be your only solution. If you have an EDB 9.1, you'll also have less problems with the legacy issues people had with planning queries using partitions. Don't go crazy, though. I try to keep it under a couple dozen partitions per table, or under 100M records per partition. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice - opinions about HP?
On Tue, Oct 2, 2012 at 10:51:46AM -0400, Franklin, Dan (FEN) wrote: > > Look around and find another vendor, even if your company has to pay > > > more for you to have that blame avoidance. > > We're currently using Dell and have had enough problems to think about > switching. > > What about HP? If you need a big vendor, I think HP is a good choice. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice
On 10/2/2012 2:20 AM, Glyn Astill wrote: newer R910s recently all of a sudden went dead to the world; no prior symptoms showing in our hardware and software monitoring, no errors in the os logs, nothing in the dell drac logs. After a hard reset it's back up as if nothing happened, and it's an issue I'm none the wiser to the cause. Not good piece of mind. This could be an OS bug rather than a hardware problem. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice - opinions about HP?
On Tue, Oct 2, 2012 at 9:14 AM, Bruce Momjian wrote: > On Tue, Oct 2, 2012 at 10:51:46AM -0400, Franklin, Dan (FEN) wrote: >> We're currently using Dell and have had enough problems to think about >> switching. >> >> What about HP? > > If you need a big vendor, I think HP is a good choice. This brings up a point I make sometimes to folks. Big companies can get great treatment from big vendors. When you work somewhere that orders servers by the truckload, you need a vendor who can fill trucks with servers in a day's notice, and send you a hundred different replacement parts the next. Conversely, if you are a smaller company that orders a dozen or so servers a year, then often a big vendor is not the best match. You're just a drop in the ocean to them. A small vendor is often a much better match here. They can carefully test those two 48 core opteron servers with 100 drives over a week's time to make sure it works the way you need it to. It might take them four weeks to build a big specialty box, but it will usually get built right and for a decent price. Also the sales people will usually be more knowledgeable about the machines they sell. Recent job: 20 or fewer servers ordered a year, boutique shop for them (aberdeeninc in this case). Other recent job: 20 or more servers a week. Big reseller (not at liberty to release the name). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] suboptimal query plan
I'm struggling with a query that seems to use a suboptimal query plan. Schema: units reference a subjob reference a job. In other words: a job contains multiple subjobs. A subjob contains multiple units. (full schema below) We're trying to select all subjobs that need to be reviewed and that contain units that aren't reviewed yet (either because validated is NULL or validated is 'N') Notice the EXISTS with subquery which will turn out to be the problem: (SELECT s0_m0_msubJobs."__id" AS s0_msubJobs_mid, s0_m0_msubJobs."document_mflow" AS s0_msubJobs_mdocument_mflow, s0_m0_msubJobs."status" AS s0_msubJobs_mstatus, s0_m0_msubJobs."error_mmessage" AS s0_msubJobs_merror_mmessage, s0_m0_msubJobs."validation_mrequired" AS s0_msubJobs_mvalidation_mrequired, s0_m0_msubJobs."completion_mdate" AS s0_msubJobs_mcompletion_mdate, s0_m0_msubJobs."creation_mdate" AS s0_msubJobs_mcreation_mdate, s0_m0_msubJobs."file_mlocation" AS s0_msubJobs_mfile_mlocation, s0_m1_mjob."__id" AS s0_mjob_mid, s0_m1_mjob."xml_mname" AS s0_mjob_mxml_mname, ( s0_m0_msubJobs."creation_mdate" ) AS e0_m4 FROM "subJobs" s0_m0_msubJobs, "job" s0_m1_mjob WHERE ( ( ( ( s0_m0_msubJobs."status" ) = ( 'IN_PROGRESS' ) ) AND ( ( s0_m0_msubJobs."validation_mrequired" ) = ( 'Y' ) ) ) AND ( EXISTS (((SELECT s1_m1_munit."__id" AS s1_munit_mid FROM "subJobs" s1_m0_msubJobs, "unit" s1_m1_munit WHERE ( ( ( s0_m0_msubJobs."__id" ) = ( s1_m0_msubJobs."__id" ) ) AND ( s1_m0_msubJobs."__id" = s1_m1_munit."subJobs_mid" ) ) AND ( ( NOT ( s1_m1_munit."validated" IS NOT NULL ) ) OR ( ( s1_m1_munit."validated" ) = ( 'N' ) ) ))) ) ) ) AND ( s0_m0_msubJobs."job_mid" = s0_m1_mjob."__id" )) ORDER BY e0_m4 DESC, s0_mjob_mid nulls first, s0_msubjobs_mid nulls first This generates the following query plan Sort (cost=63242.75..63242.83 rows=30 width=503) (actual time=804.180..804.182 rows=49 loops=1) Sort Key: s0_m0_msubjobs.creation_mdate, s0_m1_mjob.__id, s0_m0_msubjobs.__id Sort Method: quicksort Memory: 31kB Buffers: shared hit=3855 read=13852 -> Hash Join (cost=63087.27..63242.02 rows=30 width=503) (actual time=803.045..804.144 rows=49 loops=1) Hash Cond: (s0_m0_msubjobs.job_mid = s0_m1_mjob.__id) Buffers: shared hit=3855 read=13852 -> Hash Join (cost=63069.02..63223.35 rows=30 width=484) (actual time=802.875..803.953 rows=49 loops=1) Hash Cond: (s1_m0_msubjobs.__id = s0_m0_msubjobs.__id) Buffers: shared hit=3848 read=13852 -> HashAggregate (cost=63014.58..63060.13 rows=4555 width=16) (actual time=802.733..803.452 rows=4555 loops=1) Buffers: shared hit=3808 read=13852 -> Hash Join (cost=149.49..59533.65 rows=1392372 width=16) (actual time=1.157..620.181 rows=1392372 loops=1) Hash Cond: (s1_m1_munit."subJobs_mid" = s1_m0_msubjobs.__id) Buffers: shared hit=3808 read=13852 -> Seq Scan on unit s1_m1_munit (cost=0.00..35017.65 rows=1392372 width=8) (actual time=0.004..211.780 rows=1392372 loops=1) Filter: ((validated IS NULL) OR ((validated)::text = 'N'::text)) Buffers: shared hit=3761 read=13852 -> Hash (cost=92.55..92.55 rows=4555 width=8) (actual time=1.140..1.140 rows=4555 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 178kB Buffers: shared hit=47 -> Seq Scan on "subJobs" s1_m0_msubjobs (cost=0.00..92.55 rows=4555 width=8) (actual time=0.004..0.551 rows=4555 loops=1) Buffers: shared hit=47 -> Hash (cost=54.07..54.07 rows=30 width=484) (actual time=0.122..0.122 rows=49 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 5kB Buffers: shared hit=40 -> Bitmap Heap Scan on "subJobs" s0_m0_msubjobs (cost=5.20..54.07 rows=30 width=484) (actual time=0.046..0.110 rows=49 loops=1) Recheck Cond: ((status)::text = 'IN_PROGRESS'::text) Filter: ((validation_mrequired)::text = 'Y'::text) Buffers: shared hit=40 -> Bitmap Index Scan on subjob_status (cost=0.00..5.19 rows=125 width=0) (actual time
Re: [PERFORM] suboptimal query plan
Nick Hofstede writes: > I'm struggling with a query that seems to use a suboptimal query plan. Try it in 9.2 - this is the same type of join ordering restriction complained of last week here: http://archives.postgresql.org/pgsql-performance/2012-09/msg00201.php regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
