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