> 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@xxxxxxxxxxxxxx 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/
