Re: [PERFORM] How to tell what your postgresql server is doing
Stats are updated only after transaction ends. In case you have a really long transaction you need something else. To help myself I made a little Perl utility to parse strace output. It recognizes read/write calls, extracts file handle, finds the file name using information in /proc filesystem, then uses oid2name utility to translate file name to PostgreSQL relation name. See attachment. It works well enough for me, but I didn't take time to polish it. Basically it works with Linux /proc filesystem layout, expects PostgreSQL data directory to be /home/postgres/data and oid2name in /usr/lib/postgresql/bin. Usage is pgtrace . Tambet > -Original Message- > From: Jeff Frost [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 20, 2005 7:45 AM > To: pgsql-performance@postgresql.org > Subject: How to tell what your postgresql server is doing > > > Is there a way to look at the stats tables and tell what is > jamming up your > postgres server the most? Other than seeing long running > queries and watch > top, atop, iostat, vmstat in separate xterms...I'm wondering > if postgres keeps > some stats on what it spends the most time doing or if > there's a way to > extract that sort of info from other metrics it keeps in the > stats table? > > Maybe a script which polls the stats table and correlates the > info with stats > about the system in /proc? > > -- > Jeff Frost, Owner <[EMAIL PROTECTED]> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > pgtrace Description: pgtrace ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Question on REINDEX
>
> Josh Berkus writes:
> >> 1) When is it necessary to run REINDEX or drop/create
> >> an index? All I could really find in the docs is:
>
> > If you need to VACUUM FULL, you need to REINDEX as well.
> For example,
> > if you drop millions of rows from a table.
>
> That's probably a pretty good rule of thumb. It's worth
> noting that VACUUM FULL tends to actively bloat indexes, not
> reduce them in size, because it has to create new index
> entries for the rows it moves before it can delete the old
> ones. So if a VACUUM FULL moves many rows you are likely to
> see the indexes get bigger not smaller.
>
Is my current understanding correct:
1) VACUUM defragments each page locally - moves free space to the end of
page.
2) VACUUM FULL defragments table globally - tries to fill up all
partially free pages and deletes all resulting empty pages.
3) Both VACUUM and VACUUM FULL do only local defragment for indexes.
4) If you want indexes to become fully defragmented, you need to
REINDEX.
If you happen to use triggers for denormalization, like I do, then you
have a lot of updates, which means that tables and indexes become quicky
cluttered with pages, which contain mostly dead tuples. If those tables
and indexes fill up shared buffers, then PostgreSQL slows down, because
it has to do a lot more IO than normal. Regular VACUUM FULL helped, but
I needed REINDEX as well, otherwise indexes grew bigger than tables
itself!
> > Better to up your max_fsm_pages and do regular VACUUMs regularly and
> > frequently so that you don't have to REINDEX at all.
>
> Yes, definitely. Also consider using CLUSTER rather than
> VACUUM FULL when you need to clean up after massive deletions
> from a table. It's not any less intrusive in terms of
> locking, but it's often faster and it avoids the index bloat
> problem (since it effectively does a REINDEX).
>
Hmm, thanks for a tip. BTW, is output of
select count(1), sum(relpages) from pg_class where relkind in
('r','i','t')
good estimate for max_fsm_relations and max_fsm_pages?
Are these parameters used only during VACUUM or in runtime too?
Tambet
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [PERFORM] performance - triggers, row existence etc.
... > > 2) Is there some (performance) difference between BEFORE and AFTER >triggers? I believe there's no measurable difference. > BEFORE triggers might be faster, because you get a chance to reject the record before it is inserted into table. Common practice is to put validity checks into BEFORE triggers and updates of other tables into AFTER triggers. See also http://archives.postgresql.org/pgsql-sql/2005-04/msg00088.php. > 3) Vast majority of SQL commands inside the trigger checks > whether there >exists a row that suits some conditions (same IP, visitor ID etc.) >Currently I do this by > >SELECT INTO tmp id FROM ... JOIN ... WHERE ... LIMIT 1 >IF NOT FOUND THEN > >END IF; > >and so on. I believe this is fast and low-cost solution (compared >to the COUNT(*) way I've used before), but is there some > even better >(faster) way to check row existence? > You could save one temporary variable by using PERFORM: PERFORM 1 FROM ... JOIN ... WHERE ... LIMIT 1; IF NOT FOUND THEN ... END IF; You might want to consider, if you need FOR UPDATE in those queries, so that the referenced row maintains it's state until the end of transaction. BTW, foreign keys weren't enough? Tambet ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Weird index scan
I'm getting weird results for one of my queries. The actual time of this
index scan doesn't make any sense:
-> Index Scan using dok_dok_fk_i on dokumendid a (cost=0.00..566.24
rows=184 width=8) (actual time=0.170..420806.563 rows=1 loops=1)
dok_dok_fk_i is index on dokumendid(dok_dok_id). Currently it contains
mostly NULLs:
pos1=# select dok_dok_id, count(1) from dokumendid group by dok_dok_id;
dok_dok_id | count
+---
| 11423
8034 |76
(2 rows)
If I drop the index, seq scan + sort is used instead and everything is
fast again.
The PostgreSQL version:
pos1=# select version();
version
--
PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.4 (Debian 1:3.3.4-9)
(1 row)
The full EXPLAIN ANALYZE output:
pos1=# explain analyze select * from v_inventuuri_vahed_kaubagrupiti;
QUERY PLAN
-
Subquery Scan v_inventuuri_vahed_kaubagrupiti (cost=50896.04..50896.61
rows=46 width=128) (actual time=437007.670..437007.817 rows=45 loops=1)
-> Sort (cost=50896.04..50896.15 rows=46 width=42) (actual
time=437007.664..437007.692 rows=45 loops=1)
Sort Key: (COALESCE(sum(ir.summa_kmta), 0::numeric))::raha
-> HashAggregate (cost=50893.85..50894.77 rows=46 width=42)
(actual time=437007.229..437007.488 rows=45 loops=1)
-> Hash Join (cost=5533.44..50807.93 rows=5728
width=42) (actual time=436226.533..436877.499 rows=16271 loops=1)
Hash Cond: ("outer".kau_kau_id = "inner".kau_id)
-> Merge Right Join (cost=4759.52..49858.92
rows=15696 width=26) (actual time=436117.333..436600.653 rows=16271
loops=1)
Merge Cond: (("outer".dok_dok_id =
"inner".dok_id) AND ("outer".kau_kau_id = "inner".kau_kau_id))
-> Index Scan using dor_dok_kau_i on
dokumentide_read ar (cost=0.00..42789.44 rows=480962 width=19) (actual
time=0.023..7873.117 rows=205879 loops=1)
-> Sort (cost=4759.52..4798.76 rows=15696
width=19) (actual time=428381.719..428392.204 rows=16271 loops=1)
Sort Key: a.dok_id, ir.kau_kau_id
-> Merge Left Join
(cost=0.00..3665.65 rows=15696 width=19) (actual time=0.245..428279.595
rows=16258 loops=1)
Merge Cond: ("outer".dok_id =
"inner".dok_dok_id)
-> Nested Loop
(cost=0.00..3620.23 rows=15696 width=19) (actual time=0.063..7243.529
rows=16258 loops=1)
-> Index Scan using dok_pk
on dokumendid i (cost=0.00..3.73 rows=1 width=4) (actual
time=0.030..0.035 rows=1 loops=1)
Index Cond: (dok_id =
8034)
Filter: (tyyp =
'IN'::bpchar)
-> Index Scan using
dor_dok_fk_i on dokumentide_read ir (cost=0.00..3459.55 rows=15696
width=19) (actual time=0.023..7150.257 rows=16258 loops=1)
Index Cond: (8034 =
dok_dok_id)
-> Index Scan using dok_dok_fk_i
on dokumendid a (cost=0.00..566.24 rows=184 width=8) (actual
time=0.170..420806.563 rows=1 loops=1)
Filter: (tyyp =
'IA'::bpchar)
-> Hash (cost=757.71..757.71 rows=6487 width=24)
(actual time=109.178..109.178 rows=0 loops=1)
-> Hash Join (cost=15.56..757.71 rows=6487
width=24) (actual time=1.787..85.554 rows=17752 loops=1)
Hash Cond: ("outer".kag_kag_id =
"inner".a_kag_id)
-> Seq Scan on kaubad k
(cost=0.00..588.52 rows=17752 width=8) (actual time=0.005..30.952
rows=17752 loops=1)
-> Hash (cost=15.35..15.35 rows=83
width=24) (actual time=1.770..1.770 rows=0 loops=1)
-> Hash Join (cost=5.39..15.35
rows=83 width=24) (actual time=0.276..1.491 rows=227 loops=1)
Hash Cond:
("outer".y_kag_id = "inner".kag_id)
-> Seq Scan on
kaubagruppide_kaubagrupid gg (cost=0.00..7.09 rows=409 width=8) (actual
time=0.004..0.405 rows=409 loops=1)
-> Hash (cost=5.27..5.27
rows=46 width=20) (actual time=0.259..0.259 rows=0 loops=1)
-> Seq Scan on
kaubagrupid g (cost=0.00.
[PERFORM] SQL function inlining (was: View vs function)
I observed slowdowns when I declared SQL function as strict. There were no slowdowns, when I implmented the same function in plpgsql, in fact it got faster with strict, if parameters where NULL. Could it be side-effect of SQL function inlining? Is there CASE added around the function to not calculate it, when one of the parameters is NULL? The functions: create or replace function keskmine_omahind(kogus, raha) returns raha language sql immutable strict as ' SELECT CASE WHEN $1 > 0 THEN $2 / $1 ELSE NULL END::raha; '; create or replace function keskmine_omahind2(kogus, raha) returns raha language plpgsql immutable strict as ' BEGIN RETURN CASE WHEN $1 > 0 THEN $2 / $1 ELSE NULL END::raha; END; '; With strict: epos=# select count(keskmine_omahind(laokogus, laosumma)) from kaubad; count --- 9866 (1 row) Time: 860,495 ms epos=# select count(keskmine_omahind2(laokogus, laosumma)) from kaubad; count --- 9866 (1 row) Time: 178,922 ms Without strict: epos=# select count(keskmine_omahind(laokogus, laosumma)) from kaubad; count --- 9866 (1 row) Time: 88,151 ms epos=# select count(keskmine_omahind2(laokogus, laosumma)) from kaubad; count --- 9866 (1 row) Time: 178,383 ms epos=# select version(); version -- PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9) Tambet > -Original Message- > From: Neil Conway [mailto:[EMAIL PROTECTED] > Sent: Monday, March 21, 2005 7:13 AM > To: Bruno Wolff III > Cc: Keith Worthington; pgsql-performance@postgresql.org > Subject: Re: View vs function > > > Bruno Wolff III wrote: > > Functions are just black boxes to the planner. > > ... unless the function is a SQL function that is trivial > enough for the > planner to inline it into the plan of the invoking query. > Currently, we > won't inline set-returning SQL functions that are used in the query's > rangetable, though. This would be worth doing, I think -- I'm > not sure > how much work it would be, though. > > -Neil > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] What about utility to calculate planner cost constants?
I was following the cpu_tuple_cost thread and wondering, if it could be possible to make PQA style utility to calculate configuration-specific values for planner cost constants. It could make use of output of log_(statement|parser|planner|executor)_stats, tough I'm not sure if the output contains anything useful for those purposes. Otherwise it could just collect statements, run EXPLAIN ANALYZE for all of them and then play with planner cost constants to get the estimated values as close as possible to actual values. Something like Goal Seek in Excel, if you pardon my reference to MS :). Somewhat similar project seems to be pgautotune from pgFoundry, but it only considers shared_buffers, sort_mem and vacuum_mem. And it seems to use synthetic data instead of actual database and actual statements from log. And it has been inactive for a while. Tambet ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] One tuple per transaction
> -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 15, 2005 11:38 AM > To: Tambet Matiisen > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] One tuple per transaction > ... > > > Consider the often suggested solution for speeding up > "select count(*) > > from table" query: make another table rowcounts and for each of the > > original tables add insert and delete triggers to update > row count in > > rowcounts table. Actually this is standard denormalization > technique, > > which I use often. For example to ensure that order.total = > > sum(order_line.total). > > This does of course completely destroy concurrency. Since you need to > lock the summary table, other clients have to wait until you are done. > Yes, it does for rowcounts table. But consider the orders example - it only locks the order which I add lines. As there is mostly one client dealing with one order, but possibly thousands dealing with different orders, it should not pose any concurrency restrictions. > > Now, if typical inserts into your most active table occur > in batches > > of 3 rows, in one transaction, then row count for this table is > > updated 3 times during transaction. 3 updates generate 3 > tuples, while > > 2 of them are dead from the very start. You effectively commit 2 > > useless tuples. After millions of inserts you end up with rowcounts > > table having 2/3 of dead tuples and queries start to slow down. > > > > Current solution is to vacuum often. My proposal was to create new > > tuple only with first update. The next updates in the same > transaction > > would update the existing tuple, not create a new. > > How do you roll back to a savepoint with this model? > Every savepoint initiates a new (sub)transaction. Tambet ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] One tuple per transaction
> -- > > Date: Mon, 14 Mar 2005 09:41:30 +0800 > From: "Qingqing Zhou" <[EMAIL PROTECTED]> > To: pgsql-performance@postgresql.org > Subject: Re: One tuple per transaction > Message-ID: <[EMAIL PROTECTED]> > > ""Tambet Matiisen"" <[EMAIL PROTECTED]> writes ... > > If I'm correct, the dead tuples must be scanned also during > table and > > index scan, so a lot of dead tuples slows down queries > considerably, > > especially when the table doesn't fit into shared buffers any more. > > And as I'm in transaction, I can't VACUUM to get rid of > those tuples. > > In one occasion the page count for a table went from 400 to > 22000 at > > the end. > > Not exactly. The dead tuple in the index will be scanned the > first time (and its pointed heap tuple as well), then we will > mark it dead, then next time we came here, we will know that > the index tuple actually points to a uesless tuple, so we > will not scan its pointed heap tuple. > But the dead index tuple will still be read from disk next time? Maybe really the performance loss will be neglible, but if most of tuples in your table/index are dead, then it might be significant. Consider the often suggested solution for speeding up "select count(*) from table" query: make another table rowcounts and for each of the original tables add insert and delete triggers to update row count in rowcounts table. Actually this is standard denormalization technique, which I use often. For example to ensure that order.total = sum(order_line.total). Now, if typical inserts into your most active table occur in batches of 3 rows, in one transaction, then row count for this table is updated 3 times during transaction. 3 updates generate 3 tuples, while 2 of them are dead from the very start. You effectively commit 2 useless tuples. After millions of inserts you end up with rowcounts table having 2/3 of dead tuples and queries start to slow down. Current solution is to vacuum often. My proposal was to create new tuple only with first update. The next updates in the same transaction would update the existing tuple, not create a new. But as I'm writing this, I'm starting to get some of the associated implementation problems. The updated tuple might not be the same size as previous tuple. Tuple updates are probably not implemented anyway. And for a reason, as disk write takes the same time, regardless if you update or write new data. And tons of other problems, which developers are probably more aware of. But one thing still bothers me. Why is new index tuple generated when I update non-indexed column? OK, I get it again. Index tuple points to heap tuple, thus after update it would point to dead tuple. And as it takes the same time to update pointer or to write a new tuple, it's easier to write a new. Case closed. Tambet ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] One tuple per transaction
> -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED] > Sent: Sunday, March 13, 2005 12:05 AM > To: Tambet Matiisen > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] One tuple per transaction > > > Tambet, > > > In one of our applications we have a database function, which > > recalculates COGS (cost of good sold) for certain period. This > > involves deleting bunch of rows from one table, inserting > them again > > in correct order and updating them one-by-one (sometimes one row > > twice) to reflect current state. The problem is, that this > generates > > an enormous amount of tuples in that table. > > Sounds like you have an application design problem ... how > about re-writing > your function so it's a little more sensible? > I agree, that I have violated the no 1 rule of transactions - don't make the transaction last too long. But imagine a situation, where a table is updated twice in transaction. Why? Perhaps programmer felt, that the code is more modular in this way. Now if you have tons of those transactions, the I/O throughput is twice as big as it could be, because every transaction creates two tuples instead of one. One tuple per transaction could allow the programmer to keep his modular code and benefit from the increased performance. Tambet ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] One tuple per transaction
Hi! In one of our applications we have a database function, which recalculates COGS (cost of good sold) for certain period. This involves deleting bunch of rows from one table, inserting them again in correct order and updating them one-by-one (sometimes one row twice) to reflect current state. The problem is, that this generates an enormous amount of tuples in that table. If I'm correct, the dead tuples must be scanned also during table and index scan, so a lot of dead tuples slows down queries considerably, especially when the table doesn't fit into shared buffers any more. And as I'm in transaction, I can't VACUUM to get rid of those tuples. In one occasion the page count for a table went from 400 to 22000 at the end. All this made me wonder, why is new tuple created after every update? One tuple per transaction should be enough, because you always commit or rollback transaction as whole. And my observations seem to indicate, that new index tuple is created after column update even if this column is not indexed. One tuple per transaction would save a loads of I/O bandwidth, so I believe there must be a reason why it isn't implemented as such. Or were my assumptions wrong, that dead tuples must be read from disk? Tambet ---(end of broadcast)--- TIP 8: explain analyze is your friend
