[rt-users] ShowCustomFields extremely slow.

[Michelle Sullivan]

Alex Vandiver wrote:
> On Mon, 2014-01-27 at 10:06 +0100, Michelle Sullivan wrote:
>   
>> After upgrade to v4.0.17 from 3.x loading tickets is extremely slow..
>> can anyone give me any clues please...?
>>     
>
> What version were you upgrading from?  What database backend are you
> using?
>   

3.6.3
Postgres 8.4.x

>   
>> Profiler shows this for a load of a single ticket:
>>
>>  /Ticket/Display.html BEGINS {{{
>>      /autohandler {{{
>>          /Elements/SetupSessionCookie {{{
>>          /Elements/SetupSessionCookie }}} 0.2310
>>     
>
> The fact that even cookie setup takes a quarter of a second is
> problematic, and suggests that your database badly needs tuning.
>   
I doubt it, it's probably because it's busy.

3.6.3 was running against Pg8.3.x on a 2 proc 64bit intels (early ones)
with 3G RAM on a 6 disk SCSI hardware raid 5 running FreeBSD and Bucardo
4.5 to replicate to a backup database.  The front end is running on a
800Mhz PIII BL10e blade with Apache2.2 on FreeBSD 6.x and Mod_perl2.

4.0.17 setup I'm trying to migrate to has a clean DB dump, against 4 off
Pg 8.4.x each with 16 cores, 128G RAM, and 12x15k  SAS Drives using
Bucardo 5 in multi-master mode on CentOS.  Front ends are 2 off,  Intel,
2 procs x 2 cores x 8G RAM FreeBSD 9.2, Apache 2.2 and mod_perl2.

3.6.3 setup is a dedicated DB for RT.  The 4.0.17 DB hardware has 2
databases, RT4 and SORBS - needless to say SORBS is busy on reads and
writes, however as it's 4 servers with 2G bonded interfaces in 2 DCs 
One DC is East Coast, the other West Coast.  The primary writer for
SORBS is East Cost, primary writer for RT is west coast, so contention
is only against Bucardo.  Bucardo replication writes takes 7 seconds to
all four DB Hosts for 10,000 qps.
>   
>>                                  /Elements/ShowCustomFields {{{
>>                                  /Elements/ShowCustomFields }}} 429.2604
>>     
>
> Your database's slow query log will hopefully shed some light on the
> query/queries that are being problematic.
>   
Slow query log is set to 100ms on each of the servers, when displaying a
ticket, only one query shows in the log (usually) and that's nearly
always 1800-1900ms.

>   
>>                                  /Elements/ShowUser {{{
>>                                      /Elements/ShowUserConcise {{{
>>                                      /Elements/ShowUserConcise }}} 0.0014
>>                                  /Elements/ShowUser }}} 0.4606
>>     
>
> The fact that displaying a user takes half a second is another sign that
> your database accesses, in general, are being problematic.
>   

The SQL log only shows 20s (and most of them are the same query over and
over again - sometimes as much as 100 times per ticket..  This query:
SELECT * FROM Attributes WHERE LOWER(ObjectType) = LOWER(?) AND
LOWER(Name) = LOWER(?) AND ObjectId = ?; ) (67 times takes 8 seconds
according to the SQL log.. running by hand with the same params shows it
take 0.062ms - so that's possibly network latency as the frontends are
not in the same location as the DBs)

Regards,

-- 
Michelle Sullivan
http://www.mhix.org/