Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCGKn-0005Ej-0N for pgsql-performance@arkaria.postgresql.org; Tue, 16 Oct 2018 03:47:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gCGKj-0003a5-OL for pgsql-performance@arkaria.postgresql.org; Tue, 16 Oct 2018 03:47:49 +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.89) (envelope-from ) id 1gCGKj-0003Zy-3C for pgsql-performance@lists.postgresql.org; Tue, 16 Oct 2018 03:47:49 +0000 Received: from sonic308-4.consmr.mail.bf2.yahoo.com ([74.6.130.43]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCGKf-00027g-Pm for pgsql-performance@lists.postgresql.org; Tue, 16 Oct 2018 03:47:47 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1539661664; bh=BXyZuEY8PpBTt/YCZowpLAOv9WA7bDckMFY+pJeljNY=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=EZKEdNs3f+E4icorjYVuObrAgybWDd4lGSF8WCADT3uLoh5LAhXMM/zQvCWxjsqgEXfjORcmkLdoPnqykA7/sySIOarFJ6s1vGC1beCRbyRGeJqtgnHXXJkeQwg5uVyvgP/wGfYoHRy9uyT1vBywdpbIvsbSsLkbojjbw1MagrPFRfyEiM8TIaIGhnlIDFcrG6tIrbsZQqOjeFnxP07E8LzX7QNAUqYOQuEiZ8YT9l9NayvrgiuXDu+rGn+nmbUi1qsectbAHI1C+CA0iBKVTqFGoTdDOGBP6t6uLfVlJQCdfEn21gl7yZq3PLPsiMBGHnMq7huUxaPuDSNydGrC9Q== X-YMail-OSG: l5uoPNMVM1lhGAV63dhHGpRjR9VcD14EKlxI2SCLWPx06OVECZaDIjPQccsi4Gx yKjomNrrWQioJZpobvx2mEsYpiEiIXObkz9pObL7d2HJxY0kJmjHMGsEy.NWaNiPCqZu0nLWFbIj TpvajjI2Jg75CYXVQByuj5.8n6YLB.iDyQsoYMNwbnPZ6dHzFWh_.KisfQSdLGNrb3Ee32ObUSCe RVPU4_vOPOB2ePj72mSqtJtFp2VA8jP9jJ6FANDh4IumYPBAbsmNQOu7HaFEiIohFKflNAaSJL5y x56Ab_rCy0rPtiacgRsTUI.V7ShgOA.rXnIZ0mqhZsuHw1.xhCOo71KuTIr0HGp4zcZaUnYhXB4i LSyExuD7GKA66DtTmGXFrzElD4y2i6YIy_5qMH4iJXHz2VnZDW0a4hs4EJLi4Nz4REU.84iy7XiW .ZQgCgvrCTBKfwkbY8IVMaEHtcAhIXEWD.u9RMAOvNtCZCWpbSTDZnhBRKzALOLS9pjdU98pX0rZ prWFYn3NnIHt3tqVzn0n4vs7qCk.QDSXBBB6sn.OFrX6BNzAkypJXSN16rZPJg2v_pq2Zx6Gg5D3 GXXELuCXYn1xNbYZV.QvaSJmFxW399oElafExOdRBKIjGssa23NAsV1HNZRT6HiaqgDLisXzJFJz .jD3ur4PNNz61.pC0txQvkO6bE6nGKalgOk3N1dbVoh_RpfxSgN2hlnVnqtVA.G7ItUpfyh8iUye necQB5f.QJt9Vuz6SjIXY1nFe2fqd28z8v0ATUpxOY55uWPeTjPKBOE4h7uda0oJT0rbK62DNbyW ROTllszt8x6X4Dv4PpVnJJfSuPqHJc5ErT8R.TUQTNh3eJ0MSX.h4PzriSk6ESUDTfDVGPn1hNmG 8XfATWBEQ0WuNDu_8sekJ4PYbVSRtnyzHZJqdPMtybf81TwsrgoAq8EWkGjRU0Rn1MVq3zQeIdCC WNtE1fSv2uS085ieLBJtmIFDJcQW1IyzmRekmCpZG6sQvTMzQkYXyr5a6.AxgAayJknUKZx9UXnu 5hWd. Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.bf2.yahoo.com with HTTP; Tue, 16 Oct 2018 03:47:44 +0000 Date: Tue, 16 Oct 2018 03:47:39 +0000 (UTC) From: "Sam R." Reply-To: "samruohola@yahoo.com" To: Pgsql Performance Message-ID: <1018411307.10529045.1539661659590@mail.yahoo.com> In-Reply-To: <1288355243.8943320.1539361665724@mail.yahoo.com> References: <4dbb8bea-bea3-74d8-a537-36bb9864cb9f@portavita.eu> <1288355243.8943320.1539361665724@mail.yahoo.com> Subject: Re: One big table or split data? Writing data. From disk point of view. With a good storage (GBs/s, writing speed) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10529044_1344868447.1539661659587" X-Mailer: WebService/1.1.12512 YahooMailAndroidMobile YMobile/1.0 (com.yahoo.mobile.client.android.mail/5.32.1; Android/8.1.0; OPR1.170623.026; B2N_sprout; HMD Global; Nokia 7 plus; 5.71; 2034x1080;) Content-Length: 6707 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_10529044_1344868447.1539661659587 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi! Still, "writing" speed to disk: With "fio" 3 GB / s.With PostgreSQL: 350 -500 MB / s, also with a partition= ed table. (and something similar with an other DBMS).Monitored with dstat. With about 12 threads, then it does not get better anymore.Same results wit= h data split to 4 partitions, in a partitioned table. CPU load increased, b= ut not full yet.Same results with open_datasync. BR Sam =20 =20 On pe, lokak. 12, 2018 at 19:27, Sam R. wrote: Hi= ! Could someone discuss about following? It would be great to hear comments! There is a good storage. According to "fio", write speed could be e.g. 3 GB= /s.=C2=A0(It is First time using the command for me, so I am not certain of= the real speed with "fio". E.g. with --bs=3D100m, direct=3D1, in fio. The = measurement result may be faulty also. So, checking still.) Currently there is one table without partitioning. The table contains json = data. In PostgreSQL, in Linux. Write speed can be e.g. 300 - 600 MB/s, through PostgreSQL. Measured with d= stat while inserting. Shared buffers is large is PostgreSQL. With a storage/disk which "scales", is there some way to write faster to th= e disk in the system through PostgreSQL? Inside same server. Does splitting data help? Partitioned table / splitting to smaller tables? = Should I test it? Change settings somewhere? Block sizes? 8 KB / 16 KB, ... "Dangerous" to ch= ange? 2nd question, sharding: If the storage / "disk" scales, could better *disk writing speed* be achiev= ed (in total) with sharding kind of splitting of data? (Same NAS storage, w= hich scales, in use in all shards.)Sharding or use only one server? From pu= re disk writing speed point of view. BR Sam =20 ------=_Part_10529044_1344868447.1539661659587 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi!

Still, "writing" speed to dis= k:

With "fio" 3 GB / s.=
With PostgreSQL: 350 -= 500 MB / s, also with a partitioned table.

(and something similar with an other DBMS).
Monitored with dstat.

With about 12 threads, then it does not g= et better anymore.
Same results with data split to 4 partitions, in a partitioned table. CPU= load increased, but not full yet.
Same results with open_datasync.

BR Sam


On pe, lokak. 12, 2018 at 19:27, Sam R.
<samruohola@yah= oo.com> wrote:
Hi!

<= /div>
Could someone dis= cuss about following? It would be great to hear comments!

There is a good storage. According to "fio", w= rite speed could be e.g. 3 GB/s.
 (It is First time using the command for me, so I am n= ot certain of the real speed with "fio". E.g. with --bs=3D100m, direct=3D1,= in fio. The measurement result may be faulty also. So, checking still.)

Currently there is one table = without partitioning. The table contains json data. In PostgreSQL, in Linux= .

Write speed can be e.g. 300= - 600 MB/s, through PostgreSQL. Measured with dstat while inserting. Share= d buffers is large is PostgreSQL.

With a storage/disk which "scales", is there some way to write faster = to the disk in the system through PostgreSQL?

Inside same server.

Does splitting data help? Partitioned table / splitting to smaller = tables? Should I test it?

Cha= nge settings somewhere? Block sizes? 8 KB / 16 KB, ... "Dangerous" to chang= e?

2nd question, sharding:

If the storage / "disk" scale= s, could better *disk writing speed* be achieved (in total) with sharding k= ind of splitting of data? (Same NAS storage, which scales, in use in all sh= ards.)
Sharding o= r use only one server? From pure disk writing speed point of view.


BR Sam

------=_Part_10529044_1344868447.1539661659587--