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
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
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.
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
> 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,
>
>
> 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
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
>
> 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
Here's the previous table again -- trying to prevent the wrapping.
+++-+
|| synchronous_commit=on | sync_commit=off |
+++
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?
10 matches
Mail list logo