[rt-users] Slow query results on search in content.

Dave Wells dave.wells at foreshore.net
Wed May 24 10:31:18 EDT 2006


Hi,

My users have been experiencing issues when they try and search on
content in all Tickets. The problems they experience are a very long
wait 30-40 mins for the results to appear or most often the page will
just hang and go to a blank page.

We have approx 45000 tickets in our database.

I have done a little experimenting with the kind of queries they have
been using as below:

 SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2  WHERE
((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId =
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket'))
AND ( ( (Attachments_2.Content LIKE
'%catalog%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                     244 |
+-------------------------+
1 row in set (3 min 57.46 sec)

As you can see the mysql query is appearing to take a lil under 4 mins
which is ok, however it does not seem to reflect this speed using the
web browser.

I also ran an Explain as below:

 explain SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2  WHERE
((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId =
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket'))
AND ( ( (Attachments_2.Content LIKE
'%catalog%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
+----------------+--------+-----------------------+---------+---------+-
----------------------------+-------+-------------+
| table          | type   | possible_keys         | key     | key_len |
ref                         | rows  | Extra       |
+----------------+--------+-----------------------+---------+---------+-
----------------------------+-------+-------------+
| Attachments_2  | ALL    | Attachments2          | NULL    |    NULL |
NULL                        | 67372 | Using where |
| Transactions_1 | eq_ref | PRIMARY,Transactions1 | PRIMARY |       4 |
Attachments_2.TransactionId |     1 | Using where |
| main           | eq_ref | PRIMARY               | PRIMARY |       4 |
Transactions_1.ObjectId     |     1 | Using where |
+----------------+--------+-----------------------+---------+---------+-
----------------------------+-------+-------------+


I think the indeices look right but I am NO mysql guru so not entriley
sure.

I shall include the main RT conf options below:
Loaded perl modules
Perl v5.8.7 under linux
  Apache v1.27;
  Apache::Connection v1.00;
  Apache::Constants v1.09;
  Apache::DBI v0.9901;
  Apache::Request v1.33;
  Apache::Server v1.01;
  Apache::Session v1.6;
  Apache::Session::Generate::MD5 v2.1;
  Apache::Session::Lock::MySQL v1.00;
  Apache::Session::MySQL v1.01;
  Apache::Session::Serialize::Storable v1.00;
  Apache::Session::Store::DBI v1.02;
  Apache::Session::Store::MySQL v1.04;
  Apache::Status v2.03;
  Apache::Table v0.01;
  AutoLoader v5.60;
  B v1.09;
  base v2.07;
  Benchmark v1.07;
  bytes v1.02;
  Cache::Simple::TimedExpiry v0.23;
  Carp v1.04;
  CGI v3.10;
  CGI::Cookie v1.25;
  CGI::Util v1.5;
  Class::Container v0.12;
  Class::Data::Inheritable v0.02;
  Class::ReturnValue v0.53;
  constant v1.05;
  Cwd v3.05;
  Data::Dumper v2.121_04;
  DBD::mysql v3.0002_1;
  DBI v1.48;
  DBIx::SearchBuilder v1.32;
  DBIx::SearchBuilder::Unique v0.01;
  Devel::StackTrace v1.11;
  Devel::StackTraceFrame v0.6;
  Digest::base v1.00;
  Digest::MD5 v2.33;
  DynaLoader v1.05;
  Encode v2.10;
  Encode::Alias v2.03;
  Encode::Config v2.00;
  Encode::Encoding v2.02;
  Errno v1.0901;
  Exception::Class v1.21;
  Exception::Class::Base v1.2;
  Exporter v5.58;
  Exporter::Heavy v5.58;
  Fcntl v1.05;
  fields v2.03;
  File::Basename v2.73;
  File::Glob v1.04;
  File::Path v1.07;
  File::Spec v3.05;
  File::Spec::Unix v1.5;
  File::Temp v0.16;
  FileHandle v2.01;
  HTML::Entities v1.29;
  HTML::Mason v1.3101;
  HTML::Mason::ApacheHandler v1.69;
  HTML::Mason::Exception v1.1;
  HTML::Mason::Exception::Abort v1.1;
  HTML::Mason::Exception::Compilation v1.1;
  HTML::Mason::Exception::Compilation::IncompatibleCompiler v1.1;
  HTML::Mason::Exception::Compiler v1.1;
  HTML::Mason::Exception::Decline v1.1;
  HTML::Mason::Exception::Params v1.1;
  HTML::Mason::Exception::Syntax v1.1;
  HTML::Mason::Exception::System v1.1;
  HTML::Mason::Exception::TopLevelNotFound v1.1;
  HTML::Mason::Exception::VirtualMethod v1.1;
  HTML::Mason::Exceptions v1.43;
  HTML::Parser v3.45;
  HTML::Scrubber v0.08;
  I18N::LangTags v0.35;
  I18N::LangTags::Detect v1.03;
  integer v1.00;
  IO v1.21;
  IO::File v1.11;
  IO::Handle v1.24;
  IO::InnerFile v2.110;
  IO::Lines v2.110;
  IO::Scalar v2.110;
  IO::ScalarArray v2.110;
  IO::Seekable v1.09;
  IO::Wrap v2.110;
  IO::WrapTie v2.110;
  IPC::Open2 v1.01;
  IPC::Open3 v1.0106;
  lib v0.5565;
  List::Util v1.14;
  locale v1.00;
  Locale::Maketext v1.09;
  Locale::Maketext::Fuzzy v0.02;
  Locale::Maketext::Lexicon v0.49;
  Locale::Maketext::Lexicon::Gettext v0.14;
  Log::Dispatch v2.11;
  Log::Dispatch::Base v1.09;
  Log::Dispatch::File v1.22;
  Log::Dispatch::Output v1.26;
  Log::Dispatch::Screen v1.17;
  Log::Dispatch::Syslog v1.18;
  Mail::Address v1.67;
  Mail::Field v1.67;
  Mail::Field::AddrList v1.67;
  Mail::Header v1.67;
  Mail::Internet v1.67;
  MIME::Base64 v3.05;
  MIME::Body v5.417;
  MIME::Decoder v5.417;
  MIME::Decoder::NBit v5.417;
  MIME::Entity v5.417;
  MIME::Field::ContDisp v5.417;
  MIME::Field::ConTraEnc v5.417;
  MIME::Field::ContType v5.417;
  MIME::Field::ParamVal v5.417;
  MIME::Head v5.417;
  MIME::Parser v5.417;
  MIME::QuotedPrint v3.03;
  MIME::Tools v5.417;
  MIME::Words v5.417;
  mod_perl v1.29;
  Module::Versions::Report v1.02;
  overload v1.03;
  Params::Validate v0.78;
  POSIX v1.08;
  re v0.04;
  Regexp::Common v2.120;
  Regexp::Common::_support v2.101;
  Regexp::Common::balanced v2.101;
  Regexp::Common::CC v2.100;
  Regexp::Common::comment v2.116;
  Regexp::Common::delimited v2.104;
  Regexp::Common::lingua v2.105;
  Regexp::Common::list v2.103;
  Regexp::Common::net v2.105;
  Regexp::Common::number v2.108;
  Regexp::Common::profanity v2.104;
  Regexp::Common::SEN v2.102;
  Regexp::Common::URI v2.108;
  Regexp::Common::URI::fax v2.100;
  Regexp::Common::URI::file v2.100;
  Regexp::Common::URI::ftp v2.101;
  Regexp::Common::URI::gopher v2.100;
  Regexp::Common::URI::http v2.101;
  Regexp::Common::URI::news v2.100;
  Regexp::Common::URI::pop v2.100;
  Regexp::Common::URI::prospero v2.100;
  Regexp::Common::URI::RFC1035 v2.100;
  Regexp::Common::URI::RFC1738 v2.104;
  Regexp::Common::URI::RFC1808 v2.100;
  Regexp::Common::URI::RFC2384 v2.102;
  Regexp::Common::URI::RFC2396 v2.100;
  Regexp::Common::URI::RFC2806 v2.100;
  Regexp::Common::URI::tel v2.100;
  Regexp::Common::URI::telnet v2.100;
  Regexp::Common::URI::tv v2.100;
  Regexp::Common::URI::wais v2.100;
  Regexp::Common::whitespace v2.103;
  Regexp::Common::zip v2.112;
  RT v3.4.4;
  RT::Interface::Email v1.02;
  Scalar::Util v1.14;
  SelectSaver v1.01;
  Socket v1.77;
  Storable v2.13;
  strict v1.03;
  Symbol v1.06;
  Sys::Hostname v1.11;
  Sys::Syslog v0.06;
  Text::Autoformat v1.13;
  Text::Quoted v1.8;
  Text::Reform v1.11;
  Text::Tabs v98.112801;
  Text::Template v1.44;
  Text::Wrapper v1.000;
  Time::HiRes v1.66;
  Time::JulianDay v2003.1125;
  Time::Local v1.11;
  Time::ParseDate v2003.1126;
  Time::Timezone v2003.0211;
  Tree::Simple v1.15;
  URI v1.35;
  URI::Escape v3.28;
  URI::URL v5.03;
  URI::WithBase v2.19;
  utf8 v1.05;
  vars v1.01;
  warnings v1.03;
  warnings::register v1.00;
  XSLoader v0.02;


RT Variables
RT::AmbiguousDayInPast	1 	
RT::BasePath	/usr/local/rt3 	
RT::BinPath	/usr/local/rt3/bin 	
RT::CORE_CONFIG_FILE	/usr/local/rt3/etc/RT_Config.pm 	
RT::CommentAddress	X	
RT::CorrespondAddress	X	
RT::DatabaseHost	localhost 	
RT::DatabaseName	X	
RT::DatabasePassword	Password not printed 	
RT::DatabaseRTHost	localhost 	
RT::DatabaseType	mysql 	
RT::DatabaseUser	X	
RT::DateDayBeforeMonth	1 	
RT::DefaultSearchResultFormat	'<B><A
HREF="/Ticket/Display.html?id=__id__">__id__</a></B>/TITLE:#', '<B><A
HREF="/Ticket/Display.html?id=__id__">__Subject__</a></B>/TITLE:Subject'
, '<small><A
HREF="/Ticket/Display.html?id=__id__"></a>__CustomField.{ClientID}__</sm
all>/TITLE:ClientID', Status, QueueName, OwnerName, '__NEWLINE__', '',
'<small>__Requestors__</small>', '<small><A
HREF="/Ticket/Display.html?id=__id__"></a>__CustomField.{AccountsBillabl
e}__</small>/TITLE:AccountsBillable',
'<small>__CreatedRelative__</small>', '<small>__ToldRelative__</small>',
'<small>__LastUpdatedRelative__</small>' 	
RT::EmailOutputEncoding	utf-8 	
RT::EtcPath	/usr/local/rt3/etc 	
RT::FriendlyFromLineFormat	"%s via RT" <%s> 	
RT::FriendlyToLineFormat	X	
RT::LocalEtcPath	/usr/local/rt3/local/etc 	
RT::LocalLexiconPath	/usr/local/rt3/local/po 	
RT::LocalPath	/usr/local/rt3/local 	
RT::LogDir	/usr/local/rt3/var/log 	
RT::LogToFile	info 	
RT::LogToFileNamed	rt.log 	
RT::LogToScreen	error 	
RT::LogToSyslog	debug 	
RT::LogoURL	/NoAuth/images/rt.jpg 	
RT::LoopsToRTOwner	1 	
RT::MailCommand	sendmailpipe 	
RT::MasonComponentRoot	/usr/local/rt3/share/html 	
RT::MasonDataDir	/usr/local/rt3/var/mason_data 	
RT::MasonLocalComponentRoot	/usr/local/rt3/local/html 	
RT::MasonSessionDir	/usr/local/rt3/var/session_data 	
RT::MaxAttachmentSize	10000000 	
RT::MaxInlineBody	13456 	
RT::MessageBoxWidth	72 	
RT::MessageBoxWrap	HARD 	
RT::MinimumPasswordLength	5 	
RT::MyRequestsLength	20 	
RT::MyTicketsLength	20 	
RT::Organization	X	
RT::OwnerEmail	X	
RT::RTAddressRegexp	^rt\@example.com$ 	
RT::RecordOutgoingEmail	1 	
RT::RedistributeAutoGeneratedMessages	1 	
RT::SITE_CONFIG_FILE	/usr/local/rt3/etc/RT_SiteConfig.pm 	
RT::SendmailArguments	-oi -t 	
RT::SendmailPath	/usr/sbin/sendmail 	
RT::Timezone	Europe/London 	
RT::UseFriendlyFromLine	1 	
RT::VERSION	3.4.4 	
RT::VarPath	/usr/local/rt3/var 	
RT::WebBaseURL	http://rt.foreshore.net 	
RT::WebFlushDbCacheEveryRequest	1 	
RT::WebImagesURL	/NoAuth/images/ 	
RT::WebURL	http://rt.foreshore.net/ 	
RT::rtname	foreshore.net 	
Perl configuration
Summary of my perl5 (revision 5 version 8 subversion 7) configuration:
  Platform:
    osname=linux, osvers=2.4.21-32.0.1.elsmp, archname=i686-linux
    uname='linux linuxserv02.foreshore.net 2.4.21-32.0.1.elsmp #1 smp
tue may 17 17:52:23 edt 2005 i686 i686 i386 gnulinux '
    config_args=''
    hint=previous, useposix=true, d_sigaction=define
    usethreads=undef use5005threads=undef useithreads=undef
usemultiplicity=undef
    useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
    use64bitint=undef use64bitall=undef uselongdouble=undef
    usemymalloc=n, bincompat5005=undef
  Compiler:
    cc='gcc', ccflags ='-fno-strict-aliasing -pipe -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm',
    optimize='-O2',
    cppflags='-fno-strict-aliasing -pipe -I/usr/local/include
-I/usr/include/gdbm -fno-strict-aliasing -pipe -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm'
    ccversion='', gccversion='3.2.3 20030502 (Red Hat Linux 3.2.3-53)',
gccosandvers=''
    intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
    d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12
    ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
    alignbytes=4, prototype=define
  Linker and Libraries:
    ld='gcc', ldflags =' -L/usr/local/lib'
    libpth=/usr/local/lib /lib /usr/lib
    libs=-lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lc
    perllibs=-lnsl -ldl -lm -lcrypt -lutil -lc
    libc=/lib/libc-2.3.2.so, so=so, useshrplib=true, libperl=libperl.so
    gnulibc_version='2.3.2'
  Dynamic Linking:
    dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E
-Wl,-rpath,/usr/local/lib/perl5/5.8.7/i686-linux/CORE'
    cccdlflags='-fpic', lddlflags='-shared -L/usr/local/lib'


I have also tried to add the Content-Type matches 'text/plain' to the
search but this seems to not cause much benefit as below:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2  WHERE
((Transactions_1.ObjectType = 'RT::Ticket')AND(Transactions_1.ObjectType
= 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content
LIKE '%catalog%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) AND (
(Attachments_2.ContentType LIKE
'%text/plain%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                     159 |
+-------------------------+
1 row in set (2 min 58.34 sec)

mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main ,
Transactions Transactions_1, Attachments Attachments_2  WHERE
((Transactions_1.ObjectType = 'RT::Ticket')AND(Transactions_1.ObjectType
= 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content
LIKE '%catalog%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) AND (
(Attachments_2.ContentType LIKE
'%text/plain%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
+----------------+--------+-----------------------+---------+---------+-
----------------------------+-------+-------------+
| table          | type   | possible_keys         | key     | key_len |
ref                         | rows  | Extra       |
+----------------+--------+-----------------------+---------+---------+-
----------------------------+-------+-------------+
| Attachments_2  | ALL    | Attachments2          | NULL    |    NULL |
NULL                        | 67382 | Using where |
| Transactions_1 | eq_ref | PRIMARY,Transactions1 | PRIMARY |       4 |
Attachments_2.TransactionId |     1 | Using where |
| main           | eq_ref | PRIMARY               | PRIMARY |       4 |
Transactions_1.ObjectId     |     1 | Using where |
+----------------+--------+-----------------------+---------+---------+-
----------------------------+-------+-------------+
3 rows in set (0.03 sec)

If anyone has any suggestions on how to speed this up (i.e. Indexs to
create, or perl modules to update) please let me know I have checked on
the forums but couldn't seem to find anything relevant. 


Regards

David Wells
MCSE, RHCE & CCNP

Senior Network Engineer

Foreshore Limited

Direct Line: +44 1534 752316

Facsimile: +44 1534 752301

Email@ dave.wells at foreshore.net

http://www.foreshore.net

http://privacyprofessional.com

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________



More information about the rt-users mailing list