Re: [PERFORM] Is Diskeeper Automatic Mode safe?
cb wrote: My understanding is, before I joined the company, they did an upgrade from 7 on Linux to 8 on Windows and got bit by some change in PG that broke a bunch of code. After that, they have just refused to budge from the 8.0.4 version we are on and know the code works against. Yes; that's one of the reasons there was a major version number bump there. That's a completely normal and expected issue to run into. A similar problem would happen if they tried to upgrade to 8.3 or later from 8.0--you can expect the app to break due to a large change made in 8.3. Sounds to me like the app doesn't really work against the version you're running against now though, from the issues you described. Which brings us to the PostgreSQL patching philosophy, which they may not be aware of. Upgrades to later 8.0 releases will contain *nothing* but bug and security fixes. The basic guideline for changes made as part of the small version number changes (8.0.1 to 8.0.2 for example) are that the bug must be more serious than the potential to cause a regression introduced by messing with things. You shouldn't get anything by going to 8.0.22 but fixes to real problems. A behavior change that broke code would be quite unexpected--the primary way you might run into one is by writing code that expects buggy behavior that then breaks. That's not a very common situation though, whereas the way they got bit before was beyond common--as I said, it was expected to happen. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Performance regression 8.3.8 - 8.4.1 with NOT EXISTS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, we are facing a performance regression regarding certain NOT EXISTS clauses when moving from 8.3.8 to 8.4.1. It is my understanding that the planer treats LEFT JOINs and NOT EXISTS equally with antijoin in 8.4, but this is causing an issue for us. Here is the table and index definition: antijoin=# \d a Table public.a Column | Type | Modifiers - +-+--- a_id | integer | not null a_oid | integer | b_fk | integer | Indexes: a_pkey PRIMARY KEY, btree (a_id) idx_a_oid btree (a_oid) antijoin=# \d b Table public.b Column | Type | Modifiers - +-+--- b_id | integer | not null c_id | integer | b_fk | integer | b_date | date| Indexes: b_pkey PRIMARY KEY, btree (b_id) idx_b_b_date btree (b_date) idx_b_fk btree (b_fk) idx_c_id btree (c_id) antijoin=# \d c Table public.c Column | Type | Modifiers - +-+--- c_id | integer | not null c_bool | boolean | Indexes: c_pkey PRIMARY KEY, btree (c_id) The statement in question is the following: select a_id from a where a_oid = 5207146 and (not exists( select b.b_id from b join c on b.c_id=c.c_id where a.b_fk=b.b_fk and b.b_datenow()) ); Table statistics: antijoin=# select count(*) from a; count - - 3249915 (1 row) antijoin=# select count(*) from b; count - -- 30616125 (1 row) antijoin=# select count(*) from c; count - --- 261 (1 row) The execution plan for 8.3: QUERY PLAN - Index Scan using idx_a_oid on a (cost=0.00..323.38 rows=1 width=4) (actual time=22.155..22.156 rows=1 loops=1) Index Cond: (a_oid = 5207146) Filter: (NOT (subplan)) SubPlan - Nested Loop (cost=0.00..314.76 rows=1 width=4) (actual time=0.113..0.113 rows=0 loops=1) Join Filter: (b.c_id = c.c_id) - Index Scan using idx_b_fk on b (cost=0.00..306.88 rows=1 width=8) (actual time=0.111..0.111 rows=0 loops=1) Index Cond: ($0 = b_fk) Filter: (b_date now()) - Seq Scan on c (cost=0.00..4.61 rows=261 width=4) (never executed) Total runtime: 22.197 ms (11 rows) The execution plan for 8.4: QUERY PLAN - -- Nested Loop Anti Join (cost=3253.47..182470.42 rows=1 width=4) (actual time=377.362..377.370 rows=1 loops=1) Join Filter: (a.b_fk = b.b_fk) - Index Scan using idx_a_oid on a (cost=0.00..8.62 rows=1 width=8) (actual time=0.019..0.025 rows=1 loops=1) Index Cond: (a_oid = 5207146) - Hash Join (cost=3253.47..180297.30 rows=173159 width=4) (actual time=137.360..336.169 rows=187509 loops=1) Hash Cond: (b.c_id = c.c_id) - Bitmap Heap Scan on b (cost=3245.59..177908.50 rows=173159 width=8) (actual time=137.144..221.287 rows=187509 loops=1) Recheck Cond: (b_date now()) - Bitmap Index Scan on idx_b_b_date (cost=0.00..3202.30 rows=173159 width=0) (actual time=135.152..135.152 rows=187509 loops=1) Index Cond: (b_date now()) - Hash (cost=4.61..4.61 rows=261 width=4) (actual time=0.189..0.189 rows=261 loops=1) - Seq Scan on c (cost=0.00..4.61 rows=261 width=4) (actual time=0.008..0.086 rows=261 loops=1) Total runtime: 377.451 ms (13 rows) The hardware is a 4 way Quad Core2 96GB box, both databases configured with the values: shared_buffers=32GB work_mem=128MB effective_cache_size=48GB Default statistics target is 200, all tables are freshly vacuum analyzed. The system is x86_64 with postgres compiled from source. As you can see the 8.4 run is 16 times slower. It was even worse before we added the index idx_b_b_date which we didn't have initially. Is there anything we can do about this issue? Do you need more information? - -- Regards, Wiktor Wodecki net mobile AG, Zollhof 17, 40221 Duesseldorf, Germany 923B DCF8 070C 9FDD 5E05 9AE3 E923 5A35 182C 9783 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAksCoygACgkQ6SNaNRgsl4PpKwCguGSDd2ehmVXM6mzzLWABEOnR WWcAoM7PnSUyHGr0tLymFLhJuO0JtpZ5 =Oq8F -END PGP SIGNATURE- -- 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] Is Diskeeper Automatic Mode safe?
cb c...@mythtech.net wrote: On Nov 16, 2009, at 8:31 PM, Tom Lane wrote: Make sure you're not in the line of fire when (not if) that version eats your data. Particularly on Windows, insisting on not upgrading that version is unbelievably, irresponsibly stupid. There are a *large* number of known bugs. I hear ya, and have agreed with you for a long while. There is a fairly regular and constant fight in house over the issue of upgrading. We get hit on a regular basis with problems that as far as I know are bugs that have been fixed (transaction log rename crashes that take down PG, as well as queries just vanishing into the aether at times of heavy load resulting in hung threads in our Tomcat front end as it waits for something to come back that has disappeared). If you could track down some unmodified 1971 Ford Pintos, you could give them some perspective by having them drive those until they upgrade. -Kevin -- 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] Is Diskeeper Automatic Mode safe?
On Tue, Nov 17, 2009 at 7:59 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: cb c...@mythtech.net wrote: On Nov 16, 2009, at 8:31 PM, Tom Lane wrote: Make sure you're not in the line of fire when (not if) that version eats your data. Particularly on Windows, insisting on not upgrading that version is unbelievably, irresponsibly stupid. There are a *large* number of known bugs. I hear ya, and have agreed with you for a long while. There is a fairly regular and constant fight in house over the issue of upgrading. We get hit on a regular basis with problems that as far as I know are bugs that have been fixed (transaction log rename crashes that take down PG, as well as queries just vanishing into the aether at times of heavy load resulting in hung threads in our Tomcat front end as it waits for something to come back that has disappeared). If you could track down some unmodified 1971 Ford Pintos, you could give them some perspective by having them drive those until they upgrade. And they all get 1993 era Pentium 60s with 32 Megs of RAM running windows 3.11 for workgroups and using the trumpet TCP stack. Upgrades, who needs 'em?! -- 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] Is Diskeeper Automatic Mode safe?
Greg Smith wrote: cb wrote: My understanding is, before I joined the company, they did an upgrade from 7 on Linux to 8 on Windows and got bit by some change in PG that broke a bunch of code. After that, they have just refused to budge from the 8.0.4 version we are on and know the code works against. Yes; that's one of the reasons there was a major version number bump there. That's a completely normal and expected issue to run into. A similar problem would happen if they tried to upgrade to 8.3 or later from 8.0--you can expect the app to break due to a large change made in 8.3. Sounds to me like the app doesn't really work against the version you're running against now though, from the issues you described. Which brings us to the PostgreSQL patching philosophy, which they may not be aware of. Upgrades to later 8.0 releases will contain *nothing* but bug and security fixes. To elaborate on Greg's point: One of the cool things about Postgres minor releases (e.g. everything in the 8.0.*) series, is that you can backup your software, turn off Postgres, install the new version, and just fire it up again, and it works. Any problems? Just revert to the old version. It's an easy sell to management. They can try it, confirm that none of the apps have broken, and if there are problems, you simple say oops, and revert to the old version. If it works, you're the hero, if not, it's just a couple hours of your time. Craig -- 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] Performance regression 8.3.8 - 8.4.1 with NOT EXISTS
Wiktor Wodecki wiktor.wode...@net-m.de writes: As you can see the 8.4 run is 16 times slower. It was even worse before we added the index idx_b_b_date which we didn't have initially. Is there anything we can do about this issue? Do you need more information? You could prevent flattening of the EXISTS subquery by adding an OFFSET 0 or some such to it. A real fix involves being able to handle nestloop indexscans where the parameter comes from more than one join level up; there's been some discussion about that but it's not going to happen in 8.4.x. 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
Re: [PERFORM] SSD + RAID
2009/11/13 Greg Smith g...@2ndquadrant.com: As far as what real-world apps have that profile, I like SSDs for small to medium web applications that have to be responsive, where the user shows up and wants their randomly distributed and uncached data with minimal latency. SSDs can also be used effectively as second-tier targeted storage for things that have a performance-critical but small and random bit as part of a larger design that doesn't have those characteristics; putting indexes on SSD can work out well for example (and there the write durability stuff isn't quite as critical, as you can always drop an index and rebuild if it gets corrupted). I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. I am becoming increasingly suspicious that peter's results are not representative: given that 90% of bonnie++ seeks are read only, the math doesn't add up, and they contradict broadly published tests on the internet. Has anybody independently verified the results? merlin -- 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] SSD + RAID
On Tue, 2009-11-17 at 11:36 -0500, Merlin Moncure wrote: 2009/11/13 Greg Smith g...@2ndquadrant.com: As far as what real-world apps have that profile, I like SSDs for small to medium web applications that have to be responsive, where the user shows up and wants their randomly distributed and uncached data with minimal latency. SSDs can also be used effectively as second-tier targeted storage for things that have a performance-critical but small and random bit as part of a larger design that doesn't have those characteristics; putting indexes on SSD can work out well for example (and there the write durability stuff isn't quite as critical, as you can always drop an index and rebuild if it gets corrupted). I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. I am becoming increasingly suspicious that peter's results are not representative: given that 90% of bonnie++ seeks are read only, the math doesn't add up, and they contradict broadly published tests on the internet. Has anybody independently verified the results? How many times have the run the plug test? I've read other reports of people (not on Postgres) losing data on this drive with the write cache on. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- 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] SSD + RAID
On Tue, Nov 17, 2009 at 9:54 AM, Brad Nicholson bnich...@ca.afilias.info wrote: On Tue, 2009-11-17 at 11:36 -0500, Merlin Moncure wrote: 2009/11/13 Greg Smith g...@2ndquadrant.com: As far as what real-world apps have that profile, I like SSDs for small to medium web applications that have to be responsive, where the user shows up and wants their randomly distributed and uncached data with minimal latency. SSDs can also be used effectively as second-tier targeted storage for things that have a performance-critical but small and random bit as part of a larger design that doesn't have those characteristics; putting indexes on SSD can work out well for example (and there the write durability stuff isn't quite as critical, as you can always drop an index and rebuild if it gets corrupted). I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. I am becoming increasingly suspicious that peter's results are not representative: given that 90% of bonnie++ seeks are read only, the math doesn't add up, and they contradict broadly published tests on the internet. Has anybody independently verified the results? How many times have the run the plug test? I've read other reports of people (not on Postgres) losing data on this drive with the write cache on. When I run the plug test it's on a pgbench that's as big as possible (~4000) and I remove memory if there's a lot in the server so the memory is smaller than the db. I run 100+ concurrent and I set checkoint timeouts to 30 minutes, and make a lots of checkpoint segments (100 or so), and set completion target to 0. Then after about 1/2 checkpoint timeout has passed, I issue a checkpoint from the command line, take a deep breath and pull the cord. -- 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] Is Diskeeper Automatic Mode safe?
On Mon, 2009-11-16 at 23:57 -0500, cb wrote: On Nov 16, 2009, at 8:31 PM, Tom Lane wrote: Myself and the other guy responsible for the underlying hardware have already gone down this route. The big bosses know our stance and know it isn't us preventing the upgrade. After that, there isn't too much more I can do except sit back and shake my head each time something goes wrong and I get sent on a wild goose chase to find any reason for the failure OTHER than PG. What you need to do is stop the wild goose chases. If problem is you PG version, no amount of investigation into other areas is going to change that. Your company is simply wasting money by ignoring this and blindly hoping that the problem will be something else. It can be a difficult battle, but it can be won. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- 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] SSD + RAID
On tis, 2009-11-17 at 11:36 -0500, Merlin Moncure wrote: I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. I am becoming increasingly suspicious that peter's results are not representative: given that 90% of bonnie++ seeks are read only, the math doesn't add up, and they contradict broadly published tests on the internet. Has anybody independently verified the results? Notably, between my two blog posts and this email thread, there have been claims of 400 1800 4000 7000 14000 15000 35000 iops (of some kind) per second. That alone should be cause of concern. -- 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] SSD + RAID
Merlin Moncure wrote: I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. The funny thing about Murphy is that he doesn't visit when things are quiet. It's quite possible the window for data loss on the drive is very small. Maybe you only see it one out of 10 pulls with a very aggressive database-oriented write test. Whatever the odd conditions are, you can be sure you'll see them when there's a bad outage in actual production though. A good test program that is a bit better at introducing and detecting the write cache issue is described at http://brad.livejournal.com/2116715.html -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- 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] SSD + RAID
On Tue, Nov 17, 2009 at 1:51 PM, Greg Smith g...@2ndquadrant.com wrote: Merlin Moncure wrote: I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. The funny thing about Murphy is that he doesn't visit when things are quiet. It's quite possible the window for data loss on the drive is very small. Maybe you only see it one out of 10 pulls with a very aggressive database-oriented write test. Whatever the odd conditions are, you can be sure you'll see them when there's a bad outage in actual production though. A good test program that is a bit better at introducing and detecting the write cache issue is described at http://brad.livejournal.com/2116715.html Sure, not disputing that...I don't have one to test myself, so I can't vouch for the data being safe. But what's up with the 400 iops measured from bonnie++? That's an order of magnitude slower than any other published benchmark on the 'net, and I'm dying to get a little clarification here. merlin -- 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] SSD + RAID
On 11/17/2009 01:51 PM, Greg Smith wrote: Merlin Moncure wrote: I am right now talking to someone on postgresql irc who is measuring 15k iops from x25-e and no data loss following power plug test. The funny thing about Murphy is that he doesn't visit when things are quiet. It's quite possible the window for data loss on the drive is very small. Maybe you only see it one out of 10 pulls with a very aggressive database-oriented write test. Whatever the odd conditions are, you can be sure you'll see them when there's a bad outage in actual production though. A good test program that is a bit better at introducing and detecting the write cache issue is described at http://brad.livejournal.com/2116715.html I've been following this thread with great interest in your results... Please continue to share... For write cache issues - is it possible that the reduced power utilization of SSD allows for a capacitor to complete all scheduled writes, even with a large cache? Is it this particular drive you are suggesting that is known to be insufficient or is it really the technology or maturity of the technology? Cheers, mark -- Mark Mielkem...@mielke.cc -- 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] SSD + RAID
Merlin Moncure wrote: But what's up with the 400 iops measured from bonnie++? I don't know really. SSD writes are really sensitive to block size and the ability to chunk writes into larger chunks, so it may be that Peter has just found the worst-case behavior and everybody else is seeing something better than that. When the reports I get back from people I believe are competant--Vadim, Peter--show worst-case results that are lucky to beat RAID10, I feel I have to dismiss the higher values reported by people who haven't been so careful. And that's just about everybody else, which leaves me quite suspicious of the true value of the drives. The whole thing really sets off my vendor hype reflex, and short of someone loaning me a drive to test I'm not sure how to get past that. The Intel drives are still just a bit too expensive to buy one on a whim, such that I'll just toss it if the drive doesn't live up to expectations. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- 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] SSD + RAID
On Wed, 18 Nov 2009, Greg Smith wrote: Merlin Moncure wrote: But what's up with the 400 iops measured from bonnie++? I don't know really. SSD writes are really sensitive to block size and the ability to chunk writes into larger chunks, so it may be that Peter has just found the worst-case behavior and everybody else is seeing something better than that. When the reports I get back from people I believe are competant--Vadim, Peter--show worst-case results that are lucky to beat RAID10, I feel I have to dismiss the higher values reported by people who haven't been so careful. And that's just about everybody else, which leaves me quite suspicious of the true value of the drives. The whole thing really sets off my vendor hype reflex, and short of someone loaning me a drive to test I'm not sure how to get past that. The Intel drives are still just a bit too expensive to buy one on a whim, such that I'll just toss it if the drive doesn't live up to expectations. keep in mind that bonnie++ isn't always going to reflect your real performance. I have run tests on some workloads that were definantly I/O limited where bonnie++ results that differed by a factor of 10x made no measurable difference in the application performance, so I can easily believe in cases where bonnie++ numbers would not change but application performance could be drasticly different. as always it can depend heavily on your workload. you really do need to figure out how to get your hands on one for your own testing. David Lang -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
