pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTuning
16+ messages / 14 participants
[nested] [flat]
* Tuning
@ 2008-03-24 22:56 Jack <jdetate@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Jack @ 2008-03-24 22:56 UTC (permalink / raw)
To: pgsql-admin
Is there a Postgres equivalent of tkprofs for tuning?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-03-25 15:29 Scott Marlowe <scott.marlowe@gmail.com>
parent: Jack <jdetate@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2008-03-25 15:29 UTC (permalink / raw)
To: Jack <jdetate@gmail.com>; +Cc: pgsql-admin
On Mon, Mar 24, 2008 at 4:56 PM, Jack <jdetate@gmail.com> wrote:
>
>
>
>
> Is there a Postgres equivalent of tkprofs for tuning?
you might want to provide an explanation of what tkprofs is and which
parts you're interested in emulating in pgsql.
Have you read up on explain, explain analyze, and the various
pg_stat_* tables? Do any of those help?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-03-26 12:24 Peter Kovacs <maxottovonstirlitz@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Peter Kovacs @ 2008-03-26 12:24 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Jack <jdetate@gmail.com>; pgsql-admin
tkprof generates performance characteristics for an arbitrary number
of SQL statements. The output includes measurements for the individual
SQL statements as well as cumulated values for the sequence of SQL
statements being measured. Data is presented in a two dimensional
format with the first dimension being the type of operation (parsing,
compiling, executing, fetching) and the second dimension being the
time spent in CPU or IO. You can see a sample output here:
http://www.jlcomp.demon.co.uk/tkprof_01.html .
The trick is that the Oracle code is instrumented to track all the
information necessary for these measurements. When tracing is enabled,
the measure values are output sequentially and this output is the
post-processed by tkprof. The raw (tracing) output is also fairly
readable, so if you want to dig deeper than the tkprof output, you can
use it (eventually post-processing it in your own way).
I am not familiar with PostgreSQL's explain analyze method, but as far
as I remember, it can be used only for one SQL statement and requires
a significant amount of practice to interpret easily.
BTW, I had a bookmark for a good tutorial on explain analyze, but the
page is no longer available:
http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10120 .
Can you suggest a good tutorial?
Thanks
Peter
On Tue, Mar 25, 2008 at 4:29 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Mar 24, 2008 at 4:56 PM, Jack <jdetate@gmail.com> wrote:
> >
> >
> >
> >
> > Is there a Postgres equivalent of tkprofs for tuning?
>
> you might want to provide an explanation of what tkprofs is and which
> parts you're interested in emulating in pgsql.
>
> Have you read up on explain, explain analyze, and the various
> pg_stat_* tables? Do any of those help?
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-03-26 12:39 Milen A. Radev <milen@radev.net>
parent: Peter Kovacs <maxottovonstirlitz@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Milen A. Radev @ 2008-03-26 12:39 UTC (permalink / raw)
To: pgsql-admin
Peter Kovacs написа:
[...]
> BTW, I had a bookmark for a good tutorial on explain analyze, but the
> page is no longer available:
> http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10120 .
> Can you suggest a good tutorial?
I'm not sure what was the tutorial at the above URL but this what I know:
http://redivi.com/~bob/oscon2005_pgsql_pdf/OSCON_Explaining_Explain_Public.pdf
(google for "explaining explain")
[...]
--
Milen A. Radev
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-03-26 13:15 Jonah H. Harris <jonah.harris@gmail.com>
parent: Jack <jdetate@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Jonah H. Harris @ 2008-03-26 13:15 UTC (permalink / raw)
To: Jack <jdetate@gmail.com>; +Cc: pgsql-admin
On Mon, Mar 24, 2008 at 6:56 PM, Jack <jdetate@gmail.com> wrote:
> Is there a Postgres equivalent of tkprofs for tuning?
No. But what you could do is combine several Postgres things to try
and get the same information.
IIRC, you could use:
- log_parser_stats
- log_planner_stats
- log_executor_stats
while running EXPLAIN ANALYZE on the query.
psql -U user1 -d postgres > /tmp/pgprof.out 2>&1 <<SQL
SET log_parser_stats TO on;
SET log_planner_stats TO on;
SET log_executor_stats TO on;
SET client_min_messages TO log;
EXPLAIN ANALYZE SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp;
SQL
This will output:
SET
SET
SET
SET
LOG: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.047635 elapsed 0.000000 user 0.000000 system sec
! [0.008000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 2/19 [10/726] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 2/0 [41/13] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000003 elapsed 0.000000 user 0.000000 system sec
! [0.008000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [10/726] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [41/14] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000002 elapsed 0.000000 user 0.000000 system sec
! [0.008000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [10/726] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [41/15] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.142050 elapsed 0.004000 user 0.000000 system sec
! [0.012000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/42 [10/786] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 7/0 [50/16] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 10 read, 0 written, buffer hit
rate = 75.61%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.075110 elapsed 0.000000 user 0.000000 system sec
! [0.012000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 1/6 [11/795] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 1/0 [51/17] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: EXECUTOR STATISTICS
DETAIL: ! system usage stats:
! 0.164599 elapsed 0.000000 user 0.000000 system sec
! [0.012000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 2/95 [12/884] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 8/1 [58/18] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 10 read, 0 written, buffer hit
rate = 80.77%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on emp (cost=0.00..5.25 rows=225 width=56) (actual
time=19.637..20.404 rows=150 loops=1)
Total runtime: 42.937 ms
(2 rows)
You could write a quick awk script to parse this out into something
more usable... but you get the idea.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Tuning
@ 2008-04-07 15:33 Carol Walter <walterc@indiana.edu>
0 siblings, 2 replies; 16+ messages in thread
From: Carol Walter @ 2008-04-07 15:33 UTC (permalink / raw)
To: pgsql-admin
I have some questions about tuning. The PostgreSQL documentation
says that you don't need to worry about index maintenance and tuning
with PostgreSQL. I'm used to systems that work a lot better if they
are periodically re-orged or re-indexed. Is it true that one need
not be concerned with this? I'm certain that the databases must
require some human intervention. What kind of tuning or other
intervention are you doing. What kind of tools are available and are
being used.
Carol Walter
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-07 16:27 Scott Marlowe <scott.marlowe@gmail.com>
parent: Carol Walter <walterc@indiana.edu>
1 sibling, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2008-04-07 16:27 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On Mon, Apr 7, 2008 at 9:33 AM, Carol Walter <walterc@indiana.edu> wrote:
> I have some questions about tuning. The PostgreSQL documentation says that
> you don't need to worry about index maintenance and tuning with PostgreSQL.
> I'm used to systems that work a lot better if they are periodically re-orged
> or re-indexed. Is it true that one need not be concerned with this? I'm
> certain that the databases must require some human intervention. What kind
> of tuning or other intervention are you doing. What kind of tools are
> available and are being used.
Not sure where in the docs it exactly says to never worry about your indexes.
While most access patterns can indeed be handled fine by autovacuum,
you can use the reindex command in pgsql to optimize your indexes if
they're chock full of empty space that can't be reclaimed by vacuum.
You can monitor things like index and table bloat with the pg_stat_*
series of views.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-07 16:45 Chris Browne <cbbrowne@acm.org>
parent: Carol Walter <walterc@indiana.edu>
1 sibling, 1 reply; 16+ messages in thread
From: Chris Browne @ 2008-04-07 16:45 UTC (permalink / raw)
To: pgsql-admin
walterc@indiana.edu (Carol Walter) writes:
> I have some questions about tuning. The PostgreSQL documentation
> says that you don't need to worry about index maintenance and tuning
> with PostgreSQL. I'm used to systems that work a lot better if they
> are periodically re-orged or re-indexed. Is it true that one need
> not be concerned with this? I'm certain that the databases must
> require some human intervention. What kind of tuning or other
> intervention are you doing. What kind of tools are available and are
> being used.
Well, there is one side to things where yes, indeed, "maintenance is
quite necessary," and that being in the area of "vacuuming."
<http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html;
The need to 'reindex' or 'reorg' tables is not non-existent, however
it is needed *way* less frequently than was the case in much older
versions of PostgreSQL. e.g. - with v7.2, there were patterns of
updates that would leave portions of indexes not usable, but the issue
was rectified in ~7.4, and people have not been observing problems
relating to this former scenario.
Back when we had systems on v7.2, we had to shut down every few months
and reindex some tables in order to keep performance OK. That's no
longer the case with systems running on v8.1, and as we bring 8.3 into
production, I expect even less need for manual interventions.
If you are running VACUUM and ANALYZE often enough, and autovacuum
pretty much does so, now, then there shouldn't be much need to do
"re-orging" of the system.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/nonrdbms.html
In the name of the Lord-High mutant, we sacrifice this suburban girl
-- `Future Schlock'
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-07 17:39 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 16+ messages in thread
From: Andrew Sullivan @ 2008-04-07 17:39 UTC (permalink / raw)
To: pgsql-admin
On Mon, Apr 07, 2008 at 12:45:38PM -0400, Chris Browne wrote:
> versions of PostgreSQL. e.g. - with v7.2, there were patterns of
> updates that would leave portions of indexes not usable, but the issue
> was rectified in ~7.4, and people have not been observing problems
> relating to this former scenario.
There remain use patterns that will leave the indexes in pretty bad shape.
This is an inherent limiation with btrees, though -- if you just unbalanced
the tree with a large number of deletes, there's nothing you can do except
REINDEX.
A
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-08 07:17 Michael Monnerie <michael.monnerie@it-management.at>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Michael Monnerie @ 2008-04-08 07:17 UTC (permalink / raw)
To: pgsql-admin
On Montag, 7. April 2008 Scott Marlowe wrote:
> You can monitor things like index and table bloat with the pg_stat_*
> series of views.
Are there scripts to automate this look at stats?
Or could one draw graphs from that values, to visualize how well the db
is? E.g., if you could calculate a % value, you could make RRD stats to
see it's change over time. Is there any project on this?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 193B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-08 07:37 paul rivers <rivers.paul@gmail.com>
parent: Michael Monnerie <michael.monnerie@it-management.at>
0 siblings, 1 reply; 16+ messages in thread
From: paul rivers @ 2008-04-08 07:37 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@it-management.at>; +Cc: pgsql-admin
Michael Monnerie wrote:
> On Montag, 7. April 2008 Scott Marlowe wrote:
>
>> You can monitor things like index and table bloat with the pg_stat_*
>> series of views.
>>
>
> Are there scripts to automate this look at stats?
> Or could one draw graphs from that values, to visualize how well the db
> is? E.g., if you could calculate a % value, you could make RRD stats to
> see it's change over time. Is there any project on this?
>
> mfg zmi
>
I don't see a direct way to monitor bloat from pg_stat*. If I'm wrong,
please set me straight.
For example, monitoring index bloat would involve deciding how many
pages an index would ideally consume, based on either sampling the table
yourself or raiding stats, and making assumptions. Then you'd compare
to actual (or approximated actual) and decide if you were within
whatever threshold you think is OK.
Paul
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-08 07:47 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 16+ messages in thread
From: Jeff Frost @ 2008-04-08 07:47 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@it-management.at>; +Cc: pgsql-admin
> Are there scripts to automate this look at stats?
> Or could one draw graphs from that values, to visualize how well the db
> is? E.g., if you could calculate a % value, you could make RRD stats to
> see it's change over time. Is there any project on this?
I don't know about rrd graphing it, but there are some great nagios plugins for this at http://bucardo.org/nagios
You could have a look at the queries used in those and roll your own mrtg plugins. If you do, please share.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Tuning
@ 2008-04-08 08:55 Michael Monnerie <michael.monnerie@it-management.at>
parent: paul rivers <rivers.paul@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Michael Monnerie @ 2008-04-08 08:55 UTC (permalink / raw)
To: pgsql-admin
On Dienstag, 8. April 2008 paul rivers wrote:
> I don't see a direct way to monitor bloat from pg_stat*. If I'm
> wrong, please set me straight.
>
> For example, monitoring index bloat would involve deciding how many
> pages an index would ideally consume, based on either sampling the
> table yourself or raiding stats, and making assumptions. Then you'd
> compare to actual (or approximated actual) and decide if you were
> within whatever threshold you think is OK.
If there's no rule of thumb, how could you manually decide on actions,
other than by reading from a crystal ball?
Also, if there are no tools, almost no admin can/will do anything.
Except for the specialist having too much free time to dig into the db
just for fun and reading tons of pg_stat* tables and values *g*
As most won't have that funny time, tools which show possible problems
or performance losses would greatly help.
On Dienstag, 8. April 2008 Jeff Frost wrote:
> I don't know about rrd graphing it, but there are some great nagios
> plugins for this at http://bucardo.org/nagios
>
> You could have a look at the queries used in those and roll your own
> mrtg plugins. If you do, please share.
Thank you. I'll have a look at that nagios plugins.
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 193B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* tuning
@ 2020-12-18 03:54 Ankush Chawla <ankushchawla03@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Ankush Chawla @ 2020-12-18 03:54 UTC (permalink / raw)
To: pgsql-admin
hi all
how to find the candidates tables or indexes for reindex or vacuum full
--
Best Regards,
Ankush Chawla
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: tuning
@ 2020-12-18 06:45 Keith <keith@keithf4.com>
parent: Ankush Chawla <ankushchawla03@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Keith @ 2020-12-18 06:45 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: pgsql-admin
On Thu, Dec 17, 2020 at 10:55 PM Ankush Chawla <ankushchawla03@gmail.com>
wrote:
> hi all
> how to find the candidates tables or indexes for reindex or vacuum full
>
>
> --
> Best Regards,
> Ankush Chawla
>
>
I've written a python script to help check for bloat which would indicate a
possible need for a reindex/vacuum.
https://github.com/keithf4/pg_bloat_check
Keith
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: tuning
@ 2020-12-18 07:12 bangalore umesh <bangalore.raj@gmail.com>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 16+ messages in thread
From: bangalore umesh @ 2020-12-18 07:12 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Ankush Chawla <ankushchawla03@gmail.com>; pgsql-admin
Hi
Please share the document how to do migration for oracle to postgres
using ora2pgtool.
I am working on Migration Project.
Thanks and Regards
Umesh
On Fri, Dec 18, 2020 at 12:16 PM Keith <keith@keithf4.com> wrote:
>
>
> On Thu, Dec 17, 2020 at 10:55 PM Ankush Chawla <ankushchawla03@gmail.com>
> wrote:
>
>> hi all
>> how to find the candidates tables or indexes for reindex or vacuum full
>>
>>
>> --
>> Best Regards,
>> Ankush Chawla
>>
>>
> I've written a python script to help check for bloat which would indicate
> a possible need for a reindex/vacuum.
>
> https://github.com/keithf4/pg_bloat_check
>
> Keith
>
--
Thanks and Regards
Rajshekhariah Umesh
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2020-12-18 07:12 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-24 22:56 Tuning Jack <jdetate@gmail.com>
2008-03-25 15:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-26 12:24 ` Peter Kovacs <maxottovonstirlitz@gmail.com>
2008-03-26 12:39 ` Milen A. Radev <milen@radev.net>
2008-03-26 13:15 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-04-07 15:33 Tuning Carol Walter <walterc@indiana.edu>
2008-04-07 16:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-04-08 07:17 ` Michael Monnerie <michael.monnerie@it-management.at>
2008-04-08 07:37 ` paul rivers <rivers.paul@gmail.com>
2008-04-08 08:55 ` Michael Monnerie <michael.monnerie@it-management.at>
2008-04-07 16:45 ` Chris Browne <cbbrowne@acm.org>
2008-04-07 17:39 ` Andrew Sullivan <ajs@crankycanuck.ca>
2008-04-08 07:47 Re: Tuning Jeff Frost <jeff@frostconsultingllc.com>
2020-12-18 03:54 tuning Ankush Chawla <ankushchawla03@gmail.com>
2020-12-18 06:45 ` Re: tuning Keith <keith@keithf4.com>
2020-12-18 07:12 ` Re: tuning bangalore umesh <bangalore.raj@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox