Re: [PERFORM] Replication Syatem
On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup ---++---+---+---+ 16461 | table1 | 0 | 8352496 | 5389 |8351242 Hmm.. So indeed there are very few HOT updates. What is the fillfactor you are using for these tests ? If its much less than 100, the very low percentage of HOT updates would make me guess that you are updating one of the index columns. Otherwise at least the initial updates until you fill up the free space should be HOT. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
table1 structure : idintegernot null codeintegernot null cridintegernot null statuscharacter varying(1)default 'A'::character varying delta1bigintdefault 0 delta2bigintdefault 0 delta3bigintdefault 0 delta4bigintdefault 0 tz_idintegerdefault 0 Indexes: idx1 PRIMARY KEY, btree (id) idx2 UNIQUE, btree (code, crid) idx3 btree (tz_id) idx4 btree (status) code as crid are foreign key. update table1 set delta1 = 100 where code/100 =999; On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: fillfactor is set to 80 as you suggested. delta* fields r updated and these fields are no where related to any of the index fields. On Wed, Apr 30, 2008 at 12:13 PM, Pavan Deolasee [EMAIL PROTECTED] wrote: On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup ---++---+---+---+ 16461 | table1 | 0 | 8352496 | 5389 |8351242 Hmm.. So indeed there are very few HOT updates. What is the fillfactor you are using for these tests ? If its much less than 100, the very low percentage of HOT updates would make me guess that you are updating one of the index columns. Otherwise at least the initial updates until you fill up the free space should be HOT. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Regards Gauri -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: fillfactor is set to 80 as you suggested. delta* fields r updated and these fields are no where related to any of the index fields. That's weird. With that fillfactor, you should have a very high percentage of HOT update ratio. It could be a very special case that we might be looking at. I think a self contained test case or a very detail explanation of the exact usage is what we need to explain this behavior. You may also try dropping non-critical indexes and test again. Btw, I haven't been able to reproduce this at my end. With the given indexes and kind of updates, I get very high percentage of HOT updates. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Gauri Kanekar wrote: HOT doesn't seems to be working in our case. This is table1 structure : idintegernot null codeintegernot null cridintegernot null statuscharacter varying(1)default 'A'::character varying delta1bigintdefault 0 delta2bigintdefault 0 delta3bigintdefault 0 delta4bigintdefault 0 tz_idintegerdefault 0 Indexes: idx1 PRIMARY KEY, btree (id) idx2 UNIQUE, btree (code, crid) idx3 btree (tz_id) idx4 btree (status) code as crid are foreign key. Here delta* fields get updated through out the day. and most of the time it may update the same row again n again. table1 contains around 12843694 records. Now not understanding y HOT don't work in our case. Changed fillfactor to 80, 75,70 but nothing seems to work. Did you dump and reload the table after setting the fill factor? It only affects newly inserted data. Another possibility is that there's a long running transaction in the background, preventing HOT/vacuum from reclaiming the dead tuples. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Heikki Linnakangas wrote: Did you dump and reload the table after setting the fill factor? It only affects newly inserted data. VACUUM FULL or CLUSTER should do the job too, right? After all, they recreate the table so they must take the fillfactor into account. -- Craig Ringer -- 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] Replication Syatem
Craig Ringer wrote: Heikki Linnakangas wrote: Did you dump and reload the table after setting the fill factor? It only affects newly inserted data. VACUUM FULL or CLUSTER should do the job too, right? After all, they recreate the table so they must take the fillfactor into account. CLUSTER, yes. VACUUM FULL won't move tuples around just to make room for the fillfactor. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Please keep list in the loop. On Wed, Apr 30, 2008 at 6:45 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: Hi, We have recreated the indices with fillfactor set to 80, which has improved HOT a little, Wait. Did you say, you recreated the indexes with fill factor ? That's no help for HOT. You need to recreate the TABLEs with a fill factor. And as Heikki pointed out, you need to dump and reload, just altering the table won't affect the current data. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Pavan Deolasee [EMAIL PROTECTED] writes: That's weird. With that fillfactor, you should have a very high percentage of HOT update ratio. It could be a very special case that we might be looking at. He's testing update table1 set delta1 = 100 where code/100 =999; so all the rows being updated fall into a contiguous range of code values. If the table was loaded in such a way that those rows were also physically contiguous, then the updates would be localized and would very soon run out of freespace on those pages. 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] Replication Syatem
[EMAIL PROTECTED] (Frank Ch. Eigler) writes: Tom Lane [EMAIL PROTECTED] writes: Also, you need to make sure you have the FSM parameters set high enough so that all the free space found by a VACUUM run can be remembered. Would it be difficult to arrange FSM parameters to be automatically set from the VACUUM reclaim results? Yeah, because the problem is that FSM is kept in shared memory which cannot be resized on-the-fly. In retrospect, trying to keep FSM in shared memory was a spectacularly bad idea (one for which I take full blame). There is work afoot to push it out to disk so that the whole problem goes away; so I don't see much point in worrying about band-aid solutions. 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] Replication Syatem
On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: That's weird. With that fillfactor, you should have a very high percentage of HOT update ratio. It could be a very special case that we might be looking at. He's testing It's She :-) Oh yes. Apologies if I sounded harsh; did not mean that. I was just completely confused why she is not seeing the HOT updates. update table1 set delta1 = 100 where code/100 =999; so all the rows being updated fall into a contiguous range of code values. If the table was loaded in such a way that those rows were also physically contiguous, then the updates would be localized and would very soon run out of freespace on those pages. Yeah, that seems like the pattern. I tested with the similar layout and a fill factor 80. The initial few bulk updates had comparatively less HOT updates (somewhere 20-25%), But within 4-5 iterations of updating the same set of rows, HOT updates were 90-95%. That's because after few iterations (and because of non-HOT updates) the tuples get scattered in various blocks, thus improving chances of HOT updates. I guess the reason probably is that she is using fill factor for indexes and not heap, but she hasn't yet confirmed. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
We have tried fillfactor for indices and it seems to work. Need to try fillfactor for table. May for that reason the bulk update queries don't get the advantage of HOT :) On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee [EMAIL PROTECTED] wrote: On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: That's weird. With that fillfactor, you should have a very high percentage of HOT update ratio. It could be a very special case that we might be looking at. He's testing It's She :-) Oh yes. Apologies if I sounded harsh; did not mean that. I was just completely confused why she is not seeing the HOT updates. update table1 set delta1 = 100 where code/100 =999; so all the rows being updated fall into a contiguous range of code values. If the table was loaded in such a way that those rows were also physically contiguous, then the updates would be localized and would very soon run out of freespace on those pages. Yeah, that seems like the pattern. I tested with the similar layout and a fill factor 80. The initial few bulk updates had comparatively less HOT updates (somewhere 20-25%), But within 4-5 iterations of updating the same set of rows, HOT updates were 90-95%. That's because after few iterations (and because of non-HOT updates) the tuples get scattered in various blocks, thus improving chances of HOT updates. I guess the reason probably is that she is using fill factor for indexes and not heap, but she hasn't yet confirmed. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Tue, 29 Apr 2008, Gauri Kanekar wrote: We do vacuum full, as vacuum verbose analyse dont regain space for us. Ah, now we're getting to the root of your problem here. You expect that VACUUM should reclaim space. Whenever you UPDATE a row, it writes a new one out, then switches to use that version. This leaves behind the original. Those now unused rows are what VACUUM gathers, but it doesn't give that space back to the operating system. The model here assumes that you'll need that space again for the next time you UPDATE or INSERT a row. So instead VACUUM just keeps those available for database reuse rather than returning it to the operating system. Now, if you don't VACUUM frequently enough, this model breaks down, and the table can get bigger with space that may never get reused. The idea is that you should be VACUUMing up now unneeded rows at about the same rate they're being re-used. When you don't keep up, the database can expand in space that you don't get back again. The right answer to this problem is not to use VACUUM FULL; it's to use regular VACUUM more often. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Replication Syatem
From most of the reply found that upgrade to higher version of postgres may be to 8.3.1 may be one of the solution to tackle this problem Checked about HOT feature in 8.3.1. Do we need to do any special config changes or any other setting for HOT to work?? Any special guideline to follow to make HOT working?? ~ Gauri On Tue, Apr 29, 2008 at 2:07 PM, Greg Smith [EMAIL PROTECTED] wrote: On Tue, 29 Apr 2008, Gauri Kanekar wrote: We do vacuum full, as vacuum verbose analyse dont regain space for us. Ah, now we're getting to the root of your problem here. You expect that VACUUM should reclaim space. Whenever you UPDATE a row, it writes a new one out, then switches to use that version. This leaves behind the original. Those now unused rows are what VACUUM gathers, but it doesn't give that space back to the operating system. The model here assumes that you'll need that space again for the next time you UPDATE or INSERT a row. So instead VACUUM just keeps those available for database reuse rather than returning it to the operating system. Now, if you don't VACUUM frequently enough, this model breaks down, and the table can get bigger with space that may never get reused. The idea is that you should be VACUUMing up now unneeded rows at about the same rate they're being re-used. When you don't keep up, the database can expand in space that you don't get back again. The right answer to this problem is not to use VACUUM FULL; it's to use regular VACUUM more often. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Regards Gauri
Re: [PERFORM] Replication Syatem
Gauri Kanekar wrote: Andrew, Can you explain me in detail why u said vacuum full is making the things worst. We do vacuum full, as vacuum verbose analyse dont regain space for us. vacuum full stops all access so that the data files can be re-writen without the unused space. normal vacuum will update the records of what space is no longer used so that it can then be reused with the next update/insert. Your db size will not shrink straight away but it will stop growing until you use all the free space left from previous update/delete The more frequently you do a normal vacuum the less time it will take and things will run a lot smoother with your file size growing slowly to accommodate new data. Expanding on what others have mentioned as a drawback of vacuum full - you should look at REINDEX'ing as well (maybe one index or table at a time). You will most likely find this will reclaim some disk space for you as well. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Replication Syatem
On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: Do we need to do any special config changes or any other setting for HOT to work?? No. HOT is enabled by default, on all tables. There is no way and need to disable it. Any special guideline to follow to make HOT working?? You can do couple of things to benefit from HOT. 1. HOT addresses a special, but common case where UPDATE operation does not change any of the index keys. So check if your UPDATE changes any of the index keys. If so, see if you can avoid having index involving that column. Of course, I won't advocate dropping an index if it would drastically impact your frequently run queries. 2. You may leave some free space in the heap (fillfactor less than 100). My recommendation would be to leave space worth of one row or slightly more than that to let first UPDATE be an HOT update. Subsequent UPDATEs in the page may reuse the dead row created by earlier UPDATEs. 3. Avoid any long running transactions. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Thanx for the help. Need some more help. table1 has two indices unique indx1 = pkfld unique indx2 = fkfld1,fkfld2 did following steps in the listed order - 1. vacuumed the whole DB 2. table1 RecCnt == 11970789 Size == 2702.41 MB 3.update table1 set fld7 = 1000 where fld1/100 = 999 ; this UPDATED 1230307 records 4. checked table1 size again Reccnt = 11970789 Size == 2996.57MB 5. Again did the update, update table1 set fld7 = 1000 where fld1/100 = 999 ; this UPDATED 1230307 records 6. Got table1 size as RecCnt == 11970789 Size == 3290.64 7. Updated again, update table1 set fld7 = 1000 where fld1/100 = 999 ; this UPDATED 1230307 records 6. table1 size as RecCnt == 11970789 Size == 3584.66 Found that the size increased gradually. Is HOT working over here ?? Guide me if im doing something wrong. ~ Gauri On Tue, Apr 29, 2008 at 4:55 PM, Pavan Deolasee [EMAIL PROTECTED] wrote: On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: Do we need to do any special config changes or any other setting for HOT to work?? No. HOT is enabled by default, on all tables. There is no way and need to disable it. Any special guideline to follow to make HOT working?? You can do couple of things to benefit from HOT. 1. HOT addresses a special, but common case where UPDATE operation does not change any of the index keys. So check if your UPDATE changes any of the index keys. If so, see if you can avoid having index involving that column. Of course, I won't advocate dropping an index if it would drastically impact your frequently run queries. 2. You may leave some free space in the heap (fillfactor less than 100). My recommendation would be to leave space worth of one row or slightly more than that to let first UPDATE be an HOT update. Subsequent UPDATEs in the page may reuse the dead row created by earlier UPDATEs. 3. Avoid any long running transactions. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Regards Gauri
Re: [PERFORM] Replication Syatem
Gauri Kanekar escribió: Do we need to do any special config changes or any other setting for HOT to work?? No. HOT is always working, if it can. You don't need to configure it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Replication Syatem
Gauri Kanekar escribió: Found that the size increased gradually. Is HOT working over here ?? Guide me if im doing something wrong. Probably not. Try vacuuming between the updates. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Replication Syatem
On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: Found that the size increased gradually. Is HOT working over here ?? Guide me if im doing something wrong. You have chosen a bad case for HOT. Since you are repeatedly updating the same set of rows, the dead space created in the first step is the blocks which are not touched in the subsequent updates. Is this a real scenario or are you just testing ? If its just for testing, I would suggest updating different sets of rows in each step and then check. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Thats how our updates works. We usually tend to touch the same row many times a day. ~ Gauri On Tue, Apr 29, 2008 at 6:39 PM, Pavan Deolasee [EMAIL PROTECTED] wrote: On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: Found that the size increased gradually. Is HOT working over here ?? Guide me if im doing something wrong. You have chosen a bad case for HOT. Since you are repeatedly updating the same set of rows, the dead space created in the first step is the blocks which are not touched in the subsequent updates. Is this a real scenario or are you just testing ? If its just for testing, I would suggest updating different sets of rows in each step and then check. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Tue, Apr 29, 2008 at 6:42 PM, Gauri Kanekar [EMAIL PROTECTED] wrote: Thats how our updates works. We usually tend to touch the same row many times a day. Then start with a non-100 fillfactor. I would suggest something like 80 and then adjust based on the testing. Since you are anyways have a update intensive setup, leaving free space in the heap won't harm you much in the long term. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
Pavan Deolasee [EMAIL PROTECTED] writes: Any special guideline to follow to make HOT working?? You can do couple of things to benefit from HOT. 1. HOT addresses a special, but common case where UPDATE operation does not change any of the index keys. So check if your UPDATE changes any of the index keys. If so, see if you can avoid having index involving that column. Of course, I won't advocate dropping an index if it would drastically impact your frequently run queries. 2. You may leave some free space in the heap (fillfactor less than 100). My recommendation would be to leave space worth of one row or slightly more than that to let first UPDATE be an HOT update. Subsequent UPDATEs in the page may reuse the dead row created by earlier UPDATEs. 3. Avoid any long running transactions. Perhaps we should put this list in the FAQ. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- 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] Replication Syatem
Greg Smith [EMAIL PROTECTED] writes: The model here assumes that you'll need that space again for the next time you UPDATE or INSERT a row. So instead VACUUM just keeps those available for database reuse rather than returning it to the operating system. Now, if you don't VACUUM frequently enough, this model breaks down, and the table can get bigger with space that may never get reused. The idea is that you should be VACUUMing up now unneeded rows at about the same rate they're being re-used. When you don't keep up, the database can expand in space that you don't get back again. The right answer to this problem is not to use VACUUM FULL; it's to use regular VACUUM more often. Also, you need to make sure you have the FSM parameters set high enough so that all the free space found by a VACUUM run can be remembered. The less often you run VACUUM, the more FSM space you need, because there'll be more free space reclaimed per run. 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] Replication Syatem
On Apr 29, 2008, at 10:16 AM, Tom Lane wrote: Greg Smith [EMAIL PROTECTED] writes: The model here assumes that you'll need that space again for the next time you UPDATE or INSERT a row. So instead VACUUM just keeps those available for database reuse rather than returning it to the operating system. [ ... ] Also, you need to make sure you have the FSM parameters set high enough so that all the free space found by a VACUUM run can be remembered. The less often you run VACUUM, the more FSM space you need, because there'll be more free space reclaimed per run. I can actually watch one of our applications slow down once the free space in the table is used up. Extending the data file seems to be much more expensive than using the free space found in existing pages of the file. -- 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] Replication Syatem
[EMAIL PROTECTED] (Gauri Kanekar) writes: Basically we have some background process which updates table1 and we don't want the application to make any changes to table1 while vacuum. Vacuum requires exclusive lock on table1 and if any of the background or application is ON vacuum don't kick off. Thats the reason we need to get the site down. VACUUM has not required an exclusive lock on tables since version 7.1. What version of PostgreSQL are you running? -- output = (cbbrowne @ acm.org) http://linuxdatabases.info/info/sap.html Rules of the Evil Overlord #192. If I appoint someone as my consort, I will not subsequently inform her that she is being replaced by a younger, more attractive woman. http://www.eviloverlord.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] Replication Syatem
Alvaro Herrera wrote: Gauri Kanekar escribió: Do we need to do any special config changes or any other setting for HOT to work?? No. HOT is always working, if it can. You don't need to configure it. Unless you have upgraded since you started this thread you are still running 8.1.3. HOT is only available in 8.3 and 8.3.1 You DO need to upgrade to get the benefits of HOT -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Replication Syatem
HOT doesn't seems to be working in our case. This is table1 structure : idintegernot null codeintegernot null cridintegernot null statuscharacter varying(1)default 'A'::character varying delta1bigintdefault 0 delta2bigintdefault 0 delta3bigintdefault 0 delta4bigintdefault 0 tz_idintegerdefault 0 Indexes: idx1 PRIMARY KEY, btree (id) idx2 UNIQUE, btree (code, crid) idx3 btree (tz_id) idx4 btree (status) code as crid are foreign key. Here delta* fields get updated through out the day. and most of the time it may update the same row again n again. table1 contains around 12843694 records. Now not understanding y HOT don't work in our case. Changed fillfactor to 80, 75,70 but nothing seems to work. ~Gauri On Tue, Apr 29, 2008 at 10:18 PM, Shane Ambler [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Gauri Kanekar escribió: Do we need to do any special config changes or any other setting for HOT to work?? No. HOT is always working, if it can. You don't need to configure it. Unless you have upgraded since you started this thread you are still running 8.1.3. HOT is only available in 8.3 and 8.3.1 You DO need to upgrade to get the benefits of HOT -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: HOT doesn't seems to be working in our case. Can you please post output of the following query ? SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup from pg_stat_user_tables WHERE relname = 'table1'; Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup ---++---+---+---+ 16461 | table1 | 0 | 8352496 | 5389 |8351242 On Wed, Apr 30, 2008 at 11:07 AM, Pavan Deolasee [EMAIL PROTECTED] wrote: On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: HOT doesn't seems to be working in our case. Can you please post output of the following query ? SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup from pg_stat_user_tables WHERE relname = 'table1'; Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Regards Gauri
[PERFORM] Replication Syatem
All, We have a table table1 which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming table1 take almost 30min and during that time the site is down. We need to cut down on this downtime.So thought of having a replication system, for which the replicated DB will be up during the master is getting vacuumed. Can anybody guide which will be the best suited replication solution for this. Thanx for any help ~ Gauri
Re: [PERFORM] Replication Syatem
2008/4/28 Gauri Kanekar [EMAIL PROTECTED]: All, We have a table table1 which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming table1 take almost 30min and during that time the site is down. We need to cut down on this downtime.So thought of having a replication system, for which the replicated DB will be up during the master is getting vacuumed. Can anybody guide which will be the best suited replication solution for this. Thanx for any help ~ Gauri I home your not using Vacuum Full... (Standard Reply for this type of question) What version of Postgresql are you using? Have you tried autovacuum? Run plain vacuum even more often on this even more often (like ever half hour) and it should not take as long and save space. If still have trouble run vacuum analyse verbose table1; and see what it says. If your doing it right you should be able to vacuum with the database up. Sounds like you might be happier a fix for the problem rather than a complex work around which will actually solve a completely different problem. Regards Peter.
Re: [PERFORM] Replication Syatem
Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. We are currently using 8.1.3 version. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). Thanx ~ Gauri On Mon, Apr 28, 2008 at 7:28 PM, Peter Childs [EMAIL PROTECTED] wrote: 2008/4/28 Gauri Kanekar [EMAIL PROTECTED]: All, We have a table table1 which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming table1 take almost 30min and during that time the site is down. We need to cut down on this downtime.So thought of having a replication system, for which the replicated DB will be up during the master is getting vacuumed. Can anybody guide which will be the best suited replication solution for this. Thanx for any help ~ Gauri I home your not using Vacuum Full... (Standard Reply for this type of question) What version of Postgresql are you using? Have you tried autovacuum? Run plain vacuum even more often on this even more often (like ever half hour) and it should not take as long and save space. If still have trouble run vacuum analyse verbose table1; and see what it says. If your doing it right you should be able to vacuum with the database up. Sounds like you might be happier a fix for the problem rather than a complex work around which will actually solve a completely different problem. Regards Peter. -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. We are currently using 8.1.3 version. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. You should seriously consider upgrading to PG 8.3. There have been substantial improvements to VACUUM since 8.1 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] Replication Syatem
Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. We are currently using 8.1.3 version. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). Thanx ~ Gauri We use slony for exactly this type of a situation. It's not the most user-friendly piece of software, but it works well enough that I can schedule maintenance windows (we're a 24/7 shop) and do clustering and other tasks on our DB to reclaim space, etc. -salman -- 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] Replication Syatem
Thats one of the thingsto be done in near future. But it need some changes from application point of view. :( ... so just got escalated for that reason. But for now, which one will be a well suited replication system ? ~ Gauri On Mon, Apr 28, 2008 at 7:43 PM, Brad Nicholson [EMAIL PROTECTED] wrote: On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. We are currently using 8.1.3 version. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. You should seriously consider upgrading to PG 8.3. There have been substantial improvements to VACUUM since 8.1 Brad. -- Regards Gauri
Re: [PERFORM] Replication Syatem
Salman, Slony don't do automatic failover. And we would appreciate a system with automatic failover :( ~ Gauri On Mon, Apr 28, 2008 at 7:46 PM, salman [EMAIL PROTECTED] wrote: Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. We are currently using 8.1.3 version. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). Thanx ~ Gauri We use slony for exactly this type of a situation. It's not the most user-friendly piece of software, but it works well enough that I can schedule maintenance windows (we're a 24/7 shop) and do clustering and other tasks on our DB to reclaim space, etc. -salman -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. VACUUM FULL is making your problem worse, not better. Don't do that. We are currently using 8.1.3 version. You need immediately to upgrade to the latest 8.1 stability and security release, which is 8.1.11. This is a drop-in replacement. It's an urgent fix for your case. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. You probably need to tune autovacuum not to do that table, and just vacuum that table in a constant loop or something. VACUUM should _never_ take the site down. If it does, you're doing it wrong. Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). I think you will find that no replication system will solve your underlying problems. That said, I happen to work for a company that will sell you a replication system to work with 8.1 if you really want it. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] Replication Syatem
On Mon, Apr 28, 2008 at 07:48:48PM +0530, Gauri Kanekar wrote: Slony don't do automatic failover. And we would appreciate a system with automatic failover :( No responsible asynchronous system will give you automatic failover. You can lose data that way. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] Replication Syatem
On Mon, 28 Apr 2008, Gauri Kanekar wrote: We are doing vacuum full every alternate day. We also do vacuum analyze very often. We are currently using 8.1.3 version...Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. Andrew Sullivan has already given a response here I agree with, I wanted to expland on that. You have a VACUUM problem. The fact that you need (or feel you need) to VACUUM FULL every other day says there's something very wrong here. The way to solve most VACUUM problems is to VACUUM more often, so that the work in each individual one never gets so big that your system takes an unnaceptable hit, and you shouldn't ever need VACUUM FULL. Since your problem is being aggrevated because you're running a dangerously obsolete version, that's one of the first things you should fix--to at least the latest 8.1 if you can't deal with a larger version migration. The fact that you're happily running 8.1.3 says you most certainly haven't tried all the other options here. Every minute you spend looking into a replication system is wasted time you could be spending on the right fix here. You've fallen into the common trap where you're fixated on a particular technical solution so much that you're now ignoring suggestions on how to resolve the root problem. Replication is hard to get going even on a system that works perfectly, and replicating a known buggy system just to work around a problem really sounds like a bad choice. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Replication Syatem
On Mon, Apr 28, 2008 at 9:38 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: All, We have a table table1 which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming table1 take almost 30min and during that time the site is down. Slony is an open source replication system built for Postgres. But the real problem is that you are doing a vaccum full every day. This is highly invasive. Take a look at the postgres docs on Vacuuming the db. Analyze is best on a daily basis. If you have a lot of deletes, then try vacuum truncate. The postgres documentation describes the various vaccuum options and explains the merits of each. Hope that helps. Radhika -- It is all a matter of perspective. You choose your view by choosing where to stand. --Larry Wall -- 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] Replication Syatem
[EMAIL PROTECTED] (Gauri Kanekar) writes: We have a table table1 which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming table1 take almost 30min and during that time the site is down. We need to cut down on this downtime.So thought of having a replication system, for which the replicated DB will be up during the master is getting vacuumed. Can anybody guide which will be the best suited replication solution for this. The only reason that it would be necessary for VACUUM to take the site down would be if you are running version 7.1, which was obsoleted in 2002, which, it should be noted, was SIX YEARS AGO. As has been noted, you seem to be presupposing a remarkably complex solution to resolve a problem which is likely to be better handled via running VACUUM rather more frequently. -- output = reverse(ofni.sesabatadxunil @ enworbbc) http://www3.sympatico.ca/cbbrowne/postgresql.html Rules of the Evil Overlord #181. I will decree that all hay be shipped in tightly-packed bales. Any wagonload of loose hay attempting to pass through a checkpoint will be set on fire. http://www.eviloverlord.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] Replication Syatem
Basically we have some background process which updates table1 and we don't want the application to make any changes to table1 while vacuum. Vacuum requires exclusive lock on table1 and if any of the background or application is ON vacuum don't kick off. Thats the reason we need to get the site down. ~ Gauri On Tue, Apr 29, 2008 at 3:13 AM, Chris Browne [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Gauri Kanekar) writes: We have a table table1 which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming table1 take almost 30min and during that time the site is down. We need to cut down on this downtime.So thought of having a replication system, for which the replicated DB will be up during the master is getting vacuumed. Can anybody guide which will be the best suited replication solution for this. The only reason that it would be necessary for VACUUM to take the site down would be if you are running version 7.1, which was obsoleted in 2002, which, it should be noted, was SIX YEARS AGO. As has been noted, you seem to be presupposing a remarkably complex solution to resolve a problem which is likely to be better handled via running VACUUM rather more frequently. -- output = reverse(ofni.sesabatadxunil @ enworbbc) http://www3.sympatico.ca/cbbrowne/postgresql.html Rules of the Evil Overlord #181. I will decree that all hay be shipped in tightly-packed bales. Any wagonload of loose hay attempting to pass through a checkpoint will be set on fire. http://www.eviloverlord.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Regards Gauri
Re: [PERFORM] Replication Syatem
But unless we do full vacuum the space is not recovered. Thats y we prefer full vacuum. ~ Gauri On Tue, Apr 29, 2008 at 10:38 AM, Greg Smith [EMAIL PROTECTED] wrote: On Tue, 29 Apr 2008, Gauri Kanekar wrote: Basically we have some background process which updates table1 and we don't want the application to make any changes to table1 while vacuum. Vacuum requires exclusive lock on table1 and if any of the background or application is ON vacuum don't kick off. VACUUM FULL needs an exclusive lock, the regular one does not in 8.1. It's one of the reasons FULL should be avoided. If you do regular VACUUM frequently enough, you shouldn't ever need to do a FULL one anyway. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Tue, 29 Apr 2008, Gauri Kanekar wrote: Basically we have some background process which updates table1 and we don't want the application to make any changes to table1 while vacuum. Vacuum requires exclusive lock on table1 and if any of the background or application is ON vacuum don't kick off. VACUUM FULL needs an exclusive lock, the regular one does not in 8.1. It's one of the reasons FULL should be avoided. If you do regular VACUUM frequently enough, you shouldn't ever need to do a FULL one anyway. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Replication Syatem
Andrew, Can you explain me in detail why u said vacuum full is making the things worst. We do vacuum full, as vacuum verbose analyse dont regain space for us. ~ Gauri On Mon, Apr 28, 2008 at 9:52 PM, Andrew Sullivan [EMAIL PROTECTED] wrote: On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. VACUUM FULL is making your problem worse, not better. Don't do that. We are currently using 8.1.3 version. You need immediately to upgrade to the latest 8.1 stability and security release, which is 8.1.11. This is a drop-in replacement. It's an urgent fix for your case. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. You probably need to tune autovacuum not to do that table, and just vacuum that table in a constant loop or something. VACUUM should _never_ take the site down. If it does, you're doing it wrong. Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). I think you will find that no replication system will solve your underlying problems. That said, I happen to work for a company that will sell you a replication system to work with 8.1 if you really want it. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Regards Gauri
Re: [PERFORM] Replication Syatem
On Tue, Apr 29, 2008 at 10:41 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: But unless we do full vacuum the space is not recovered. Thats y we prefer full vacuum. There is no point in recovering the space by moving tuples and truncating the relation (that's what VACUUM FULL does) because you are doing frequent updates on the table and that would again extend the relation. If you run plain VACUUM, that would recover dead space and update the free space maps. It may not be able to reduce the table size, but you should not be bothered much about it because the following updates/inserts will fill in the fragmented free space. You may want to check your FSM settings as well to make sure that you are tracking free space properly. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] Replication Syatem
On Tue, Apr 29, 2008 at 11:16 AM, Gauri Kanekar [EMAIL PROTECTED] wrote: Andrew, Can you explain me in detail why u said vacuum full is making the things worst. 1. VACUUM FULL takes exclusive lock on the table. That makes table unavailable for read/writes. 2. VACUUM FULL moves live tuples around. When a tuple is moved, the old index entry is deleted and a new index entry is inserted. This causes index bloats which are hard to recover. We do vacuum full, as vacuum verbose analyse dont regain space for us. As I mentioned in the other reply, you are not gaining much by regaining space. The subsequent UPDATEs/INSERTs will quickly extend the relation and you loose all the work done by VACUUM FULL. Plain VACUUM will update FSM to track the free space scattered across the relation which is later reused by updates/inserts. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
