Re: [PERFORM] How to reduce IOWAIT and CPU idle time?
On Sat, Sep 10, 2016 at 4:19 PM, Samir Magar wrote: > Hello, > > > My Application has normally 25 to 30 connections and it is doing lot of > insert/update/delete operation. > The database size is 100GB. > iowait is at 40% to 45 % and CPU idle time is at 45% to 50% > TOTAL RAM = 8 GB TOTAL CPU = 4 > > postgresql.conf parametre: > > > autovacuum = off > > That could be the source of your problem. Why autovacuum is turned off? Has database grown from 5GB to 100GB because of bloat or so much new data has been inserted? If it's a bloat, vacuum may not now be enough to recover from that and you would need a vacuum full. In general, it's not a good idea to turn autovacuum off. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [PERFORM] The same query - much different runtimes
On Mon, Apr 7, 2014 at 3:55 PM, Johann Spies wrote: > > > I then ran the query and the result was produced in about the same time as > (2945 ms). > > What can cause such a huge discrepancy? > May be when you reran the query, most of the data blocks were cached either in the shared buffers or the OS cache. That could drastically improve the performance. I can see a large number of shared buffer hits in the explain analyze output of the query ran through psql session. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [PERFORM] Fillfactor in postgresql 9.2
On Tue, Jul 2, 2013 at 5:15 PM, Albe Laurenz wrote: > Niels Kristian Schjødt wrote: > > I am experiencing a similar issue as the one mentioned in this post > > > http://stackoverflow.com/questions/3100072/postgresql-slow-on-a-large-table-with-arrays-and-lots-of- > > updates/3100232#3100232 > > However the post is written for a 8.3 installation, so I'm wondering if > the fillfactor problem is > > still roughly the same in 9.2, and hence would have a similar effect > when adjusted? > > Yes, lowering the fillfactor for a table will still > increase the chances of HOT updates, improving performance > and reducing the need for maintenance. > Our experience while testing HOT was a bit different (and I think that's why the default fillfactor was left unchanged). Even if you start at 100, the system quickly stabilizes after first update on every page. Even though the first update is a non-HOT update, the subsequent update on the same page will be a HOT update assuming there are no long running transactions and HOT gets a chance to clean up the dead space left by previous update. Having said that, it may not be a bad idea to start with a small free space in each page, may be just enough to hold one more row (plus a few more bytes for the line pointers etc). Also, you need to be careful about updating many rows on the same page in a single transaction or having an open long running transaction. They can easily stop HOT's ability to aggressively clean up dead space and stop the bloat. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [PERFORM] pgbench intriguing results: better tps figures for larger scale factor
On Wed, Feb 27, 2013 at 3:15 AM, Costin Oproiu wrote: > I took some time to figure out a reasonable tuning for my fresh 9.2.3 > installation when I've noticed the following: > > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -i -s 1 > ... > 10 tuples done. > ... > vacuum...done. > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -c 32 -t 5000 > ... > tps = 245.628075 (including connections establishing) > tps = 245.697421 (excluding connections establishing) > ... > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -i -s 100 > ... > 1000 tuples done. > ... > vacuum...done. > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -c 32 -t 5000 > ... > tps = 1125.035567 (including connections establishing) > tps = 1126.490634 (excluding connections establishing) > > 32 connections makes a comfortable load for the 8 core 4GB production > server, a rather old machine. I kept testing for almost two days with > various configuration parameters. In the beginning I was warned to > increase the checkpoint_segments, which is now 32. The results were > consistent and always showing small scale test (-s 1) at about 245-248 > tps while big scale test (-s 100) at least 4 and up to 7 times > better. > > According to top, at small scale tests, server processes are doing a > lot of UPDATE waiting. A "select relation::regclass, * from pg_locks > where not granted" showed frequent contention on tellers rows. > > First, I've got no good explanation for this and it would be nice to > have one. As far as I can understand this issue, the heaviest update > traffic should be on the branches table and should affect all tests. > Its not very surprising. The smallest table in the test i.e. pgbench_branches has the number of rows equal to the scale factor. When you test with scale factor 1 and 32 clients, all those clients are contending to update that single row in the table. Since a transaction must wait for the other updating transaction before it can update the same row, you would get a almost linear behaviour in this test. You may actually want to test with just 1 or 5 or 10 clients and my gut feel is you will still get the same or similar tps. As the scale factor is increased, the contention on the smaller tables reduces and you will start seeing an increase in the tps as you increase the number of clients. Of course, beyond a point either it will flatten out or even go down. While testing with pgbench, its recommended that the scale factor should be set larger than the number of clients. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Slow Query Help
On Wed, Feb 6, 2013 at 9:52 PM, Kevin Grittner wrote: > Will Platnick wrote: >> Will Platnick wrote: > >>> The only thing that stands out is: on your production server I see >>> "Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows >>> actual time as 0.179 ms. Not sure where that additional time is being >>> spent though. It could be ExecutorStart/End, but have no idea why they >>> should take so long. > >> Any ideas on how to troubleshoot this delay? > > Is the client which is running the query on the same machine as the > server? If not, what's the ping time between them? > I don't think the network latency can cause that. The "Total runtime" is calculated on the server side itself - see ExplainOnePlan(). Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Slow Query Help
On Tue, Feb 5, 2013 at 9:15 AM, Will Platnick wrote: > We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries > has raised significantly, especially during our peak time where lots of > users are logging in. According to New Relic, this query is now taking up > the most amount of time during peak activity and my pg_stat_activity and > slow log sampling agrees. We have 3 DB servers referenced here, production > running 9.2.2, semi-idle (idle except for replication when I ran the test) > running 9.2.2, and 9.1.3 completely idle with an old dump restored. > The only thing that stands out is: on your production server I see "Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows actual time as 0.179 ms. Not sure where that additional time is being spent though. It could be ExecutorStart/End, but have no idea why they should take so long. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] postgresql.conf recommendations
On Wed, Feb 6, 2013 at 3:32 AM, Johnny Tan wrote: > > maintenance_work_mem = 24GB # min 1MB I'm quite astonished by this setting. Not that it explains the problem at hand, but I wonder if this is a plain mistake in configuration. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Limit & offset effect on query plans
On Thu, Dec 13, 2012 at 9:38 AM, Amitabh Kant wrote: > Hi > > Our scripts automatically add "LIMIT ALL" & "OFFSET 0" to every select query > if no values are passed on for these parameters. I remember reading through > the mailing list that it's better not to pass them if they are not needed as > they add a cost to the query plan. Is this the case, or am i looking at a > very minor optimization. > I would tend to think that is the latter. While undoubtedly limit/offset clause will add another node during query planning and execution, AFAICS the OFFSET 0 and LIMIT ALL cases are optimized to a good extent. So the overhead of having them will not be significant. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
On Thu, Nov 29, 2012 at 6:06 PM, Andres Freund wrote: > On 2012-11-29 17:59:39 +0530, Pavan Deolasee wrote: > > > > > > > Yeah, that looks fairly easy to have. Thinking about it more, now that we > > have ability to skip WAL for the case when a table is created and > populated > > in the same transaction, we could also set the visibility map bits for > such > > a table (if we are not doing that already). That should be fairly safe > too. > > I don't think the latter would be safe at all. Every repeatable read > transaction that started before the table creation would see that tables > content based on the visibilitymap instead of seeing it as empty. > Yeah, but that should be easy to fix, no ? We know the transaction that created the table and we can check if that transaction is visible to our snapshot or not. If the creating transaction itself is not visible, the data in the table is not visible either. OTOH if the creating transaction is visible and is committed, we can trust the visibility map as well. Thats probably better than scanning the entire table just to find that we can/can't see all/any rows. Its getting slightly off-topic, so my apologies anyways. Thanks, Pavan
Re: [PERFORM] 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund wrote: > On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote: > > > Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits > > set to visible, thats an entirely different question. I don't think it > can, > > but then I haven't thought through this completely. > > It can't set everything to visible as it also copies RECENTLY_DEAD > tuples and tuples which are not yet visible to other transactions, but > it should be relatively easy to keep enough information about whether it > can set the current page to all visible. Yeah, that looks fairly easy to have. Thinking about it more, now that we have ability to skip WAL for the case when a table is created and populated in the same transaction, we could also set the visibility map bits for such a table (if we are not doing that already). That should be fairly safe too. Thanks, Pavan
Re: [PERFORM] 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau wrote: > Hello, > > I am toying around with 9.2.1, trying to measure/determine how > index-only scans can improve our performance. > > A small script which is attached to this mail, shows that as long > as the table has been VACUUM FULL'd, there is a unusual high > amount of heap fetches. It is strange that the visibilitymap_test > predicate fails in these situations, is the visibility map > somehow trashed in this situation? It should not, or at least the > documentation[1] should state it (my understanding is that vacuum > full does *more* than vacuum, but nothing less) (note to usual > anti vacuum full trollers: I know you hate vacuum full). > > I don't find it very surprising given that VACUUM FULL is now implemented as a CLUSTER command which rewrites the entire heap, thus invalidating all the visibility map info whatsoever. The code paths that VACUUM FULL and LAZY VACUUM takes are now completely different. Even with the old VACUUM FULL we would have seen some impact on heap fetches because it used to move tuples around and thus potentially resetting visibility map bits. But its definitely going to be worse with the new implementation. Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits set to visible, thats an entirely different question. I don't think it can, but then I haven't thought through this completely. Thanks, Pavan
Re: [PERFORM] Execution from java - slow
On Mon, Aug 27, 2012 at 6:07 PM, Jayadevan M wrote: > Hello all, > > I have a plpgsql function that takes a few seconds (less than 5) when > executed from psql. The same function, when invoked from java via a > prepared statement takes a few minutes. There are a few queries in the > function. Out of these, the first query takes input parameters for > filtering the data. It is this query which takes a long time when the > procedure is invoked from java. To ensure that the query does use actual > values (and not bind variables) for optimization, we used > > execute > ' > select x.col_type_desc,x.acc_id,acc_svr from (. > ' > using d_from_date,d_to_date > > It did not help. Any suggestions? It is from_date and to_date on which > data gets filtered. We are using the same values for filtering, when we > execute it from java/psql > > It looks highly unlikely that a function execution will take more time through different client interfaces. May be you want to log the function input parameters and see if they are coming different through these interfaces (I think you can use RAISE NOTICE for that). I'm not sure but client side encoding might also cause changes in the real values of the date parameters you are passing (e.g mm/dd/yy vs dd/mm/yy). So that will be worth checking as well. Thanks, Pavan
Re: [PERFORM] Vacuum problems with 9.1
On Tue, Aug 28, 2012 at 10:03 AM, Nimesh Satam wrote: > Hi, > > We have been using the current version of postgres i.e. 9.1.4 with > streaming replication on. While vacuuming we noticed that certain dead rows > are not getting removed and following debug information is printed: > > "DETAIL: 12560 dead row versions cannot be removed yet." > > As per suggestion, we made sure that no long running transactions are > active. Also all the applications were stopped during this time. > > Can anybody highlight the possible reason for the dead rows not been > cleaned? > > Are you absolutely sure that there are no other client connections open which are actively deleting/updating records ? The above message would usually come when certain rows which are otherwise DEAD (meaning, deleting or updating transaction has already committed) but can't be removed just yet because there is at least one old transaction that may still see the tuple as visible. If there are no open transactions, then I can only think about a concurrent auto-analyze running that can prevent some tuples from being vacuumed. What happens if you run the command again ? Do you get the exact same number again ? Also note that any concurrent transaction can cause this, even if the transaction does not access the table under vacuum operation. > FYI: We used the command VACUUM FULL ANALYZE VERBOSE table_name; command. > > I hope you are aware that VACUUM FULL is a costly operation because it rewrites the entire table again. You need VACUUM FULL only in cases of severe bloat. Otherwise a plain VACUUM (or auto-vacuum) should be enough to handle regular bloat. Thanks, Pavan
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On Wed, Jul 13, 2011 at 10:52 AM, Merlin Moncure wrote:
> On Tue, Jul 12, 2011 at 6:15 PM, lars wrote:
>> Back to the first case, here's an strace from the backend doing the select
>> right after the updates.
>> write(13,
>> "f\320\1\0\1\0\0\0\273\0\0\0\0\340\27\22`\32\0\0002833!000"..., 2400256)
>> = 2400256
>
> On Wed, Jul 13, 2011 at 9:46 AM, Kevin Grittner
> wrote:
>> Code comments indicate that they expect the pruning to be a pretty
>> clear win on multiple reads, although I don't know how much that was
>> benchmarked. Jeff does raise a good point, though -- it seems odd
>> that WAL-logging of this pruning would need to be synchronous. We
>> support asynchronous commits -- why not use that feature
>
> Right -- here are my thoughts. notice the above is writing out 293
> pages. this is suggesting to me that Kevin is right and you've
> identified a pattern where you are aggravating the page cleanup
> facilities of HOT. What threw me off here (and perhaps bears some
> additional investigation) is that early on in the report you were
> claiming an update to an indexed field which effectively disables HOT.
There are couple of other (very important) things that HOT does, but
probably its not advertised a lot. Even for non-HOT updates (which
means either indexed columns were changed or page ran out of free
space) or deletes, HOT prunes those tuples and instead mark the line
pointer as DEAD. The page is defragmented and dead space is recovered.
Each such dead tuple now only consumes two bytes in the page until
vacuum removes the dead line pointers. Thats the reason why OP is
seeing the behavior even when index columns are being updated.
We made a few adjustments to ensure that a page is not pruned too
early. So we track the oldest XID that did any updates/deletes to the
page and attempt pruning only when the RecentXmin is past the XID. We
also mark the page as "full" if some previous update did not find
enough free space to do in-block update and use that hint to decide if
we should attempt to prune the page. Finally, we prune only if we get
the cleanup lock without blocking.
What might be worth looking at this condition in pruneheap.c:
/*
* We prune when a previous UPDATE failed to find enough space on the page
* for a new tuple version, or when free space falls below the relation's
* fill-factor target (but not less than 10%).
*
* Checking free space here is questionable since we aren't holding any
* lock on the buffer; in the worst case we could get a bogus answer. It's
* unlikely to be *seriously* wrong, though, since reading either pd_lower
* or pd_upper is probably atomic. Avoiding taking a lock seems more
* important than sometimes getting a wrong answer in what is after all
* just a heuristic estimate.
*/
minfree = RelationGetTargetPageFreeSpace(relation,
HEAP_DEFAULT_FILLFACTOR);
minfree = Max(minfree, BLCKSZ / 10);
if (PageIsFull(page) || PageGetHeapFreeSpace(page) < minfree)
{
So if the free space in a page falls below the fill-factor or 10% of
the block size, we would try to prune the page. We probably need to
revisit this area and see if we need to tune HOT ever better. One
option could be to see how much space we are going to free and carry
out the operation only if its significant enough to justify the cost.
I know we had done several benchmarking tests while HOT development,
but the tuning mechanism still may not be perfect for all kinds of
work loads and it would probably never be.
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] caching written values?
On Thu, Jan 22, 2009 at 4:42 PM, Thomas Finneid wrote: > > As I understand it, data is stored in pages and those pages have to be > retrieved in order to write or read data from them. So my assumption is that > a page used to write data would not be replaced until memory is low and > different pages needs to be retrieved. Is this approximately correct? > Yes. That's how it works. 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
[PERFORM] Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow
2008/6/26 jay <[EMAIL PROTECTED]>: >If we can do commit very 1000 row per round, it may resolve the > problem. > But PG not support transaction within function yet? > Yeah, transaction control is not supported inside functions. There are some hacks using dblink to do transactions inside functions. You may want to check that out. I had suggested another hack in the past for very simplistic updates, when you are sure that the tuple length does not change between updates and you are ready to handle half updated table if there is a crash or failure in between. May be for your case, where you are updating a single column of the entire table and setting it to some default value for all the rows, it may work fine. But please be aware of data consistency issues before you try that. And it must be once in a lifetime kind of hack. http://postgresql-in.blogspot.com/2008/04/postgresql-in-place-update.html 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] I/O on select count(*)
On Thu, May 15, 2008 at 7:51 AM, Greg Smith <[EMAIL PROTECTED]> wrote: > > > So is vacuum helpful here because it will force all that to happen in one > batch? To put that another way: if I've run a manual vacuum, is it true > that it will have updated all the hint bits to XMIN_COMMITTED for all the > tuples that were all done when the vacuum started? > Yes. For that matter, even a plain SELECT or count(*) on the entire table is good enough. That will check every tuple for visibility and set it's hint bits. Another point to note is that the hint bits are checked and set on a per tuple basis. So especially during index scan, the same heap page may get rewritten many times. I had suggested in the past that whenever we set hint bits for a tuple, we should check all other tuples in the page and set their hint bits too to avoid multiple writes of the same page. I guess the idea got rejected because of lack of benchmarks to prove the benefit. 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 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
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
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
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
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
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
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
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
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
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] Exact index overhead
On Thu, Apr 17, 2008 at 2:57 PM, Gunther Mayer <[EMAIL PROTECTED]> wrote: > > > You see, all updates change most of the data fields but never ever touch > the time field. Assuming correct and efficient behaviour of postgresql it > should then also never touch the time index and incur zero overhead in its > presence, but is this really the case? Normally, whenever a row is updated, Postgres inserts a new index entry in each of the index. So to answer your question, there is certainly index overhead during updates, even if you are not changing the indexed column. But if you are using 8.3 then HOT may help you here, assuming you are not updating any index keys. HOT optimizes the case by *not* inserting a new index entry and also by performing retail vacuuming. The two necessary conditions for HOT are: 1. Update should not change any of the index keys. So if you have two indexes, one on column A and other on column B, update must not be modifying either A or B. 2. The existing block should have enough free space to accommodate the new version A less than 100 fillfactor may help you given your rate of updates. If your application satisfies 1, then I would suggest you to upgrade to 8.3 (if you are not using it already) and then you can create the index without bothering much about overheads. 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] EXPLAIN detail
On Wed, Apr 9, 2008 at 3:21 PM, Luigi N. Puleio <[EMAIL PROTECTED]> wrote: > Hello everyone!! > > I have a table with 17 columns and it has almost > 53 records and doing just a > > SELECT * FROM table > > with the EXPLAIN ANALYZE I get: > > Seq Scan on table (cost=0.00...19452.95 rows=529395 > width=170) (actual time=0.155...2194.294 rows=529395 > loops=1) > total runtime=3679.039 ms > > and this table has a PK... > Do you think is too much time for a simple select?... > Well, PK won't help you here because you are selecting all rows from the table and that seq scan is the right thing for that. Without knowing your hardware its difficult to judge if the time taken is more or not. Anyways, I don't think there is much tweaking you can do for such a query except making sure that your table is not bloated with dead tuples. 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] increasing shared buffer slow downs query performance.
On Mon, Mar 24, 2008 at 3:37 PM, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > petchimuthu lingam <[EMAIL PROTECTED]> schrieb: > > > > Hi friends, > > > > I am using postgresql 8.1, I have shared_buffers = 5, now i execute the > > query, it takes 18 seconds to do sequential scan, when i reduced to 5000, > it > > takes one 10 seconds, Why. > > Wild guess: the second time the data are in the filesystem cache. > Another wild possibility is that the first query sets the hint bits for the rows involved and hence the second time it runs fast. May be you want to run the query few times in both the settings and then compare. 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] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit
On Mon, Mar 10, 2008 at 4:31 PM, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > According > to oprofile, all the time is spent in TransactionIdIsInProgress. I think > it would be pretty straightforward to store the committed subtransaction > ids in a sorted array, instead of a linked list, and binary search. Assuming that in most of the cases, there will be many committed and few aborted subtransactions, how about storing the list of *aborted* subtransactions ? 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] 7 hrs for a pg_restore?
On Feb 19, 2008 11:53 PM, Jeff Davis <[EMAIL PROTECTED]> wrote: > > > Keep in mind, if you have several GB worth of indexes, they take up > basically no space in the logical dump (just the "CREATE INDEX" command, > and that's it). But they can take a lot of processor time to build up > again, especially with localized text. > > I think it would be interesting if we can build these indexes in parallel. Each index build requires a seq scan on the table. If the table does not fit in shared buffers, each index build would most likely result in lots of IO. One option would be to add this facility to the backend so that multiple indexes can be built with a single seq scan of the table. In theory, it should be possible, but might be tricky given the way index build works (it calls respective ambuild method to build the index which internally does the seq scan). Other option is to make pg_restore multi-threaded/processed. The synchronized_scans facility would then synchronize the multiple heap scans. ISTM that if we can make pg_restore mult-processed, then we can possibly add more parallelism to the restore process. My two cents. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] select count(*) performance (vacuum did not help)
On 9/24/07, Gábor Farkas <[EMAIL PROTECTED]> wrote: > > > INFO: "main_activity": found 41001 removable, 11672 nonremovable row > versions in 160888 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 14029978 unused item pointers. > 0 pages are entirely empty. > CPU 5.53s/1.71u sec elapsed 227.35 sec. > INFO: analyzing "public.main_activity" > INFO: "main_activity": 160888 pages, 4500 rows sampled, 4594 estimated > total rows > > Looking at the number of rows vs number of pages, ISTM that VACUUM FULL should help you. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
