Is this slow? It seems slow

RT Users
1

Hello.

I have just configured RT 3.0.2 with apache 1.3.27 (mod_perl) and Postgres 7.3.2. We are about to turn 5 support people live using RT.

As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from “display” to “jumbo” mode.

As it is, I am the only user testing RT. The same server (Dell server running Red Hat 8, dual 900 mhz cpus, 1gb memory, scsi 10k drives) is running both apache/mod_perl/postgres.

This seems kind of slow, doesn’t it? What will happen when 5 people are beating on it?

While clicking around in RT, it’s common for the postgres db process ‘postmaster’ jump to 100% and sit there for the duration. Additionally, I saw an earlier message posted about adding indexes to the RT database. I had assumed that he was doing custom queries…? Are their any extra indexes we could(should) add that are sanctioned by the RT programmers/DBAs that could speed things up?

Any comments / experiences / insights?

Thanks!

Val

p.s. Don’t get me wrong, I’m not complaining about RT. I think RT is great!!

2

This one time, at band camp, Val Luck wrote:

As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from “display” to “jumbo” mode.

And similarly when clicking on Update from the front page, or
Resolve/Comment/Reply on the Display page. Yes, this behaviour has been
seen in the past. version 3.0.3pre2 of RT with a recent version of
DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
Unfortunately, I am not one of them. My experience - 3.0.0 and 3.0.1 ran
dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
loads were still taking about 30 seconds for the above operations, and now
with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
minute database queries.

This seems kind of slow, doesn’t it? What will happen when 5 people are beating on it?

postgres will fork a subprocess for each query, so that when 5 people are
doing things, you’ll have 5 processes taking up about 20% CPU each. And
when you hit the stop button, the subprocess won’t stop.

While clicking around in RT, it’s common for the postgres db process ‘postmaster’ jump to 100% and sit there for the duration. Additionally, I saw an earlier message posted about adding indexes to the RT database. I had assumed that he was doing custom queries…? Are their any extra indexes we could(should) add that are sanctioned by the RT programmers/DBAs that could speed things up?

I’ve added some indexes to a few tables, but in the mists of time and
experimentation, have forgotten which ones they are.

Turn on postgres logging (log_statements = true; syslog = 2 and so on) and
you’ll see the large ACL query run for a while. Copy that into psql and run
it yourself with “explain analyze” prepended, and you’ll have some profiling
information you can use to work out the problem.

I’ll be doing that mysel shortly, but it would be useful to have more than
one explain to help debug it.

jaq@spacepants.org http://spacepants.org/jaq.gpg

3

This one time, at band camp, Val Luck wrote:

As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from “display” to “jumbo” mode.

And similarly when clicking on Update from the front page, or
Resolve/Comment/Reply on the Display page. Yes, this behaviour has been
seen in the past. version 3.0.3pre2 of RT with a recent version of
DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
Unfortunately, I am not one of them. My experience - 3.0.0 and 3.0.1 ran
dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
loads were still taking about 30 seconds for the above operations, and now
with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
minute database queries.

That seems Surprising. More details would be appreciated.

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

4

As it is right now with testing (creating tickets, re-assigning
them, resolving them, etc) I can get up to a 5 to 15 second delay
when going from “display” to “jumbo” mode.

I have the same problem. I believe this is supposed to be fixed in
3.0.3 pre-releases, but I haven’t attempted the upgrade myself yet.

meeroh

http://web.meeroh.org/ | KB1FMP

A: Because it reverses the logical flow of conversation.
Q: Why is top posting frowned upon?

5

This one time, at band camp, Jesse Vincent wrote:

This one time, at band camp, Val Luck wrote:

As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from “display” to “jumbo” mode.

And similarly when clicking on Update from the front page, or
Resolve/Comment/Reply on the Display page. Yes, this behaviour has been
seen in the past. version 3.0.3pre2 of RT with a recent version of
DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
Unfortunately, I am not one of them. My experience - 3.0.0 and 3.0.1 ran
dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
loads were still taking about 30 seconds for the above operations, and now
with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
minute database queries.

That seems Surprising. More details would be appreciated.

Attached is the query from DBIx::Searchbuilder 0.86, the query plan from an
explain on the query, and an explain analyze (which is probably more useful,
and shows the total running time of the query).

I’m trying to see a way in which the query has differed from earlier
versions, but apart from the extra parentheses, it looks familiar.

What does the CachedGroupMembers table do, and what do each of the columns
mean?

jaq@spacepants.org http://spacepants.org/jaq.gpg

rt-query-0.86 (1.75 KB)

rt-query-explain-0.86 (12.6 KB)

rt-query-explainanalyze-0.86 (13.4 KB)

6

This one time, at band camp, Jamie Wilkinson wrote:

This one time, at band camp, Jesse Vincent wrote:

This one time, at band camp, Val Luck wrote:

As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from “display” to “jumbo” mode.

And similarly when clicking on Update from the front page, or
Resolve/Comment/Reply on the Display page. Yes, this behaviour has been
seen in the past. version 3.0.3pre2 of RT with a recent version of
DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
Unfortunately, I am not one of them. My experience - 3.0.0 and 3.0.1 ran
dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
loads were still taking about 30 seconds for the above operations, and now
with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
minute database queries.

That seems Surprising. More details would be appreciated.

Attached is the query from DBIx::Searchbuilder 0.86, the query plan from an
explain on the query, and an explain analyze (which is probably more useful,
and shows the total running time of the query).

I’m trying to see a way in which the query has differed from earlier
versions, but apart from the extra parentheses, it looks familiar.

What does the CachedGroupMembers table do, and what do each of the columns
mean?

Is cachedgroupmembers supposed to be a mapping between member ids of user
principals and the group ids of a groups?

If so, are the two CachedGroupMembers JOINs supposed to be in fact making
two separate queries:

  • one to return all the groups whose groupid (in this case) is 4,

and

  • one to return all the groups that are user defined, system internal, or
    ACLEquivalent?

If that’s true, could it be that a UNION query would be much better,
allowing the database to perform each of these queries separately and fast,
and then composing the results together?

jaq@spacepants.org http://spacepants.org/jaq.gpg

7

Jamie,

Thanks so much for your message and comments. I will definitely look into postgres and see if there are any indexes that could be added to speed things up.

With 3 people actively using RT, clicking on Resolve/Comment/Reply can now take up to 60 seconds for the screen to come up… which if people are on the phone with someone is a long time to have them sit there and wait. :frowning:

You mentioned that upgrading to RT version 3.0.3 (which is out now) could fix it? Updating DBIx::SearchBuilder looks like a perl mod upgrade from CPAN, not RT? Regardless, I’ll look into it.

I can’t believe that you have some db queries that are taking 20 minutes …?!? Wow. Are you sure that’s minutes and not seconds? That would render RT useless. :frowning: I sure hope I don’t see any degradation of that magnitude when I upgrade.

If you do add any more indexes for performance, it would be great if you could email me or the rt-users list. I’ll do the same.

Thanks again,

ValFrom: Jamie Wilkinson [mailto:jaq@spacepants.org]
Sent: Tuesday, June 10, 2003 6:34 PM
To: rt-users@lists.fsck.com
Subject: Re: [rt-users] Is this slow? It seems slow.

This one time, at band camp, Val Luck wrote:

As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from “display” to “jumbo” mode.

And similarly when clicking on Update from the front page, or
Resolve/Comment/Reply on the Display page. Yes, this behaviour has been
seen in the past. version 3.0.3pre2 of RT with a recent version of
DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
Unfortunately, I am not one of them. My experience - 3.0.0 and 3.0.1 ran
dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
loads were still taking about 30 seconds for the above operations, and now
with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
minute database queries.

This seems kind of slow, doesn’t it? What will happen when 5 people are beating on it?

postgres will fork a subprocess for each query, so that when 5 people are
doing things, you’ll have 5 processes taking up about 20% CPU each. And
when you hit the stop button, the subprocess won’t stop.

While clicking around in RT, it’s common for the postgres db process ‘postmaster’ jump to 100% and sit there for the duration. Additionally, I saw an earlier message posted about adding indexes to the RT database. I had assumed that he was doing custom queries…? Are their any extra indexes we could(should) add that are sanctioned by the RT programmers/DBAs that could speed things up?

I’ve added some indexes to a few tables, but in the mists of time and
experimentation, have forgotten which ones they are.

Turn on postgres logging (log_statements = true; syslog = 2 and so on) and
you’ll see the large ACL query run for a while. Copy that into psql and run
it yourself with “explain analyze” prepended, and you’ll have some profiling
information you can use to work out the problem.

I’ll be doing that mysel shortly, but it would be useful to have more than
one explain to help debug it.

jaq@spacepants.org http://spacepants.org/jaq.gpg
rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

8

This one time, at band camp, Val Luck wrote:

You mentioned that upgrading to RT version 3.0.3 (which is out now) could fix it? Updating DBIx::SearchBuilder looks like a perl mod upgrade from CPAN, not RT? Regardless, I’ll look into it.

I can’t believe that you have some db queries that are taking 20 minutes …?!? Wow. Are you sure that’s minutes and not seconds? That would render RT useless. :frowning: I sure hope I don’t see any degradation of that magnitude when I upgrade.

If you do add any more indexes for performance, it would be great if you could email me or the rt-users list. I’ll do the same.

You’re replying to an old email, since then we’ve upgraded RT to 3.0.3 and
DBIx::SearchBuilder to 0.86 (I think that’s the latest version), and the
WhoHaveRight query is much much faster.

But see the thread on rt-devel regarding Sybase and mysql, for a patch that
seems to improve performanve even more for everyone.

jaq@spacepants.org http://spacepants.org/jaq.gpg