Re: Query sometimes takes down server

Jeff Davis wrote:
> On Fri, 2009-01-16 at 08:43 -0600, Jason Long wrote:
>
>> The numbers in the table names are due to hibernate generating the
>> query.
>>
>
> Well, that's what auto-generated schemas and queries do, I guess.
>
The schema is not auto generated. It evolved as I created my inventory
system.
It is relatively easy for humans to understand. Or at least for me
since I wrote it.
>
>> Now we are getting somewhere.
>> Someone suggested tweaking the genetic algorithm parameters.
>> Has anyone else had to do this and what results did you acheive?
>> Can someone offer me some detailed advice on tweaking these
>> parameters?
>>
>
> There are a lot of tables, so no matter what you do will require GEQO
> (the genetic algorithm I was talking about).
>
I am familiar with with Global Optimization. I was part of my research
for my masters degree.
> The fact that some of the plans are fast is good news: it means that
> it's possible to execute the query quickly.
>
> The other good news is that the slower plans are, indeed, estimated to
> be slower in the examples you provided (not by exactly proportional
> amounts, but it's still a good sign). If the estimations are so far off
> that they are basically random, GEQO won't help much; but in your case
> they look surprisingly good.
>
> I would try increasing geqo_effort, and tweaking geqo_pool_size and
> geqo_generations (mostly try increasing these last two, but smaller
> values might be useful), and tweak geqo_selection_bias randomly between
> 1.5 and 2.
>
>
I raised the geqo_effort to 10 and this made this happen my less
frequently, but still fairly often.
Thanks for the advice. I will post my results if I achieve improvement.
> See useful ranges of the parameters here:
> http://www.postgresql.org/docs/8.3/static/runtime-config-query.html
>
> When you start to get stable execution times (make sure you don't just
> get lucky once), keep the values you're using. Post to the list with
> your results.
>
I think I am going to write a script an run the query enough times for
me to see some statistics on how my tuning of the parameters work.
Would anyone have such a script already made?

Maybe I should use a genetic algorithm to analyze all the possible
combinations of GEQO parameters for may case. :)
But, realistically I am slammed with work and while this is very
interesting to me I will have to keep cranking out new features to keep
the clients happy.
Hopefully, I can find a a better set of parameters through trial and error.
> You may be able to fix some of your queries (like this one), but I
> suspect this will just make the problem more rare. When you come up with
> some new query later, I think the problem will come back. The solution
> is really to have a more reasonable schema, something that PostgreSQL
> (and humans) can understand well enough to optimize.
>
Making them more rare is probably good enough for now. Thank you very
much for the advice.
> Regards,
> Jeff Davis
>
>
>