RT2 Database design

RT Users
1

Hey folks, probably more Jesse than others:

I find that for our purposes RT2 is a little slower to respond (in the web
gui) than RT1. My Attachments table has grown to be > 2GB in size and I
think this is slowing access to the entire RT system somewhat. We have
about 50,000 archived tickets and we sort of want to keep them.

I think the design of RT2 is vastly superior to RT1, however I’m sad it’s
slower right now. I have basically two choices that I see: 1) throw more
hardware at it, 2) look to optimizing RT2.

So, my question is – what indexing, if any, exists on the Attachments
table, and secondly, what fields does anybody (Jesse) think would gain
performance from additional indexing?

Thanks,
Dave

David C. Troy [dave@toad.net] 410-544-6193 Sales
ToadNet - Want to go fast? 410-544-1329 FAX
570 Ritchie Highway, Severna Park, MD 21146-2925 www.toad.net

2

I realise this deviates from the question a little, but while we are
talking about database design, what about the addition of several
customisable fields in the database which could be included to reference
each ticket by …

I’m thinking about the possibility for organisations to add customer or
account numbers that coincide with their own internal databases which
could then be searched on and keyed as well as by email address.

Although going back to your question, It looks to me like the
attachments table has one primary key, that of id and one key of
transaction id I think the more indexing the better, and the more things
that are searchable (as far as the RT interface is concerned) the
better. But indexing will (afaik, please correct me if I am wrong here,
I’m not a DBA ;-)) only improve speed if the fields are the ones being
searched in the select query, so if the searching is only using the id
and TransactionId fields then I cant see what else could be done here.

Simon.

3

One of the things I’m looking at for 2.2 is per-queue custom ticket fields
which allow arbitrary data-entry, rather than just keywords.On Thu, Aug 16, 2001 at 04:37:25PM +0100, Simon Woodward wrote:

I realise this deviates from the question a little, but while we are
talking about database design, what about the addition of several
customisable fields in the database which could be included to reference
each ticket by …

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

. . . when not in doubt, get in doubt. – Old Discordian Proveb

4

What are you running RT’s database on now? How much proccessor? How much ram?
Are there specific operations that are slower? Does mysql’s slow-queries
log show anything interesting?

It might be worth looking at mysql’s regular query log and feeding some
of the queries that look like they’re taking a long time to mysql(1)'s
“EXPLAIN” to see if it appears to need better indices.

Another option is an RT data-warehousing tool that will move old archived
informational tickets out of the core database to somewhere else
that’s still searchable and accessable, so it doesn’t impact performance
as much.

Depending on which operations are slow, optimizing RT2 might or might
not be the right call. You may simply be pushing mysql’s limits.
I don’t know whether oracle or postgres will scale better, but those
may also be options.

    -jOn Thu, Aug 16, 2001 at 08:18:44AM -0400, David C. Troy wrote:

Hey folks, probably more Jesse than others:

I find that for our purposes RT2 is a little slower to respond (in the web
gui) than RT1. My Attachments table has grown to be > 2GB in size and I
think this is slowing access to the entire RT system somewhat. We have
about 50,000 archived tickets and we sort of want to keep them.

I think the design of RT2 is vastly superior to RT1, however I’m sad it’s
slower right now. I have basically two choices that I see: 1) throw more
hardware at it, 2) look to optimizing RT2

So, my question is – what indexing, if any, exists on the Attachments
table, and secondly, what fields does anybody (Jesse) think would gain
performance from additional indexing?

Thanks,
Dave

=====================================================================
David C. Troy [dave@toad.net] 410-544-6193 Sales
ToadNet - Want to go fast? 410-544-1329 FAX
570 Ritchie Highway, Severna Park, MD 21146-2925 www.toad.net

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

I have images of Marc in well worn combat fatigues, covered in mud,
sweat and blood, knife in one hand and PSION int he other, being
restrained by several other people, screaming “Let me at it!
Just let me at it!” Eichin standing calmly by with something
automated, milspec, and likely recoilless.
-xiphmont on opensource peer review

5

What are you running RT’s database on now? How much proccessor? How much ram?
Are there specific operations that are slower? Does mysql’s slow-queries
log show anything interesting?

One 800MHz PIII, 768MB, IDE disks; nothing is so slow that it could be
considered really problematic, and I’m confident that throwing hardware
at it would improve it somewhat. The slowest stuff just seems to be
‘searches’ (I use that generically – even clicking on the ‘canned’ queue
lists on ‘Home’ takes a few fractions of a second longer than you’d like).

I use the same box for other DB things and it performs perfectly
reasonably. I have one table that’s about 1.6GB and well indexed, for the
operations I do with it, it performs really well.

It might be worth looking at mysql’s regular query log and feeding some
of the queries that look like they’re taking a long time to mysql(1)'s
“EXPLAIN” to see if it appears to need better indices.

I’ll give this a shot. Just wondered if you had a feeling like, “Hm, I
guess I could have indexed such & such a field – never thought someone
would have a DB that big.”

Another option is an RT data-warehousing tool that will move old archived
informational tickets out of the core database to somewhere else
that’s still searchable and accessable, so it doesn’t impact performance
as much.

Depending on which operations are slow, optimizing RT2 might or might
not be the right call. You may simply be pushing mysql’s limits.
I don’t know whether oracle or postgres will scale better, but those
may also be options.

Yep – all good thoughts. Will see what I can come up with.

Dave

6

RT 2.0.5 causes searches to display only 50 results per page by default…
Which should increase the percieved performance a whole lot… Also,
dunno if you’re using netscape 4, but its table rendering is abysmal. And adds
precious seconds to every page draw.

    -jOn Thu, Aug 16, 2001 at 02:59:38PM -0400, David C. Troy wrote:

What are you running RT’s database on now? How much proccessor? How much ram?
Are there specific operations that are slower? Does mysql’s slow-queries
log show anything interesting?

One 800MHz PIII, 768MB, IDE disks; nothing is so slow that it could be
considered really problematic, and I’m confident that throwing hardware
at it would improve it somewhat. The slowest stuff just seems to be
‘searches’ (I use that generically – even clicking on the ‘canned’ queue
lists on ‘Home’ takes a few fractions of a second longer than you’d like).

I use the same box for other DB things and it performs perfectly
reasonably. I have one table that’s about 1.6GB and well indexed, for the
operations I do with it, it performs really well.

It might be worth looking at mysql’s regular query log and feeding some
of the queries that look like they’re taking a long time to mysql(1)'s
“EXPLAIN” to see if it appears to need better indices.

I’ll give this a shot. Just wondered if you had a feeling like, “Hm, I
guess I could have indexed such & such a field – never thought someone
would have a DB that big.”

Another option is an RT data-warehousing tool that will move old archived
informational tickets out of the core database to somewhere else
that’s still searchable and accessable, so it doesn’t impact performance
as much.

Depending on which operations are slow, optimizing RT2 might or might
not be the right call. You may simply be pushing mysql’s limits.
I don’t know whether oracle or postgres will scale better, but those
may also be options.

Yep – all good thoughts. Will see what I can come up with.

Dave

    -j

On Thu, Aug 16, 2001 at 08:18:44AM -0400, David C. Troy wrote:

Hey folks, probably more Jesse than others:

I find that for our purposes RT2 is a little slower to respond (in the web
gui) than RT1. My Attachments table has grown to be > 2GB in size and I
think this is slowing access to the entire RT system somewhat. We have
about 50,000 archived tickets and we sort of want to keep them.

I think the design of RT2 is vastly superior to RT1, however I’m sad it’s
slower right now. I have basically two choices that I see: 1) throw more
hardware at it, 2) look to optimizing RT2

So, my question is – what indexing, if any, exists on the Attachments
table, and secondly, what fields does anybody (Jesse) think would gain
performance from additional indexing?

Thanks,
Dave

=====================================================================
David C. Troy [dave@toad.net] 410-544-6193 Sales
ToadNet - Want to go fast? 410-544-1329 FAX
570 Ritchie Highway, Severna Park, MD 21146-2925 www.toad.net

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

–
jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

I have images of Marc in well worn combat fatigues, covered in mud,
sweat and blood, knife in one hand and PSION int he other, being
restrained by several other people, screaming “Let me at it!
Just let me at it!” Eichin standing calmly by with something
automated, milspec, and likely recoilless.
-xiphmont on opensource peer review

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

…realized that the entire structure of the net could be changed to be made
more efficient, elegant, and spontaneously make more money for everyone
involved. It’s a marvelously simple diagram, but this form doesn’t have a way
for me to draw it. It’ll wait. -Adam Hirsch