Message-ID: <451C0243.2060307@commandprompt.com>
Date: Thu, 28 Sep 2006 10:11:31 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Thunderbird 1.5.0.7 (X11/20060918)
MIME-Version: 1.0
To: Carlo Stonebanks <stonec.register@sympatico.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Performace Optimization for Dummies
References: <efgu4v$276m$1@news.hub.org>
In-Reply-To: <efgu4v$276m$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


> The import is slow - and degrades as the tables grow. With even more 
> millions of rows in dozens of import tables to come, the imports will take 
> forever. My ability to analyse the queries is limited; because of the nature 
> of the import process, the SQL queries are mutable, every imported row can 
> change the structure of a SQL query as the program adds and subtracts search 
> conditions to the SQL command text before execution. The import program is 
> scripted in Tcl. An attempt to convert our queries to prepared queries 
> (curiousy) did not bring any performance improvements, and we converted back 
> to simplify the code.

How are you loading the tables? Copy? Insert?

> 
> We urgently need a major performance improvement. We are running the 
> PostgreSQL 8.1.4 on a Windows 2003 x64 Server on a dual processor, dual core 
> 3.2Ghz Xeon box with 4gb RAM and a RAID (sorry, I don't know what type) disc 
> subsystem. Sorry about the long intro, but here are my questions:
> 
> 1) Are we paying any big penalties by running Windows vs LINUX (or any other 
> OS)?

Yes. Linux or FreeBSD is going to stomp Win32 for PostgreSQL performance.

> 
> 2) Has the debate over PostgreSQL and Xeon processors been settled? Is this 
> a factor?

Depends. PostgreSQL is much better with the Xeon in general, but are you
running woodcrest based CPUs or the older models?

> 
> 3) Are there any easy-to-use performance analysis/optimisation tools that we 
> can use? I am dreaming of one that could point out problems and suggest 
> and.or effect solutions.

I don't know about Windows, but *nix has a number of tools available
directly at the operating system level to help you determine various
bottlenecks.

> 
> 4) Can anyone recommend any commercial PostgreSQL service providers that may 
> be able to swiftly come in and assist us with our performance issues?

http://www.commandprompt.com/ (disclaimer, I am an employee)

> 
> Below, please find what I believe are the configuration settings of interest 
> in our system
> 
> Any help and advice will be much appreciated. TIA,
> 
> Carlo
> 
> max_connections = 100
> shared_buffers = 50000

This could probably be higher.

> work_mem = 32768

Depending on what you are doing, this is could be to low or to high.

> maintenance_work_mem = 32768
> checkpoint_segments = 128
> effective_cache_size = 10000

This coudl probably be higher.

> random_page_cost = 3
> stats_start_collector = on
> stats_command_string = on
> stats_row_level = on
> autovacuum = on

Stats are a hit... you need to determine if you actually need them.

Joshua D. Drake


> 
> 
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/