Re: Now I am back, next thing. Final PGS tuning.

On Wed, Apr 8, 2009 at 5:23 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:

> In response to Jennifer Trey <jennifer(dot)trey(at)gmail(dot)com>:
> >
> > I have 8GB memory, Intel Quad Core 2.4Ghz with 8MB L2 cache. I am running
> > Windows Web Server 2008 x64 and will be running a Java (64 bit version)
> > application.
> >
> > I want to give the java app room for working on 2-3GB. The operating
> system
> > is currently consuming around 1GB but lets give it a little more room.
> Lets
> > give it a total of 2GB.
> >
> > That leaves 8-3-2 = 3 GB for PostgreSQL alone. Sounds good?
> >
> > Here is my config file :
> >
> > http://85.235.31.35/resources/postgresql.conf
> >
> > I see there is a setting
> >
> > *max_connections = 100*
> >
> > What does this do? Should I be looking at this as max similtaneous
> queries ?
> > is 100 really enough? I think I want to max this more.
> >
> > I am looking for a worst scenario around like 50-100 similitaneous user
> > clicks (per second?). But the querying might be around like 200 queries
> per
> > seocond, not really, but I want to be prepared. :)
>
> Depends on how long your "clicks" take to process. If you're doing 100
> page views (clicks) /second and each view takes 2 seconds to process,
> you're
> tying up 200 connections on a continual basis.
>
> Unless you're using some sort of connection pooling ... I'm no Java expert,
> but doesn't Java have connection pooling built in? If so, it becomes
> more difficult to estimate the # of simultaneous connections because each
> instance of a running script might share a connection with other scripts.
>
> In that case, you'll probably have to test to see what a good max is, as
> it's going to be difficult or impossible to estimate.
>
> In any event, 100 is probably a good starting point (based on my
> experience). Note that if you find that you have to raise that value too
> high, (much over a few hundred) then you probably want to investigate some
> form of connection pooling, such as pgpool.
>
**
*Yes.* I think java uses these things. Looking at jConsole I can see that
there is these things (pools) going on.
I think I will increase this to 175. Just to be on the safe side...

> > Here is some other settings I am thinking about :
> >
> > *effective_cache_size = 449697*
>
> What version of Postgres? In modern versions, you can specify MB, GB, etc.
>
I use 8.3.7 for windows.
I think this is kb since no MB is specified afterwards, which makes it 439
MB. The old value before tuning wizard was 128 MB.

>
>
> This value should be the memory that's left unused when everything is
> running (including Postgres). It helps the planner estimate how much of
> the filesystem is cached in memory. Based on the other numbers you've
> mentioned, this should probably be set to about 2G.
>
> > *maintenance_work_mem = 16384 *
> >
> > *work_mem = 1024 # I think this is kb. Way to low, right? What is a
> better
> > value?*
>
> I haven't noticed much value in tweaking this. It only affects a few
> commands, such as vacuum and analyze. Test to see if tweaking it speeds
> up vacuum without pushing the system into swap.

Yes, I will leave those as is then. But is it possible to set a time on when
the auto vacuum should kick in? Perhpas late at night would be better than
in the day.

>
>
> > *shared_buffers = 1024 # min 128kB or max_connections*16kB ## Also to
> low.
> > Right? I've got 3GB to work with!*
>
> Assuming that's equating to 1G, then the value is about right. Common
> best practice is to set this value to 1/4 - 1/3 of the memory available
> for PostgreSQL. You're saying you'll have ~3G for PG, so 1G is about
> right to start with.

Yes, about 3GB but now I started to think about the OS cache aswell, which I
believe will be involved so perhpas put his at 2.75 GB with at java 2.75GB
and 2.5GB on the OS.

>
>
> Once the system is up and running, you can install pg_buffercache to
> monitor usage and help tune it.
>
> > *wal_buffers = 256 # Also kB...*
> >
> > Please give your thoughts. I was also wondering about the Vacuum, force
> > reindex and stuff. Are those things good to run once in a while? Force
> > sounds a little brutal though!
>
> Turn on autovacuum. I've found it's the best way to go in 99% of installs
> (the corner cases being servers that have _very_ predictable workloads ...
> in which case explicit, scheduled vacuums are better).

I will :) But as I mentioned earlier. Is there a way to set a more suited
time for this happen (autovacuum)?

>
>
> REINDEXing is an occasional topic of discussion. Doing it occasionally
> definitely saves disk space on frequently updated databases, but the
> impact (if any) on performance is a subject for debate. I've yet to see
> any drastic performance improvement from REINDEXing, but if you've got
> obvious off-peak times (i.e., if nobody uses the system over weekends or
> something) it probably doesn't hurt to reindex everything on a regular
> schedule. Don't obsess over it, though.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>