Re: Final decision - Mailing list pgsql-performance

Just realize, you probably *don't* want to set that in postgresql.conf.
You just want to issue an "SET enable_seqscan TO off" before issuing one
of the queries that are mis-planned.

I believe all the tested queries (90 some odd views) saw an improvement.
I will however take the time to verify this and take your suggestion as I
can certainly put the appropriate settings in each as opposed to using the
config option, Thanks for the good advice (I believe Josh from
Commandprompt.com also suggested this approach and I in my lazy self some
how blurred the concept.)


Also, I second the notion of getting a confidentiality contract. There
have been several times where someone had a pathological case, and by
sending the data to someone (Tom Lane), they were able to track down and
fix the problem.

Excellent point, Our data is confidential, but I should write something to
allow me to ship concept without confidential, so in the future I can just
send a backup and not have it break our agreements, but allow minds greater
then my own to see, and feel my issues.


What do you mean by "blew up"?
IIS testing was being done with an old 2300 and a optiplex both machines
reached 100%CPU utilization and the test suite (ASP code written in house by
one of programmers) was not returning memory correctly, so it ran out of
memory and died. Prior to death I did see cpu utilization on the 4proc linux
box running postgres fluctuate and at times hit the 100% level, but the
server seemed very stable. I did fix the memory usage of the suite and was
able to see 50 concurrent users with fairly high RPS especially on select
testing, the insert and update seemed to fall apart (many 404 errors etc)


I assume you have IIS on a different
machine than the database. Are you saying that the database slowed down
dramatically, or that the machine crashed, or just that the web
interface became unresponsive? Just the web interface.

It probably depends on what queries are being done, and what kind of
times you need. Usually the update machine needs the stronger hardware,
so that it can do the writing.

But it depends if you can wait longer to update data than to query data,
obviously the opposite is true. It all depends on load, and that is
pretty much application defined.

I am guessing our app is like 75% data entry and 25% reporting, but the
reporting is taking the toll SQL wise.

This was from my insert test with 15 users.
Test type: Dynamic
 Simultaneous browser connections: 15
 Warm up time (secs): 0
 Test duration: 00:00:03:13
 Test iterations: 200
 Detailed test results generated: Yes
Response Codes

 Response Code: 403 - The server understood the request, but is refusing to
fulfill it.
  Count: 15
  Percent (%): 0.29


 Response Code: 302 - The requested resource resides temporarily under a
different URI (Uniform Resource Identifier).
  Count: 200
  Percent (%): 3.85


 Response Code: 200 - The request completed successfully.
  Count: 4,980
  Percent (%): 95.86

My select test with 25 users had this
Properties

 Test type: Dynamic
 Simultaneous browser connections: 25
 Warm up time (secs): 0
 Test duration: 00:00:06:05
 Test iterations: 200
 Detailed test results generated: Yes

Summary

 Total number of requests: 187
 Total number of connections: 200

 Average requests per second: 0.51
 Average time to first byte (msecs): 30,707.42
 Average time to last byte (msecs): 30,707.42
 Average time to last byte per iteration (msecs): 28,711.44

 Number of unique requests made in test: 1
 Number of unique response codes: 1

Errors Counts

 HTTP: 0
 DNS: 0
 Socket: 26

Additional Network Statistics

 Average bandwidth (bytes/sec): 392.08

 Number of bytes sent (bytes): 64,328
 Number of bytes received (bytes): 78,780

 Average rate of sent bytes (bytes/sec): 176.24
 Average rate of received bytes (bytes/sec): 215.84

 Number of connection errors: 0
 Number of send errors: 13
 Number of receive errors: 13
 Number of timeout errors: 0

Response Codes

 Response Code: 200 - The request completed successfully.
  Count: 187
  Percent (%): 100.00




Joel