Q on SQL Performance tuning

2019-01-27 Thread Bhupathi, Kaushik (CORP)
Hi Team, I've few Questions on SQL perf tuning. 1) Is there any SQL monitoring report that's available in Oracle. Highlight of the report is it tells the % of time spent on CPU & IO. And which step took how much % in overall execution. 2) Is there anyway to know the historical execu

Re: Q on SQL Performance tuning

2019-01-27 Thread legrand legrand
Hi, There are many tools: - (core) extension pg_stat_statements will give you informations of SQL executions, - extension pgsentinel https://github.com/pgsentinel/pgsentinel gives the same results as Oracle ASH view - java front end PASH viewer https://github.com/dbacvetkov/PASH-Viewer gives

Re: Q on SQL Performance tuning

2019-01-27 Thread Justin Pryzby
On Sun, Jan 27, 2019 at 08:43:15AM +, Bhupathi, Kaushik (CORP) wrote: > 2) Is there anyway to know the historical execution plan details of a > particular SQL ? Per my understanding so far since there is no concept of > shared pool unlike Oracle every execution demands a new hard parse.

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Justin Pryzby
On Sun, Jan 27, 2019 at 01:09:16PM +0530, Saurabh Nanda wrote: > It seems that PGOPTIONS="-c synchronous_commit=off" has a significant > impact. However, I still can not understand why the TPS for the optimised > case is LOWER than the default for higher concurrency levels! Do you know which of th

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Saurabh Nanda
> Do you know which of the settings is causing lower TPS ? > I suggest to check shared_buffers. > I'm trying to find this, but it's taking a lot of time in re-running the benchmarks changing one config setting at a time. Thanks for the tip related to shared_buffers. > > If you haven't done it,

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Jeff Janes
> > > You could also try pg_test_fsync to get low-level information, to >> supplement the high level you get from pgbench. > > > Thanks for pointing me to this tool. never knew pg_test_fsync existed! > I've run `pg_test_fsync -s 60` two times and this is the output - > https://gist.github.com/saura

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Jeff Janes
On Sun, Jan 27, 2019 at 2:39 AM Saurabh Nanda wrote: > >> PGOPTIONS="-c synchronous_commit=off" pgbench -T 3600 -P 10 >> >> >> I am currently running all my benchmarks with synchronous_commit=off and >> will get back with my findings. >> > > > It seems that PGOPTIONS="-c synchronous_commit=o

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Saurabh Nanda
> > It is usually not acceptable to run applications with > synchronous_commit=off, so once you have identified that the bottleneck is > in implementing synchronous_commit=on, you probably need to take a deep > dive into your hardware to figure out why it isn't performing the way you > need/want/ex

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Saurabh Nanda
Here's the previous table again -- trying to prevent the wrapping. +++-+ || synchronous_commit=on | sync_commit=off | +++

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

2019-01-27 Thread Saurabh Nanda
All this benchmarking has led me to a philosophical question, why does PG need shared_buffers in the first place? What's wrong with letting the OS do the caching/buffering? Isn't it optimised for this kind of stuff?