Re: [PERFORM] Insert are going slower ...

2004-07-26 Thread Gaetano Mendola
Josh Berkus wrote:
Herve'
I forgot to ask about your hardware.   How much RAM, and what's your disk 
setup?  CPU?


sort_mem =   512000

Huh?   Sort_mem is in K.   The above says that you've allocated 512MB sort 
mem.  Is this process the *only* thing going on on the machine?
And also is not system wide but let me say for backend...

Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Insert are going slower ...

2004-07-26 Thread Gaetano Mendola
Hervé Piedvache wrote:
Josh,
Le mardi 13 Juillet 2004 19:10, Josh Berkus a écrit :
What can I do to get better results ?? (configuration option, and/or
hardware update ?)
What can I give you to get more important informations to help me ?
1) What PostgreSQL version are you using?

v7.4.3

2) What's your VACUUM, ANALYZE, VACUUM FULL, REINDEX schedule?

VACUUM FULL VERBOSE ANALYZE;
Every day after the calculation I was talking about ...

3) Can you list the non-default settings in your PostgreSQL.conf?
Particularly, shared_buffers, sort_mem, checkpoint_segments,
estimated_cache, and max_fsm_pages?


sort_mem =   512000
This is too much, you are instructing Postgres to use 512MB
for each backend ( some time each backend can use this quantity
more then one )
vacuum_mem = 409600
max_fsm_pages = 5000
 max_fsm_relations = 2000
50 milions ? HUG.
what tell you postgres in the log after performing
a vacuum full ?
max_files_per_process = 2000
wal_buffers = 1000
checkpoint_segments = 3
For massive insert you have to increase this number,
pump it up to 16

effective_cache_size = 500
5GB for 8 GB system is too much
random_page_cost = 3	
on your HW you can decrease it to 2
and also decrease the other cpu costs
Regards
Gaetano Mendola
BTW, I live in Paris too, if you need a hand...


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] Insert are going slower ...

2004-07-26 Thread Scott Marlowe
On Mon, 2004-07-26 at 08:20, Gaetano Mendola wrote:
 Herv Piedvache wrote:

SNIP

  sort_mem =   512000
 
 This is too much, you are instructing Postgres to use 512MB
 for each backend ( some time each backend can use this quantity
 more then one )

agreed.  If any one process needs this much sort mem, you can set it in
that sessions with set sort_mem anyway, so to let every sort consume up
to 512 meg is asking for trouble.

  effective_cache_size = 500
 
 5GB for 8 GB system is too much

No, it's not.  Assuming that postgresql with all it's shared buffers is
using 2 gig, it's quite likely that the kernel is caching at least 5
gigs of disk data.  Effective cache size doesn't set any cache size, it
tells the planner about how much the kernel is caching.

  random_page_cost = 3
 
 on your HW you can decrease it to 2
 and also decrease the other cpu costs

On fast machines it often winds up needing to be set somewhere around
1.2 to 2.0


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] Insert are going slower ...

2004-07-18 Thread Josh Berkus
Herve'

 Hum ... it's only for speed aspect ... I was using postgresql with this
 option since 7.01 ... and for me fsync=on was so slow ...
 Is it really no time consuming for the system to bring it ON now with
 v7.4.3 ??

Well, I wouldn't do it until you've figured out the current performance 
problem.

The issue with having fsync=off is that, if someone yanks the power cord on 
your server, there is a significant chance that you will have to restore the 
database from backup becuase it will be corrupted.   But clearly you've been 
living with that risk for some time.

It *is* true that there is significantly less performance difference between 
7.4 with fsync off and on than there was between 7.1 with fsync off and on.  
But there is still a difference.   In 7.0 and 7.1 (I think), when you turned 
fsync off it turned WAL off completely, resulting in a substantial difference 
in disk activity.   Now, it just stops checkpointing WAL but WAL is still 
recording -- meaning that disk activity decreases some but not a lot.   The 
difference is more noticable the more vulnerable to contention your disk 
system is.

The other reason not to think of fsync=off as a permanent performance tweak is 
that we're likely to remove the option sometime in the next 2 versions, since 
an increasing number of features depend on WAL behavior, and the option is 
largely a legacy of the 7.0 days, when WAL was sometimes buggy and needed to 
be turned off to get the database to start.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Insert are going slower ...

2004-07-16 Thread Herv Piedvache
Josh,

Le jeudi 15 Juillet 2004 20:09, Josh Berkus a écrit :
  I suggest you check this first. Check the performance tuning guide..
 
  http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
 
  That is a starters. As Josh suggested, increase checkpoint segments if
  you

 have

  disk space. Correspondingly WAL disk space requirements go up as well.

 Well, not if he has fsync=off.   But having fsync off is a very bad idea. 
 You do realize, Herve', that if you lose power on that machine you'll most
 likely have to restore from backup?

Hum ... it's only for speed aspect ... I was using postgresql with this option 
since 7.01 ... and for me fsync=on was so slow ...
Is it really no time consuming for the system to bring it ON now with 
v7.4.3 ??

Tell me ...
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] Insert are going slower ...

2004-07-15 Thread Shridhar Daithankar
Hervé Piedvache wrote:
Josh,
Le mercredi 14 Juillet 2004 18:28, Josh Berkus a écrit :
checkpoint_segments = 3
You should probably increase this if you have the disk space.  For massive
insert operations, I've found it useful to have as much as 128 segments
(although this means about 1.5GB disk space)

Other point I have also read this : 
NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop 
checkpointing.

So ... still true for 7.4.3 ??? So I'm with fsync = off so the value of 
checkpoint_segments have no interest ??

Thanks for your help...
I suggest you check this first. Check the performance tuning guide..
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
That is a starters. As Josh suggested, increase checkpoint segments if you have 
disk space. Correspondingly WAL disk space requirements go up as well.

HTH
 Shridhar
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] Insert are going slower ...

2004-07-15 Thread Josh Berkus
Shridhar,

 I suggest you check this first. Check the performance tuning guide..
 
 http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
 
 That is a starters. As Josh suggested, increase checkpoint segments if you 
have 
 disk space. Correspondingly WAL disk space requirements go up as well.

Well, not if he has fsync=off.   But having fsync off is a very bad idea.  You 
do realize, Herve', that if you lose power on that machine you'll most likely 
have to restore from backup?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread gnari

From: Hervé Piedvache [EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 11:42 PM


 effective_cache_size = 500

looks like madness to me.
my (modest) understanding of this, is that
you are telling postgres to assume a 40Gb disk
cache (correct me if I am wrong).

btw, how much effect does this setting have on the planner?

is there a recommended procedure to estimate
the best value for effective_cache_size on a
dedicated DB server ?

gnari






---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread Shridhar Daithankar
gnari wrote:
is there a recommended procedure to estimate
the best value for effective_cache_size on a
dedicated DB server ?
Rule of thumb(On linux): on a typically loaded machine, observe cache memory of 
the machine and allocate good chunk of it as effective cache.

To define good chunck of it, you need to consider how many other things are 
running on that machine. If it is file server + web server + database server, 
you have to allocate the resources depending upon requirement.

But remember It does not guarantee that it will be a good value. It is just a 
starting point..:-) You have to tune it further if required.

HTH
 Shridhar
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread Herv Piedvache
Le mercredi 14 Juillet 2004 12:13, Shridhar Daithankar a écrit :
 gnari wrote:
  is there a recommended procedure to estimate
  the best value for effective_cache_size on a
  dedicated DB server ?

 Rule of thumb(On linux): on a typically loaded machine, observe cache
 memory of the machine and allocate good chunk of it as effective cache.

 To define good chunck of it, you need to consider how many other things are
 running on that machine. If it is file server + web server + database
 server, you have to allocate the resources depending upon requirement.

 But remember It does not guarantee that it will be a good value. It is just
 a starting point..:-) You have to tune it further if required.

In my case it's a PostgreSQL dedicated server ...

effective_cache_size = 500 

For me I give to the planner the information that the kernel is able to cache 
500 disk page in RAM

free
 total   used   free sharedbuffers cached
Mem:   79591207712164 246956  0  173727165704
-/+ buffers/cache: 5290887430032
Swap:  2097136   98802087256

What should I put ?

Regards,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread Shridhar Daithankar
Hervé Piedvache wrote:
In my case it's a PostgreSQL dedicated server ...
effective_cache_size = 500 

For me I give to the planner the information that the kernel is able to cache 
500 disk page in RAM
That is what? 38GB of RAM?

free
 total   used   free sharedbuffers cached
Mem:   79591207712164 246956  0  173727165704
-/+ buffers/cache: 5290887430032
Swap:  2097136   98802087256
What should I put ?
7165704 / 8 = 895713
So counting variations, I would say 875000. That is a 8GB box, right? So 875000 
is about 7000MB. Which should be rather practical. Of course you can give it 
everything you can but that's upto you.

Can you get explain analze for inserts? I think some foreign key check etc. are 
taking long and hence it accumulates. But that is just a wild guess.

Off the top of my head, you have allocated roughly 48K shard buffers which seems 
bit on higher side. Can you check with something like 10K-15K?

HTH
 Shridhar
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread Josh Berkus
Herve'

I forgot to ask about your hardware.   How much RAM, and what's your disk 
setup?  CPU?

 sort_mem =   512000

Huh?   Sort_mem is in K.   The above says that you've allocated 512MB sort 
mem.  Is this process the *only* thing going on on the machine?

 vacuum_mem = 409600

Again, 409.6MB vacuum mem?   That's an odd number, and quite high.  

 max_fsm_pages = 5000

50million?   That's quite high.   Certianly enough to have an effect on your 
memory usage.   How did you calculate this number?

 checkpoint_segments = 3

You should probably increase this if you have the disk space.  For massive 
insert operations, I've found it useful to have as much as 128 segments 
(although this means about 1.5GB disk space)

 effective_cache_size = 500

If you actually have that much RAM, I'd love to play on your box.  Please?

 Off the top of my head, you have allocated roughly 48K shard buffers which
 seems bit on higher side. Can you check with something like 10K-15K?

Shridhar, that depends on how much RAM he has.   On 4GB dedicated machines, 
I've set Shared_Buffers as high as 750MB.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread Herv Piedvache
Josh,

Le mercredi 14 Juillet 2004 18:28, Josh Berkus a écrit :

 I forgot to ask about your hardware.   How much RAM, and what's your disk
 setup?  CPU?

8 Gb of RAM
Bi - Intel Xeon 2.00GHz
Hard drive in SCSI RAID 5
/dev/sdb6 101G   87G  8.7G  91% /usr/local/pgsql/data
/dev/sda7 1.8G  129M  1.6G   8% /usr/local/pgsql/data/pg_xlog

Server dedicated to PostgreSQL with only one database.

  sort_mem =   512000

 Huh?   Sort_mem is in K.   The above says that you've allocated 512MB sort
 mem.  Is this process the *only* thing going on on the machine?

PostgreSQL dedicated server yes ... so it's too much ?
How you decide the good value ?

  vacuum_mem = 409600

 Again, 409.6MB vacuum mem?   That's an odd number, and quite high.

Yep but I have 8 Gb of memory ... ;o) So why not ?
Just explain me why it's not a good choice ... I have done this because of 
this text from you found somewhere :
As this setting only uses RAM when VACUUM is running, you may wish to 
increase it on high-RAM machines to make VACUUM run faster (but never more 
than 20% of available RAM!)
So that's less than 20% of my memory ...

  max_fsm_pages = 5000

 50million?   That's quite high.   Certianly enough to have an effect on
 your memory usage.   How did you calculate this number?

Not done by me ... and the guy is out ... but in same time with 8 Gb of 
RAM ... that's not a crazy number ?

  checkpoint_segments = 3

 You should probably increase this if you have the disk space.  For massive
 insert operations, I've found it useful to have as much as 128 segments
 (although this means about 1.5GB disk space)

  effective_cache_size = 500

 If you actually have that much RAM, I'd love to play on your box.  Please?

Hum ... yes as Shridhar told me the number is a crazy one and now down to 
875000 ...

  Off the top of my head, you have allocated roughly 48K shard buffers
  which seems bit on higher side. Can you check with something like
  10K-15K?

 Shridhar, that depends on how much RAM he has.   On 4GB dedicated machines,
 I've set Shared_Buffers as high as 750MB.

Could you explain me the interest to reduce this size ??
I really miss understand this point ...

regards,
-- 
Bill Footcow


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] Insert are going slower ...

2004-07-14 Thread Herv Piedvache
Josh,

Le mercredi 14 Juillet 2004 18:28, Josh Berkus a écrit :

  checkpoint_segments = 3

 You should probably increase this if you have the disk space.  For massive
 insert operations, I've found it useful to have as much as 128 segments
 (although this means about 1.5GB disk space)

Other point I have also read this : 
NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop 
checkpointing.

So ... still true for 7.4.3 ??? So I'm with fsync = off so the value of 
checkpoint_segments have no interest ??

Thanks for your help...
-- 
Bill Footcow


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] Insert are going slower ...

2004-07-13 Thread Herv Piedvache
Josh,

Le mardi 13 Juillet 2004 19:10, Josh Berkus a écrit :

  What can I do to get better results ?? (configuration option, and/or
  hardware update ?)
  What can I give you to get more important informations to help me ?

 1) What PostgreSQL version are you using?

v7.4.3

 2) What's your VACUUM, ANALYZE, VACUUM FULL, REINDEX schedule?

VACUUM FULL VERBOSE ANALYZE;

Every day after the calculation I was talking about ...

 3) Can you list the non-default settings in your PostgreSQL.conf?
 Particularly, shared_buffers, sort_mem, checkpoint_segments,
 estimated_cache, and max_fsm_pages?

shared_buffers = 48828
sort_mem =   512000
vacuum_mem = 409600
max_fsm_pages = 5000
max_fsm_relations = 2000
max_files_per_process = 2000
wal_buffers = 1000
checkpoint_segments = 3
effective_cache_size = 500
random_page_cost = 3
default_statistics_target = 20
join_collapse_limit = 10

Regards,
-- 
Hervé Piedvache


---(end of broadcast)---
TIP 8: explain analyze is your friend