Thread

1. performance contradiction

   Gabriel Dodan <gabriel.dodan@gmail.com> — 2017-01-23T16:55:04Z

   Hi All,
   
   I have two servers. On the first one I have postgresql version 9.6 . On the
   second one I have version 9.3 . I ran pgbench on both servers.
   
   First server results:
   scaling factor: 10
   query mode: simple
   number of clients: 1
   number of threads: 1
   duration: 10 s
   number of transactions actually processed: 4639
   latency average = 2.156 ms
   tps = 463.818971 (including connections establishing)
   tps = 464.017489 (excluding connections establishing)
   
   Second server results:
   scaling factor: 10
   query mode: simple
   number of clients: 1
   number of threads: 1
   duration: 10 s
   number of transactions actually processed: 3771
   tps = 377.084162 (including connections establishing)
   tps = 377.134546 (excluding connections establishing)
   So first server perform much better.
   
   BUT if I run a trivial select on both servers, on a similar table, the
   select perform much much better on second server!
   
   First server explain results:
   
   Limit  (cost=0.00..0.83 rows=30 width=33) (actual time=0.152..0.794 rows=30
   loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on symbols_tests  (cost=0.00..1.57 rows=57 width=33) (actual
   time=0.040..0.261 rows=30 loops=1)
           Buffers: shared hit=1
   Planning time: 0.282 ms
   Execution time: 1.062 ms
   Second server explain results:
   
   Limit  (cost=0.00..0.83 rows=30 width=622) (actual time=0.006..0.010
   rows=30 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on symbols_tests  (cost=0.00..1.57 rows=57 width=622)
   (actual time=0.006..0.007 rows=30 loops=1)
           Buffers: shared hit=1
   Total runtime: 0.020 ms
   
   Both servers have SSD. First server is a VPS, the second server is a
   dedicated server.
   
   Any idea why this contradiction ? If you need more details regarding server
   resources(CPU, memory etc) please let me know.
   
   Regards
   
   -- 
   Stock Monitor - Stock Analysis Made EASY <https://www.stockmonitor.com>
   

2. Re: performance contradiction

   Feike Steenbergen <feikesteenbergen@gmail.com> — 2017-02-15T08:25:11Z

   On 23 January 2017 at 17:55, Gabriel Dodan <gabriel.dodan@gmail.com> wrote:
   >
   > BUT if I run a trivial select on both servers, on a similar table, the
   select
   > perform much much better on second server!
   
   You're comparing two very different systems it seems, therefore you might be
   looking at difference in the performance of EXPLAIN, just getting timing
   information of your system may be the most expensive part[1], you could
   disable
   the timing explicity:
   
   EXPLAIN (ANALYZE ON, TIMING OFF) <query>
   
   And, there is something that stands out:
   
   So it seems there is also some difference in the data, we could validate the
   actual numbers:
   
   SELECT sum(pg_column_size(symbols_tests))/count(*) FROM symbols_tests;
   
   regards,
   
   Feike
   
   [1]
   https://www.postgresql.org/docs/current/static/using-explain.html#USING-EXPLAIN-CAVEATS