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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
