Re: Insert are going slower ...

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 = 50000000
>
> 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 = 5000000
>
> 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