Re: Query sometimes takes down server

[Jason Long <mailing.list@xxxxxxxxxxxxxxxxxxxxx>]

David Wilson wrote:

On Fri, Jan 16, 2009 at 3:27 PM, Jason Long
<mailing.list@xxxxxxxxxxxxxxxxxxxxx> wrote:

  

I just tried it by sending text only instead of text and html.  We will see
if it goes through this time.
Other than that do you see anything weird about my email?
    

Still nothing. Do you have webspace you could place it on? If not, you
could use http://explain-analyze.info and simply provide the list with
links to the plans.

  

Actually I tweaked the GEQO parameters as follows:

# - Genetic Query Optimizer -
geqo = on
geqo_threshold = 12
geqo_effort = 10                        # range 1-10
geqo_pool_size = 1000              # selects default based on effort
geqo_generations = 1000           # selects default based on effort
geqo_selection_bias = 2.0          # range 1.5-2.0

I use the following script to test the settings.

#!/bin/bash
for ((i=0;i<=1000;i+=1)); do
psql -d pipetracker-dev -f /opt/main_list_count.sql  | grep runtime >>  /var/lib/pgsql/test.txt
done

The query was able to run 1000 times without even getting close to the 2 minute timeout I have set.
Here are some statistics I have from the runs.

Out of 1000 runs only these were more that 3 seconds

17377.86 ms
15040.32 ms
12343.12 ms
11915.26 ms
11409.88 ms
  9719.72 ms
  8535.49 ms
  5531.8 ms
  5286.9 ms
  4920.17 ms
  3849.4 ms

Avg 468.74 ms
Min 173.3 ms
Max 17337.86 ms
STDEV 1102.35 ms

I then put the settings back to the default and got a timeout after 45 tries.
These are the results greater than 3 seconds from those 45

114450.17 ms
  79529.79 ms
  40999.69 ms
  28981.87 ms
  25802.51 ms
  13346.41 ms
    9569.23 ms
    9267.75 ms
    8221.4 ms
    8213.87 ms
    5975.05 ms

Thank you everyone for your help.

Has anyone developed some sort of testing script that is much better than my simple bash script shown above?

I would like to just let some queries run and have the script run and then alter some parameters in postgres.conf and run again.
After running for a while it could tune these parameters based on the queries I have it run.

Thank you everyone for your advice.  My users will be much happier. :)

Can anyone see any problems with the settings I have and how they might affect my application as a whole?