[rt-users] Performance problems with 3.8.1

[Kenneth Marshall]

On Wed, Sep 17, 2008 at 01:13:31PM +0300, Sahlberg Mauri wrote:
> Hi,
> 
> Just upgraded from 3.6 to 3.8.1 (via 3.8) and at the same time moved the database to it's own server. We also removed all closed tickets from the database. The move and upgrade was done as our old installation got too slow. Unfortunately the new installation is also very slow despite the ticket removal and dedicated database server. Especially search building view and ticket view take time to open.
> 
> We have:
> 
> -          Checked that weI have newest DBIx:search builder installed
> 
> -          Checked that both our database server (CentOS 5.2 final) and web server (CentOS 5.2 final)  fulfill the minimum shared memory settings suggested at the wiki
> 
> -          Have FastCGI installed on Apache 2.2.3, standard CentOS 5.2 final binary (standard CentOS 5.2 perl 5.8.8 with make fixdeps installed modules)
> 
> -          Our Postgresql 8.1.11 (Centos 5.2 standard binary) has more shared_buffers and temp_buffers than suggested at the wiki
> 
> -          Installed pg_top and tried to isolate the query that hogs the machine
> 
> Any suggestions what we could yet do to speed things up?

I just checked the latest DBIx::SearchBuilder and the fix that I
thought had been incorporated into the Handle/Pg.pm has not as of
yet. The problem line is line number 245:

  $$statementref = "SELECT DISTINCT main.* FROM $$statementref";

If you replace it with a line like the one in the Oracle.pm at
line number 279:

  $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";

you should see a nice performance boost on the page loads. I have sent
this change in before, but it has not been adopted even though it is
being used in the Oracle.pm file. I examined the query used by mysql.pm
for this query, and the Oracle.pm version of the line duplicates the
query used by mysql.pm exactly. The query as currently written spends
a lot of sorting fields that are not used. The alternate query is
much faster. Maybe BP can re-examine this suggestion again.

Cheers,
Ken