From: Hervé Piedvache <footcow@noos.fr>
To: pgsql-performance@postgresql.org
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Hervé Piedvache <herve@elma.fr>
Cc: Shridhar Daithankar <shridhar@frodo.hserus.net>
Subject: Re: Insert are going slower ...
Date: Wed, 14 Jul 2004 23:19:16 +0200
Message-ID: <200407142319.16607.footcow@noos.fr> (raw)
In-Reply-To: <200407140928.29498.josh@agliodbs.com>
References: <200407131850.52095.herve@elma.fr>
<200407140142.11720.footcow@noos.fr>
<200407140928.29498.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: footcow@noos.fr, josh@agliodbs.com, herve@elma.fr, shridhar@frodo.hserus.net
Subject: Re: Insert are going slower ...
In-Reply-To: <200407142319.16607.footcow@noos.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox