[rt-users] ShowCustomFields extremely slow.

[Alex Vandiver]

On Thu, 2014-01-30 at 10:57 +0100, Michelle Sullivan wrote:
> Jan 28 02:11:09 to Jan 28 02:18:31  ... a full 7 minutes of being in
> ShowCustomFields there are just 100's (like more than 1900 of them) of
> statements like this: [snip]
>
> .. and no other queries...  The start being immediately after: [snip]
> 
> Which is a column_info() call in DBD::Pg
> 
> Which is called from Fields() in DBIx::Searchbuilder ..
> 
> Which I assume is being called in ShowCustomFields somewhere...

Correct.  It should also only run once per process, and be cached
thereafter.

> The problem is network latency between the front ends and the backend
> DB...  Running this on the same host returns in under a second.

Yes.  These queries aren't a notable performance hit on most servers.
At worst case, they may take a second or two -- they certainly don't
take upwards of _five minutes_.  The latency is, as you've diagnosed,
almost certainly the root cause of your problems.

> My frontends are on completely different networks (and indeed different
> data centers) from the database servers for security (mainly DDoS type)
> reasons
>
> [snip discussion of optimization of column_info()]

RT simply isn't designed to operate in an environment where every query
has tens to hundreds of ms of latency.  Attempts to optimize this
particular set of queries will merely cause the limiting factor to
become some other set.  While we're absolutely interested in patches
that help decrease the number of queries that RT runs (as you've noted,
there are several queries run multiple times), I expect you will find
this to be a frustrating game of whack-a-mole.  If at all possible,
altering your network topology to remove the latency is most probably a
more straightforward way to have a performant RT instance.
 - Alex