Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cgJjR-00015B-D0 for pgsql-performance@arkaria.postgresql.org; Tue, 21 Feb 2017 23:20:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cgJjQ-0005Fd-N6 for pgsql-performance@arkaria.postgresql.org; Tue, 21 Feb 2017 23:20:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cgJhk-0003GS-89 for pgsql-performance@postgresql.org; Tue, 21 Feb 2017 23:18:44 +0000 Received: from mail-wm0-x22c.google.com ([2a00:1450:400c:c09::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cgJhh-0005ZB-6K for pgsql-performance@postgresql.org; Tue, 21 Feb 2017 23:18:43 +0000 Received: by mail-wm0-x22c.google.com with SMTP id r141so87846919wmg.1 for ; Tue, 21 Feb 2017 15:18:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=UFZHCsW0ilaVNQHeNSKU5/rsHQ3O38nGpuN1L4mrq/c=; b=ZsbiLOo5te47qRtQYewDTWEnSUv9rLNeElldP1FAx9yzImpk69GDkVg1jrV1qGUY1T ru/NgzNaid2hJVtSpmGx0JOHWHj9aRIcjwUn0iF+ecXeTfqCCw+1/JYnTOy6Sx1gWWMW VzUrlZ9rv5UCr9U4wgo0Bdm5Cf9IT/Kvy9VXUjCA0khb8nRRJ3+0G0/ipeUe+jylygju +AXXoiF3E88hIRZMzevLbOr6NFNQNkWW9Lly5z69spplcT0uTbqvz4wdVSDIHVMXbDUP p0S3b8kB+aJS7X09JOpc5rVmiftOZ7qSlGkG29ZLEzAbg8CMDYKE83UDXhHGHkuQzfGx rLtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=UFZHCsW0ilaVNQHeNSKU5/rsHQ3O38nGpuN1L4mrq/c=; b=UbgEj3Hc60Cy2ikKowgJRwE/tcXRfKP5T7VdLPScglWU2eG2Teyx5ClSu4a6QQHfG0 sJYPagWOaBOuPQgyvlotKS0L+453FB5aPb3V0D+77oFc1CP0c+OQeNqFTIhPpmQbaTQn KCbVzHpGbQkFFGffT3r0glA1lqIW/HW3F8xEBkPyibPjGMwnCrYyySNbN52UV9pwEvK6 WeMxO+CvI/3dVyk6qGdsAipF2V/1d5Ig4EhvFCx4YgvvihYMYaGlDDnfkhxvnqU/IN6J mKVbsDZqZyz5OWxW06/G8wo7LmARvan3FruIVW7O7f5a9yyfRjKQo+U7O1sQR721diaV rBfQ== X-Gm-Message-State: AMke39lTLENVNWPntZn+NP38zOdLKUEftoIws7DHrR0Fm/lk+dzdzGoIH1ryjilPgkxcaw== X-Received: by 10.28.213.193 with SMTP id m184mr29249294wmg.28.1487719118922; Tue, 21 Feb 2017 15:18:38 -0800 (PST) Received: from [192.168.2.5] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by smtp.gmail.com with ESMTPSA id 32sm21039193wre.15.2017.02.21.15.18.37 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Tue, 21 Feb 2017 15:18:38 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_D4FAB23C-557B-42D5-BEE9-D97997C9AF54" Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) Subject: Re: Suggestions for a HBA controller (6 x SSDs + madam RAID10) From: Pietro Pugni In-Reply-To: Date: Wed, 22 Feb 2017 00:18:36 +0100 Cc: "pgsql-performance@postgresql.org" Message-Id: References: To: Merlin Moncure X-Mailer: Apple Mail (2.3124) 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=_D4FAB23C-557B-42D5-BEE9-D97997C9AF54 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Thank you a lot for your suggestions. > Random points/suggestions: > *) mdadm is the way to go. I think you'll get bandwidth constrained = on most modern hba unless they are really crappy. On reasonably modern = hardware storage is rarely the bottleneck anymore (which is a great = place to be). Fancy raid controllers may actually hurt performance -- = they are obsolete IMNSHO. >=20 > *) Small point, but you'll want to crank effective_io_concurrency = (see: = https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%= 3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com = ). It only affects certain kinds = of queries, but when it works it really works. Those benchmarks were = done on my crapbox dell workstation! This is really impressive, thank you for sharing it. > *) For very high transaction rates, you can get a lot of benefit from = disabling synchronous_commit if you are willing to accommodate the risk. = I do not recommend disabling fsync unless you are prepared to = regenerate the entire database at any time. >=20 > *) Don't assume indefinite linear scaling as you increase storage = capacity -- the database itself can become the bottleneck, especially = for writing. To improve write performance, classic optimization = strategies of trying to intelligently bundle writes around units of work = still apply. If you are expecting high rates of write activity your = engineering focus needs to be here for sure (read scaling is = comparatively pretty easy). What do you mean with =E2=80=9Cunits of work=E2=80=9D? > *) I would start doing your benchmarking with pgbench since that is = going to most closely reflect measured production performance. =20 My final benchmark will be my application, it=E2=80=99s quite = articulated and does also query parallelization using a custom splitter, = so it will be hard to reproduce it using pgbench. At the moment I was = just figuring out why my SSD weren=E2=80=99t performing as expected with = comparable benchmarks found on hardware review websites (fio with 4k and = 8k workloads).=20 > If this is the case your stack performance is going to be based on = data structure design. Make liberal use of: > *) natural keys=20 > *) constraint exclusion for partition selection > *) BRIN index is amazing (if you can work into it's limitations) > *) partial indexing > *) covering indexes. Don't forget to vacuum your partitions before = you make them live if you use them The data definition is optimized for Postgres yet, but didn=E2=80=99t = know about covering indexes. I read about BRIN but never tried them. = Will do some testing. > If your data is going to get really big and/or query activity is = expected to be high, keep an eye on your scale out strategy. Going = monolithic to bootstrap your app is the right choice IMO but start = thinking about the longer term if you are expecting growth. I'm = starting to come out to the perspective that lift/shift scaleout using = postgres fdw without an insane amount of app retooling could be a viable = option by postgres 11/12 or so. For my part I scaled out over = asynchronous dblink which is a much more maintenance heavy strategy (but = works fabulous although I which you could asynchronously connect). Thank you for your hints=20 Pietro Pugni --Apple-Mail=_D4FAB23C-557B-42D5-BEE9-D97997C9AF54 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Thank you a lot for your = suggestions.

Random = points/suggestions:
*) mdadm is the way to go. =   I think you'll get bandwidth constrained on most modern hba = unless they are really crappy.  On reasonably modern hardware = storage is rarely the bottleneck anymore (which is a great place to = be).  Fancy raid controllers may actually hurt performance -- they = are obsolete IMNSHO.

*) Small point, but you'll want to crank = effective_io_concurrency (see: https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH%3DHW= h1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com). It only affects = certain kinds of queries, but when it works it really works.   =  Those benchmarks were done on my crapbox dell = workstation!

This is really impressive, thank you for sharing it.

*) For very high transaction = rates, you can get a lot of benefit from = disabling synchronous_commit if you are willing to accommodate the = risk.  I do not recommend disabling fsync unless you are prepared = to regenerate the entire database at any time.

*) Don't assume indefinite linear = scaling as you increase storage capacity -- the database itself can = become the bottleneck, especially for writing.   To improve write = performance, classic optimization strategies of trying to intelligently = bundle writes around units of work still apply.  = If you are expecting high rates of write activity your engineering focus = needs to be here for sure (read scaling is comparatively pretty = easy).

What do you mean with =E2=80=9Cunits of = work=E2=80=9D?


*) I = would start doing your benchmarking with pgbench since that is going to = most closely reflect measured production performance. =  

My final benchmark will be my application, it=E2=80=99= s quite articulated and does also query parallelization using a custom = splitter, so it will be hard to reproduce it using pgbench. At the = moment I was just figuring out why my SSD weren=E2=80=99t performing as = expected with comparable benchmarks found on hardware review websites = (fio with 4k and 8k workloads). 


If this is the case your stack = performance is going to be based on data structure design.  Make = liberal use of:
*) natural keys 
*) constraint exclusion for partition selection
*) BRIN index is amazing (if you can work into it's = limitations)
*) partial indexing
*) covering indexes.  Don't forget to vacuum your = partitions before you make them live if you use = them

The data definition is optimized for Postgres yet, = but didn=E2=80=99t know about covering indexes. I read about BRIN but = never tried them. Will do some testing.

If your data is going to get = really big and/or query activity is expected to be high, keep an eye on = your scale out strategy.   Going monolithic to bootstrap your app = is the right choice IMO but start thinking about the longer term if you = are expecting growth.  I'm starting to come out to the perspective = that lift/shift scaleout using postgres fdw without an insane amount of = app retooling could be a viable option by postgres 11/12 or so.  = For my part I scaled out over asynchronous dblink which is a much more = maintenance heavy strategy (but works fabulous although I which you = could asynchronously = connect).

Thank you for your hints 

 Pietro Pugni

= --Apple-Mail=_D4FAB23C-557B-42D5-BEE9-D97997C9AF54--