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 _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
