Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YdcoW-0007Xs-4y for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:57:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YdcoU-0002LL-QD for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:57:30 +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 1YdcoT-0002LF-TR for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:57:29 +0000 Received: from mail-wg0-x229.google.com ([2a00:1450:400c:c00::229]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YdcoP-0006oX-Fq for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:57:28 +0000 Received: by wgbdm7 with SMTP id dm7so81458699wgb.1 for ; Thu, 02 Apr 2015 03:57:24 -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=p+Jc1Ge0LupIsYRy24WPhG3Pkn2lwDDJfmo7UizI01A=; b=q8grQfGCu6lNVLgGZyVPk7ScZMNoFZsfn5hM3yCuz6TjTmNOYqt8qyl+Sds0fe1ZO4 Jqckaw1SYfNpYmWZzQ15IxkhIJQACSQGTZ8nLli8IALc9O/JHcLqRO0ItWBYuy6Kz4fm kJVuPDutvltBYHO0yLXusOdfj1SPbCMDPYXsKkx/iodDi/5rv1Oly7D1EHIGn/gKS758 JVw5uQKVKegwSxQpPml8ochpLhVl+Y+54UgJ0gacX1KkGBOJcK71TtOmOo/35VDdfH9y Km/2izVnb8SN1ps3pu9GN15Kf2CT1V5ur65Ev8g0BtefVYZGoukQb/jobJ2iRRtLBEYy IQHQ== X-Received: by 10.180.14.7 with SMTP id l7mr23720292wic.40.1427972244305; Thu, 02 Apr 2015 03:57:24 -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 ax10sm6718882wjc.26.2015.04.02.03.57.23 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 02 Apr 2015 03:57:23 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_C64FB72A-2A06-4F9D-884D-92363050C4D0" 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: Thu, 2 Apr 2015 12:57:22 +0200 Cc: "pgsql-performance@postgresql.org" Message-Id: <9A77A65B-061D-4712-A21D-83B02DF18C57@gmail.com> References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com> To: ik@postgresql-consulting.com 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=_C64FB72A-2A06-4F9D-884D-92363050C4D0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Hi Ilya, thank your for your response. Both system were configured for each test I=92ve done. On T420 I=92ve = optimized the kernel following the official Postgres documentation ( = http://www.postgresql.org/docs/9.4/static/kernel-resources.html ): kernel.shmmax=3D68719476736 kernel.shmall=3D16777216 vm.overcommit_memory=3D2 vm.overcommit_ratio=3D90 RAID controllers were configured as following: - Write cache: WriteBack - Read cache: ReadAhead - Disk cache (only T420): disabled to take full advantage of WriteBack = cache (BBU is charged and working) - NCQ (only MacMini because it=92s a SATA option): enabled (this affects = a lot the overall performance) For postgresql.conf: T420 Normal operations autovacuum =3D on maintenance_work_mem =3D 512MB work_mem =3D 512MB wal_buffers =3D 64MB effective_cache_size =3D 64GB # this helps A LOT in disk write speed = when creating indexes shared_buffers =3D 32GB checkpoint_segments =3D 2000 checkpoint_completion_target =3D 1.0 effective_io_concurrency =3D 0 # 1 doesn=92t make any substantial = difference max_connections =3D 10 # 20 doesn=92t make any difference Data loading (same as above with the following changes): autovacuum =3D off maintenance_work_mem =3D 64GB MacMini Normal operations autovacuum =3D on maintenance_work_mem =3D 128MB work_mem =3D 32MB wal_buffers =3D 32MB effective_cache_size =3D 800MB shared_buffers =3D 512MB checkpoint_segments =3D 32 checkpoint_completion_target =3D 1.0 effective_io_concurrency =3D 1 max_connections =3D 20 Data loading (same as above with the following changes): autovacuum =3D off maintenance_work_mem =3D 6GB Best regards, Pietro Il giorno 01/apr/2015, alle ore 16:27, Ilya Kosmodemiansky = ha scritto: > Hi Pietro, >=20 > On Wed, Apr 1, 2015 at 3:56 PM, Pietro Pugni = wrote: >> T420: went from 311seconds (default postgresql.conf) to 195seconds = doing >> tuning adjustments over RAID, kernel and postgresql.conf; >> MacMini: 40seconds. >=20 > I'am afraid, the matter is, that PostgreSQL is not configured properly > (and so do operating system and probably controller, however > pg_test_fsync shows that things are not so bad there as with > postgresql.conf). >=20 > It is pretty useless to benchmark a database using out-of-the-box > configuration. You need at least configure shared memory related, > checkpoints-related and autovacuum-related settings. And as a first > step, please compare postgresql.conf on Mac and on the server: > sometimes (with some mac installers) default postgresql.conf can be > not the same as on server. >=20 > Best regards, > Ilya >=20 >=20 > --=20 > Ilya Kosmodemiansky, >=20 > PostgreSQL-Consulting.com > tel. +14084142500 > cell. +4915144336040 > ik@postgresql-consulting.com --Apple-Mail=_C64FB72A-2A06-4F9D-884D-92363050C4D0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
Hi Ilya,
thank your for your = response.
Both system were configured for each test I=92ve = done. On T420 I=92ve optimized the kernel following the official = Postgres documentation ( h= ttp://www.postgresql.org/docs/9.4/static/kernel-resources.html = ):
kernel.shmmax=3D68719476736
kernel.shmall=3D16777216
vm.overcommit_memory=3D2
vm.overcommit_ratio=3D90


RAID controllers were configured as = following:
- Write cache: WriteBack
- Read cache: = ReadAhead
- Disk cache (only T420): disabled to take full = advantage of WriteBack cache (BBU is charged and working)
- = NCQ (only MacMini because it=92s a SATA option): enabled (this affects a = lot the overall performance)

For = postgresql.conf:

T420
No= rmal operations
autovacuum =3D = on
maintenance_work_mem =3D = 512MB
work_mem =3D = 512MB
wal_buffers =3D 64MB
effective_cache_size =3D 64GB # this helps A LOT in disk write = speed when creating indexes
shared_buffers =3D 32GB
checkpoint_segments =3D 2000
checkpoint_completion_target =3D 1.0
effective_io_concurrency =3D 0 # 1 doesn=92t make = any substantial difference
max_connections =3D 10 # 20 doesn=92t make any = difference

Data loading (same as = above with the following changes):
autovacuum =3D off
maintenance_work_mem =3D = 64GB


MacMini
Normal operations
autovacuum =3D= on
maintenance_work_mem =3D = 128MB
work_mem =3D = 32MB
wal_buffers =3D 32MB
effective_cache_size =3D 800MB
shared_buffers =3D 512MB
checkpoint_segments =3D 32
checkpoint_completion_target =3D 1.0
effective_io_concurrency =3D = 1
max_connections =3D = 20

Data loading (same as above with = the following changes):
autovacuum =3D off
maintenance_work_mem =3D = 6GB


Best = regards,
 Pietro



Il giorno 01/apr/2015, alle ore 16:27, Ilya Kosmodemiansky <ilya.kosmode= miansky@postgresql-consulting.com> ha scritto:

Hi = Pietro,

On Wed, Apr 1, 2015 at 3:56 PM, Pietro Pugni <pietro.pugni@gmail.com> = wrote:
T420: went from 311seconds (default = postgresql.conf) to 195seconds doing
tuning adjustments over RAID, = kernel and postgresql.conf;
MacMini: = 40seconds.

I'am afraid, the matter is, that = PostgreSQL is not configured properly
(and so do operating system and = probably controller, however
pg_test_fsync shows that things are not = so bad there as with
postgresql.conf).

It is pretty useless to = benchmark a database using out-of-the-box
configuration. You need at = least configure shared memory related,
checkpoints-related and = autovacuum-related settings. And as a first
step, please compare = postgresql.conf on Mac and on the server:
sometimes (with some mac = installers) default postgresql.conf can be
not the same as on = server.

Best regards,
Ilya


--
Ilya = Kosmodemiansky,

PostgreSQL-Consulting.comtel. +14084142500
cell. = +4915144336040
ik@postgresql-consulting.com

<= /body>= --Apple-Mail=_C64FB72A-2A06-4F9D-884D-92363050C4D0--