Thread: 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@web.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 isresponsible for (all measurements are single query only). I think that kind of behaviour is somewhat expected. Run1: 26.559sRun2: 28.280s Unfortunately the variance between the runs seems to remain high. In fact I have exclusive access tothe machine and I made sure not to run in any i/o related problems regarding buffer caches. Are there any other stumblingblocks I'm missing at the moment? Maybe I've to rethink my (end-to-end) measurement strategy. In your experiencehow small is it possible to get measurement variances on Postgres? Thank you very much. Kind regards, Dennis <br/><hr align="left" width="300" /> View this message in context: <a href="http://postgresql.1045698.n5.nabble.com/Quantify-small-changes-to-predicate-evaluation-tp5807190p5807542.html">Re: Quantifysmall changes to predicate evaluation</a><br /> Sent from the <a href="http://postgresql.1045698.n5.nabble.com/PostgreSQL-hackers-f1928748.html">PostgreSQL- hackers mailing list archive</a>at Nabble.com.<br />
On Tue, Jun 17, 2014 at 10:23 AM, Dennis Butterstein <soullinuxer@web.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@web.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