From: Richard Huxton <dev@archonet.com>
To: dforums <dforums@vieonet.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Plz Heeeelp! performance settings
Date: Thu, 07 Aug 2008 11:40:24 +0100
Message-ID: <489AD118.5010902@archonet.com> (raw)
In-Reply-To: <489ABC8F.4030007@vieonet.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, dforums@vieonet.com, mmoncure@gmail.com
Subject: Re: Plz Heeeelp! performance settings
In-Reply-To: <489AD118.5010902@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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