Wishlist emails

Roy_El-Hames · February 18, 2008, 3:28pm

Hi ;

I have seen a couple of emails in the past a couple of months about wish
list etc which I assume for a new major release, and I have a couple of
wishes wondering if these can be looked into,

Ticket Merge/Unmerge:
As far as I can see, once 2 tickets are merged details about Requesters
and individual tickets Cc 's are lost (ie which Requester/Cc for which
ticket etc), my users (and I am sure many others) have merged tickets by
mistake many times and for this I have created a function for them that
unmerge tickets, however my function can’t deal with the individual
ticket requesters for the reason I mentioned above, any changes will
need to be done in few places and wondering if the developers can look
into cleaner way to merge and unmerge … (If this is already there then
please can some one correct me)

Content searching
I have tried many hacks to get an efficient content searching working,
however with a 40G db mostly Attachment table (~ 22G in size) and
Transactions table is not small either the content searching takes on
average 15 minutes , we have a quad processor 3.8 GHz systems with 16G
mem, the db is optimised, doing select * from Attachment where content
like ‘%my search string%’ returns in 5 minutes for the same query that
may take over 15 minutes from the front end (and sometimes comes back
with 0 tickets, my guess most the processing is building the join
between Tickets, Transactions and Attachments, and I am wondering if the
join can be eliminated:
1- Adding smaller table of Ticket.id and Attachement.Id populated
with every new attachement
2- Take out Tickets table and use the Transactions.ObjectId as
ticket Id’s

In both of the above any customisation will take my RT far away from the
base release and wondering if these are addressed in the new version ,
then I would rather wait.
I hope the above makes sense and looking forward to the new version.

Roy

Joop · February 18, 2008, 3:54pm

Roy El-Hames wrote:

Hi ;

I have seen a couple of emails in the past a couple of months about wish
list etc which I assume for a new major release, and I have a couple of
wishes wondering if these can be looked into,

Content searching
I have tried many hacks to get an efficient content searching working,
however with a 40G db mostly Attachment table (~ 22G in size) and
Transactions table is not small either the content searching takes on
average 15 minutes , we have a quad processor 3.8 GHz systems with 16G
mem, the db is optimised, doing select * from Attachment where content
like ‘%my search string%’ returns in 5 minutes for the same query that
With wildcards in front of your search you’ll always force the db NOT to
use indices so performance will always be ‘sub’ optimal.
The way I fixed this is by changing the source NOT to pre/post pend
wildcards but let the user to this depending on its needs. This cuts on
the non-indexed queries alot.

may take over 15 minutes from the front end (and sometimes comes back
with 0 tickets, my guess most the processing is building the join
between Tickets, Transactions and Attachments, and I am wondering if the
join can be eliminated:
You’ll need to capture the sql responsible for this and get an ‘explain
plan’ (Oracle term) and see where most of time is spend. From memory I
think it was doing full table scan of tickets, but I would need to check
that.

Joop

Kenneth_Marshall · February 18, 2008, 3:58pm

Content searching
I have tried many hacks to get an efficient content searching working,
however with a 40G db mostly Attachment table (~ 22G in size) and
Transactions table is not small either the content searching takes on
average 15 minutes , we have a quad processor 3.8 GHz systems with 16G
mem, the db is optimised, doing select * from Attachment where content
like ‘%my search string%’ returns in 5 minutes for the same query that
may take over 15 minutes from the front end (and sometimes comes back
with 0 tickets, my guess most the processing is building the join
between Tickets, Transactions and Attachments, and I am wondering if the
join can be eliminated:
1- Adding smaller table of Ticket.id and Attachement.Id populated
with every new attachement
2- Take out Tickets table and use the Transactions.ObjectId as
ticket Id’s

In both of the above any customisation will take my RT far away from the
base release and wondering if these are addressed in the new version ,
then I would rather wait.
I hope the above makes sense and looking forward to the new version.

Roy

Roy,

The content searching problem really needs to be addressed through
the use of full-text indexing. There is currently an item in the
wiki describing how to use Oracle’s full-text support with RT. Here
is the URL: OracleText - Request Tracker Wiki. I have
not seen a similar entry for MySQL or PostgreSQL, although I will
be submitting one for PostgreSQL soon. We are upgrading to RT 3.6.x
and PostgreSQL 8.3. 8.3 supports full-text indexing as a core feature.
Maybe with two examples, someone will be able to submit a MySQL version
as well.

If you are familiar with the full-text index support for your backend
database, you should be able to make these changes yourself. This would
provide much better performance. I think that this solution will scale
much better than just making incremental DB layout improvements.

Good luck,
Ken

Roy_El-Hames · February 18, 2008, 4:33pm

Hi Ken;

The content searching problem really needs to be addressed through
the use of full-text indexing. There is currently an item in the
wiki describing how to use Oracle’s full-text support with RT. Here
is the URL: OracleText - Request Tracker Wiki. I have
not seen a similar entry for MySQL or PostgreSQL, although I will
be submitting one for PostgreSQL soon. We are upgrading to RT 3.6.x
and PostgreSQL 8.3. 8.3 supports full-text indexing as a core feature.
Maybe with two examples, someone will be able to submit a MySQL version
as well.

If you are familiar with the full-text index support for your backend
database, you should be able to make these changes yourself. This would
provide much better performance. I think that this solution will scale
much better than just making incremental DB layout improvements.

Totally agree with you, and the hack I have in place is to use myisam
version of the rt database (I use mysql and rt likes innodb which does
not support full-text indexing),
So what happens here is content searches are intercepted , the content
string is queried on the myisam with full-text indexing), joins
attachments and transaction table and returns a list of ObjectIds (where
ObjectType = ‘RT::Ticket’), the returned list of Ids are then put back
into the rest to the search string as “Ticket.Id = bla or Ticket.Id =
bla2 etc” … not ideal but it actually works --most of the time –
As I said in my original mail , I would have preferred if this is
addressed in the core rt development cause I am sure I am not the only
one with this issue , and this kind of customisation drift from the core
quite a bit

Thanks;
Roy

Kenneth_Marshall · February 18, 2008, 5:05pm

Hi Ken;

The content searching problem really needs to be addressed through
the use of full-text indexing. There is currently an item in the
wiki describing how to use Oracle’s full-text support with RT. Here
is the URL: OracleText - Request Tracker Wiki. I have
not seen a similar entry for MySQL or PostgreSQL, although I will
be submitting one for PostgreSQL soon. We are upgrading to RT 3.6.x
and PostgreSQL 8.3. 8.3 supports full-text indexing as a core feature.
Maybe with two examples, someone will be able to submit a MySQL version
as well.
If you are familiar with the full-text index support for your backend
database, you should be able to make these changes yourself. This would
provide much better performance. I think that this solution will scale
much better than just making incremental DB layout improvements.

Totally agree with you, and the hack I have in place is to use myisam
version of the rt database (I use mysql and rt likes innodb which does not
support full-text indexing),
So what happens here is content searches are intercepted , the content
string is queried on the myisam with full-text indexing), joins
attachments and transaction table and returns a list of ObjectIds (where
ObjectType = ‘RT::Ticket’), the returned list of Ids are then put back into
the rest to the search string as “Ticket.Id = bla or Ticket.Id = bla2 etc”
… not ideal but it actually works --most of the time –
As I said in my original mail , I would have preferred if this is addressed
in the core rt development cause I am sure I am not the only one with this
issue , and this kind of customisation drift from the core quite a bit

Thanks;
Roy

Wow! I wondered why there was not a full-text indexing support page in
the wiki for MySQL. I had not realized that it was not supported under
InnoDB. That would be enough for me to want to change database backends.
Would it be possible for you to put up a wiki page for this? I am certain
that other users of MySQL would benefit. I wonder if the full-text support
is going to be added to the InnoDB table type. Then RT could have official
full-text indexing support.

Regards,
Ken

Matthew_Seaman · February 18, 2008, 6:22pm

Kenneth Marshall wrote:

Wow! I wondered why there was not a full-text indexing support page in
the wiki for MySQL. I had not realized that it was not supported under
InnoDB. That would be enough for me to want to change database backends.
Would it be possible for you to put up a wiki page for this? I am certain
that other users of MySQL would benefit. I wonder if the full-text support
is going to be added to the InnoDB table type. Then RT could have official
full-text indexing support.

http://www.sphinxsearch.com/ works nicely with any MySQL table engines.

Cheers,

Matthew

Dr Matthew Seaman The Bunker, Ash Radar Station
PGP: 0x60AE908C on servers Marshborough Rd
Tel: +44 1304 814890 Sandwich
Fax: +44 1304 814899 Kent, CT13 0PL, UK

signature.asc (250 Bytes)

Roy_El-Hames · February 18, 2008, 6:31pm

Matthew;

Have you used it with searchbuilder/RT, ??

Roy

Matthew Seaman wrote:

Matthew_Seaman · February 18, 2008, 7:29pm