Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3BDAA6501C9 for ; Mon, 21 Jul 2008 11:01:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30882-04-7 for ; Mon, 21 Jul 2008 11:01:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152]) by postgresql.org (Postfix) with ESMTP id 9B2C365008C for ; Mon, 21 Jul 2008 11:00:25 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so875416fgg.23 for ; Mon, 21 Jul 2008 07:00:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=BWZsUydtfi6lV4pME3Dc0jRUz3wetL24ty5v6EvthsE=; b=cJLJzloTgxj0FSgKLnladFN5mhtjQhFCLQXNB+hZ0HwWJaLUgmPV096tz7tGjYKLfB lThP6WLLgkxtCoeF8uPafVbB7BkmrLwOjPVo1k649VXfvyOm+/1DBSt3GO7MOFNffOk7 +nQ0hdpM/dpsqf4yxGy07OEJXUrEW58anxSa0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=nO2Dnx4vLEhAXTpNHa0MJLM6ADZq5Gq5S7C32XpWeEOjEJ54kFqcbV5sUj96Tp0+0X g9kbzoiudrUlBZdGhZ3S6rmQ0xaWXrbY6caQnkcSUQNmAWWzDwEmFGsNQact2HcWyLN8 rfvcAAwOooDUU2FS3Hfs8CRkWL6qRFNVdw7iw= Received: by 10.86.80.17 with SMTP id d17mr5008858fgb.33.1216648823921; Mon, 21 Jul 2008 07:00:23 -0700 (PDT) Received: by 10.86.71.5 with HTTP; Mon, 21 Jul 2008 07:00:23 -0700 (PDT) Message-ID: <1568f9ad0807210700k78d55744mcd36838df5b78e8e@mail.gmail.com> Date: Mon, 21 Jul 2008 16:00:23 +0200 From: "Christian GRANDIN" To: "Richard Huxton" Subject: Re: Less rows -> better performance? Cc: "Andreas Hartmann" , pgsql-performance@postgresql.org In-Reply-To: <48847CD6.6080101@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3961_19452135.1216648823926" References: <48846A02.7090702@apache.org> <48846DA3.2090308@archonet.com> <4884722A.1000405@apache.org> <48847CD6.6080101@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/181 X-Sequence-Number: 30781 ------=_Part_3961_19452135.1216648823926 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, Reducing the amount of data will only have effect on table scan or index scan. If your queries are selective and optimized, it will have no effect. Before looking for solutions, the first thing to do is to understand what's happen. If you already know the queries then explain them. Otherwise, you must log duration with the log_statement and log_min_duration parameters in the postgresql.conf. Before this, you must at least run VACUUM ANALYZE on the database to collect actual statistics and have current explain plans. Best regards. Christian 2008/7/21 Richard Huxton > Andreas Hartmann wrote: > >> >> Here's some info about the actual amount of data: >> >> SELECT pg_database.datname, >> pg_size_pretty(pg_database_size(pg_database.datname)) AS size >> FROM pg_database where pg_database.datname = 'vvz_live_1'; >> >> datname | size >> ---------------+--------- >> vvz_live_1 | 2565 MB >> >> I wonder why the actual size is so much bigger than the data-only dump - >> is this because of index data etc.? >> > > I suspect Guillame is right and you've not been vacuuming. That or you've > got a *LOT* of indexes. If the database is only 27MB dumped, I'd just > dump/restore it. > > Since the database is read-only it might be worth running CLUSTER on the > main tables if there's a sensible ordering for them. > > What in particular is slow? >>> >> >> There's no particular bottleneck (at least that we're aware of). During >> the first couple of days after the beginning of the semester the application >> request processing tends to slow down due to the high load (many students >> assemble their schedule). The customer upgraded the hardware (which already >> helped a lot), but they asked us to find further approaches to performance >> optimiziation. >> > > 1. Cache sensibly at the application (I should have thought there's plenty > of opportunity here). > 2. Make sure you're using a connection pool and have sized it reasonably > (try 4,8,16 see what loads you can support). > 3. Use prepared statements where it makes sense. Not sure how you'll manage > the interplay between this and connection pooling in JDBC. Not a Java man > I'm afraid. > > If you're happy with the query plans you're looking to reduce overheads as > much as possible during peak times. > > 4. Offload more of the processing to clients with some fancy ajax-ed > interface. > 5. Throw in a spare machine as an app server for the first week of term. > Presumably your load is 100 times average at this time. > > -- > Richard Huxton > Archonet Ltd > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > ------=_Part_3961_19452135.1216648823926 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi,

Reducing the amount of data will only have effect on table scan or index scan. If your queries are selective and optimized, it will have no effect.

Before looking for solutions, the first thing to do is to understand what's happen.

If you already know the queries then explain them. Otherwise, you must log duration with the log_statement and log_min_duration parameters in the postgresql.conf.

Before this, you must at least run VACUUM ANALYZE on the database to collect actual statistics and have current explain plans.

Best regards.

Christian

2008/7/21 Richard Huxton <dev@archonet.com>
Andreas Hartmann wrote:

Here's some info about the actual amount of data:

SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database where pg_database.datname = 'vvz_live_1';

   datname    |  size
---------------+---------
 vvz_live_1    | 2565 MB

I wonder why the actual size is so much bigger than the data-only dump - is this because of index data etc.?

I suspect Guillame is right and you've not been vacuuming. That or you've got a *LOT* of indexes. If the database is only 27MB dumped, I'd just dump/restore it.

Since the database is read-only it might be worth running CLUSTER on the  main tables if there's a sensible ordering for them.


What in particular is slow?

There's no particular bottleneck (at least that we're aware of). During the first couple of days after the beginning of the semester the application request processing tends to slow down due to the high load (many students assemble their schedule). The customer upgraded the hardware (which already helped a lot), but they asked us to find further approaches to performance optimiziation.

1. Cache sensibly at the application (I should have thought there's plenty of opportunity here).
2. Make sure you're using a connection pool and have sized it reasonably (try 4,8,16 see what loads you can support).
3. Use prepared statements where it makes sense. Not sure how you'll manage the interplay between this and connection pooling in JDBC. Not a Java man I'm afraid.

If you're happy with the query plans you're looking to reduce overheads as much as possible during peak times.

4. Offload more of the processing to clients with some fancy ajax-ed interface.
5. Throw in a spare machine as an app server for the first week of term.    Presumably your load is 100 times average at this time.

--
 Richard Huxton
 Archonet Ltd


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


------=_Part_3961_19452135.1216648823926--