Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ye1sQ-0000Oq-GS for pgsql-performance@arkaria.postgresql.org; Fri, 03 Apr 2015 13:43:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Ye1sP-0003Qa-NK for pgsql-performance@arkaria.postgresql.org; Fri, 03 Apr 2015 13:43:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Ye1sO-0003QU-Jw for pgsql-performance@postgresql.org; Fri, 03 Apr 2015 13:43:12 +0000 Received: from mail-wi0-x235.google.com ([2a00:1450:400c:c05::235]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Ye1sJ-0003ZN-6o for pgsql-performance@postgresql.org; Fri, 03 Apr 2015 13:43:11 +0000 Received: by wizk4 with SMTP id k4so44759478wiz.1 for ; Fri, 03 Apr 2015 06:43:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=Hft10WvIRGFnqWJMDiOwQ0sSvw0Ee9xnW5N8cUhcg3Y=; b=lcV5TTcdrcFEMcx/4YLmVa35awiPFrq84uRPnSYMStRL1gmNG/wWSgVFrbvFCwEcnI gT+hUBJCYk5guzxYstanaFaRVajgYqC0keyKoCq61TVgTp3LuUv47sA5qCb0mzqSTxLV pUeIj4PtgPWlE0etXcEG9bR4DpuUFpAZdgWZmu3BTe/tCtG3I3tX8JLrcdbUnLN3i7J7 i/YgkAk08gIgRTNSpIzaVZVP86s2O/6t1XlwlGbIE3Z8dobyJidO+Ozba4mpSIWfry8j 0ZR9gbnwIZqZaqTeP6RSPNAl5kJN6zWhcRyxKiOPPCTOAVC9REDAWb5kgJPmenjPx0x9 pWFA== X-Received: by 10.180.206.98 with SMTP id ln2mr33694079wic.94.1428068584931; Fri, 03 Apr 2015 06:43:04 -0700 (PDT) Received: from [192.168.2.2] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by mx.google.com with ESMTPSA id r14sm2799668wiv.13.2015.04.03.06.43.03 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 03 Apr 2015 06:43:04 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_D48B9B6E-E1C1-45E2-A362-9521A99F2D9B" Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL From: Pietro Pugni In-Reply-To: Date: Fri, 3 Apr 2015 15:43:02 +0200 Cc: "pgsql-performance@postgresql.org" Message-Id: <0E6681E8-7412-4A8F-8AEC-FD6A29820B66@gmail.com> References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com> <2F18FF84-5408-4AE8-9F2B-9110F76DED86@gmail.com> To: didier X-Mailer: Apple Mail (2.1878.6) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_D48B9B6E-E1C1-45E2-A362-9521A99F2D9B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Hi didier, thank you for your time. I forgot to display before the output of free. I=92ve 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=92s an example of free output when queries B_1 and B_2 are running = (they=92re part of the same transaction). Generally values remains the = same. For what I can understand, RAM isn=92t used at all (there=92s 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=85 Here=92s the output for the two queries: > select tmp.cf, tmp.dt from grep_studi.tmp; "Seq Scan on grep_studi.tmp (cost=3D0.00..11007.74 rows=3D1346868 = width=3D72) (actual time=3D0.082..618.709 rows=3D2951191 loops=3D1)" " Output: cf, dt" " Buffers: shared hit=3D512 read=3D7802 dirtied=3D8314" "Planning time: 0.087 ms" "Execution time: 745.505 ms" > select tmp.cf, tmp.dt from grep_studi.tmp; "Sort (cost=3D38431.55..39104.99 rows=3D1346868 width=3D72) (actual = time=3D3146.548..3306.179 rows=3D2951191 loops=3D1)" " Output: cf, dt" " Sort Key: tmp.cf" " Sort Method: quicksort Memory: 328866kB" " Buffers: shared hit=3D8317" " -> Seq Scan on grep_studi.tmp (cost=3D0.00..11007.74 rows=3D1346868 = width=3D72) (actual time=3D0.012..373.346 rows=3D2951191 loops=3D1)" " Output: cf, dt" " Buffers: shared hit=3D8314" "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=92ve set shared_buffers to be 1/4 of the total RAM. I=92ve changed = kernel values to accomodate this value. Lowering to smaller values = doesn=92t improve the transaction results. Here=92s 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=92t be a issue. I=92ve done some tests with sysbench on Dell T420 and MacMini. T420 - RAM READ - 16GB / 1MB sh-4.3# sysbench --test=3Dmemory --memory-oper=3Dread = --memory-block-size=3D1MB --memory-total-size=3D16GB 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=3Dmemory --memory-oper=3Dread = --memory-block-size=3D1MB --memory-total-size=3D16GB 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=3Dmemory --memory-oper=3Dwrite = --memory-block-size=3D1MB --memory-total-size=3D16GB 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=3Dmemory --memory-oper=3Dwrite = --memory-block-size=3D1MB --memory-total-size=3D16GB 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=3Dcpu 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=3Dcpu 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=92ve 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-pgb= ench Hope this helps to better understand the problem. Thank you very much. Best regards, Pietro= --Apple-Mail=_D48B9B6E-E1C1-45E2-A362-9521A99F2D9B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
Hi didier,
thank you for your = time.
I forgot to display before the output of free. I=92ve = 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=92s an example of free = output when queries B_1 and B_2 are running (they=92re part of the same = transaction). Generally values remains the same. For what I can = understand, RAM isn=92t used at all (there=92s 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=85
Here=92s the output for = the two queries:

select tmp.cf, tmp.dt from = grep_studi.tmp;
"Seq Scan on grep_studi.tmp  (cost=3D0.00..11007.74 = rows=3D1346868 width=3D72) (actual time=3D0.082..618.709 rows=3D2951191 = loops=3D1)"
"  Output: cf, = dt"
"  Buffers: shared = hit=3D512 read=3D7802 dirtied=3D8314"
"Planning time: 0.087 ms"
"Execution time: 745.505 ms"

select tmp.cf, tmp.dt from = grep_studi.tmp;
"Sort  (cost=3D38431.55..39104.99 rows=3D1346868 = width=3D72) (actual time=3D3146.548..3306.179 rows=3D2951191 = loops=3D1)"
"  Output: cf, = dt"
"  Sort Key: = tmp.cf"
"  Sort Method: = quicksort  Memory: 328866kB"
"  Buffers: shared = hit=3D8317"
"  -> =  Seq Scan on grep_studi.tmp  (cost=3D0.00..11007.74 = rows=3D1346868 width=3D72) (actual time=3D0.012..373.346 rows=3D2951191 = loops=3D1)"
"     =    Output: cf, dt"
" =        Buffers: shared = hit=3D8314"
"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=92ve set = shared_buffers to be 1/4 of the total RAM. I=92ve changed kernel values = to accomodate this value. Lowering to smaller values doesn=92t improve = the transaction results. Here=92s 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=92t be a issue.

I=92ve = done some tests with sysbench on Dell T420 and = MacMini.

T420 - = RAM READ - 16GB / 1MB
sh-4.3# = sysbench --test=3Dmemory --memory-oper=3Dread --memory-block-size=3D1MB = --memory-total-size=3D16GB 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=3Dmemory --memory-oper=3Dread --memory-block-size=3D1MB = --memory-total-size=3D16GB 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=3Dmemory --memory-oper=3Dwrite --memory-block-size=3D1MB = --memory-total-size=3D16GB 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=3Dmemory --memory-oper=3Dwrite --memory-block-size=3D1MB= --memory-total-size=3D16GB 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=3Dcpu = 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=3Dcpu = 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=92ve 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
= --Apple-Mail=_D48B9B6E-E1C1-45E2-A362-9521A99F2D9B--