Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YdcRG-0006Xm-8v for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:33:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YdcRF-0004Iq-DQ for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:33:29 +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 1YdcRE-0004Ik-9I for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:33:28 +0000 Received: from mail-wi0-x229.google.com ([2a00:1450:400c:c05::229]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YdcR5-0005iv-KD for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:33:26 +0000 Received: by wiaa2 with SMTP id a2so99804447wia.0 for ; Thu, 02 Apr 2015 03:33:17 -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=wMwt+Qn+haOoGBY0p4IUAinZtCzokaZM98eYFD5xpsE=; b=pVf4IGcvJmA18ZDlN4D8HpdJjL3P/rLI8PhxqJeR2TaQXUlMMet79Vbt/FRrpLxdvQ DyRja6IqrJyY307KI5CEfzy93f49F3lqdZVUwxcPZqL0TWFploMGb4kq1Yauvl05Hhdz uxFdKO5VYYxAGvx0CXm5/rSAAhF2kjj2cAJ5VvWQlk2Ps+AzKfVjXqYMM5zq+vS6sFby RTYbbVcr2GUxCJly9/eq+SipJl6viniAI6Nmn7FWDKpcL5OBqIzTuGUtUUpMFxHhF3fi vaqJAw5kK/q/EFuXNU37iXGhlhXToknWMuzMLVgbYSQedTllJZyWIdP9HIIaAqfdcm4Y kv1g== X-Received: by 10.180.228.104 with SMTP id sh8mr22776272wic.61.1427970797877; Thu, 02 Apr 2015 03:33:17 -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 m4sm6631320wjb.25.2015.04.02.03.33.16 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 02 Apr 2015 03:33:17 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_25E88976-10B9-4E08-878B-7FFFABBE2C25" 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: <87131335.225702.1427941178942.JavaMail.root@fmed.uba.ar> Date: Thu, 2 Apr 2015 12:33:15 +0200 Cc: pgsql-performance@postgresql.org Message-Id: References: <87131335.225702.1427941178942.JavaMail.root@fmed.uba.ar> To: Gerardo Herzig 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=_25E88976-10B9-4E08-878B-7FFFABBE2C25 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Hi Gerardo, thank you for your response. At the moment I can=92t switch to RAID10. I know it has best = performance, but both systems have RAID5 and MacMini has a consumer = desktop RAID solution while T420 has a server-grade one. Anyway, I used two configurations for each system: one for data loading = operations and the other one for any other kind of operation (SELECT = etc.). These configurations were made studying different combinations. = I=92ve changed kernel parameters as stated in the official Postgres = documentation ( www.postgresql.org/docs/9.4/static/kernel-resources.html = ). I copy and paste here the various postgresql.conf involved: 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 02/apr/2015, alle ore 04:19, Gerardo Herzig = ha scritto: > Ok, a quick view on the system, and some things that may be important = to note: >=20 >> Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID >> controller configured in this way: >>=20 >> * VD0: two 15k SAS disks (ext4, OS partition, WAL partition, >> RAID1) >> * VD1: ten 10k SAS disks (XFS, Postgres data partition, RAID5) >>=20 >=20 > Well...usually RAID5 have the worst performance in writing...EVER!!! = Have you tested this in another raid configuration? RAID10 is usually = the best bet. >=20 >>=20 >>=20 >> This system has the following configuration: >>=20 >> * Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-48-generic x86_64) >> * 128GB RAM (DDR3, 8x16GB @1600Mhz) >> * two Intel Xeon E5-2640 v2 @2Ghz >> * Dell Perc H710 with 512MB RAM (Write cache: "WriteBack", Read >> cache: "ReadAhead", Disk cache: "disabled"): >> * VD0 (OS and WAL partition): two 15k SAS disks (ext4, RAID1) >> * VD1 (Postgres data partition): ten 10k SAS disks (XFS, >> RAID5) >> * PostgreSQL 9.4 (updated to the latest available version) >> * moved pg_stat_tmp to RAM disk >>=20 >>=20 > [...]> versions. >>=20 > You did not mention any "postgres" configuration at all. If you let = the default checkpoint_segments=3D3, that would be an IO hell for your = disk controler...and the RAID5 making things worst...Can you show us the = values of: >=20 > checkpoint_segments > shared_buffers > work_mem > maintenance_work_mem > effective_io_concurrency >=20 > I would start from there, few changes, and check again. I would change = the RAID first of all things, and try those tests again. >=20 > Cheers. > Gerardo --Apple-Mail=_25E88976-10B9-4E08-878B-7FFFABBE2C25 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
Hi Gerardo,
thank you for your = response.
At the moment I can=92t switch to RAID10. I know it = has best performance, but both systems have RAID5 and MacMini has a = consumer desktop RAID solution while T420 has a server-grade = one.
Anyway, I used two configurations for each system: one = for data loading operations and the other one for any other kind of = operation (SELECT etc.). These configurations were made studying = different combinations. I=92ve changed kernel parameters as stated in = the official Postgres documentation ( w= ww.postgresql.org/docs/9.4/static/kernel-resources.html ).
<= div>I copy and paste here the various postgresql.conf = involved:

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 02/apr/2015, alle ore 04:19, Gerardo Herzig = <gherzig@fmed.uba.ar> = ha scritto:

Ok, a quick view on the system, and some things that may = be important to note:

Our deployment = machine is a Dell PowerEdge T420 with a Perc H710 RAID
controller = configured in this way:

   * VD0: two 15k SAS = disks (ext4, OS partition, WAL partition,
=    RAID1)
   * VD1: ten 10k SAS disks = (XFS, Postgres data partition, = RAID5)


Well...usually RAID5 have the worst = performance in writing...EVER!!! Have you tested this in another raid = configuration? RAID10 is usually the best bet.



This system has the following = configuration:

   * Ubuntu 14.04.2 LTS (GNU/Linux = 3.13.0-48-generic x86_64)
   * 128GB RAM (DDR3, = 8x16GB @1600Mhz)
   * two Intel Xeon E5-2640 v2 = @2Ghz
   * Dell Perc H710 with 512MB RAM (Write = cache: "WriteBack", Read
   cache: "ReadAhead", Disk = cache: "disabled"):
       * VD0 = (OS and WAL partition): two 15k SAS disks (ext4, RAID1)
=        * VD1 (Postgres data = partition): ten 10k SAS disks (XFS,
=        RAID5)
   * = PostgreSQL 9.4 (updated to the latest available version)
=    * moved pg_stat_tmp to RAM = disk


[...]> versions.

You did not mention any "postgres" = configuration at all. If you let the default checkpoint_segments=3D3, = that would be an IO hell for your disk controler...and the RAID5 making = things worst...Can you show us the values = of:

checkpoint_segments
shared_buffers
work_mem
maintenanc= e_work_mem
effective_io_concurrency

I would start from there, = few changes, and check again. I would change the RAID first of all = things, and try those tests = again.

Cheers.
Gerardo

= --Apple-Mail=_25E88976-10B9-4E08-878B-7FFFABBE2C25--