Performance and responsetime in RT 3.0.10

Hilde_Therese_Lauvse · April 30, 2004, 12:13pm

Hi,

We think RT’s response time is very low. It take about 6-7 seconds to
load a new page every time. I was wondering if anybody has the same
response time; better or worse. I have tried to do some research on how
to better the performance and has done a few changes, but with no luck.

Is there an idea to use indexing in the mysql database? Or isn’t the
database a problem at all?

If anyone has an idea of what we can do, so please give us a hint.

Here are our settings:

Machine: 2 CPU with 1 GHz and 1 GB RAM

RT-3.0.10

Apache 2.0

Fastcgi 2.4.2

Mysql 4

httpd.conf:

LoadModule fastcgi_module modules/mod_fastcgi.so

AddHandler fastcgi-script fcgi

FastCgiServer /opt/rt3/bin/mason_handler.fcgi

NameVirtualHost xxx.xxx.x.x:80

    ScriptAlias / /opt/rt3/bin/mason_handler.fcgi/ 

    ServerName blabla

    DocumentRoot /opt/rt3/share/html

    AddDefaultCharset UTF-8

hack to fix graphics with fastcgi

NameVirtualHost xxx.xxx.x.x:81

    DocumentRoot /opt/rt3/share/html/NoAuth/images

PerlSetVar MasonCodeCacheMaxSize 20000000

PerlSetVar MasonStaticSource 1

If you find something strange in my httpd.conf file please tell me

Hilde Therese

Ruslan_U_Zakirov · May 1, 2004, 1:21pm

Where you should start from.

ML Archives.
MasonX::Profiler
DB query log. Especialy long queries
Apache::DProf
Turn on Apache::Status before mason has loaded. Mason options also
should be set before Mason itself.
```
 	Good luck. Ruslan.
```

Hilde Therese Lauvset wrote:

Palle_Girgensohn · May 2, 2004, 11:26am

Hi,

We have terrible response times, at least 6-7 seconds, often more,
sometimes enough to make fastcgi give up (max time set to 30 s).

Please note that this is not mainly a problem with the database, it is
more or less only perl that uses the CPU. My guess is you will get the
same results if you check using e.g. top. I just checked logging in to our
RT, and the after hitting the login button with my credentials typed in, it
took approx 25 seconds to load the “Home” page. Hitting “Home” again loads
the page in about 6-7 seconds, since things are cached in the session, I
guess… Anyway, checking the database log, of the 25 seconds spent only
3.5 s where spent running database queries. Top shows about 15% load on
postgresql and 100% load on perl (2 CPUs, remember). Nothing else loads the
machine.

We don’t have a very fancy machine, which might explain the slowness to a
certain degree. Two 450 MHz P3s.

One thing about the postgresql queries is bad, there’s a mail thread about
this a while back, but I believe it is postgresql specific - the
SearchBuilder uses lower() on IDs, so indices are not used. Still only
10-15 % of the time is spent in the database, so there is something else,
something in the perl code.

I’ve had this discussion a few times before on the list, but I’m not sure
there is any way to fix this? Perhaps it is just the design of RT?
Hopefully throwing faster hardware at RT will help, but it is a pity.

I have FreeBSD 4.9, Postgresql-7.4.2, Perl 5.8.3, RT 3.0.10

/Palle–On fredag, april 30, 2004 14.13.32 +0200 Hilde Therese Lauvset Hilde.Lauvset@cc.uit.no wrote:

Fra: Hilde Therese Lauvset
Sendt: 30. april 2004 14:07
Til: ‘rt-users@bestpractical.com’
Emne: Performance and responsetime in RT 3.0.10

Hi,

We think RT’s response time is very low. It take about 6-7 seconds to
load a new page every time. I was wondering if anybody has the same
response time; better or worse. I have tried to do some research on how
to better the performance and has done a few changes, but with no luck.

Is there an idea to use indexing in the mysql database? Or isn’t the
database a problem at all?

If anyone has an idea of what we can do, so please give us a hint.

Here are our settings:

Machine: 2 CPU with 1 GHz and 1 GB RAM

RT-3.0.10

Apache 2.0

Fastcgi 2.4.2

Mysql 4

httpd.conf:

LoadModule fastcgi_module modules/mod_fastcgi.so

AddHandler fastcgi-script fcgi

FastCgiServer /opt/rt3/bin/mason_handler.fcgi

NameVirtualHost xxx.xxx.x.x:80
    ScriptAlias / /opt/rt3/bin/mason_handler.fcgi/

    ServerName blabla

    DocumentRoot /opt/rt3/share/html

    AddDefaultCharset UTF-8
hack to fix graphics with fastcgi

NameVirtualHost xxx.xxx.x.x:81
    DocumentRoot /opt/rt3/share/html/NoAuth/images
PerlSetVar MasonCodeCacheMaxSize 20000000

PerlSetVar MasonStaticSource 1

If you find something strange in my httpd.conf file please tell me

Hilde Therese

Ruslan_U_Zakirov · May 2, 2004, 12:30pm

Preface:
I run RT at home for tests on Duron600 with 196MB memory on
A1/mp1/mysql4.0.x with >3GB DB 40k tickets, 15k users…
and home page doesn’t take much time to load about 3sec no more.

Palle Girgensohn wrote:

Hi,

We have terrible response times, at least 6-7 seconds, often more,
sometimes enough to make fastcgi give up (max time set to 30 s).
I have no experience with FCGI

Please note that this is not mainly a problem with the database, it is
more or less only perl that uses the CPU. My guess is you will get the
same results if you check using e.g. top. I just checked logging in to
our RT, and the after hitting the login button with my credentials typed
in, it took approx 25 seconds to load the “Home” page. Hitting “Home”
again loads the page in about 6-7 seconds, since things are cached in
the session, I guess… Anyway, checking the database log, of the 25
seconds spent only 3.5 s where spent running database queries. Top shows
about 15% load on postgresql and 100% load on perl (2 CPUs, remember).
Nothing else loads the machine.
top isn’t good method to analize perfomance and it doesn’t show real
situation.
Could you send full DB report for one request to home page?
Did you try MasonX::Profiler, RT_Config:

@MasonParameters is the list of parameters for the constructor of

HTML::Mason’s Apache or CGI Handler. This is normally only useful

for debugging, eg. profiling individual components with

(preamble => ‘my $p = MasonX::Profiler->new($m, $r);’);

@MasonParameters = () unless (@MasonParameters);

We don’t have a very fancy machine, which might explain the slowness to
a certain degree. Two 450 MHz P3s.
I wrote test machine specs which run RT for me at home.

One thing about the postgresql queries is bad, there’s a mail thread
about this a while back, but I believe it is postgresql specific - the
SearchBuilder uses lower() on IDs, so indices are not used. Still only
10-15 % of the time is spent in the database, so there is something
else, something in the perl code.
Does DBIx::SB use ‘lower’ for you? May be ILIKE, because this fix to SB
only in latest SVN and not in 0.99v as I remember.
In Pg you can create index on lower(column). iirc Pg schema on RT SVN
allready modified.

rt15 · May 2, 2004, 3:54pm

I found solutions to the performance problems we were having with RT, read
on if your interested.

First, configuration info:

2x1GHz processor
1GB ram
Redhat Fedora Core 1
RT 3.0.10
Postgresql 7.3.4

Symptoms:
A single httpd process would eat 99% of available CPU
The Postgres postmaster daemon might use .5%
Ticket display could take as much as 15 to 20 seconds when moving
through a list of tickets.

Solutions:
Increase shared memory sizes and allocate more shared
memory to postgresql.
Ensure that maxrequests per child for Apache are set to
something low.

Results:
Apache processes run at 66% to 90% for brief periods and
the postgresql postmaster process will be seen using 3%
to 20%. Page loads are less than 3 seconds on most occasions.

Details:
Shared memory increases at the OS level:

Edit /etc/sysctl.conf to adjust shared memory sizes
Here is what is at the bottom of my /etc/sysctl.conf file

#for postgres, its shared memory tweaks
#kernel.shmmax=2147483648
kernel.shmmax=536870912
kernel.shmmni=4096
kernel.shmall=2097152
Execute “/sbin/sysctl -p” to re-read the parameters file.

Shared memory increases in the postgresql configuration:

Edit the postgresql.conf file in /var/lib/psql/data
and change shared_buffers to:

shared_buffers = 6004
You may also want to bump up the amount of memory allocated
to sorting by changing sort_mem to:

sort_mem = 8096
Stop Apache
Stop Postgres
Restart Postgres

Apache performance tuning:

Edit /etc/httpd.conf and change MaxRequestsPerChild to:

MaxRequestsPerChild 100
Start Apache

These changes resolved most of our performance issues. Once in a while
there continue to be episodes of slow response time, but on the whole its
a great deal better.

A friend has also implemented these changes on an OpenBSD system with
similar good results.

The original suggestion came from Arnold Cavazos, Jr. who participates in
this list.

Remember, this solution is specific to problem encountered with the
configuration listed a the top. This is not to say that such changes
might improve a mysql or Oracle based installation, but that it isn’t
tested.

What this experience reminded me of is that RT is a database intensive
application. Database servers almost always require customizations at the
operating system level to get the maximum possible performance out of the
hardware.

Let the rest of the list know if these suggestions improve your RT
installations performance.

Regards,

dano

Palle_Girgensohn · May 2, 2004, 4:47pm

Preface:
I run RT at home for tests on Duron600 with 196MB memory on
A1/mp1/mysql4.0.x with >3GB DB 40k tickets, 15k users…
and home page doesn’t take much time to load about 3sec no more.

Palle Girgensohn wrote:

Hi,

We have terrible response times, at least 6-7 seconds, often more,
sometimes enough to make fastcgi give up (max time set to 30 s).
I have no experience with FCGI

I run Apache, and since each process snatched > 70 megs when running with
mmod_perl, 10 apache processes soon ate 900 megs of RAM, the machine has
about 800 MB, so it became very slow. Hence, I switched to fastcgi and just
run one process for now. This was a major speedup.

Please note that this is not mainly a problem with the database, it is
more or less only perl that uses the CPU. My guess is you will get the
same results if you check using e.g. top. I just checked logging in to
our RT, and the after hitting the login button with my credentials typed
in, it took approx 25 seconds to load the “Home” page. Hitting “Home”
again loads the page in about 6-7 seconds, since things are cached in
the session, I guess… Anyway, checking the database log, of the 25
seconds spent only 3.5 s where spent running database queries. Top shows
about 15% load on postgresql and 100% load on perl (2 CPUs, remember).
Nothing else loads the machine.
top isn’t good method to analize perfomance and it doesn’t show real
situation.

That it quite true, but for procs running for some time, it does give you a
good clue, esp on FreeBSD where it does not eat cycles the way I’ve seen on
some other systems.

Could you send full DB report for one request to home page?

enclosed is all db queries with milliseconds duration, sorted on duration.
Total sum at the bottom. In “Comma Separated Values” format.

Did you try MasonX::Profiler, RT_Config:

Nope, but I could. What kind of parameters would I use?

@MasonParameters is the list of parameters for the constructor of

HTML::Mason’s Apache or CGI Handler. This is normally only useful

for debugging, eg. profiling individual components with

(preamble => ‘my $p = MasonX::Profiler->new($m, $r);’);

@MasonParameters = () unless (@MasonParameters);

We don’t have a very fancy machine, which might explain the slowness to
a certain degree. Two 450 MHz P3s.
I wrote test machine specs which run RT for me at home.

One thing about the postgresql queries is bad, there’s a mail thread
about this a while back, but I believe it is postgresql specific - the
SearchBuilder uses lower() on IDs, so indices are not used. Still only
10-15 % of the time is spent in the database, so there is something
else, something in the perl code.
Does DBIx::SB use ‘lower’ for you? May be ILIKE, because this fix to SB
only in latest SVN and not in 0.99v as I remember.
In Pg you can create index on lower(column). iirc Pg schema on RT SVN
allready modified.

Main point is that there is a bug where it wants to use ILIKE for primary
key and ID:s, and this is not very clever. Jesse admitted that this was
wrong, but I have no idea how to fix it. It bumps queries from 10 ms to 30
ms, it is not really a big deal.

/Palle

rt.csv.gz (3.62 KB)

Ruslan_U_Zakirov · May 2, 2004, 5:27pm

Palle Girgensohn wrote:

Preface:
I run RT at home for tests on Duron600 with 196MB memory on
A1/mp1/mysql4.0.x with >3GB DB 40k tickets, 15k users…
and home page doesn’t take much time to load about 3sec no more.

Palle Girgensohn wrote:

Hi,

We have terrible response times, at least 6-7 seconds, often more,
sometimes enough to make fastcgi give up (max time set to 30 s).

I have no experience with FCGI

I run Apache, and since each process snatched > 70 megs when running
with mmod_perl, 10 apache processes soon ate 900 megs of RAM, the
machine has about 800 MB, so it became very slow. Hence, I switched to
fastcgi and just run one process for now. This was a major speedup.
‘>70megs’ - it’s very strange for me, our apache/mp is only 25-30 meg
and only under some conditions one process can be 200 and more.

Please note that this is not mainly a problem with the database, it is
more or less only perl that uses the CPU. My guess is you will get the
same results if you check using e.g. top. I just checked logging in to
our RT, and the after hitting the login button with my credentials typed
in, it took approx 25 seconds to load the “Home” page. Hitting “Home”
again loads the page in about 6-7 seconds, since things are cached in
the session, I guess… Anyway, checking the database log, of the 25
seconds spent only 3.5 s where spent running database queries. Top shows
about 15% load on postgresql and 100% load on perl (2 CPUs, remember).
Nothing else loads the machine.

top isn’t good method to analize perfomance and it doesn’t show real
situation.

That it quite true, but for procs running for some time, it does give
you a good clue, esp on FreeBSD where it does not eat cycles the way
I’ve seen on some other systems.
Did you read report about similar conditions under linux from Dan
O’Neill? Situation can be same IMHO.

Could you send full DB report for one request to home page?

enclosed is all db queries with milliseconds duration, sorted on
duration. Total sum at the bottom. In “Comma Separated Values” format.

Did you try MasonX::Profiler, RT_Config:

Nope, but I could. What kind of parameters would I use?
@MasonParameters=(preamble=>‘my $p = MasonX::Profiler->new($m,$r);’);

and you should install MasonX::Profiler module. It’ll give more info
about where RT spins more while request.

@MasonParameters is the list of parameters for the constructor of

HTML::Mason’s Apache or CGI Handler. This is normally only useful

for debugging, eg. profiling individual components with

(preamble => ‘my $p = MasonX::Profiler->new($m, $r);’);

@MasonParameters = () unless (@MasonParameters);

We don’t have a very fancy machine, which might explain the slowness to
a certain degree. Two 450 MHz P3s.

I wrote test machine specs which run RT for me at home.

One thing about the postgresql queries is bad, there’s a mail thread
about this a while back, but I believe it is postgresql specific - the
SearchBuilder uses lower() on IDs, so indices are not used. Still only
10-15 % of the time is spent in the database, so there is something
else, something in the perl code.

Does DBIx::SB use ‘lower’ for you? May be ILIKE, because this fix to SB
only in latest SVN and not in 0.99v as I remember.
In Pg you can create index on lower(column). iirc Pg schema on RT SVN
allready modified.

Main point is that there is a bug where it wants to use ILIKE for
primary key and ID:s, and this is not very clever. Jesse admitted that
this was wrong, but I have no idea how to fix it. It bumps queries from
10 ms to 30 ms, it is not really a big deal.
This should be fixed in DBIx::SB, but it’s not so easy. DBIx::SB doesn’t
know anything about field types so can’t make decision where use
ILIKE/LOWER and where not to make query case insensetive. Pg doesn’t
optimize ILIKE or LOWER too if field is numeric.

Jesse_Vincent · May 2, 2004, 6:23pm

Main point is that there is a bug where it wants to use ILIKE for
primary key and ID:s, and this is not very clever. Jesse admitted that
this was wrong, but I have no idea how to fix it. It bumps queries from
10 ms to 30 ms, it is not really a big deal.
This should be fixed in DBIx::SB, but it’s not so easy. DBIx::SB doesn’t
know anything about field types so can’t make decision where use
ILIKE/LOWER and where not to make query case insensetive. Pg doesn’t
optimize ILIKE or LOWER too if field is numeric.

FWIW, I believe that RT does much better with not trying to “lower” id
or other numeric columns in 3.1. It’s not perfect yet, but it’s better.

Palle_Girgensohn · May 2, 2004, 9:57pm

Main point is that there is a bug where it wants to use ILIKE for
primary key and ID:s, and this is not very clever. Jesse admitted that
this was wrong, but I have no idea how to fix it. It bumps queries
from 10 ms to 30 ms, it is not really a big deal.
This should be fixed in DBIx::SB, but it’s not so easy. DBIx::SB doesn’t
know anything about field types so can’t make decision where use
ILIKE/LOWER and where not to make query case insensetive. Pg doesn’t
optimize ILIKE or LOWER too if field is numeric.

FWIW, I believe that RT does much better with not trying to “lower” id
or other numeric columns in 3.1. It’s not perfect yet, but it’s better.

Cool. Looking forward to the release

Palle

Vivek_Khera1 · May 3, 2004, 6:46pm

I run Apache, and since each process snatched > 70 megs when running
with mmod_perl, 10 apache processes soon ate 900 megs of RAM, the
machine has about 800 MB, so it became very slow. Hence, I switched to
fastcgi and just run one process for now. This was a major speedup.

Well, that’s why we have this really nice long huge extensive and
detailed documentation on mod_perl performance. usually you don’t want
mod_perl talking to your clients directly – you want a proxy in front
of it. i rarely have more than 7 or 9 mod_perl processes while the
front end proxy could be handling upwards of 100 clients (not that this
is on my RT installation, but it could be). Especially true when
you’re serving up images…