RT Query Logging

Varun_Vyas · June 30, 2009, 6:08am

Hello All

Can anyone can tell me how i can log my queries that hits my database in
RT.log. I am using oracle 9i and RT 3.6.3 we have a performance issue as
our RT is very slow and takes 15-20 seconds for database operations. So
i want to log my queries so that i can then analyze them for performance
optimisation. And also i m getting internal server error frequently can
any one tell me how can i correct it

Any help in highly appreciated

Thanks & Regards
Varun Vyas

Raed_El-Hames · June 30, 2009, 8:19am

From RT_Config.pm

DBIx-SearchBuilder 1.31_1 or higher; simply set $StatementLog to be

the level that you wish SQL statements to be logged at.

Set($StatementLog,undef);

I am not sure what the different levels but :
Set($StatementLog,1);

will be a good start.
Remember to restart your web server.

Regards;

Roy

Varun Vyas wrote:

Raed_El-Hames · June 30, 2009, 8:59am

Varun;

There is no straight forward answer to this , you possibly should start
by stating your RT version, DB type(oracle,mysql ??) and version ,
apache, mod_perl or fastcgi etc …
Also search the wiki and mailing list there are lots of posts and a good
article in the wiki covering this.(sorry don’t remember the url).

Good luck;

Roy

Varun Vyas wrote:

Raed_El-Hames · June 30, 2009, 9:20am

Varun;

You should make it a habit to reply to the list and not individuals , I
have never deployed RT with Oracle so can’t help you –
few in the may though

ps: A pointer so you know what RT is capable of , our RT db is > 50G in
size, the attachments table alone is > 26G, our db server is quad dual
core with 16 G Ram (4x4) and the web server is standard 3.6G cpu with 3G
Ram, average per day tickets is ~ 1000 with 200 internal users and
10000+ external customer users.

Regards;
Roy

Varun Vyas wrote:

Varun_Vyas · June 30, 2009, 9:28am

Hi Raed El-Hames

I am so sorry if i have bothered you but as i am new to this mail list i
am also not aware that i need to reply to all users in list and not
individual.

Raed El-Hames wrote:

Varun;

You should make it a habit to reply to the list and not individuals ,
I have never deployed RT with Oracle so can’t help you –
few in the may though

ps: A pointer so you know what RT is capable of , our RT db is > 50G
in size, the attachments table alone is > 26G, our db server is quad
dual core with 16 G Ram (4x4) and the web server is standard 3.6G cpu
with 3G Ram, average per day tickets is ~ 1000 with 200 internal users
and 10000+ external customer users.

Regards;
Roy

Varun Vyas wrote:

We have deployed RT 3.6.3 on oracle 9i database as an backend
in a medium sized firm and we have lots of tickets processed
everyday more than 100. My problem is that the application is very
irresponsive and very slow. We have already 8 gigs of RAM on our
webserver with Apache 2.0 and fastCGI implemented. Also we have
performed indexing on our oracle database with fastcgi implemented.
But nothing seems to be going in our way. So please anyone can
suggest wat will be the problem. I m looking for concerete answer to
my problem. On which side i need to tune on application side or on
database side and if on both side what step i need to take to make my
application lightning fast.With acceptable level of performance.

And i have looked for all this things in google and have searched a
lot from past two months but has found nothing.

Raed El-Hames wrote:

Varun;

There is no straight forward answer to this , you possibly should
start by stating your RT version, DB type(oracle,mysql ??) and
version , apache, mod_perl or fastcgi etc …
Also search the wiki and mailing list there are lots of posts and a
good article in the wiki covering this.(sorry don’t remember the url).

Good luck;

Roy

Varun Vyas wrote:

And i want to know one more thing as my RT is slow right now .What
steps should i take to increase its performance on application side
or on database side. We have remote database on our RT and it has 2
gigs RAM.

Raed El-Hames wrote:

From RT_Config.pm

DBIx-SearchBuilder 1.31_1 or higher; simply set $StatementLog to be

the level that you wish SQL statements to be logged at.

Set($StatementLog,undef);

I am not sure what the different levels but :
Set($StatementLog,1);

will be a good start.
Remember to restart your web server.

Regards;

Roy

Varun Vyas wrote:

Hello All

Can anyone can tell me how i can log my queries that hits my
database in RT.log. I am using oracle 9i and RT 3.6.3 we have a
performance issue as our RT is very slow and takes 15-20 seconds
for database operations. So i want to log my queries so that i
can then analyze them for performance optimisation. And also i m
getting internal server error frequently can any one tell me how
can i correct it

Any help in highly appreciated

Thanks & Regards
Varun Vyas
Software Engineer - Automation
Elitecore Technologies

Varun_Vyas · June 30, 2009, 9:30am

And can you can tell me what kind of maintainance you have done on
application side i.e RT side to enhance its performance and what kind of
tweaks you have applied in apache configuration file and
RT_SiteConfig.pm file

Thanks In Advance

Raed El-Hames wrote:

Varun;

You should make it a habit to reply to the list and not individuals ,
I have never deployed RT with Oracle so can’t help you –
few in the may though

ps: A pointer so you know what RT is capable of , our RT db is > 50G
in size, the attachments table alone is > 26G, our db server is quad
dual core with 16 G Ram (4x4) and the web server is standard 3.6G cpu
with 3G Ram, average per day tickets is ~ 1000 with 200 internal users
and 10000+ external customer users.

Regards;
Roy

Varun Vyas wrote:

We have deployed RT 3.6.3 on oracle 9i database as an backend
in a medium sized firm and we have lots of tickets processed
everyday more than 100. My problem is that the application is very
irresponsive and very slow. We have already 8 gigs of RAM on our
webserver with Apache 2.0 and fastCGI implemented. Also we have
performed indexing on our oracle database with fastcgi implemented.
But nothing seems to be going in our way. So please anyone can
suggest wat will be the problem. I m looking for concerete answer to
my problem. On which side i need to tune on application side or on
database side and if on both side what step i need to take to make my
application lightning fast.With acceptable level of performance.

And i have looked for all this things in google and have searched a
lot from past two months but has found nothing.

Raed El-Hames wrote:

Varun;

There is no straight forward answer to this , you possibly should
start by stating your RT version, DB type(oracle,mysql ??) and
version , apache, mod_perl or fastcgi etc …
Also search the wiki and mailing list there are lots of posts and a
good article in the wiki covering this.(sorry don’t remember the url).

Good luck;

Roy

Varun Vyas wrote:

And i want to know one more thing as my RT is slow right now .What
steps should i take to increase its performance on application side
or on database side. We have remote database on our RT and it has 2
gigs RAM.

Raed El-Hames wrote:

From RT_Config.pm

DBIx-SearchBuilder 1.31_1 or higher; simply set $StatementLog to be

the level that you wish SQL statements to be logged at.

Set($StatementLog,undef);

I am not sure what the different levels but :
Set($StatementLog,1);

will be a good start.
Remember to restart your web server.

Regards;

Roy

Varun Vyas wrote:

Hello All

Can anyone can tell me how i can log my queries that hits my
database in RT.log. I am using oracle 9i and RT 3.6.3 we have a
performance issue as our RT is very slow and takes 15-20 seconds
for database operations. So i want to log my queries so that i
can then analyze them for performance optimisation. And also i m
getting internal server error frequently can any one tell me how
can i correct it

Any help in highly appreciated

Thanks & Regards
Varun Vyas
Software Engineer - Automation
Elitecore Technologies

Raed_El-Hames · June 30, 2009, 9:42am

There is no bother, what I meant is the list will be more helpful than me.
for tweaks and performance tuning the wiki and mailing list is better
place than me, because it varies depending on the database server you use.
Start with observation and detect your bottle necks , ist the web part
or the database, in most cases the later, which part of the application
is slow, creating tickets or searching tickets etc etc

Regards

Roy

Varun Vyas wrote:

Varun_Vyas · July 1, 2009, 6:33am

Hello All

In my RT application my creating, updating and closing of tickets are
very slow and takes almost 20 sec to insert a ticket and nearly 35
seconds to update a ticket. We have applied many changes on our database
side and application side too. But still our RT application is not
stabilized. We have oracle 9i as our database with indexing enabled and
we also have implemented COMPUTE stats gathering and apart from that we
have also tried to trace SQL for finding the cause that which queries
are taking long time. But still we are not able to find the cause of
sluggishness. And i need to know if anyone has faced such kind of
performance problem before and if yes then where should i look for
improving my performance. And make RT more responsive.

Any help is highly appriciated.

Raed El-Hames wrote:

There is no bother, what I meant is the list will be more helpful than
me.
for tweaks and performance tuning the wiki and mailing list is better
place than me, because it varies depending on the database server you
use.
Start with observation and detect your bottle necks , ist the web part
or the database, in most cases the later, which part of the
application is slow, creating tickets or searching tickets etc etc

Regards

Roy

Varun Vyas wrote:

Hi Raed El-Hames

I am so sorry if i have bothered you but as i am new to this mail
list i am also not aware that i need to reply to all users in list
and not individual.

Raed El-Hames wrote:

Varun;

You should make it a habit to reply to the list and not individuals
, I have never deployed RT with Oracle so can’t help you –
few in the may though

ps: A pointer so you know what RT is capable of , our RT db is > 50G
in size, the attachments table alone is > 26G, our db server is quad
dual core with 16 G Ram (4x4) and the web server is standard 3.6G
cpu with 3G Ram, average per day tickets is ~ 1000 with 200 internal
users and 10000+ external customer users.

Regards;
Roy

Varun Vyas wrote:

We have deployed RT 3.6.3 on oracle 9i database as an backend
in a medium sized firm and we have lots of tickets processed
everyday more than 100. My problem is that the application is very
irresponsive and very slow. We have already 8 gigs of RAM on our
webserver with Apache 2.0 and fastCGI implemented. Also we have
performed indexing on our oracle database with fastcgi implemented.
But nothing seems to be going in our way. So please anyone can
suggest wat will be the problem. I m looking for concerete answer
to my problem. On which side i need to tune on application side or
on database side and if on both side what step i need to take to
make my application lightning fast.With acceptable level of
performance.

And i have looked for all this things in google and have searched a
lot from past two months but has found nothing.

Raed El-Hames wrote:

Varun;

There is no straight forward answer to this , you possibly should
start by stating your RT version, DB type(oracle,mysql ??) and
version , apache, mod_perl or fastcgi etc …
Also search the wiki and mailing list there are lots of posts and
a good article in the wiki covering this.(sorry don’t remember the
url).

Good luck;

Roy

Varun Vyas wrote:

And i want to know one more thing as my RT is slow right now
.What steps should i take to increase its performance on
application side or on database side. We have remote database on
our RT and it has 2 gigs RAM.

Raed El-Hames wrote:

From RT_Config.pm

DBIx-SearchBuilder 1.31_1 or higher; simply set $StatementLog

to be

the level that you wish SQL statements to be logged at.

Set($StatementLog,undef);

I am not sure what the different levels but :
Set($StatementLog,1);

will be a good start.
Remember to restart your web server.

Regards;

Roy

Varun Vyas wrote:

Hello All

Can anyone can tell me how i can log my queries that hits my
database in RT.log. I am using oracle 9i and RT 3.6.3 we have a
performance issue as our RT is very slow and takes 15-20
seconds for database operations. So i want to log my queries so
that i can then analyze them for performance optimisation. And
also i m getting internal server error frequently can any one
tell me how can i correct it

Any help in highly appreciated

Thanks & Regards
Varun Vyas
Software Engineer - Automation
Elitecore Technologies

Joop · July 1, 2009, 7:02am

Varun Vyas wrote:

Hello All

In my RT application my creating, updating and closing of tickets are
very slow and takes almost 20 sec to insert a ticket and nearly 35
seconds to update a ticket. We have applied many changes on our database
side and application side too. But still our RT application is not
stabilized. We have oracle 9i as our database with indexing enabled and
we also have implemented COMPUTE stats gathering and apart from that we
have also tried to trace SQL for finding the cause that which queries
are taking long time. But still we are not able to find the cause of
sluggishness. And i need to know if anyone has faced such kind of
performance problem before and if yes then where should i look for
improving my performance. And make RT more responsive.

We skipped Oracle9i as a production platform and went from 8i to 10g. We
had a few customers who ran 9i but can’t say if they had performance
problems which were related to 9i specifically. As already suggested you
can turn on sqltracing in the DBD and/or DBI layer. Check the
documentation of DBI and DBD(::Oracle). Further you could use the info
inside the SGA to findout which queries are running real slow by either
using Statspack of EnterpriseManager.

As a last resort you could install an instance of OracleXE, which in
essence is Oracle10g, and see what kind of performance you get with
that. Keep in mind though that XE has a few limitations on database size.

What I and probably the rest of the list would like to know is what is
your hardware config and what is your software config, OS/perl versions etc.
It could be that someone knows that your config combinations is
problematic, now we’re all shooting in the dark hoping to hit the magic
button.

Regards,

Joop

Varun_Vyas · July 1, 2009, 8:20am

Hello All

My RT version is 3.6.3 os version and configuration on my application
server is as follows

*Linux rt.xxdomain.com 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005
i686 i686 i386 GNU/Linux
Red Hat Enterprise Linux AS release 4 (Nahant)
Linux version 2.6.9-5.ELsmp (bhcompile@decompose.build.redhat.com) (gcc
version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)) #1 SMP Wed Jan 5 19:30:39
EST 2005
We have Perl 5.8.8 in our system and DBIx::SearchBuilder 1.49 and 8
gigs of RAM

And on database server we have
*Linux eliteweb 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686
i686 i386 GNU/Linux
Red Hat Enterprise Linux ES release 3 (Taroon)
Linux version 2.4.21-4.ELsmp (bhcompile@daffy.perf.redhat.com) (gcc
version 3.2.3 20030502 (Red Hat Linux 3.2.3-20)) #1 SMP Fri Oct 3
17:52:56 EDT 2003

*with 2 gigs of RAM.

Joop wrote:

Varun Vyas wrote:

Hello All

In my RT application my creating, updating and closing of tickets are
very slow and takes almost 20 sec to insert a ticket and nearly 35
seconds to update a ticket. We have applied many changes on our
database side and application side too. But still our RT application
is not stabilized. We have oracle 9i as our database with indexing
enabled and we also have implemented COMPUTE stats gathering and
apart from that we have also tried to trace SQL for finding the cause
that which queries are taking long time. But still we are not able to
find the cause of sluggishness. And i need to know if anyone has
faced such kind of performance problem before and if yes then where
should i look for improving my performance. And make RT more responsive.

We skipped Oracle9i as a production platform and went from 8i to 10g.
We had a few customers who ran 9i but can’t say if they had
performance problems which were related to 9i specifically. As already
suggested you can turn on sqltracing in the DBD and/or DBI layer.
Check the documentation of DBI and DBD(::Oracle). Further you could
use the info inside the SGA to findout which queries are running real
slow by either using Statspack of EnterpriseManager.

As a last resort you could install an instance of OracleXE, which in
essence is Oracle10g, and see what kind of performance you get with
that. Keep in mind though that XE has a few limitations on database size.

What I and probably the rest of the list would like to know is what is
your hardware config and what is your software config, OS/perl
versions etc.
It could be that someone knows that your config combinations is
problematic, now we’re all shooting in the dark hoping to hit the
magic button.

Regards,

Joop

Thanks & Regards
Varun Vyas
Software Engineer - Automation
Elitecore Technologies

Varun_Vyas · July 1, 2009, 8:25am

Hardware Info on Application Server is

processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5120 @ 1.86GHz
stepping : 6
cpu MHz : 1866.868
cache size : 64 KB
physical id : 0
siblings : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht t
m pbe nx lm pni monitor ds_cpl est tm2 xtpr
bogomips : 3702.78

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5120 @ 1.86GHz
stepping : 6
cpu MHz : 1866.868
cache size : 64 KB
physical id : 0
siblings : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht t
m pbe nx lm pni monitor ds_cpl est tm2 xtpr
bogomips : 3727.36On database server is processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Pentium(R) 4 CPU 3.00GHz stepping : 1 cpu MHz : 3000.234 cache size : 1024 KB physical id : 0 siblings : 2 runqueue : 0 fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht t m bogomips : 5989.99 processor : 1 vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Pentium(R) 4 CPU 3.00GHz stepping : 1 cpu MHz : 3000.234 cache size : 1024 KB physical id : 0 siblings : 2 runqueue : 0 fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht t m bogomips : 5989.99 Joop wrote:

Varun Vyas wrote:

Hello All

In my RT application my creating, updating and closing of tickets are
very slow and takes almost 20 sec to insert a ticket and nearly 35
seconds to update a ticket. We have applied many changes on our
database side and application side too. But still our RT application
is not stabilized. We have oracle 9i as our database with indexing
enabled and we also have implemented COMPUTE stats gathering and
apart from that we have also tried to trace SQL for finding the cause
that which queries are taking long time. But still we are not able to
find the cause of sluggishness. And i need to know if anyone has
faced such kind of performance problem before and if yes then where
should i look for improving my performance. And make RT more responsive.

We skipped Oracle9i as a production platform and went from 8i to 10g.
We had a few customers who ran 9i but can’t say if they had
performance problems which were related to 9i specifically. As already
suggested you can turn on sqltracing in the DBD and/or DBI layer.
Check the documentation of DBI and DBD(::Oracle). Further you could
use the info inside the SGA to findout which queries are running real
slow by either using Statspack of EnterpriseManager.

As a last resort you could install an instance of OracleXE, which in
essence is Oracle10g, and see what kind of performance you get with
that. Keep in mind though that XE has a few limitations on database size.

What I and probably the rest of the list would like to know is what is
your hardware config and what is your software config, OS/perl
versions etc.
It could be that someone knows that your config combinations is
problematic, now we’re all shooting in the dark hoping to hit the
magic button.

Regards,

Joop

Thanks & Regards
Varun Vyas
Software Engineer - Automation
Elitecore Technologies

Alexander_Bourgett · July 1, 2009, 9:58am

Hi Varun,

We have the situation here that our mail server (Exim4) is very slow on
sending the RT emails wich are generated on ticket creation and update. So
we also have to wait around 20s sometimes for RT webpage coming back after
update. Without exim the RT responses are coming fast. Possibly you also
have this kind of problem.

Currently I do not have a solution for this, even searching the wiki and the
web.

Regards,
Alex.

Raed_El-Hames · July 1, 2009, 11:11am

Alex;

For your issue have a look at:

http://archives.free.net.ph/message/20090424.164012.5961a3ca.en.html

It may help?!

Regards;
Roy

Alexander Bourgett wrote:

Joop · July 4, 2009, 6:24am

Varun Vyas wrote:

Hello All

Can anyone can tell me how i can log my queries that hits my database in
RT.log. I am using oracle 9i and RT 3.6.3 we have a performance issue as
I saw that it is now working for you, query logging that is.

our RT is very slow and takes 15-20 seconds for database operations. So
i want to log my queries so that i can then analyze them for performance
optimisation. And also i m getting internal server error frequently can
any one tell me how can i correct it

Timeout can be circumvented by increasing the fastcgi timeout but that
doesn’t speed up RT.
I gather from your emails that it is not displaying that is reaaaal slow
but the updates and inserts which points to problems with the indices of
Oracle. Each insert/update updates the index. Do you have your index
tablespace of the rt schema on a different drive than your data tablespace?
Do you have archiving enabled? and where are your archives being written
to? and what about the redo logs?

What is the general write performance to that drive?

Hopefully some of these question will help you pinpoint where the
problem is.

Regards,

Joop

Varun_Vyas · July 4, 2009, 6:43am

Hi Joop

Thanks for your reply. I will let you know all this by concerning with my
DBA.

Thanks & Regards
Varun VyasFrom: Joop van de Wege [mailto:JoopvandeWege@mococo.nl]
Sent: Saturday, July 04, 2009 11:55 AM
To: Varun Vyas; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] RT Query Logging

Varun Vyas wrote:

Hello All

Can anyone can tell me how i can log my queries that hits my database in
RT.log. I am using oracle 9i and RT 3.6.3 we have a performance issue as
I saw that it is now working for you, query logging that is.

our RT is very slow and takes 15-20 seconds for database operations. So
i want to log my queries so that i can then analyze them for performance
optimisation. And also i m getting internal server error frequently can
any one tell me how can i correct it

Timeout can be circumvented by increasing the fastcgi timeout but that
doesn’t speed up RT.
I gather from your emails that it is not displaying that is reaaaal slow
but the updates and inserts which points to problems with the indices of
Oracle. Each insert/update updates the index. Do you have your index
tablespace of the rt schema on a different drive than your data tablespace?
Do you have archiving enabled? and where are your archives being written
to? and what about the redo logs?

What is the general write performance to that drive?

Hopefully some of these question will help you pinpoint where the
problem is.

Regards,

Joop