Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ye249-0000mT-31 for pgsql-performance@arkaria.postgresql.org; Fri, 03 Apr 2015 13:55:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Ye248-0007Py-EC for pgsql-performance@arkaria.postgresql.org; Fri, 03 Apr 2015 13:55:20 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Ye246-0007MS-Is for pgsql-performance@postgresql.org; Fri, 03 Apr 2015 13:55:19 +0000 Received: from mail-wg0-x232.google.com ([2a00:1450:400c:c00::232]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Ye23x-0002tI-QO for pgsql-performance@postgresql.org; Fri, 03 Apr 2015 13:55:16 +0000 Received: by wgin8 with SMTP id n8so22257552wgi.0 for ; Fri, 03 Apr 2015 06:55:07 -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=SF1HppKx5gO6ZL4BZLlDmlsBJoAPnhzRZgv4dRx5poc=; b=ZM4MRwtdoEm+ILgMCYKhSO5mrARw+HCIw/FqQ+iitEZlYpL/cJfmP+/SvtPsf19jjD tcr1zGPc3+vQi4pFSTqZ85BwO56o4gpduQAXJi/eRCr3SDSyb8jyhxuV0Wo9PUQ+AZAZ q+9HR3yg6oj03WXWVOWtmtx1/qbYq3S93vL4YxlGQMe8Nv8+i7LTpo8OtWK2V0I0HXdl gkbsgvLqUi4/Xft3diX4Lmf1j702lQmIC2kTqjF1HbyyRsLmQoXy1wjhFzlFTI2zKiAq IBpkm3Hn3/N5w7bYAsz4AfroMETzY/1oDOJ0KDHi2u/PjWdMCl7Of+Pu3p1BKm36BdaO mOdA== X-Received: by 10.181.8.99 with SMTP id dj3mr34437927wid.83.1428069307353; Fri, 03 Apr 2015 06:55:07 -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 k6sm2846073wia.6.2015.04.03.06.55.06 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 03 Apr 2015 06:55:06 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_DFFC7DF6-E968-4F81-B3EA-31577BB008F9" 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:55:04 +0200 Cc: pgsql-performance@postgresql.org Message-Id: <76EF504C-06AF-471F-8BCD-AFD2B6F85178@gmail.com> References: <87131335.225702.1427941178942.JavaMail.root@fmed.uba.ar> <2EBBCD54-D3C8-436F-AF73-84EEF258AB70@gmail.com> To: Aidan Van Dyk 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=_DFFC7DF6-E968-4F81-B3EA-31577BB008F9 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Hi Aidan, thank you again for your support. I found an interesting article showing better performance from a Intel = i5 vs a Intel Xeon on different Postgres versions: = http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-pgb= ench I have to say that MacMini has a 2011 CPU ( = http://ark.intel.com/it/products/53463/Intel-Core-i7-2635QM-Processor-6M-C= ache-up-to-2_90-GHz ) while Dell has two 2013 CPU ( = http://ark.intel.com/products/75267/Intel-Xeon-Processor-E5-2640-v2-20M-Ca= che-2_00-GHz ), both at 2.0Ghz. > NUMA stands for "Non-Uniform-Memory-Access" . It's basically the = "label" for systems which have memory attached to different cpu sockets, = such that accessing all of the memory from a paritciular cpu thread has = different costs based on where the actual memory is located (i.e. on = some other socket, or the local socket). Thanks, good to know. > QPI is the the intel "QuickPath Interconnect". It's the communication = path between CPU sockets. Memory ready by one cpu thread that has to = come from another cpu socket's memory controller goes through QPI. > Google has lots of info on these, and how they impact performance, = etc. When I=92ll get access to BIOS (probably next week or later) I=92ll try = to disable QPI (if possible). Meanwhile I=92ll document on Internet = about QPI vs performance. >> If you want to see how bad the NUMA/QPI is, play with stream to = benchmark memory performance. >=20 >=20 > With stream you refer to this: = https://sites.utexas.edu/jdm4372/tag/stream-benchmark/ ? Do you suggest = me some way to do this kind of tests? >=20 > Ya, that's the one. I don't have specific tests in mind. I=92ve done some tests with sysbench on Dell T420 (via apt-get install) = and MacMini (I=92ve compiled the latest available sources at = https://github.com/akopytov/sysbench ). Here are some results with 16GB RAM read and written at 1MB block size = (I don=92t know if this makes sense, but I=92ve no problem in changing = these parameters). 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 > A more simple "overview" might be "numactl =97hardware=94 It returns the following output: sh-4.3# numactl --hardware available: 2 nodes (0-1) node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 node 0 size: 64385 MB node 0 free: 56487 MB node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 node 1 size: 64508 MB node 1 free: 62201 MB node distances: node 0 1=20 0: 10 20=20 1: 20 10=20 Thank you so much for your help, really appreciate it. Best regards, Pietro --Apple-Mail=_DFFC7DF6-E968-4F81-B3EA-31577BB008F9 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
Hi Aidan,
thank you again for your = support.
I found an interesting article showing better = performance from a Intel i5 vs a Intel Xeon on different Postgres = versions: http://blog.pgaddict.com/posts/performance-since-postgresql-= 7-4-to-9-4-pgbench

NUMA stands for "Non-Uniform-Memory-Access" = .  It's basically the "label" for systems which have memory = attached to different cpu sockets, such that accessing all of the memory = from a paritciular cpu thread has different costs based on where the = actual memory is located (i.e. on some other socket, or the local = socket).
Thanks, good to = know.

QPI is the the = intel "QuickPath Interconnect". It's the communication path between CPU = sockets.   Memory ready by one cpu thread that has to come from = another cpu socket's memory controller goes through = QPI.
Google has lots of info on these, and how = they impact performance, = etc.
When I=92ll get access to = BIOS (probably next week or later) I=92ll try to disable QPI (if = possible). Meanwhile I=92ll document on Internet about QPI vs = performance.

If you want to see how bad the NUMA/QPI is, play = with stream to benchmark memory performance.

With stream you refer to = this: https://sites.utexas.edu/jdm4372/tag/stream-benchmark/ ? Do you suggest me some way to do this kind of = tests?

Ya, that's = the one.  I don't have specific tests in = mind.
I=92ve done some tests with sysbench on = Dell T420 (via apt-get install) and MacMini (I=92ve compiled the latest = available sources at https://github.com/akopytov/= sysbench ).
Here are some results with 16GB RAM read and written = at 1MB block size (I don=92t know if this makes sense, but I=92ve no = problem in changing these parameters).

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



A more simple "overview" might be = "numactl =97hardware=94
It returns the following = output:

sh-4.3# = numactl --hardware
available: 2 = nodes (0-1)
node 0 cpus: 0 2 4 6 = 8 10 12 14 16 18 20 22 24 26 28 30
node 0 size: 64385 MB
node 0 free: 56487 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 = 31
node 1 size: 64508 = MB
node 1 free: 62201 = MB
node = distances:
node   0   = 1 
  0:  10 =  20 
  1: =  20  10 

Thank you so = much for your help, really appreciate it.
Best = regards,
 Pietro


<= br>= --Apple-Mail=_DFFC7DF6-E968-4F81-B3EA-31577BB008F9--