Re: Plz Heeeelp! performance settings

The performance problem is really only on the insertion and even more on
the treatment for the aggregation.

To treat the 3000 entrances and to insert, or update the tables it needs
10 minutes.

As I told you I inject 14000 query every 2 minutes, and it needs 10
minutes to treat 3000 of those query.

As you can easly understand it's a big narrow section.

I'm not doing the treatment in ones, cause I can't, but all is managed
by procedure.

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

This action depend if the data are already present in the database.


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

The plan seems ok as it use index as well.
here is the plan :

explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06
19:28:54'::text)::date,3,'dailydisplay',2,NULL);
INFO:  method 1
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151
rows=1 loops=1)
  Total runtime: 1.160 ms
(2 lignes)



  Has you can see the runtime processs for an update in this table.

multiplying this per 10000, it is too long.

regards

david


Richard Huxton a écrit :
> 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.
>

--
<http://www.1st-affiliation.fr>

*David Bigand
*Président Directeur Générale*
*51 chemin des moulins
73000 CHAMBERY - FRANCE

Web : htttp://www.1st-affiliation.fr
Email : david@1st-affiliation.com
Tel. : +33 479 696 685
Mob. : +33 666 583 836
Skype : firstaffiliation_support