Re: Plz Heeeelp! performance settings - Mailing list pgsql-performance

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