Re: [PERFORM] Intersect/Union X AND/OR
Thanks for the answers. I found one of these cases , but I'm trying to understand this. Why the performance is better? The number of tuples is making the difference? My original query : select table1.id from table1, (select function(12345) id) table2 where table1.kind = 1234 and table1.id = table2.id "Nested Loop (cost=0.00..6.68 rows=1 width=12)" " Join Filter: ()" " -> Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159)" "Filter: (id = 616)" " -> Result (cost=0.00..0.26 rows=1 width=0)" -- function() returns a resultset I tryed with explicit join and "in" , but the plan is the same. When I changed the query to use intersect : (select table1.id from table1 where table1.kind = 1234) Intersect (select function(12345) id) The new plan is : "HashSetOp Intersect (cost=0.00..6.67 rows=1 width=80)" " -> Append (cost=0.00..6.67 rows=2 width=80)" "-> Subquery Scan on "*SELECT* 1" (cost=0.00..6.40 rows=1 width=159)" " -> Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159)" "Filter: (id = 616)" "-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.27 rows=1 width=0)" " -> Result (cost=0.00..0.26 rows=1 width=0)" The second plan is about 10 times faster than the first one. 2011/12/2 Merlin Moncure > On Fri, Dec 2, 2011 at 1:49 PM, Bruce Momjian wrote: > > Thiago Godoi wrote: > >> Hi all, > >> > >> I found this presentation from B. Momjian: > >> > >> http://momjian.us/main/writings/pgsql/performance.pdf > >> > >> I'm interested in what he said about " Intersect/Union X AND/OR " , Can > I > >> find a transcription or a video of this presentation? Can anyone > explain it > >> to me? > > > > Well, there is a recording of the webcast on the EnterpriseDB web site, > > but I am afraid they only allow viewing of 3+ hour webcasts by > > EnterpriseDB customers. > > > > The idea is that a query that uses an OR can be rewritten as two SELECTs > > with a UNION between them. I have seen rare cases where this is a win, > > so I mentioned it in that talk. Intersection is similarly possible for > > AND in WHERE clauses. > > I've seen this as well. Also boolean set EXCEPT is useful as well in > the occasional oddball case. > > merlin > -- Thiago Godoi
Re: [PERFORM] pg_upgrade
> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Bruce Momjian > Sent: Saturday, December 03, 2011 6:42 PM > To: Tory M Blue > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] pg_upgrade > > Tory M Blue wrote: > > On Sat, Dec 3, 2011 at 6:04 AM, Bruce Momjian > wrote: > > > > > Well, I am not totally clear how you are moving things around, but > I do > > > know pg_upgrade isn't happy to have the old and new cluster be very > > > different. > > > > > > What I think is happening is that you didn't properly move the > > > tablespace in the old cluster. ?We don't give you a very easy way > to do > > > that. ?You need to not only move the directory, but you need to > update > > > the symlinks in data/pg_tblspc/, and update the pg_tablespace > system > > > table. ?Did you do all of that? ?Does the 8.4 server see the > tablespace > > > properly after the move, but before pg_upgrade? > > > > > > Simple answer is umm no.. > > The "no" is an answer to which question? > > > "http://www.postgresql.org/docs/current/static/pgupgrade.html"; is > > obviously lacking than :) > > > > S I can take what you have told me and see if I can't attempt to > > make those things happen and try again. Makes sense, but boy that's a > > large piece of info missing in the document! > > You mean moving tablespaces? That isn't something pg_upgrade deals > with. If we need docs to move tablespaces, it is a missing piece of > our > main docs, not something pg_upgrade would ever mention. If I'm reading the issue correctly, and pg_upgrade gets part way through an upgrade then fails if it hits a tablespace - it seems to me like the pg_upgrade should check for such a condition at the initial validation stage not proceed if found. Brad. -- 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] Intersect/Union X AND/OR
Thiago Godoi wrote: > Thanks for the answers. > > I found one of these cases , but I'm trying to understand this. Why the > performance is better? The number of tuples is making the difference? > > My original query : > > select table1.id > from table1, (select function(12345) id) table2 > where table1.kind = 1234 > and table1.id = table2.id > > "Nested Loop (cost=0.00..6.68 rows=1 width=12)" > " Join Filter: ()" > " -> Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159)" > "Filter: (id = 616)" > " -> Result (cost=0.00..0.26 rows=1 width=0)" > > > -- function() returns a resultset > > I tryed with explicit join and "in" , but the plan is the same. > > When I changed the query to use intersect : > > > (select table1.id from table1 where table1.kind = 1234) > Intersect > (select function(12345) id) > > The new plan is : > > "HashSetOp Intersect (cost=0.00..6.67 rows=1 width=80)" > " -> Append (cost=0.00..6.67 rows=2 width=80)" > "-> Subquery Scan on "*SELECT* 1" (cost=0.00..6.40 rows=1 > width=159)" > " -> Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159)" > "Filter: (id = 616)" > "-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.27 rows=1 > width=0)" > " -> Result (cost=0.00..0.26 rows=1 width=0)" > > The second plan is about 10 times faster than the first one. Well, there are usually several ways to execute a query internally, intsersect is using a different, and faster, method. -- 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] pg_upgrade
Nicholson, Brad (Toronto, ON, CA) wrote: > > You mean moving tablespaces? That isn't something pg_upgrade deals > > with. If we need docs to move tablespaces, it is a missing piece of > > our > > main docs, not something pg_upgrade would ever mention. > > If I'm reading the issue correctly, and pg_upgrade gets part way through > an upgrade then fails if it hits a tablespace - it seems to me like > the pg_upgrade should check for such a condition at the initial > validation stage not proceed if found. Checking for all such cases would make pg_upgrade huge and unusable. If you messed up your configuration, pg_upgrade can't check for every such case. There are thosands of ways people can mess up their configuration. I think you should read up on how pg_upgrade attempts to be minimal: http://momjian.us/main/blogs/pgblog/2011.html#June_15_2011_2 On a related note, Magnus is working on code for Postgres 9.2 that would allow for easier moving of tablespaces. -- 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] pg_upgrade
> -Original Message- > From: Bruce Momjian [mailto:br...@momjian.us] > Sent: Monday, December 05, 2011 10:24 AM > To: Nicholson, Brad (Toronto, ON, CA) > Cc: Tory M Blue; pgsql-performance@postgresql.org; Magnus Hagander > Subject: Re: [PERFORM] pg_upgrade > > Nicholson, Brad (Toronto, ON, CA) wrote: > > > You mean moving tablespaces? That isn't something pg_upgrade deals > > > with. If we need docs to move tablespaces, it is a missing piece > of > > > our > > > main docs, not something pg_upgrade would ever mention. > > > > If I'm reading the issue correctly, and pg_upgrade gets part way > through > > an upgrade then fails if it hits a tablespace - it seems to me like > > the pg_upgrade should check for such a condition at the initial > > validation stage not proceed if found. > > Checking for all such cases would make pg_upgrade huge and unusable. > If > you messed up your configuration, pg_upgrade can't check for every such > case. There are thosands of ways people can mess up their > configuration. Based on the OP this does not seem like a messed up configuration. It sounds like the OP used a fully supported core feature of Postgres (tablespaces) and pg_upgrade failed as a result. I think having our upgrade utility fail under such circumstances is a bad thing. Brad. -- 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] pg_upgrade
Nicholson, Brad (Toronto, ON, CA) wrote: > > -Original Message- > > From: Bruce Momjian [mailto:br...@momjian.us] > > Sent: Monday, December 05, 2011 10:24 AM > > To: Nicholson, Brad (Toronto, ON, CA) > > Cc: Tory M Blue; pgsql-performance@postgresql.org; Magnus Hagander > > Subject: Re: [PERFORM] pg_upgrade > > > > Nicholson, Brad (Toronto, ON, CA) wrote: > > > > You mean moving tablespaces? That isn't something pg_upgrade deals > > > > with. If we need docs to move tablespaces, it is a missing piece > > of > > > > our > > > > main docs, not something pg_upgrade would ever mention. > > > > > > If I'm reading the issue correctly, and pg_upgrade gets part way > > through > > > an upgrade then fails if it hits a tablespace - it seems to me like > > > the pg_upgrade should check for such a condition at the initial > > > validation stage not proceed if found. > > > > Checking for all such cases would make pg_upgrade huge and unusable. > > If > > you messed up your configuration, pg_upgrade can't check for every such > > case. There are thosands of ways people can mess up their > > configuration. > > Based on the OP this does not seem like a messed up configuration. It > sounds like the OP used a fully supported core feature of Postgres > (tablespaces) and pg_upgrade failed as a result. I think having our > upgrade utility fail under such circumstances is a bad thing. The OP has not indicated exactly what he did to move the tablespaces, so I have to assume he changed the SQL location but not the symbolic link location, or some other misconfiguration. Can someone provide the steps that caused the failure? pg_upgrade works fine for tablespaces so there must be something different about his configuration. Unless I hear details, I have to assume the tablespace move was done incorrectly. This is the first tablespace failure like this I have ever gotten, and I do test tablespaces. Perhaps something is wrong, but I can't guess what it is. -- 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] Question about VACUUM
Hi Kevin, comments after your comments 2011/12/3 Kevin Grittner : > Ernesto Quiñones wrote: >> Scott Marlowe wrote: >>> Ernesto Quiñones wrote: > I want to know if it's possible to predict (calculate), how long a VACUUM FULL process will consume in a table? > > I don't think you said what version of PostgreSQL you're using. > VACUUM FULL prior to version 9.0 is not recommended for most > situations, and can take days or weeks to complete where other > methods of achieving the same end may take hours. If you have > autovacuum properly configured, you will probably never need to run > VACUUM FULL. I'm working with PostgreSQL 8.3 running in Solaris 10, my autovacuum paramaters are: autovacuum on autovacuum_analyze_scale_factor 0,5 autovacuum_analyze_threshold5 autovacuum_freeze_max_age 2 autovacuum_max_workers 3 autovacuum_naptime 1h autovacuum_vacuum_cost_delay -1 autovacuum_vacuum_cost_limit-1 autovacuum_vacuum_scale_factor 0,5 autovacuum_vacuum_threshold 5 my vacuums parameters are: vacuum_cost_delay 1s vacuum_cost_limit 200 vacuum_cost_page_dirty 20 vacuum_cost_page_hit1 vacuum_cost_page_miss 10 vacuum_freeze_min_age 1 > Ah, well that right there is likely to put you into a position where > you need to do painful extraordinary cleanup like VACUUM FULL. In > most situation the autovacuum defaults are pretty good. Where they > need to be adjusted, the normal things which are actually beneficial > are to change the thresholds to allow more aggressive cleanup or (on > low-powered hardware) to adjust the cost ratios so that performance > is less affected by the autovacuum runs. I have a good performance in my hard disks, I have a good amount of memory, but my cores are very poor, only 1ghz each one. I have some questions here: 1. autovacuum_max_workers= 3 , each work processes is using only one "core" or one "core" it's sharing por 3 workers? 2. when I run a "explain analyze" in a very big table (30millons of rows) , explain returning me 32 millons of rows moved, I am assuming that my statistics are not updated in 2 millons of rows, but, is it a very important number? or maybe, it's a regular result. thanks for your help? -- 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] pg_upgrade
On Mon, Dec 5, 2011 at 7:34 AM, Bruce Momjian wrote: > Nicholson, Brad (Toronto, ON, CA) wrote: >> >> Based on the OP this does not seem like a messed up configuration. It >> sounds like the OP used a fully supported core feature of Postgres >> (tablespaces) and pg_upgrade failed as a result. I think having our >> upgrade utility fail under such circumstances is a bad thing. > > The OP has not indicated exactly what he did to move the tablespaces, so > I have to assume he changed the SQL location but not the symbolic link > location, or some other misconfiguration. Can someone provide the steps > that caused the failure? > > pg_upgrade works fine for tablespaces so there must be something > different about his configuration. Unless I hear details, I have to > assume the tablespace move was done incorrectly. This is the first > tablespace failure like this I have ever gotten, and I do test > tablespaces. Perhaps something is wrong, but I can't guess what it is. > Sorry for the late response, I didn't mean to host a party and step out! Bruce is right, I didn't move tablespaces (I didn't know to be honest I had to, but it makes sense). I simply moved the location of the data files, from /data to /data1. But I did "not", change any sym links or do any other pre-steps, other than install the new binary, make sure that there was a new and old data location as well as a new and old binary location. Since my build processes installs data files at /data and binary at /pgsql/, I simply moved the old Data and binaries, before installing my new build. So /pgsql/ became /pgsql8/ and /data/ became /data1/ I do understand what you are all saying in regards to the tablespace links and tablespace locations.It made total sense when Bruce pointed it out initially. However I'm not sure if I should of known that pg_upgrade doesn't handle this, and or this would be a concern. pg_upgrade asks for old and new locations, so one would think that this information would be used for the upgrade process, including potentially changing tablespace paths during the migration step , this is above my pay grade. But initial response to all this, is umm we have not really made a dump/restore unnecessary with the latest releases of Postgres than, as I would have to think that there is a high percentage of users whom use tablespaces. Tory -- 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] Question about VACUUM
On Mon, Dec 5, 2011 at 10:19 AM, Ernesto Quiñones wrote: > Hi Kevin, comments after your comments > > 2011/12/3 Kevin Grittner : >> Ernesto Quiñones wrote: >>> Scott Marlowe wrote: Ernesto Quiñones wrote: >> > I want to know if it's possible to predict (calculate), how long > a VACUUM FULL process will consume in a table? >> >> I don't think you said what version of PostgreSQL you're using. >> VACUUM FULL prior to version 9.0 is not recommended for most >> situations, and can take days or weeks to complete where other >> methods of achieving the same end may take hours. If you have >> autovacuum properly configured, you will probably never need to run >> VACUUM FULL. > > I'm working with PostgreSQL 8.3 running in Solaris 10, my autovacuum > paramaters are: > > autovacuum on > autovacuum_analyze_scale_factor 0,5 > autovacuum_analyze_threshold5 > autovacuum_freeze_max_age 2 > autovacuum_max_workers 3 > autovacuum_naptime 1h > autovacuum_vacuum_cost_delay -1 > autovacuum_vacuum_cost_limit -1 > autovacuum_vacuum_scale_factor 0,5 > autovacuum_vacuum_threshold 5 > > my vacuums parameters are: > > vacuum_cost_delay 1s > vacuum_cost_limit 200 Those are insane settings for vacuum costing, even on a very slow machine. Basically you're starving vacuum and autovacuum so much that they can never keep up. > I have a good performance in my hard disks, I have a good amount of > memory, but my cores are very poor, only 1ghz each one. If so then your settings for vacuum costing are doubly bad. I'd start by setting the cost_delay to 1ms and raising your cost limit by a factor of 10 or more. > I have some questions here: > > 1. autovacuum_max_workers= 3 , each work processes is using only one > "core" or one "core" it's sharing por 3 workers? Each worker uses a single process and can use one core basically. Right now your vacuum costing is such that it's using 1/10th or so of a CPU. > 2. when I run a "explain analyze" in a very big table (30millons of > rows) , explain returning me 32 millons of rows moved, I am assuming > that my statistics are not updated in 2 millons of rows, but, is it a > very important number? or maybe, it's a regular result. Look for projections being off by factors of 10 or more before it starts to make a big difference. 32M versus 30M is no big deal. 30k versus 30M is a big deal. -- 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] Question about VACUUM
On Mon, Dec 5, 2011 at 10:42 AM, Scott Marlowe wrote: > On Mon, Dec 5, 2011 at 10:19 AM, Ernesto Quiñones wrote: >> vacuum_cost_delay 1s >> vacuum_cost_limit 200 > > Those are insane settings for vacuum costing, even on a very slow > machine. Basically you're starving vacuum and autovacuum so much that > they can never keep up. sorry, the word I meant there was pathological. No insult intended. -- 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] Question about VACUUM
no problem Scott, thanks for your appreciations 2011/12/5 Scott Marlowe : > On Mon, Dec 5, 2011 at 10:42 AM, Scott Marlowe > wrote: >> On Mon, Dec 5, 2011 at 10:19 AM, Ernesto Quiñones wrote: >>> vacuum_cost_delay 1s >>> vacuum_cost_limit 200 >> >> Those are insane settings for vacuum costing, even on a very slow >> machine. Basically you're starving vacuum and autovacuum so much that >> they can never keep up. > > sorry, the word I meant there was pathological. No insult intended. -- -- Visita : http://www.eqsoft.net -- Sigueme en Twitter : http://www.twitter.com/ernestoq -- 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] pg_upgrade
Tory M Blue wrote: > Bruce is right, I didn't move tablespaces (I didn't know to be honest > I had to, but it makes sense). I simply moved the location of the data > files, from /data to /data1. But I did "not", change any sym links or I was unclear if you moved the data directory or the tablespace. Your example showed you moving something that didn't look like data directories: > So I move olddata to /data1/db > oldbinary to /pgsql8/bin > > new 9.1.1 db goes to /data/db > newbinary installs at /pgsql/ > > So when I run pg_upgrade (check validates the config), however trying > to the upgrade nets; > Restoring user relation files > /data/queue/16384/16406 > error while copying queue.adm_version (/data/queue/16384/16406 to > /data/queue/PG_9.1_201105231/16407/16406): No such file or directory > Failure, exiting /data/db and /data/queue are not data locations, or at least they are not ones we create during the install. Was the real data directory and the tablespaces all under /data1? Did you define these tablespace locations using relative paths? > do any other pre-steps, other than install the new binary, make sure > that there was a new and old data location as well as a new and old > binary location. You can definitely move data directories around. > Since my build processes installs data files at /data and binary at > /pgsql/, I simply moved the old Data and binaries, before installing > my new build. So /pgsql/ became /pgsql8/ and /data/ became /data1/ I think you can do that but your error messages don't say that. > I do understand what you are all saying in regards to the tablespace > links and tablespace locations.It made total sense when Bruce pointed > it out initially. However I'm not sure if I should of known that > pg_upgrade doesn't handle this, and or this would be a concern. > pg_upgrade asks for old and new locations, so one would think that > this information would be used for the upgrade process, including > potentially changing tablespace paths during the migration step > , this is above my pay grade. There is no Postgres support for moving tablespaces, so it isn't surprising that pg_upgrade doesn't handle it. > But initial response to all this, is umm we have not really made a > dump/restore unnecessary with the latest releases of Postgres than, as > I would have to think that there is a high percentage of users whom > use tablespaces. Yes, but they don't change tablespace locations during the upgrade. In fact, we have had surprisingly few (zero) request for moving tablespaces, and now we are trying to implement this for Postgres 9.2. The normal API will be to have the user move the tablespace before the upgrade, but as I said before, it isn't easy to do now in Postgres. -- 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] pg_upgrade
On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian wrote: >> But initial response to all this, is umm we have not really made a >> dump/restore unnecessary with the latest releases of Postgres than, as >> I would have to think that there is a high percentage of users whom >> use tablespaces. > > Yes, but they don't change tablespace locations during the upgrade. In > fact, we have had surprisingly few (zero) request for moving > tablespaces, and now we are trying to implement this for Postgres 9.2. > The normal API will be to have the user move the tablespace before the > upgrade, but as I said before, it isn't easy to do now in Postgres. Okay think here is where I'm confused. "they don't change tablespace", okay how are they doing the upgrade? Do they leave the olddatadir in the default location and create a new one elsewhere, vs where I'm kind of doing the opposite? Thanks again! Tory -- 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] Question about VACUUM
Ernesto Quiñones wrote: I understand the impulse to run autovacuum less frequently or less aggressively. When we first started running PostgreSQL the default configuration was very cautious. A lot of bloat would accumulate before it kicked in, at which point there was a noticeable performance hit, as it worked though a large number of dead pages. The first thing I did was to make it run less often, which only made things worse. The numbers we settled on through testing as optimal for us are very close to current default values (for recent major releases). Not only do queries run more quickly between autovacuum runs, because there is less dead space to wade through to get the current tuples, but the autovacuum runs just don't have the same degree of impact -- presumably because they find less to do. Some small, frequently updated tables when from having hundreds of pages down to one or two. > autovacuum_analyze_scale_factor 0,5 > autovacuum_analyze_threshold 5 We use 0.10 + 10 in production. Defaults are now 0.10 + 50. That's the portion of the table plus a number of rows. Analyze just does a random sample from the table; it doesn't pass the whole table. > autovacuum_vacuum_scale_factor 0,5 > autovacuum_vacuum_threshold 5 We use 0.20 + 10 in production. Defaults are now 0.20 + 50. Again, a proportion of the table (in this case what is expected to have become unusable dead space) plus a number of unusable dead tuples. > autovacuum_naptime 1h A one-page table could easily bloat to hundreds (or thousands) of pages within an hour. You will wonder where all your CPU time is going because it will constantly be scanning the same (cached) pages to find the one version of the row which matters. I recommend 1min. > vacuum_cost_delay 1s A vacuum run will never get much done at that rate. I recommend 10ms. > vacuum_cost_limit 200 We've boosted this to 600. Once you're in a "steady state", this is the setting you might want to adjust up or down as needed to make cleanup aggressive enough without putting a noticeable dent in performance while it is running. On 8.3 I believe you still need to worry about the fsm settings. Run your regular database vacuum with the VERBOSE option, and check what the last few lines say. If you don't have enough memory set aside to track free space, no vacuum regimen will prevent bloat. -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] pg_upgrade
On Mon, Dec 5, 2011 at 10:31 AM, Tory M Blue wrote: > On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian wrote: >>> But initial response to all this, is umm we have not really made a >>> dump/restore unnecessary with the latest releases of Postgres than, as >>> I would have to think that there is a high percentage of users whom >>> use tablespaces. >> >> Yes, but they don't change tablespace locations during the upgrade. In >> fact, we have had surprisingly few (zero) request for moving >> tablespaces, and now we are trying to implement this for Postgres 9.2. >> The normal API will be to have the user move the tablespace before the >> upgrade, but as I said before, it isn't easy to do now in Postgres. > > Okay think here is where I'm confused. "they don't change tablespace", > okay how are they doing the upgrade? Do they leave the olddatadir in > the default location and create a new one elsewhere, vs where I'm kind > of doing the opposite? Okay right So changed the symlink in pg_tblspaces, and changed the path inside the db, and it appears to have worked. These were either the "doh pieces" or the missing components that you helped point me to. Thank you! Tory -bash-4.0$ /logs-all/temp/pg_upgrade --old-datadir "/data1/db" --new-datadir "/data/db" --old-bindir "/ipix/pgsql8/bin" --new-bindir "/ipix/pgsql/bin" Performing Consistency Checks - Checking current, bin, and data directories ok Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data typesok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects ok Creating catalog dump ok Checking for prepared transactions ok Checking for presence of required libraries ok | If pg_upgrade fails after this point, you must | re-initdb the new cluster before continuing. | You will also need to remove the ".old" suffix | from /data1/db/global/pg_control.old. Performing Upgrade -- Adding ".old" suffix to old global/pg_control ok Analyzing all rows in the new cluster ok Freezing all rows on the new clusterok Deleting new commit clogs ok Copying old commit clogs to new server ok Setting next transaction id for new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Creating databases in the new cluster ok Adding support functions to new cluster ok Restoring database schema to new clusterok Removing support functions from new cluster ok Restoring user relation files ok Setting next oid for new clusterok Creating script to delete old cluster ok Checking for large objects ok Upgrade complete | Optimizer statistics are not transferred by pg_upgrade | so consider running: | vacuumdb --all --analyze-only | on the newly-upgraded cluster. | Running this script will delete the old cluster's data files: | /data/pgsql/delete_old_cluster.sh -bash-4.0$ -- 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] pg_upgrade
Tory M Blue wrote: > On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian wrote: > >> But initial response to all this, is umm we have not really made a > >> dump/restore unnecessary with the latest releases of Postgres than, as > >> I would have to think that there is a high percentage of users whom > >> use tablespaces. > > > > Yes, but they don't change tablespace locations during the upgrade. ?In > > fact, we have had surprisingly few (zero) request for moving > > tablespaces, and now we are trying to implement this for Postgres 9.2. > > The normal API will be to have the user move the tablespace before the > > upgrade, but as I said before, it isn't easy to do now in Postgres. > > Okay think here is where I'm confused. "they don't change tablespace", > okay how are they doing the upgrade? Do they leave the olddatadir in > the default location and create a new one elsewhere, vs where I'm kind > of doing the opposite? If you look in a 9.0+ tablespace directory, you will see that each cluster has its own subdirectory: test=> create tablespace tb1 location '/u/pg/tb1'; CREATE TABLESPACE test=> \q $ lf /u/pg/tb1 PG_9.2_20231/ That means if I upgrade to 9.3, there will be another subdirectory for 9.3, _inside_ the same tablespace location. This change was added in Postgres 9.0 to allow for upgrades without having to move tablespaces. Now, since you are upgrading from 8.4, and don't have a subdirectory, the 9.1 cluster will be created inside the tablespace directory, so it will look like: 323234/ 423411/ 932323/ PG_9.1_201105231/ I realize that is kind of confusing, but it works just fine, and pg_upgrade will provide you with a script to delete the old cluster, and its subdirectories, when you are ready. I hope this helps clarify things. -- 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] pg_upgrade
Tory M Blue wrote: > On Mon, Dec 5, 2011 at 10:31 AM, Tory M Blue wrote: > > On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian wrote: > >>> But initial response to all this, is umm we have not really made a > >>> dump/restore unnecessary with the latest releases of Postgres than, as > >>> I would have to think that there is a high percentage of users whom > >>> use tablespaces. > >> > >> Yes, but they don't change tablespace locations during the upgrade. ?In > >> fact, we have had surprisingly few (zero) request for moving > >> tablespaces, and now we are trying to implement this for Postgres 9.2. > >> The normal API will be to have the user move the tablespace before the > >> upgrade, but as I said before, it isn't easy to do now in Postgres. > > > > Okay think here is where I'm confused. "they don't change tablespace", > > okay how are they doing the upgrade? ?Do they leave the olddatadir in > > the default location and create a new one elsewhere, vs where I'm kind > > of doing the opposite? > > Okay right > > So changed the symlink in pg_tblspaces, and changed the path inside > the db, and it appears to have worked. These were either the "doh > pieces" or the missing components that you helped point me to. Thank > you! See my other email --- this might not be necessary. --- > > Tory > > -bash-4.0$ /logs-all/temp/pg_upgrade --old-datadir "/data1/db" > --new-datadir "/data/db" --old-bindir "/ipix/pgsql8/bin" --new-bindir > "/ipix/pgsql/bin" > Performing Consistency Checks > - > Checking current, bin, and data directories ok > Checking cluster versions ok > Checking database user is a superuser ok > Checking for prepared transactions ok > Checking for reg* system oid user data typesok > Checking for contrib/isn with bigint-passing mismatch ok > Checking for large objects ok > Creating catalog dump ok > Checking for prepared transactions ok > Checking for presence of required libraries ok > > | If pg_upgrade fails after this point, you must > | re-initdb the new cluster before continuing. > | You will also need to remove the ".old" suffix > | from /data1/db/global/pg_control.old. > > Performing Upgrade > -- > Adding ".old" suffix to old global/pg_control ok > Analyzing all rows in the new cluster ok > Freezing all rows on the new clusterok > Deleting new commit clogs ok > Copying old commit clogs to new server ok > Setting next transaction id for new cluster ok > Resetting WAL archives ok > Setting frozenxid counters in new cluster ok > Creating databases in the new cluster ok > Adding support functions to new cluster ok > Restoring database schema to new clusterok > Removing support functions from new cluster ok > Restoring user relation files > ok > Setting next oid for new clusterok > Creating script to delete old cluster ok > Checking for large objects ok > > Upgrade complete > > | Optimizer statistics are not transferred by pg_upgrade > | so consider running: > | vacuumdb --all --analyze-only > | on the newly-upgraded cluster. > > | Running this script will delete the old cluster's data files: > | /data/pgsql/delete_old_cluster.sh > -bash-4.0$ -- 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] Question about VACUUM
On Mon, Dec 5, 2011 at 11:36 AM, Kevin Grittner wrote: > Ernesto Quiñones wrote: >> vacuum_cost_limit 200 > We've boosted this to 600. Once you're in a "steady state", this is > the setting you might want to adjust up or down as needed to make > cleanup aggressive enough without putting a noticeable dent in > performance while it is running. On the busy production systems I've worked on in the past, we had this cranked up to several thousand along with 10 or so workers to keep up on a busy machine. The more IO your box has, the more you can afford to make vacuum / autovacuum aggressive. -- 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] pg_upgrade
On Mon, Dec 5, 2011 at 11:08 AM, Bruce Momjian wrote: > > If you look in a 9.0+ tablespace directory, you will see that each > cluster has its own subdirectory: > > test=> create tablespace tb1 location '/u/pg/tb1'; > CREATE TABLESPACE > test=> \q > $ lf /u/pg/tb1 > PG_9.2_20231/ > > That means if I upgrade to 9.3, there will be another subdirectory for > 9.3, _inside_ the same tablespace location. This change was added in > Postgres 9.0 to allow for upgrades without having to move tablespaces. > > Now, since you are upgrading from 8.4, and don't have a subdirectory, > the 9.1 cluster will be created inside the tablespace directory, so it > will look like: > > 323234/ 423411/ 932323/ PG_9.1_201105231/ > > > I realize that is kind of confusing, but it works just fine, and > pg_upgrade will provide you with a script to delete the old cluster, and > its subdirectories, when you are ready. > > I hope this helps clarify things. > Well I could see the PG_9.1 or whatever directory being created, however I would still get a fail. Once I modified the internal tablespace path and the filesystem symlink, it worked just fine. Having to create 6-10 symlinks is kind of cruddy and altering the paths (although that is not bad). But it's working. So I at least have a method to make this work :) Tory -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
