From: Pietro Pugni <pietro.pugni@gmail.com>
To: didier <did447@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
Date: Fri, 3 Apr 2015 15:43:02 +0200
Message-ID: <0E6681E8-7412-4A8F-8AEC-FD6A29820B66@gmail.com> (raw)
In-Reply-To: <CAJRYxuKGW543ZdaZ3xmRVePo7Q=2xebv0NCo5qTejCv6UoZvvA@mail.gmail.com>
References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com>
<CAMkU=1wsRYdWrQ2m5wshTJw41wncOJeHXATxzVbCbYXbmoxTFQ@mail.gmail.com>
<2F18FF84-5408-4AE8-9F2B-9110F76DED86@gmail.com>
<CAJRYxu+B09b2E6Oad7a7gcTWFRJNHDunY7zTb78==a7yvPF9Jg@mail.gmail.com>
<FDDBF5B8-99E0-412C-8863-02145167218C@gmail.com>
<CAJRYxuKGW543ZdaZ3xmRVePo7Q=2xebv0NCo5qTejCv6UoZvvA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi didier,
thank you for your time.
I forgot to display before the output of free. I’ve looked into it before and I found difficult to fully understand if there was something wrong.
Before starting Postgres:
total used free shared buffers cached
Mem: 125G 9G 115G 15M 362M 8.1G
-/+ buffers/cache: 1.5G 124G
Swap: 127G 0B 127G
Here’s an example of free output when queries B_1 and B_2 are running (they’re part of the same transaction). Generally values remains the same. For what I can understand, RAM isn’t used at all (there’s a lot of unused RAM).
total used free shared buffers cached
Mem: 125G 13G 112G 3.1G 362M 11G
-/+ buffers/cache: 1.9G 123G
Swap: 127G 0B 127G
With Postgres running after transaction has been executed:
total used free shared buffers cached
Mem: 125G 13G 112G 3.1G 362M 11G
-/+ buffers/cache: 1.5G 124G
Swap: 127G 0B 127G
> there's also huge page
> /sys/kernel/mm/transparent_hugepage/enabled
> can you try to disable it?
It was enabled and after disabling it nothing changed: time execution is practically the same (131s for the same transaction tested in previous emails, which is composed by queries B_1 and B_2).
> Also test on the dell:
> select tmp.cf, tmp.dt from grep_studi.tmp;
> and
> select tmp.cf, tmp.dt from grep_studi.tmp order by tmp.cf;
> in Query B_2
> the sort is 9 time slower on the dell, you have to find why…
Here’s the output for the two queries:
> select tmp.cf, tmp.dt from grep_studi.tmp;
"Seq Scan on grep_studi.tmp (cost=0.00..11007.74 rows=1346868 width=72) (actual time=0.082..618.709 rows=2951191 loops=1)"
" Output: cf, dt"
" Buffers: shared hit=512 read=7802 dirtied=8314"
"Planning time: 0.087 ms"
"Execution time: 745.505 ms"
> select tmp.cf, tmp.dt from grep_studi.tmp;
"Sort (cost=38431.55..39104.99 rows=1346868 width=72) (actual time=3146.548..3306.179 rows=2951191 loops=1)"
" Output: cf, dt"
" Sort Key: tmp.cf"
" Sort Method: quicksort Memory: 328866kB"
" Buffers: shared hit=8317"
" -> Seq Scan on grep_studi.tmp (cost=0.00..11007.74 rows=1346868 width=72) (actual time=0.012..373.346 rows=2951191 loops=1)"
" Output: cf, dt"
" Buffers: shared hit=8314"
"Planning time: 0.034 ms"
"Execution time: 3459.065 ms"
> 32 GB for buffers is too high for the queries in your test but it
> doesn't matter.
I’ve set shared_buffers to be 1/4 of the total RAM. I’ve changed kernel values to accomodate this value. Lowering to smaller values doesn’t improve the transaction results. Here’s the results with 1 run for each level of shared_buffers:
32GB: 131s
16GB: 132s
8GB: 133s
4GB: 132s
2GB: 143s
1GB: 148s
512MB: 183s
256MB: 192s
Probably I can keep 4GB but I make use of several partitions with tens of millions of records each. This is why I keep shared_buffers high. My applications is also similar to a DWH solution with one user. Like you said, big values of shared_buffers shouldn’t be a issue.
I’ve done some tests with sysbench on Dell T420 and MacMini.
T420 - RAM READ - 16GB / 1MB
sh-4.3# sysbench --test=memory --memory-oper=read --memory-block-size=1MB --memory-total-size=16GB run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing memory operations speed test
Memory block size: 1024K
Memory transfer size: 16384M
Memory operations type: read
Memory scope type: global
Threads started!
Done.
Operations performed: 16384 (3643025.32 ops/sec)
16384.00 MB transferred (3643025.32 MB/sec)
Test execution summary:
total time: 0.0045s
total number of events: 16384
total time taken by event execution: 0.0031
per-request statistics:
min: 0.00ms
avg: 0.00ms
max: 0.02ms
approx. 95 percentile: 0.00ms
Threads fairness:
events (avg/stddev): 16384.0000/0.00
execution time (avg/stddev): 0.0031/0.00
MacMini - RAM READ - 16GB / 1MB
server:sysbench Pietro$ ./sysbench --test=memory --memory-oper=read --memory-block-size=1MB --memory-total-size=16GB run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Threads started!
Operations performed: 16384 ( 5484.50 ops/sec)
16384.00 MB transferred (5484.50 MB/sec)
General statistics:
total time: 2.9873s
total number of events: 16384
total time taken by event execution: 2.9836s
response time:
min: 0.18ms
avg: 0.18ms
max: 0.24ms
approx. 95 percentile: 0.19ms
Threads fairness:
events (avg/stddev): 16384.0000/0.00
execution time (avg/stddev): 2.9836/0.00
T420 - RAM WRITE - 16GB / 1MB
sh-4.3# sysbench --test=memory --memory-oper=write --memory-block-size=1MB --memory-total-size=16GB run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing memory operations speed test
Memory block size: 1024K
Memory transfer size: 16384M
Memory operations type: write
Memory scope type: global
Threads started!
Done.
Operations performed: 16384 ( 8298.97 ops/sec)
16384.00 MB transferred (8298.97 MB/sec)
Test execution summary:
total time: 1.9742s
total number of events: 16384
total time taken by event execution: 1.9723
per-request statistics:
min: 0.12ms
avg: 0.12ms
max: 0.25ms
approx. 95 percentile: 0.12ms
Threads fairness:
events (avg/stddev): 16384.0000/0.00
execution time (avg/stddev): 1.9723/0.00
MacMini - RAM WRITE - 16GB / 1MB
server:sysbench Pietro$ ./sysbench --test=memory --memory-oper=write --memory-block-size=1MB --memory-total-size=16GB run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Threads started!
Operations performed: 16384 ( 5472.90 ops/sec)
16384.00 MB transferred (5472.90 MB/sec)
General statistics:
total time: 2.9937s
total number of events: 16384
total time taken by event execution: 2.9890s
response time:
min: 0.18ms
avg: 0.18ms
max: 0.32ms
approx. 95 percentile: 0.19ms
Threads fairness:
events (avg/stddev): 16384.0000/0.00
execution time (avg/stddev): 2.9890/0.00
T420 - CPU
sh-4.3# sysbench --test=cpu run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing CPU performance benchmark
Threads started!
Done.
Maximum prime number checked in CPU test: 10000
Test execution summary:
total time: 13.0683s
total number of events: 10000
total time taken by event execution: 13.0674
per-request statistics:
min: 1.30ms
avg: 1.31ms
max: 1.44ms
approx. 95 percentile: 1.35ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 13.0674/0.00
MacMini - CPU
server:sysbench Pietro$ ./sysbench --test=cpu run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Primer numbers limit: 10000
Threads started!
General statistics:
total time: 11.5728s
total number of events: 10000
total time taken by event execution: 11.5703s
response time:
min: 1.15ms
avg: 1.16ms
max: 2.17ms
approx. 95 percentile: 1.17ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 11.5703/0.00
I’ve done these tests because someone else on this discussion asked me to investigate on memory bandwidth and because I found this interesting article about Intel Xeon vs Intel i5 with different Postgres versions: http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-pgbench
Hope this helps to better understand the problem.
Thank you very much.
Best regards,
Pietro=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pietro.pugni@gmail.com, did447@gmail.com
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
In-Reply-To: <0E6681E8-7412-4A8F-8AEC-FD6A29820B66@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox