pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhen does sequential performance matter in PG?
9+ messages / 7 participants
[nested] [flat]
* When does sequential performance matter in PG?
@ 2009-03-10 14:09 henk de wit <henk53602@hotmail.com>
2009-03-10 14:28 ` Re: When does sequential performance matter in PG? Matthew Wakeling <matthew@flymine.org>
2009-03-10 17:50 ` Re: When does sequential performance matter in PG? Greg Smith <gsmith@gregsmith.com>
0 siblings, 2 replies; 9+ messages in thread
From: henk de wit @ 2009-03-10 14:09 UTC (permalink / raw)
To: pgsql-performance
Hi,
It is frequently said that for PostgreSQL the number 1 thing to pay attention to when increasing performance is the amount of IOPS a storage system is capable of. Now I wonder if there is any situation in which sequential IO performance comes into play. E.g. perhaps during a tablescan on a non-fragmented table, or during a backup or restore?
The reason I'm asking is that we're building a storage array and for some reason are unable to
increase the number of random IOPS beyond a certain threshold when we add more controllers or more (SSD) disks to the system. However, the sequential performance keeps increasing when we do that.
Would this extra sequential performance be of any benefit to PG or would it just be wasted?
Kind regards
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: When does sequential performance matter in PG?
2009-03-10 14:09 When does sequential performance matter in PG? henk de wit <henk53602@hotmail.com>
@ 2009-03-10 14:28 ` Matthew Wakeling <matthew@flymine.org>
2009-03-10 14:57 ` Re: When does sequential performance matter in PG? henk de wit <henk53602@hotmail.com>
2009-03-10 18:01 ` Re: When does sequential performance matter in PG? Scott Carey <scott@richrelevance.com>
1 sibling, 2 replies; 9+ messages in thread
From: Matthew Wakeling @ 2009-03-10 14:28 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
On Tue, 10 Mar 2009, henk de wit wrote:
> It is frequently said that for PostgreSQL the number 1 thing to pay
> attention to when increasing performance is the amount of IOPS a storage
> system is capable of. Now I wonder if there is any situation in which
> sequential IO performance comes into play. E.g. perhaps during a
> tablescan on a non-fragmented table, or during a backup or restore?
Yes, up to a point. That point is when a single CPU can no longer handle
the sequential transfer rate. Yes, there are some parallel restore
possibilities which will get you further. Generally it only takes a few
discs to max out a single CPU though.
> The reason I'm asking is that we're building a storage array and for
> some reason are unable to increase the number of random IOPS beyond a
> certain threshold when we add more controllers or more (SSD) disks to
> the system. However, the sequential performance keeps increasing when we
> do that.
Are you sure you're measuring the maximum IOPS, rather than measuring the
IOPS capable in a single thread? The advantage of having more discs is
that you can perform more operations in parallel, so if you have lots of
simultaneous requests they can be spread over the disc array.
Matthew
--
[About NP-completeness] These are the problems that make efficient use of
the Fairy Godmother. -- Computer Science Lecturer
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: When does sequential performance matter in PG?
2009-03-10 14:09 When does sequential performance matter in PG? henk de wit <henk53602@hotmail.com>
2009-03-10 14:28 ` Re: When does sequential performance matter in PG? Matthew Wakeling <matthew@flymine.org>
@ 2009-03-10 14:57 ` henk de wit <henk53602@hotmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: henk de wit @ 2009-03-10 14:57 UTC (permalink / raw)
To: matthew@flymine.org; +Cc: pgsql-performance
Hi,
> On Tue, 10 Mar 2009, henk de wit wrote:
> > Now I wonder if there is any situation in which
> > sequential IO performance comes into play. E.g. perhaps during a
> > tablescan on a non-fragmented table, or during a backup or restore?
>
> Yes, up to a point. That point is when a single CPU can no longer handle
> the sequential transfer rate. Yes, there are some parallel restore
> possibilities which will get you further. Generally it only takes a few
> discs to max out a single CPU though.
I see, but I take it you are only referring to a backup or a restore? It's of course unlikely (even highly undesirable) that multiple processes are doing a backup, but it doesn't seem unlikely that multiple queries are doing a table scan ;)
> Are you sure you're measuring the maximum IOPS, rather than measuring the
> IOPS capable in a single thread?
I'm pretty sure we're not testing the number of IOPS for a single thread, as we're testing with 1, 10 and 40 threads. There is a significant (2x) increase in the total number of IOPS when going from 1 to 10 threads, but no increase when going from 10 to 40 threads. You can read more details about the setup I used and the problems I ran into here: http://www.xtremesystems.org/forums/showthread.php?p=3707365
Henk
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: When does sequential performance matter in PG?
2009-03-10 14:09 When does sequential performance matter in PG? henk de wit <henk53602@hotmail.com>
2009-03-10 14:28 ` Re: When does sequential performance matter in PG? Matthew Wakeling <matthew@flymine.org>
@ 2009-03-10 18:01 ` Scott Carey <scott@richrelevance.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Carey @ 2009-03-10 18:01 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
On 3/10/09 6:28 AM, "Matthew Wakeling" <matthew@flymine.org> wrote:
On Tue, 10 Mar 2009, henk de wit wrote:
> It is frequently said that for PostgreSQL the number 1 thing to pay
> attention to when increasing performance is the amount of IOPS a storage
> system is capable of. Now I wonder if there is any situation in which
> sequential IO performance comes into play. E.g. perhaps during a
> tablescan on a non-fragmented table, or during a backup or restore?
Yes, up to a point. That point is when a single CPU can no longer handle
the sequential transfer rate. Yes, there are some parallel restore
possibilities which will get you further. Generally it only takes a few
discs to max out a single CPU though.
This is not true if you have concurrent sequential scans. Then an array can be tuned for total throughput with concurrent access. Single thread sequential measurements are similarly useful to single thread random i/o measurement - not really a test like the DB will act, but useful as a starting point for tuning.
I'm past the point where a single thread can not keep up with the disk on a sequential scan. For the most simple select * queries, this is ~ 800MB/sec for me.
For any queries those with more complicated processing/filtering, its much less, usually 400MB/sec is a pretty good rate for a single thread.
However our raw array does about 1200MB/sec, and can get 75% efficiency on this or so with between 4 and 8 concurrent sequential scans. It took some significant tuning and testing time to make sure this worked, and to balance that with random i/o requirements.
Furthermore, higher sequential rates help your random IOPS when you have sequential access concurrent with random access. You can tune OS parameters (readahead in linux, I/O scheduler types) to bias throughput or latency towards random iops throughput or sequential MB/sec throughput. Having faster sequential disk access means less % of time doing sequential I/O, meaning more time left for random I/O. It only goes so far, but it does help with mixed loads.
Overall, it depends a lot on how important sequential scans are to your use case.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: When does sequential performance matter in PG?
2009-03-10 14:09 When does sequential performance matter in PG? henk de wit <henk53602@hotmail.com>
@ 2009-03-10 17:50 ` Greg Smith <gsmith@gregsmith.com>
1 sibling, 0 replies; 9+ messages in thread
From: Greg Smith @ 2009-03-10 17:50 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
On Tue, 10 Mar 2009, henk de wit wrote:
> Now I wonder if there is any situation in which sequential IO
> performance comes into play. E.g. perhaps during a tablescan on a
> non-fragmented table, or during a backup or restore?
If you're doing a sequential scan of data that was loaded in a fairly
large batch, you can approach reading at the sequential I/O rate of the
drives. Doing a backup using pg_dump is one situation where you might
actually do that.
Unless your disk performance is really weak, restores in PostgreSQL are
usually CPU bound right now. There's a new parallel restore feature in
8.4 that may make sequential write performance a more likely upper bound
to run into, assuming your table structure is amenable to loading in
parallel (situations with just one giant table won't benefit as much).
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 9+ messages in thread
* Scale out postgresql
@ 2019-03-28 10:10 Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-28 16:10 ` Re: Scale out postgresql Sam R. <samruohola@yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Mariel Cherkassky @ 2019-03-28 10:10 UTC (permalink / raw)
To: pgsql-performance
Hey,
I was searching for a solution to scale my postgresql instance in the
cloud. I'm aware of that that I can create many read only replicas in the
cloud and it would improve my reading performance. I wanted to hear what
solution are you familiar with ? Are there any sharding solution that are
commonly used (citus ? pg_shard ?) My instance has many dbs (one per
customer) and big customers can generate a load of load on others..
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Scale out postgresql
2019-03-28 10:10 Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
@ 2019-03-28 16:10 ` Sam R. <samruohola@yahoo.com>
2019-03-28 16:19 ` Re: Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sam R. @ 2019-03-28 16:10 UTC (permalink / raw)
To: mariel.cherkassky@gmail.com <mariel.cherkassky@gmail.com>; pgsql-performance
Hi!
With following kinds of keywords, it is possible to find / search for cloud native (SQL) implementations e.g. with google:
cloud native sql database
E.g. CockroachDB, YugaByteDB.
I do not know are you planning to do it by other means (by yourself).
I myself would be interested, has someone had experiences with such? Is HA provided "ready made? Is HA working fine and does it recover/handle all situations well, or is additional algorithms needed to be implemented in addition on top e.g. for automatic recovery (by "myself").
I could start an other email chain, if this chain is meant more for something else.
Best RegardsSam
On to, maalisk. 28, 2019 at 12:10, Mariel Cherkassky<mariel.cherkassky@gmail.com> wrote: Hey,I was searching for a solution to scale my postgresql instance in the cloud. I'm aware of that that I can create many read only replicas in the cloud and it would improve my reading performance. I wanted to hear what solution are you familiar with ? Are there any sharding solution that are commonly used (citus ? pg_shard ?) My instance has many dbs (one per customer) and big customers can generate a load of load on others..
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Scale out postgresql
2019-03-28 10:10 Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-28 16:10 ` Re: Scale out postgresql Sam R. <samruohola@yahoo.com>
@ 2019-03-28 16:19 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-28 16:26 ` Re: Scale out postgresql Sumedh Pathak <Sumedh.Pathak@microsoft.com>
0 siblings, 1 reply; 9+ messages in thread
From: Mariel Cherkassky @ 2019-03-28 16:19 UTC (permalink / raw)
To: samruohola@yahoo.com <samruohola@yahoo.com>; +Cc: pgsql-performance
Hey Sam,
Are you familiar with scale solutions that arent in the cloud ?
בתאריך יום ה׳, 28 במרץ 2019 ב-18:10 מאת Sam R. <samruohola@yahoo.com
>:
> Hi!
>
> With following kinds of keywords, it is possible to find / search for
> cloud native (SQL) implementations e.g. with google:
>
> cloud native sql database
>
> E.g. CockroachDB, YugaByteDB.
>
> I do not know are you planning to do it by other means (by yourself).
>
> I myself would be interested, has someone had experiences with such? Is HA
> provided "ready made? Is HA working fine and does it recover/handle all
> situations well, or is additional algorithms needed to be implemented in
> addition on top e.g. for automatic recovery (by "myself").
>
> I could start an other email chain, if this chain is meant more for
> something else.
>
> Best Regards
> Sam
>
>
> On to, maalisk. 28, 2019 at 12:10, Mariel Cherkassky
> <mariel.cherkassky@gmail.com> wrote:
> Hey,
> I was searching for a solution to scale my postgresql instance in the
> cloud. I'm aware of that that I can create many read only replicas in the
> cloud and it would improve my reading performance. I wanted to hear what
> solution are you familiar with ? Are there any sharding solution that are
> commonly used (citus ? pg_shard ?) My instance has many dbs (one per
> customer) and big customers can generate a load of load on others..
>
>
>
> Thanks.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Scale out postgresql
2019-03-28 10:10 Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-28 16:10 ` Re: Scale out postgresql Sam R. <samruohola@yahoo.com>
2019-03-28 16:19 ` Re: Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
@ 2019-03-28 16:26 ` Sumedh Pathak <Sumedh.Pathak@microsoft.com>
0 siblings, 0 replies; 9+ messages in thread
From: Sumedh Pathak @ 2019-03-28 16:26 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; samruohola@yahoo.com <samruohola@yahoo.com>; +Cc: pgsql-performance
Disclaimer, I work for Citus Data.
You can check out www.citusdata.com<http://www.citusdata.com;. We aim to scale out PostgreSQL, and offer it both in the cloud and on-prem. Happy to put you in touch with the right people if you have more questions.
Thanks,
Sumedh
________________________________
From: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Sent: Thursday, March 28, 2019 9:19 AM
To: samruohola@yahoo.com
Cc: PostgreSQL mailing lists
Subject: Re: Scale out postgresql
Hey Sam,
Are you familiar with scale solutions that arent in the cloud ?
בתאריך יום ה׳, 28 במרץ 2019 ב-18:10 מאת Sam R. <samruohola@yahoo.com<mailto:samruohola@yahoo.com>>:
Hi!
With following kinds of keywords, it is possible to find / search for cloud native (SQL) implementations e.g. with google:
cloud native sql database
E.g. CockroachDB, YugaByteDB.
I do not know are you planning to do it by other means (by yourself).
I myself would be interested, has someone had experiences with such? Is HA provided "ready made? Is HA working fine and does it recover/handle all situations well, or is additional algorithms needed to be implemented in addition on top e.g. for automatic recovery (by "myself").
I could start an other email chain, if this chain is meant more for something else.
Best Regards
Sam
On to, maalisk. 28, 2019 at 12:10, Mariel Cherkassky
<mariel.cherkassky@gmail.com<mailto:mariel.cherkassky@gmail.com>> wrote:
Hey,
I was searching for a solution to scale my postgresql instance in the cloud. I'm aware of that that I can create many read only replicas in the cloud and it would improve my reading performance. I wanted to hear what solution are you familiar with ? Are there any sharding solution that are commonly used (citus ? pg_shard ?) My instance has many dbs (one per customer) and big customers can generate a load of load on others..
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-03-28 16:26 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-10 14:09 When does sequential performance matter in PG? henk de wit <henk53602@hotmail.com>
2009-03-10 14:28 ` Matthew Wakeling <matthew@flymine.org>
2009-03-10 14:57 ` henk de wit <henk53602@hotmail.com>
2009-03-10 18:01 ` Scott Carey <scott@richrelevance.com>
2009-03-10 17:50 ` Greg Smith <gsmith@gregsmith.com>
2019-03-28 10:10 Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-28 16:10 ` Re: Scale out postgresql Sam R. <samruohola@yahoo.com>
2019-03-28 16:19 ` Re: Scale out postgresql Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-28 16:26 ` Re: Scale out postgresql Sumedh Pathak <Sumedh.Pathak@microsoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox