Ruslan, I wasn't aware that sessions had to be cleared, but now that you mentioned it I looked and there were almost 10k sessions in our table. I cleared that out and it does not seem to be slow in that section anymore. I've also added that command to crontab to run daily.
It seems much better after doing that, I don't see any pages taking over 3-4 seconds to load anymore. I do still see some taking about 2-3 seconds to load. That is more usable, although I would like to get it better if possible. I turned on logging of SQL statements in RT only for now, I'm not as familiar with Postgresql as I am with MySQL, but I could look into logging in there if necessary. We only have about 5 staff using the RT web interface. The longest queries I see are still fairly quick I think, but here are a couple examples of the longer ones for Display.html: [Wed May 30 17:52:44 2012] [debug]: SQL(0.136027s): SELECT * FROM Groups WHERE LOWER(Domain) = LOWER(?) AND LOWER(Type) = LOWER(?); [ bound values: 'SystemInternal' 'Privileged' ] (/usr/share/request-tracker4/lib/RT/Interface/Web.pm:1115) [Wed May 30 17:49:20 2012] [debug]: SQL(0.387888s): SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) WHERE (Principals_1.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '25472') AND (CachedGroupMembers_2.Disabled = '0') AND (LOWER(Principals_1.PrincipalType) = 'user') ORDER BY main.Name ASC ; (/usr/share/request-tracker4/lib/RT/Interface/Web.pm:1115) Here is an example summary of a page load (with SQL logging on and Mason profiling off) for Display.html: 48 Queries 4.1 Seconds page load Longest query: [Wed May 30 17:56:19 2012] [debug]: SQL(0.118095s): SELECT * FROM Groups WHERE LOWER(Domain) = LOWER(?) AND LOWER(Type) = LOWER(?); [ bound values: 'SystemInternal' 'Unprivileged' ] (/usr/share/request-tracker4/lib/RT/Interface/Web.pm:1115) All other queries < 0.1 seconds, most are < 0.01 seconds Thanks, Nate On Wed, May 30, 2012 at 12:09 PM, Ruslan Zakirov <[email protected]>wrote: > On Wed, May 30, 2012 at 7:53 PM, Nathan Baker <[email protected]> wrote: > > Ruslan, > > > > I actually started the other thread, so my details are in the first post. > > That thread was sort of hijacked (no big deal) and getting messy, so I > > wanted to separate them. I'm using Postgresql, not MySQL, and had > already > > turned on the SQL statement log and all queries seemed to be completing > > quickly. > > I know that your thread was hijacked, but this one lacks history and > for me it's hard > to jump between threads to figure out background. Anyway, see comments > below. > > Do you log SQL statements on RT side or in Pg? In your case I would > recommend > to enable both method for a while. > > > I think I might have been too quick to assume it's the custom fields > though, > > I've been testing it for a few hours now and I don't see that part being > > slow anymore. To be honest the only part I see being consistently slow > > right now is: > > > > =Mason= localhost - /Elements/SetupSessionCookie {{{ > > =Mason= localhost - /Elements/SetupSessionCookie }}} 1.9105 > > > > That seems to take about 2 seconds on about 30% of page loads. Is that > > normal? > > Nope. It's either big session size or you don't clean sessions table from > old > records with rt-clean-sessions. > > SELECT COUNT(1) FROM sessions; > > Above should say how many records you have in the table. > > Also, sessions are locked and if you click a link or refresh while page > is still loading then time on SetupSessionCookie can contain time it > was waiting for lock to be released by previous request. > > I'm not sure how you're testing. If your DB is actively used by many RT > users then it's hard to tell what's slow. > > > I will try to turn off caching in Postgresql though to see if that > verifies > > your theory about slow queries. > > Ah. You migrated to Pg. Caching in Pg is different from mysql. > > > Thanks! > > Nate > > -- > Best regards, Ruslan. >
