On Fri, 7 May 2004, Michael Ryan S. Puncia wrote:

> Hi,
>             I am a newbie here and just starting to use postgresql. My
> problems is how to tune up my server because it its too slow.

First, read this:


> This is my PC specs: P4, 512Ram, Linux 9

get more ram.

Hard Drives:  interface, how many, RAID???

For a mostly read database IDEs are pretty good.  Having multiple drives 
in a RAID-5 or RAID1+0 works well on a mostly read database too.  Keep the 
stripe size small is setting up a RAID array for a database.

> Because I am working in a statistical organization we have a very large data
> volume
> These are my data:
> Table 1 with 60 million data but only with 10 fields
> Table 2 with 30 million data with 15 fields
> Table 3 with 30 million data with 10 fields

That's not really that big, but it's big enough you have to make sure your 
server is tuned properly.

> I will only use this server for querying ... I already read and apply those
> articles found in the archives section but still the performance is not
> good.
> I am planning to add another 512 RAM .Another question is how to calculate
> shared_buffer size ..

I'm assuming you've recently vacuumed full and analyzed your database...

Shared buffers should probably be between 1000 and 10000 on about 98% of 
all installations.  Setting it higher than 25% of memory is usually a bad 
idea.  Since they're in 8k blocks (unless you compiled with a customer 
block size, you'd know if you did, it's not something you can accidentally 
do by leaning on the wrong switch...) you probably want about 10000 blocks 
or so to start, which will give you about 80 megs of shared buffer.

PostgreSQL doesn't really cache as well as the kernel, so it's better to 
leave more memory available for kernel cache than you allocate to buffer 
cache.  On a machine with only 512Meg, I'm guessing you'll get about 128 
to 200 megs of kernel cache if you're only running postgresql and you have 
it set to 10000 buffers.

The important things to check / set are things lik effective_cache_size.  
It too is measured in 8k blocks, and reflects the approximate amount of 
kernel cache being dedicated to postgresql.  assuming a single service 
postgresql only box, that will be the number that a server that's been up 
for a while shows under top like so:

  9:50am  up 12:16,  4 users,  load average: 0.00, 0.00, 0.00                          
104 processes: 102 sleeping, 2 running, 0 zombie, 0 stopped
CPU states:  0.7% user,  0.3% system,  0.0% nice,  1.7% idle
Mem:   512924K av,  499248K used,   13676K free,       0K shrd,   54856K buff
Swap: 2048248K av,    5860K used, 2042388K free                  229572K cached

the 229572k cached entry shows about 230 megs.  divided by 8192 we get 
about 28000.

sort_mem might do with a small bump, especially if you're only handling a 
few connections at a time.  Be careful, it's per sort, and measured in 
megs, so it's easy for folks to set it too high and make their machine 
start flushing too much kernel cache, which will slow down the other 
backends that have to go to disk for data.

A good starting point for testing is anywhere from 8192 to 32768.  32768 
is 32 megs, which can starve a machine as small as yours if there are a 
couple of queries each running a couple of sorts on large sets at the same 

Lastly, using explain analyze <your query here> you can see if postgresql 
is making a bad plan choice.  compared estimated rows to actual rows.  
Look for things like nested loops being run on what the planner thinks 
will be 80 rows but is, in fact, 8000 rows.

You can change random page cost to change the tendency of the server to 
favor seq scans to index scans.  Lower = greater tendency towards index 
scans.  the default is 4, but most production servers with enough memory 
to cache most of their data will run well on a setting of 1.2 to 1.4.  My 
dual 2800 with 2 gig ram runs quite well at 1.3 to 1.4. 

You can also change the settings to random_page_cost, as well as turning 
off options to the planner with the following env vars:


They are all on by default, and shouldn't really be turned off by default 
for the most part.  but for an individual session to figure out if the 
query planner is making the right plan you can set them to off to see if 
using another plan works better.  

so, if you've got a nested loop running over 80000 rows that the planner 
thought was gonna be 80 rows, you can force it to stop using the nested 
loop for your session with:

set enable_nestloop=off;

and use explain analyze to see if it runs faster.

You can set effective_cache_size and sort_mem on the fly for a single 
connection, or set them in postgresql.conf and restart or reload to make a 
change in the default.

shared_buffers is set on postgresql startup, and can't be changed without 
restarting the database.  Reloading won't do it.

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


Reply via email to