> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
No comments:
Post a Comment