[rt-users] Custom field queries are very slow after 3.8.8 upgrade (from 3.6.10)

[Georgi Georgiev]

Quoting Kenneth Marshall at 06/01/2011-07:56:06(-0600):
>
> Hi Georgi,
> 
> The default parameters for PostgreSQL are VERY conservative to allow
> it to run even on systems with minimal resources. I did not see any
> details of your system memory configuration, but you could set
> shared_buffers a little higher. Also, for a mainly memory resident DB
> you should make the random_page_cost closer to the seq_page_cost and
> for a fully memory resident DB both to something like 0.1. You need
> to set your effective_cache_size to about 2/3 of your systems
> memory. Finally, in order to take advantage of more hash join options
> you should bump your work_mem from the default 1MB to more based on
> the amount of system memory you have. We set it to 128MB on an 8GB
> system, but even 32MB or 64MB can really help. This is what you
> need to set:
> 
> shared_buffers = 64MB  # or 128MB if you have the memory
> work_mem = 16MB        # or higher
> effective_cache_size = 3GB # for a 4GB system or system-memory * 2/3
> random_page_cost = 0.1 # for fully cached DB use 2 for less cached
> seq_page_cost = 0.1    # for fully cached DB use 1 for less cached
> 
> These changes should allow you to take much better advantage of
> your systems memory.

Thanks again, Kenneth,

I understand now the the defaults are conservative, but as I said, RT
3.6 runs fast enough as it is.

The system I am debugging this on is a virtual machine (libvirt + KVM,
host is CentOS 5.5) and when I sent the original post it only had 768MB
of system memory. I now bumped it to 4G as you suggested (the host
still has plenty to share) and tried your suggestions as they are.

Trying only what is above did not help at all. postgres was still trying
to use nested loops, and it was taking 7 seconds for 7 conditions (7
joins).

I then disabled nested loops explicitly (enable_nestloop=off)
and it got significantly better.

7 conditions took only 1 sec (down from 7 sec), and it used hash joins

8 conditions, however, tried to use merge joins and took 25 seconds

After disabling merge joins as well (enable_mergejoin=off),
8 conditions used hash joins but still used 16.4 seconds.

I am looking at potentially comparing about 16 or so conditions, so this
still does not look like it can scale enough.

Do you think I should keep trying to optimize postgres or should I try
looking at RT as well? I am still not sure what exactly happened between
3.6 and 3.8 that requires an extra outer join for each customfield.
I curious what the justification for using multiple left joins for
something that worked perfectly well in 3.6 with only one join is.

I tracked the issue down to Tickets_Overlay.pm. In the worst case, we'll
end up adding our own Tickets_Overlay_Local.pm to make the searches
faster or we'll just wait for 4.0 to come out and see if that solves it.

-- 
(    Georgi Georgiev   (  The "cutting edge" is getting rather dull. (
 )    Sysadmin Head     ) -- Andy Purshottam                          )
(    -SBI Japannext-   (                                             (