Re: Performace Optimization for Dummies - Mailing list pgsql-performance

> How are you loading the tables? Copy? Insert?

Once the data is transformed, it is inserted. I don't have stats, but the
programs visual feedback does not spend a lot of time on the "inserting
data" message. Then again, if there is an asynchronous component to an
insert, perhaps I am not seeing how slow an insert really is until I query
the table.

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

Don't suppose you'd care to hazard a guess on what sort of scale we're
talking about? Are we talking about 10%? 100% I know this is a hard one to
judge,  My impression was that the *NIX improvements were with concurrent
use and right now, I am obsessing on this single-threaded issue.

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

Weren't those released in July? This server is a few months older, so I
guess not. But maybe? Does Dell have the ability to install CPUs from the
future like Cyberdyne does? ;-)

> 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.

Are we talking about I/O operations? I was thinking of SQL query analysis.
The stuff I read here about query analysis is pretty intruiging, but to
someone unfamiliar with this type of query analysis it all looks quite
uncertain to me. I mean, I read the threads and it all looks like a lot of
trying ot figure out how to cajole PostgreSQL to do what you want, rather
than telling it: HEY I CREATED THAT INDEX FOR A REASON, USE IT!

I know this may be non-dba sophistication on my part, but I would like a
tool that would make this whole process a little less arcane. I'm not the
Gandalf type.

>> 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)

Very much appreciated.

>> max_connections = 100
>> shared_buffers = 50000
>
 This could probably be higher.

Ok, good start...

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

Is this like "You could be too fat or too thin"? Aren't you impressed with
the fact that I managed to pick the one number that was not right for
anything?

>
>> maintenance_work_mem = 32768
>> checkpoint_segments = 128
>> effective_cache_size = 10000
>
> This coudl probably be higher.

... noted...

>
>> 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.

Unfortunately, this is the only way I know of of getting the query string to
appear in the PostgreSQL server status display. While trying to figure out
what is slowing things down, having that is really helpful. I also imagined
that this sort of thing would be a performance hit when you are getting lots
of small, concurrent queries. In my case, we have queries which are taking
around a second to perform outer joins. They aren't competing with any other
requests as the site is not running, we are just running one app to seed the
data.