Re: Quantify small changes to predicate evaluation

Hello everybody,

I am currently trying to squeeze some performance out of the current
predicate evaluation implementation. In fact I was able to get some positive
results.
I have some more points on my developement plan, but I am facing the problem
that theses changes will only help very little when talking about runtimes.

Therefore I am not able to quantify these changes at the moment as my
overall query runtimes do not seem to be stable across instance restarts.

Consider the following example:
Start postgres, run to warm up buffers
measure query wall clock time: 24.141s
Stop Postgres:

Start postgres, run to warm up buffers
measure query wall clock time: 25.845
Stop Postgres

I expect my current changes to be resposible for about 0.2-0.3s for this
query but because of the huge time differences I am not able to quantify my
changes.

Maybe somebody can tell me about a better approach to quantify my changes or
give me some input on how to get more stable postgres time measurements.

Thank you very much.

Kind regards

Dennis

If needed please find some configuration parameters below
max_connections = 2
superuser_reserved_connections = 1
ssl = off
shared_buffers = 20GB
max_prepared_transactions = 0
work_mem = 500MB
fsync = off
archive_mode = off
hot_standby = off
effective_cache_size = 20GB
logging_collector = off
debug_print_parse = off
debug_print_rewritten = off
debug_print_plan = off
debug_pretty_print = off
log_checkpoints = off
log_connections = off
log_disconnections = off
log_duration = off
log_hostname = off
track_activities = off
track_counts = off
track_io_timing = off
track_functions = none
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = off

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Quantify-small-changes-to-predicate-evaluation-tp5807190.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Fri, Jun 13, 2014 at 12:46 PM, Dennis Butterstein <soullinuxer(at)web(dot)de> wrote:
> I expect my current changes to be resposible for about 0.2-0.3s for this
> query but because of the huge time differences I am not able to quantify my
> changes.
>
> Maybe somebody can tell me about a better approach to quantify my changes or
> give me some input on how to get more stable postgres time measurements.

There can be other reasons, but for read-only benchmarks, much of this
variability seems to come from the operating system's power management
and scheduler.

I had some luck in reducing variance on Linux with some tricks.
Disable CPU frequency scaling:
for i in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor; do
echo performance > $i; done

Disable the turbo boost feature if your CPU supports it:
echo 1 > /sys/devices/system/cpu/intel_pstate/no_turbo

Always launch PostgreSQL and pgbench on a fixed CPU core, using "taskset -c3"

And exclude all other processes from this core (locking them to cores 0, 1, 2):
ps -A -o pid h |xargs -n1 taskset -cp -a 0-2 >/dev/null

Transparent hugepage support may also interfere:
echo never > /sys/kernel/mm/transparent_hugepage/defrag
echo never > /sys/kernel/mm/transparent_hugepage/enabled

I'm sure there are more tricks, but this should get you pretty far.

Regards,
Marti

Hi Marti,thank you for your quick reply.I tried the proposed tweaks and see
some differences regarding the measurements. It seems as if the overall
query performance dropped a little what I think the disabled turbo boost
mode is responsible for (all measurements are single query only). I think
that kind of behaviour is somewhat expected.Run1: 26.559sRun2:
28.280sUnfortunately the variance between the runs seems to remain high.In
fact I have exclusive access to the machine and I made sure not to run in
any i/o related problems regarding buffer caches.Are there any other
stumbling blocks I'm missing at the moment?Maybe I've to rethink my
(end-to-end) measurement strategy.In your experience how small is it
possible to get measurement variances on Postgres?Thank you very much.Kind
regards,Dennis

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Quantify-small-changes-to-predicate-evaluation-tp5807190p5807542.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Tue, Jun 17, 2014 at 10:23 AM, Dennis Butterstein <soullinuxer(at)web(dot)de> wrote:
> Hi Marti, thank you for your quick reply. I tried the proposed tweaks and
> see some differences regarding the measurements. It seems as if the overall
> query performance dropped a little what I think the disabled turbo boost
> mode is responsible for (all measurements are single query only). I think
> that kind of behaviour is somewhat expected. Run1: 26.559s Run2: 28.280s
> Unfortunately the variance between the runs seems to remain high. In fact I
> have exclusive access to the machine and I made sure not to run in any i/o
> related problems regarding buffer caches. Are there any other stumbling
> blocks I'm missing at the moment? Maybe I've to rethink my (end-to-end)
> measurement strategy. In your experience how small is it possible to get
> measurement variances on Postgres? Thank you very much. Kind regards, Dennis

I find that it's possible to get smaller variations than what you're
experiencing on read-only workloads without any special tuning, but
variation on workloads that write data is much higher, similar to what
you're seeing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jun 17, 2014 at 5:23 PM, Dennis Butterstein <soullinuxer(at)web(dot)de> wrote:
> I tried the proposed tweaks and
> see some differences regarding the measurements.
> Unfortunately the variance between the runs seems to remain high.

Using these techniques I managed to get standard deviation below 1.5%
in my read-only tests (and most below 1%). Not all workloads may be
able to achieve that, but your should be able to do better than your
results.

Maybe your cooling is not sufficient? It seems your 2nd run is always
slower than the first one, maybe the CPU is doing thermal throttling?
Lowering the max frequency might be something to try to resolve that,
like "cpupower frequency-set --max 2GHz"

How do you run your benchmark, are you using pgbench? Single threaded?
Is the client locked to the same CPU core?

Regards,
Marti