Message-ID: <5000FE32.2000909@ringerc.id.au>
Date: Sat, 14 Jul 2012 13:05:54 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: B Sreejith <bsreejithin@gmail.com>
CC: Sergey Konoplev <gray.ru@gmail.com>, pgsql-performance@postgresql.org,
	"sreejith. balakrishnan" <sreejith.balakrishnan@tcs.com>
Subject: Re: Any tool/script available which can be used to measure
	scalability of an application's database.
References: 
 <OF43A6984C.A368D80E-ON65257A37.00211A41-65257A37.002DEE2E@tcs.com>
	<CAL_0b1upfYfBdH8E8UVnn2fP2V3BG-shKa_kgzzsEN4180+2Pg@mail.gmail.com>
	<CANVt3iaeej7SSkeUDKJyg49XZy6t49Z0_vCmkGe_nY9VfSm_qw@mail.gmail.com>
In-Reply-To: 
 <CANVt3iaeej7SSkeUDKJyg49XZy6t49Z0_vCmkGe_nY9VfSm_qw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------060402060502080403060205"

This is a multi-part message in MIME format.
--------------060402060502080403060205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------060402060502080403060205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 07/14/2012 09:21 AM, B Sreejith
      wrote:<br>
    </div>
    <blockquote
cite="mid:CANVt3iaeej7SSkeUDKJyg49XZy6t49Z0_vCmkGe_nY9VfSm_qw@mail.gmail.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html;
        charset=ISO-8859-1">
      <p>Dear Sergev,</p>
      <p>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).<br>
        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.<br>
      </p>
    </blockquote>
    <br>
    Sounds like your client / boss has a case of buzz-word-itis.
    "Scalability" means lots of different things:<br>
    <br>
    - How well it copes with growth of data sizes<br>
    - How well it copes with growth of query rates / activity<br>
    - How well it copes with larger user counts (may not be the same as
    prior)<br>
    - Whether it's easily sharded onto multiple systems<br>
    - Whether it has any locking choke-points that serialize common
    operations<br>
    - ....<br>
    <br>
    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):<br>
    <br>
    SELECT id FROM customer WHERE ....<br>
    FOR attribute IN customer<br>
    &nbsp;&nbsp; SELECT :attribute.name FROM customer WHERE id = :customer.id<br>
    &nbsp;&nbsp; IF attribute.is_changed THEN<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE customer SET :attribute.name = :attribute.new_value
    WHERE id = :customer.id<br>
    &nbsp;&nbsp; END IF <br>
    <br>
    and another just does:<br>
    <br>
    UPDATE customer<br>
    SET attribute1 = value1, attribute2 = value2, attribute3 = value3 <br>
    WHERE ....<br>
    <br>
    <br>
    The first will totally melt down under load that isn't significantly
    different from idle as far as the second one is concerned.<br>
    <br>
    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.<br>
    <br>
    Thus, you can't really evaluate the scalability of the database
    under load separately from the application that's using it and the
    workload.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------060402060502080403060205--