Re: Performace Optimization for Dummies

> 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(at)postgresql(dot)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/