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 [EMAIL PROTECTED]
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@ [EMAIL PROTECTED]
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
______________________________________________________________________
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
We're hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html
