Re: Insert are going slower ... - Mailing list pgsql-performance

From Hervé Piedvache
Subject Re: Insert are going slower ...
Date
Msg-id 200407142319.16607.footcow@noos.fr
Whole thread Raw
In response to Re: Insert are going slower ...  (Josh Berkus <josh@agliodbs.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Insert are going slower ...
Next
From: Hervé Piedvache
Date:
Subject: Re: Insert are going slower ...