pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAny tool/script available which can be used to measure scalability of an application's database.
12+ messages / 7 participants
[nested] [flat]
* Any tool/script available which can be used to measure scalability of an application's database.
@ 2012-07-10 08:21 Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 18:37 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Sergey Konoplev <gray.ru@gmail.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Sreejith Balakrishnan @ 2012-07-10 08:21 UTC (permalink / raw)
To: pgsql-performance
Dear @,
Is there any tool or some sort of script available, for PostgreSQL, which
can be used to measure scalability of an application's database. Or is
there any guideline on how to do this.
I am a bit confused about the concept of measuring scalability of an
application's database.
How is the scalability measured?
Is it like loading the DB with a bulk data volume and then do performance
testing by using tools like JMeter?
Could any one kindly help me on this..
Thanks,
Sreejith.
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
@ 2012-07-13 18:37 ` Sergey Konoplev <gray.ru@gmail.com>
2012-07-14 01:21 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Sergey Konoplev @ 2012-07-13 18:37 UTC (permalink / raw)
To: Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>; +Cc: pgsql-performance
On Tue, Jul 10, 2012 at 12:21 PM, Sreejith Balakrishnan
<sreejith.balakrishnan@tcs.com> wrote:
> Dear @,
>
> Is there any tool or some sort of script available, for PostgreSQL, which
> can be used to measure scalability of an application's database. Or is there
> any guideline on how to do this.
"scalability of an application's database" can be understood either
like a relation of transactions per second to database size or like an
ability of database to be sharded/partitioned or may be like something
else.
Could you please explain more specifically the original task?
What is the goal of it?
> I am a bit confused about the concept of measuring scalability of an
> application's database.
>
> How is the scalability measured?
>
> Is it like loading the DB with a bulk data volume and then do performance
> testing by using tools like JMeter?
>
> Could any one kindly help me on this..
>
> Thanks,
> Sreejith.
>
> =====-----=====-----=====
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
--
Sergey Konoplev
a database and software architect
http://www.linkedin.com/in/grayhemp
Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 18:37 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Sergey Konoplev <gray.ru@gmail.com>
@ 2012-07-14 01:21 ` B Sreejith <bsreejithin@gmail.com>
2012-07-14 05:05 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 12+ messages in thread
From: B Sreejith @ 2012-07-14 01:21 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-performance@postgresql.org, "sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
Dear Sergev,
We have around 15 to 18 separate products.What we are told to do is to
check the scalability of the underlying DB of each product (application).
That's the requirement.Nothing more was explained to us.That's why I said
earlier that I am confused on how to approach this.
Regards,
Sreejith.
On Jul 14, 2012 12:08 AM, "Sergey Konoplev" <gray.ru@gmail.com> wrote:
> On Tue, Jul 10, 2012 at 12:21 PM, Sreejith Balakrishnan
> <sreejith.balakrishnan@tcs.com> wrote:
> > Dear @,
> >
> > Is there any tool or some sort of script available, for PostgreSQL, which
> > can be used to measure scalability of an application's database. Or is
> there
> > any guideline on how to do this.
>
> "scalability of an application's database" can be understood either
> like a relation of transactions per second to database size or like an
> ability of database to be sharded/partitioned or may be like something
> else.
>
> Could you please explain more specifically the original task?
> What is the goal of it?
>
> > I am a bit confused about the concept of measuring scalability of an
> > application's database.
> >
> > How is the scalability measured?
> >
> > Is it like loading the DB with a bulk data volume and then do performance
> > testing by using tools like JMeter?
> >
> > Could any one kindly help me on this..
> >
> > Thanks,
> > Sreejith.
> >
> > =====-----=====-----=====
> > Notice: The information contained in this e-mail
> > message and/or attachments to it may contain
> > confidential or privileged information. If you are
> > not the intended recipient, any dissemination, use,
> > review, distribution, printing or copying of the
> > information contained in this e-mail message
> > and/or attachments to it are strictly prohibited. If
> > you have received this communication in error,
> > please notify us by reply e-mail or telephone and
> > immediately and permanently delete the message
> > and any attachments. Thank you
>
>
>
> --
> Sergey Konoplev
>
> a database and software architect
> http://www.linkedin.com/in/grayhemp
>
> Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 18:37 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Sergey Konoplev <gray.ru@gmail.com>
2012-07-14 01:21 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
@ 2012-07-14 05:05 ` Craig Ringer <ringerc@ringerc.id.au>
2012-07-14 12:49 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 1 reply; 12+ messages in thread
From: Craig Ringer @ 2012-07-14 05:05 UTC (permalink / raw)
To: B Sreejith <bsreejithin@gmail.com>; +Cc: Sergey Konoplev <gray.ru@gmail.com>; pgsql-performance@postgresql.org, "sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
On 07/14/2012 09:21 AM, B Sreejith wrote:
>
> Dear Sergev,
>
> We have around 15 to 18 separate products.What we are told to do is to
> check the scalability of the underlying DB of each product (application).
> That's the requirement.Nothing more was explained to us.That's why I
> said earlier that I am confused on how to approach this.
>
Sounds like your client / boss has a case of buzz-word-itis.
"Scalability" means lots of different things:
- How well it copes with growth of data sizes
- How well it copes with growth of query rates / activity
- How well it copes with larger user counts (may not be the same as prior)
- Whether it's easily sharded onto multiple systems
- Whether it has any locking choke-points that serialize common operations
- ....
Perhaps most importantly, your database is only as scalable as your
application's use of it. Two apps can use exactly the same database
structure, but one of them can struggle massively under load another one
barely notices. For example, if one app does this (pseudocode):
SELECT id FROM customer WHERE ....
FOR attribute IN customer
SELECT :attribute.name FROM customer WHERE id = :customer.id
IF attribute.is_changed THEN
UPDATE customer SET :attribute.name = :attribute.new_value WHERE
id = :customer.id
END IF
and another just does:
UPDATE customer
SET attribute1 = value1, attribute2 = value2, attribute3 = value3
WHERE ....
The first will totally melt down under load that isn't significantly
different from idle as far as the second one is concerned.
That's a ridiculously bad example for the first app, but real examples
that aren't much better arise from badly tuned or badly written object
relational management systems. The classic "N+1 selects" problem and
massive inefficient multiple left outer joins are classics.
Thus, you can't really evaluate the scalability of the database under
load separately from the application that's using it and the workload.
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 18:37 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Sergey Konoplev <gray.ru@gmail.com>
2012-07-14 01:21 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
2012-07-14 05:05 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
@ 2012-07-14 12:49 ` Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 0 replies; 12+ messages in thread
From: Sergey Konoplev @ 2012-07-14 12:49 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: B Sreejith <bsreejithin@gmail.com>; pgsql-performance@postgresql.org, "sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
> We have around 15 to 18 separate products.What we are told to do is to check
> the scalability of the underlying DB of each product (application).
>
>> Sounds like your client / boss has a case of buzz-word-itis. "Scalability"
>> means lots of different things:
Yes, it is still not clear what exactly they want from you, but that
is what I would do...
I would take the metrics Craig described. These ones:
> - How well it copes with growth of data sizes
> - How well it copes with growth of query rates / activity
> - How well it copes with larger user counts (may not be the same as prior)
- Also hard drives activity, CPU, etc
And started to collect this statistics using monitoring tools like
http://www.cacti.net/, for example.
After a week/month/quarter, as time passes and the database activity
and size changes, you will see how the measurements are changed
(usually degraded). So you would be able to make conclusions on
whether your environment meets current requirements or not and to
forecast critical points.
As Craig mentioned, you may also try to simulate your database
activity either with pgbench. I would just like to show you this
article http://www.westnet.com/~gsmith/content/postgresql/pgbench-scaling.htm
where you will find some hints for your case.
Also look at the playback tools
http://wiki.postgresql.org/wiki/Statement_Playback.
--
Sergey Konoplev
a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com
Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
@ 2012-07-13 20:06 ` Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Robert Klemme @ 2012-07-13 20:06 UTC (permalink / raw)
To: Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>; +Cc: pgsql-performance
On Tue, Jul 10, 2012 at 10:21 AM, Sreejith Balakrishnan
<sreejith.balakrishnan@tcs.com> wrote:
> Is there any tool or some sort of script available, for PostgreSQL, which
> can be used to measure scalability of an application's database. Or is there
> any guideline on how to do this.
>
> I am a bit confused about the concept of measuring scalability of an
> application's database.
You cannot measure scalability of a database as such. You need to
know the nature of the load (i.e. operations executed against the DB -
how many INSERT, UPDATE, DELETE and SELECT, against which tables and
with what frequency and criteria). And then, as Sergey said, you need
to define whether you want to scale up the load or the size - or both.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
@ 2012-07-14 01:26 ` B Sreejith <bsreejithin@gmail.com>
2012-07-14 08:48 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 12+ messages in thread
From: B Sreejith @ 2012-07-14 01:26 UTC (permalink / raw)
To: Robert Klemme <shortcutter@googlemail.com>; +Cc: pgsql-performance@postgresql.org, "sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
Dear Robert,
We need to scale up both size and load.
Could you please provide steps I need to follow.
Warm regards,
Sreejith.
On Jul 14, 2012 1:37 AM, "Robert Klemme" <shortcutter@googlemail.com> wrote:
> On Tue, Jul 10, 2012 at 10:21 AM, Sreejith Balakrishnan
> <sreejith.balakrishnan@tcs.com> wrote:
> > Is there any tool or some sort of script available, for PostgreSQL, which
> > can be used to measure scalability of an application's database. Or is
> there
> > any guideline on how to do this.
> >
> > I am a bit confused about the concept of measuring scalability of an
> > application's database.
>
> You cannot measure scalability of a database as such. You need to
> know the nature of the load (i.e. operations executed against the DB -
> how many INSERT, UPDATE, DELETE and SELECT, against which tables and
> with what frequency and criteria). And then, as Sergey said, you need
> to define whether you want to scale up the load or the size - or both.
>
> Kind regards
>
> robert
>
> --
> remember.guy do |as, often| as.you_can - without end
> http://blog.rubybestpractices.com/
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
@ 2012-07-14 08:48 ` Craig Ringer <ringerc@ringerc.id.au>
2012-07-14 09:10 ` Re: Any tool/script available which can be used to measure scalability of an application's database. John Jones <idolchatter@gmail.com>
2012-07-14 09:50 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Craig Ringer @ 2012-07-14 08:48 UTC (permalink / raw)
To: B Sreejith <bsreejithin@gmail.com>; +Cc: Robert Klemme <shortcutter@googlemail.com>; pgsql-performance@postgresql.org, "sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
On 07/14/2012 09:26 AM, B Sreejith wrote:
>
> Dear Robert,
>
> We need to scale up both size and load.
> Could you please provide steps I need to follow.
>
For load, first you need to build a representative sample of your
application's querying patterns by logging queries and analysing the
logs. Produce a load generator based on that data, set up a test copy of
your database, and start pushing the query rate up to see what happens.
For simpler loads you can write a transaction script for pgbench based
on your queries.
For size: Copy your data set, then start duplicating it with munged
copies. Repeat, then use the load generator you wrote for the first part
to see how scaling the data up affects your queries. See if anything is
unacceptably slow (the "auto_explain" module is useful here) and examine it.
The truth is that predicting how complex database driven apps will scale
is insanely hard, because access patterns change as data sizes and user
counts grow. You're likely to land up tuning for a scenario that's quite
different to the one that you actually face when you start hitting
scaling limitations. This doesn't mean you should not investigate, it
just means your trials don't prove anything and the optimisations you
make based on what you learn may not gain you much.
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
2012-07-14 08:48 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
@ 2012-07-14 09:10 ` John Jones <idolchatter@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: John Jones @ 2012-07-14 09:10 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: B Sreejith <bsreejithin@gmail.com>; Robert Klemme <shortcutter@googlemail.com>; pgsql-performance@postgresql.org, "sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
Hammerora is a good start but does have some issues when trying to get it
started. You can also try PGBench. As someone said, there is a plethora of
choices. It all depends on what you want to measure or accomplish.
John Jones
On Sat, Jul 14, 2012 at 1:48 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 07/14/2012 09:26 AM, B Sreejith wrote:
>
> Dear Robert,
>
> We need to scale up both size and load.
> Could you please provide steps I need to follow.
>
>
> For load, first you need to build a representative sample of your
> application's querying patterns by logging queries and analysing the logs.
> Produce a load generator based on that data, set up a test copy of your
> database, and start pushing the query rate up to see what happens.
>
> For simpler loads you can write a transaction script for pgbench based on
> your queries.
>
> For size: Copy your data set, then start duplicating it with munged
> copies. Repeat, then use the load generator you wrote for the first part to
> see how scaling the data up affects your queries. See if anything is
> unacceptably slow (the "auto_explain" module is useful here) and examine it.
>
> The truth is that predicting how complex database driven apps will scale
> is insanely hard, because access patterns change as data sizes and user
> counts grow. You're likely to land up tuning for a scenario that's quite
> different to the one that you actually face when you start hitting scaling
> limitations. This doesn't mean you should not investigate, it just means
> your trials don't prove anything and the optimisations you make based on
> what you learn may not gain you much.
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
2012-07-14 08:48 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
@ 2012-07-14 09:50 ` B Sreejith <bsreejithin@gmail.com>
2012-07-14 12:17 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
1 sibling, 1 reply; 12+ messages in thread
From: B Sreejith @ 2012-07-14 09:50 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: sreejith. balakrishnan <sreejith.balakrishnan@tcs.com>; pgsql-performance@postgresql.org, Robert Klemme <shortcutter@googlemail.com>
Dear All,
Thanks alot for all the invaluable comments.
Regards,
Sreejith.
On Jul 14, 2012 2:19 PM, "Craig Ringer" <ringerc@ringerc.id.au> wrote:
> On 07/14/2012 09:26 AM, B Sreejith wrote:
>
> Dear Robert,
>
> We need to scale up both size and load.
> Could you please provide steps I need to follow.
>
>
> For load, first you need to build a representative sample of your
> application's querying patterns by logging queries and analysing the logs.
> Produce a load generator based on that data, set up a test copy of your
> database, and start pushing the query rate up to see what happens.
>
> For simpler loads you can write a transaction script for pgbench based on
> your queries.
>
> For size: Copy your data set, then start duplicating it with munged
> copies. Repeat, then use the load generator you wrote for the first part to
> see how scaling the data up affects your queries. See if anything is
> unacceptably slow (the "auto_explain" module is useful here) and examine it.
>
> The truth is that predicting how complex database driven apps will scale
> is insanely hard, because access patterns change as data sizes and user
> counts grow. You're likely to land up tuning for a scenario that's quite
> different to the one that you actually face when you start hitting scaling
> limitations. This doesn't mean you should not investigate, it just means
> your trials don't prove anything and the optimisations you make based on
> what you learn may not gain you much.
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
2012-07-14 08:48 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
2012-07-14 09:50 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
@ 2012-07-14 12:17 ` Robert Klemme <shortcutter@googlemail.com>
2012-07-14 15:10 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 12+ messages in thread
From: Robert Klemme @ 2012-07-14 12:17 UTC (permalink / raw)
To: pgsql-performance
On Sat, Jul 14, 2012 at 11:50 AM, B Sreejith <bsreejithin@gmail.com> wrote:
> Dear All,
> Thanks alot for all the invaluable comments.
Additionally to Craig's excellent advice to measurements there's
something else you can do: with the knowledge of the queries your
application fires against the database you can evaluate your schema
and index definitions. While there is no guarantee that your
application will scale well if all indexes are present you believe
need to be present based on that inspection, you can pretty easily
identify tables with can be improved. These are tables which a) are
known to grow large and b) do not have indexes nor no indexes which
support the queries your application does against these tables which
will result in full table scans. Any database which scales in size
will sooner or later hit a point where full table scans of these large
tables will be extremely slow. If these queries are done during
regular operation (and not nightly maintenance windows for example)
then you pretty surely have identified a show stopper.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Any tool/script available which can be used to measure scalability of an application's database.
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 20:06 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
2012-07-14 08:48 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Craig Ringer <ringerc@ringerc.id.au>
2012-07-14 09:50 ` Re: Any tool/script available which can be used to measure scalability of an application's database. B Sreejith <bsreejithin@gmail.com>
2012-07-14 12:17 ` Re: Any tool/script available which can be used to measure scalability of an application's database. Robert Klemme <shortcutter@googlemail.com>
@ 2012-07-14 15:10 ` Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 12+ messages in thread
From: Craig Ringer @ 2012-07-14 15:10 UTC (permalink / raw)
To: Robert Klemme <shortcutter@googlemail.com>; +Cc: pgsql-performance
On 07/14/2012 08:17 PM, Robert Klemme wrote:
> On Sat, Jul 14, 2012 at 11:50 AM, B Sreejith <bsreejithin@gmail.com> wrote:
>> Dear All,
>> Thanks alot for all the invaluable comments.
> Additionally to Craig's excellent advice to measurements there's
> something else you can do: with the knowledge of the queries your
> application fires against the database you can evaluate your schema
> and index definitions. While there is no guarantee that your
> application will scale well if all indexes are present
Don't forget that sometimes it's better to DROP an index that isn't used
much, or that only helps occasional queries that aren't time-sensitive.
Every index has a cost to maintain - it slows down your inserts and
updates and it competes for disk cache with things that might be more
beneficial.
> b) do not have indexes nor no indexes which
> support the queries your application does against these tables which
> will result in full table scans.
A full table scan is not inherently a bad thing, even for a huge table.
Sometimes you just need to examine every row, and the fastest way to do
that is without a doubt a full table scan.
Remember, a full table scan won't tend to push everything out of
shared_buffers, so it can also avoid competition for cache.
(If anyone ever wants concurrent scans badly enough to implement them,
full table scans with effective_io_concurrency > 1 will become a *lot*
faster for some types of query).
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2012-07-14 15:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-10 08:21 Any tool/script available which can be used to measure scalability of an application's database. Sreejith Balakrishnan <sreejith.balakrishnan@tcs.com>
2012-07-13 18:37 ` Sergey Konoplev <gray.ru@gmail.com>
2012-07-14 01:21 ` B Sreejith <bsreejithin@gmail.com>
2012-07-14 05:05 ` Craig Ringer <ringerc@ringerc.id.au>
2012-07-14 12:49 ` Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
2012-07-13 20:06 ` Robert Klemme <shortcutter@googlemail.com>
2012-07-14 01:26 ` B Sreejith <bsreejithin@gmail.com>
2012-07-14 08:48 ` Craig Ringer <ringerc@ringerc.id.au>
2012-07-14 09:10 ` John Jones <idolchatter@gmail.com>
2012-07-14 09:50 ` B Sreejith <bsreejithin@gmail.com>
2012-07-14 12:17 ` Robert Klemme <shortcutter@googlemail.com>
2012-07-14 15:10 ` Craig Ringer <ringerc@ringerc.id.au>
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