Message-ID: <489AD118.5010902@archonet.com>
Date: Thu, 07 Aug 2008 11:40:24 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.16 (X11/20080707)
MIME-Version: 1.0
To: dforums <dforums@vieonet.com>
CC: Merlin Moncure <mmoncure@gmail.com>,
 pgsql-performance@postgresql.org
Subject: Re: Plz Heeeelp! performance settings
References: 
 <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>	
	<276541.4189.qm@web25805.mail.ukl.yahoo.com>	
	<35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net>	
	<48908988.7020204@emolecules.com>	
	<Pine.LNX.4.64.0807301656020.4250@aragorn.flymine.org>	
	<1217436578.6288.44.camel@localhost>
	<1240.1217440309@sss.pgh.pa.us>	
	<1217441061.6288.47.camel@localhost> <489A21C2.8060801@vieonet.com>
	<b42b73150808061916j74fd5334y318ef3819fa49ea8@mail.gmail.com>
	<489AA5B5.9080109@vieonet.com> <489AB375.3090901@archonet.com>
	<489ABC8F.4030007@vieonet.com>
In-Reply-To: <489ABC8F.4030007@vieonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

dforums wrote:
> vmstat is giving :
> procs -----------memory---------- ---swap-- -----io---- --system-- 
> ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy 
> id wa
>  0  2   1540  47388  41684 7578976    0    0   131   259    0     1  9 
>  3 82  7

This system is practically idle. Either you're not measuring it at a 
useful time, or there isn't a performance problem.

>  >  > But
>  >> if I use a second machine to replicate the database, I escape this
>  >> problem isn't it ?
>  > You reduce the chance of a single failure causing disaster.
> Not clear this reply. It's scare me ....

If server A fails, you still have server B. If server A fails so that 
replication stops working and you don't notice, server B won't help any 
more.

>  > What do you mean by "take 6Go per week"? You update/delete that much
>  > data? It's growing by that amount each week?
> YES

That wasn't a yes/no question. Please choose one of:
Are you updating 6Go per week?
Are you adding 6Go per week?

>  > I'm not sure what "15000 request per 2 minutes and empty it into 10 min"
>  > means.
> I insert 15000 datas every 2 min and delete 15000 every  10 min in those 
> tables
>  >
>  > Do you have 7500 requests per minute?
> should be that, But in fact I'm not treating the datas in real time, and 
> I buffer the datas and push the data into the database every 2 min
>  > Are these updates?
> during the delete the data are aggregated in other tables which make 
> updates

OK, so every 2 minutes you run one big query that adds 15000 rows.
Every 10 minutes you run one big query that deletes 15000 rows.

>  > To the "temporary storage"?
> 
>  > What is this "temporary storage" - an ordinary table?
> Yes, I thied to use temporary tables but I never been able to connect 
> this tables over 2 different session/connection, seems that is a 
> functionnality of postgresql, or a misunderstanding from me.

That's correct - temporary tables are private to a backend (connection).

>  >  > I'm making some update or select on tables including more than 20
>  >  > millions of entrance.
>  >
>  > Again, I'm not sure what this means.
> 
> To aggregate the data, I have to check the presence of others 
> information that are stores in 2 tables which includes 24 millions of 
> entrance.

OK. I assume you're happy with the plans you are getting on these 
queries, since you've not provided any information about them.

>  > Oh - *important* - which version of PostgreSQL are you running?
> 8.1.11
>  > Is an upgrade practical?
> We are working of trying to upgrade to 8.3.3, but we are not yet ready 
> for such migration

OK

>  > Looking at your postgresql.conf settings:
>  >
>  >   max_connections = 624
>  >
>  > That's an odd number.
> Now we could decrease this number, it's not so much usefull for now. we 
> could decrease is to 350.

I don't believe you've got 350 active connections either. It will be 
easier to help if you can provide some useful information.

>  >   effective_cache_size = 625000
>  >
>  > That's around 5GB - is that roughly the amount of memory used for
>  > caching (what does free -m say for buffers/cache)?
>             total       used       free     shared    buffers     cached
> Mem:          7984       7828        156          0         38       7349
> -/+ buffers/cache:        440       7544
> Swap:          509          1        508

Not far off - free is showing 7349MB cached. You're not running 350 
clients there though - you're only using 440MB of RAM.


I don't see anything to show a performance problem from these emails.

-- 
   Richard Huxton
   Archonet Ltd