Slow Ticket History 3.8.8

Justin_Hayes1 · September 6, 2010, 4:56pm

So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($_->TransactionId == $Transaction->Id ) && ($trans_content->{$_->Id} = $_)  } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes
Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Ruslan_Zakirov1 · September 6, 2010, 5:37pm

Justin.

First of all, I can not read from the chart, but anyway history rendering
has been worked on in a new code branch. Probably this code will be part of
RT 3.10. Code at the moment is unstable, but eventually it wil be faster
then the current version.On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.comwrote:

So far we’ve tried installing RT on different hardware, both 32 and 64bit
versions of linux. RT is still very slow for long tickets. All the time is
taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this
graph of where the time was going:

We then tried to go further into those functions but can’t find a single
smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on
these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id }
@attachments;

grep { ($->TransactionId == $Transaction->Id ) &&
($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried
compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out
lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use
to take up to 20s to load, once I commented out the above lines, load time
is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I
assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying
attachements inline? That might cut back on the I/O for History. Just a
thought.

Kenn
LBNL
On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very
fast. So does look to be DB related - contrary to our earlier
investigations.

I guess it must still access the DB resultset during the ticket rendering
(which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com
On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what
they all mean first.

Justin

-------- General Statistics

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics

[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B,
RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script
(google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply
suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still
can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be
50-100 entries in a ticket, mostly plain text. Loading such a ticket can
take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code
rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory,
ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just
how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long
tickets in their systems? If you look at the page source it gives you a
value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would
be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Best regards, Ruslan.

Justin_Hayes1 · September 6, 2010, 7:49pm

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

Jeff_Fearn · September 6, 2010, 11:44pm

Hi Justin, I’ve recently been using siege to bash on RT, and have been
testing the following two settings in our RT_SiteConfig.pm

Set($UseSQLForACLChecks, 1);
Set($WebExternalAuthContinuous, 0);

The combined effect has been a serious reduction in rendering speed in
general, and particularly so for long tickets.

Cheers, Jeff.

Justin_Hayes1 · September 7, 2010, 9:29am

Thanks Jeffrey. We’re already using UseSQLForACLChecks and don’t use web external auth. Tried the combination you’ve given though and unfortunately no difference

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 00:44, Jeffrey Fearn wrote:

Hi Justin, I’ve recently been using siege to bash on RT, and have been testing the following two settings in our RT_SiteConfig.pm

Set($UseSQLForACLChecks, 1);
Set($WebExternalAuthContinuous, 0);

The combined effect has been a serious reduction in rendering speed in general, and particularly so for long tickets.

Cheers, Jeff.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Justin_Hayes1 · September 7, 2010, 9:30am

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Justin_Hayes1 · September 7, 2010, 9:36am

I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Torsten_Brumm1 · September 7, 2010, 10:13am

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both
WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with
RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder
StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.

I’m sure you will find some funny queries. Normally the Query Log of default
MySQL can only log queries taking longer than a second, but in your case i
think, you will have several much faster queries but in summary they take
longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for
Mysql/Postgresql? / 8 GB Ram
Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O
Problems?)
RT Rights Setup, does the user performance is faster or slower than the
performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and
Webservers with no huge perferomance bottlenacks.

Tob2010/9/7 Justin Hayes justin.hayes@openbet.com

I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit
faster than ours. We’re going to try it on a faster server and that should
drop our times. Guess we just wanted to explore all avenues before throwing
hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that
there are others with similar hardware who don’t get the problem. It’ll be
great if 3.10 fixes it for me, but I’d love to get to the bottom of it
first. I’m pretty much positive it’s not a DB issue, as I’ve tried different
sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve
tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly
been helping me with details of his own setup) appears to have none of the
same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu
we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering
has been worked on in a new code branch. Probably this code will be part of
RT 3.10. Code at the moment is unstable, but eventually it wil be faster
then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.comwrote:

So far we’ve tried installing RT on different hardware, both 32 and 64bit
versions of linux. RT is still very slow for long tickets. All the time is
taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this
graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single
smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on
these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id }
@attachments;

grep { ($->TransactionId == $Transaction->Id ) &&
($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried
compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out
lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets
use to take up to 20s to load, once I commented out the above lines, load
time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I
assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying
attachements inline? That might cut back on the I/O for History. Just a
thought.

Kenn
LBNL
On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very
fast. So does look to be DB related - contrary to our earlier
investigations.

I guess it must still access the DB resultset during the ticket rendering
(which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com
On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what
they all mean first.

Justin

-------- General Statistics

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics

[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B,
RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script
(google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply
suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still
can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be
50-100 entries in a ticket, mostly plain text. Loading such a ticket can
take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code
rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory,
ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just
how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long
tickets in their systems? If you look at the page source it gives you a
value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would
be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com http://rtbook.bestpracticalcom/

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Justin_Hayes1 · September 7, 2010, 10:29am

Thanks Torsten. Will have a look at some of those suggestions. We’d ruled the DB out as we couldn’t find an slow ones in the logs, and also because we tried a totally fresh DB with only 1 ticket in it, and it was still slow. But it might just be lots of tiny queries so we’ll have another look at that.

We’ve also tried running the web services on a separate server to the DB. This also made no difference.

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Torsten_Brumm1 · September 7, 2010, 10:36am

Are you running mod_perl2, FastCGI or FCGID?2010/9/7 Justin Hayes justin.hayes@openbet.com

Thanks Torsten. Will have a look at some of those suggestions. We’d ruled
the DB out as we couldn’t find an slow ones in the logs, and also because we
tried a totally fresh DB with only 1 ticket in it, and it was still slow.
But it might just be lots of tiny queries so we’ll have another look at
that.

We’ve also tried running the web services on a separate server to the DB.
This also made no difference.

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both
WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated
with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder
StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.

I’m sure you will find some funny queries. Normally the Query Log of
default MySQL can only log queries taking longer than a second, but in your
case i think, you will have several much faster queries but in summary they
take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for
Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O
Problems?)

RT Rights Setup, does the user performance is faster or slower than the
performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and
Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com

I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit
faster than ours. We’re going to try it on a faster server and that should
drop our times. Guess we just wanted to explore all avenues before throwing
hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that
there are others with similar hardware who don’t get the problem. It’ll be
great if 3.10 fixes it for me, but I’d love to get to the bottom of it
first. I’m pretty much positive it’s not a DB issue, as I’ve tried different
sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve
tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly
been helping me with details of his own setup) appears to have none of the
same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu
we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering
has been worked on in a new code branch. Probably this code will be part of
RT 3.10. Code at the moment is unstable, but eventually it wil be faster
then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.comwrote:

So far we’ve tried installing RT on different hardware, both 32 and 64bit
versions of linux. RT is still very slow for long tickets. All the time is
taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with
this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single
smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on
these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id }
@attachments;

grep { ($->TransactionId == $Transaction->Id ) &&
($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried
compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out
lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets
use to take up to 20s to load, once I commented out the above lines, load
time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I
assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you
displaying attachements inline? That might cut back on the I/O for History.
Just a thought.

Kenn
LBNL
On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s
very fast. So does look to be DB related - contrary to our earlier
investigations.

I guess it must still access the DB resultset during the ticket rendering
(which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com
On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what
they all mean first.

Justin

-------- General Statistics

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics

[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B,
RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection
usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script
(google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply
suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still
can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be
50-100 entries in a ticket, mostly plain text. Loading such a ticket can
take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code
rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory,
ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just
how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long
tickets in their systems? If you look at the page source it gives you a
value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It
would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com http://rtbook.bestpracticalcom/

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Justin_Hayes1 · September 7, 2010, 10:58am

Well we’ve captured the time for all the queries run for our long ticket (which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Torsten_Brumm1 · September 7, 2010, 11:05am

OK, now we know, it is not the DB, have you used firebug to check which part
of the page takes long?
There is a nice addon from BPS to track the page memory used for each
request inside github, possible a good point to look next.

The time you messured are taken with firefox, Internet explorer?2010/9/7 Justin Hayes justin.hayes@openbet.com

Well we’ve captured the time for all the queries run for our long ticket
(which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both
WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated
with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder
StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.

I’m sure you will find some funny queries. Normally the Query Log of
default MySQL can only log queries taking longer than a second, but in your
case i think, you will have several much faster queries but in summary they
take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for
Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O
Problems?)

RT Rights Setup, does the user performance is faster or slower than the
performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and
Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com

I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit
faster than ours. We’re going to try it on a faster server and that should
drop our times. Guess we just wanted to explore all avenues before throwing
hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that
there are others with similar hardware who don’t get the problem. It’ll be
great if 3.10 fixes it for me, but I’d love to get to the bottom of it
first. I’m pretty much positive it’s not a DB issue, as I’ve tried different
sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve
tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly
been helping me with details of his own setup) appears to have none of the
same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu
we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering
has been worked on in a new code branch. Probably this code will be part of
RT 3.10. Code at the moment is unstable, but eventually it wil be faster
then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.comwrote:

So far we’ve tried installing RT on different hardware, both 32 and 64bit
versions of linux. RT is still very slow for long tickets. All the time is
taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with
this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single
smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on
these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id }
@attachments;

grep { ($->TransactionId == $Transaction->Id ) &&
($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried
compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out
lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets
use to take up to 20s to load, once I commented out the above lines, load
time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I
assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you
displaying attachements inline? That might cut back on the I/O for History.
Just a thought.

Kenn
LBNL
On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s
very fast. So does look to be DB related - contrary to our earlier
investigations.

I guess it must still access the DB resultset during the ticket rendering
(which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com
On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what
they all mean first.

Justin

-------- General Statistics

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics

[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B,
RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection
usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script
(google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply
suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still
can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be
50-100 entries in a ticket, mostly plain text. Loading such a ticket can
take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code
rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory,
ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just
how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long
tickets in their systems? If you look at the page source it gives you a
value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It
would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com http://rtbook.bestpracticalcom/

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Torsten_Brumm1 · September 7, 2010, 11:45am

Hi Justin,
just created inside a RT Test VM (slow one with 500mb ram) a single ticket
with around 60 replies and some comments. Tested the speed with different
users

root user to open this ticket: around 26 sec → 870 single sql queries in
around 4 sec! (Queries: SQL Log - Pastebin.com)
user with full access (take, own, modify etc): around same time and
queries like root (Queries: SQL Log user with full access - Pastebin.com)
user with less rights (no take, no own, only showticket, seequeue): time
around 15 sec and 600 sql queries in around 2 sec! (Queries:
SQL Log user with less rights and no comments - Pastebin.com)

After this the apache starts to render the page from the results and push
them to the browser. The page is for my few comments/replies already 206KB
without any apache optimizations

After adding:

    SetOutputFilter DEFLATE
    SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$  no-gzip dont-vary
    SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary
    ExpiresActive On
    ExpiresByType text/css "A604800"
    ExpiresByType image/x-icon "A31536000"
    ExpiresByType image/gif "A604800"
    ExpiresByType image/jpg "A604800"
    ExpiresByType image/jpeg "A604800"
    ExpiresByType image/png "A604800"
    ExpiresByType application/x-javascript A3600
    Header set Cache-Control "must-revalidate"

to the rt vhost, the page load time goes down from 26 sec to 8 sec and from
206 kb to 10kb

you should try.

Torsten2010/9/7 Justin Hayes justin.hayes@openbet.com

Well we’ve captured the time for all the queries run for our long ticket
(which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both
WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated
with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder
StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.

I’m sure you will find some funny queries. Normally the Query Log of
default MySQL can only log queries taking longer than a second, but in your
case i think, you will have several much faster queries but in summary they
take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for
Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O
Problems?)

RT Rights Setup, does the user performance is faster or slower than the
performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and
Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com

I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit
faster than ours. We’re going to try it on a faster server and that should
drop our times. Guess we just wanted to explore all avenues before throwing
hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that
there are others with similar hardware who don’t get the problem. It’ll be
great if 3.10 fixes it for me, but I’d love to get to the bottom of it
first. I’m pretty much positive it’s not a DB issue, as I’ve tried different
sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve
tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly
been helping me with details of his own setup) appears to have none of the
same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu
we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering
has been worked on in a new code branch. Probably this code will be part of
RT 3.10. Code at the moment is unstable, but eventually it wil be faster
then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.comwrote:

So far we’ve tried installing RT on different hardware, both 32 and 64bit
versions of linux. RT is still very slow for long tickets. All the time is
taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with
this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single
smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on
these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id }
@attachments;

grep { ($->TransactionId == $Transaction->Id ) &&
($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried
compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out
lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets
use to take up to 20s to load, once I commented out the above lines, load
time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I
assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you
displaying attachements inline? That might cut back on the I/O for History.
Just a thought.

Kenn
LBNL
On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s
very fast. So does look to be DB related - contrary to our earlier
investigations.

I guess it must still access the DB resultset during the ticket rendering
(which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com
On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what
they all mean first.

Justin

-------- General Statistics

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics

[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B,
RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection
usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script
(google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply
suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still
can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be
50-100 entries in a ticket, mostly plain text. Loading such a ticket can
take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code
rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory,
ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just
how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long
tickets in their systems? If you look at the page source it gives you a
value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It
would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com http://rtbook.bestpracticalcom/

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Justin_Hayes1 · September 9, 2010, 7:08am

We’ve tried all of those. We’ve also tried the RT inbuilt webserver and results were the same in all cases.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 11:36, Torsten Brumm wrote:

Are you running mod_perl2, FastCGI or FCGID?

2010/9/7 Justin Hayes justin.hayes@openbet.com
Thanks Torsten. Will have a look at some of those suggestions. We’d ruled the DB out as we couldn’t find an slow ones in the logs, and also because we tried a totally fresh DB with only 1 ticket in it, and it was still slow. But it might just be lots of tiny queries so we’ll have another look at that.

We’ve also tried running the web services on a separate server to the DB. This also made no difference.

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Justin_Hayes1 · September 9, 2010, 7:10am

Thanks Torsten. We’ll have a look at those settings as well.

Appreciate the help!

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 12:45, Torsten Brumm wrote:

Hi Justin,
just created inside a RT Test VM (slow one with 500mb ram) a single ticket with around 60 replies and some comments. Tested the speed with different users

root user to open this ticket: around 26 sec → 870 single sql queries in around 4 sec! (Queries: SQL Log - Pastebin.com)

user with full access (take, own, modify etc): around same time and queries like root (Queries: SQL Log user with full access - Pastebin.com)

user with less rights (no take, no own, only showticket, seequeue): time around 15 sec and 600 sql queries in around 2 sec! (Queries: SQL Log user with less rights and no comments - Pastebin.com)

After this the apache starts to render the page from the results and push them to the browser. The page is for my few comments/replies already 206KB without any apache optimizations

After adding:
    SetOutputFilter DEFLATE
    SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$  no-gzip dont-vary
    SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary
    ExpiresActive On
    ExpiresByType text/css "A604800"

    ExpiresByType image/x-icon "A31536000"
    ExpiresByType image/gif "A604800"
    ExpiresByType image/jpg "A604800"
    ExpiresByType image/jpeg "A604800"

    ExpiresByType image/png "A604800"
    ExpiresByType application/x-javascript A3600
    Header set Cache-Control "must-revalidate"
to the rt vhost, the page load time goes down from 26 sec to 8 sec and from 206 kb to 10kb

you should try.

Torsten

2010/9/7 Justin Hayes justin.hayes@openbet.com
Well we’ve captured the time for all the queries run for our long ticket (which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Justin_Hayes1 · September 9, 2010, 7:13am

Aren’t those options just compressing the page to send out to the browser and caching the output?

We’re on an internal gigabit network so seems unlikely that would help. All our time goes on the server actually building the page to send out I think.

Can try it though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 7 Sep 2010, at 12:45, Torsten Brumm wrote:

Hi Justin,
just created inside a RT Test VM (slow one with 500mb ram) a single ticket with around 60 replies and some comments. Tested the speed with different users

root user to open this ticket: around 26 sec → 870 single sql queries in around 4 sec! (Queries: SQL Log - Pastebin.com)

user with full access (take, own, modify etc): around same time and queries like root (Queries: SQL Log user with full access - Pastebin.com)

user with less rights (no take, no own, only showticket, seequeue): time around 15 sec and 600 sql queries in around 2 sec! (Queries: SQL Log user with less rights and no comments - Pastebin.com)

After this the apache starts to render the page from the results and push them to the browser. The page is for my few comments/replies already 206KB without any apache optimizations

After adding:
    SetOutputFilter DEFLATE
    SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$  no-gzip dont-vary
    SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary
    ExpiresActive On
    ExpiresByType text/css "A604800"

    ExpiresByType image/x-icon "A31536000"
    ExpiresByType image/gif "A604800"
    ExpiresByType image/jpg "A604800"
    ExpiresByType image/jpeg "A604800"

    ExpiresByType image/png "A604800"
    ExpiresByType application/x-javascript A3600
    Header set Cache-Control "must-revalidate"
to the rt vhost, the page load time goes down from 26 sec to 8 sec and from 206 kb to 10kb

you should try.

Torsten

2010/9/7 Justin Hayes justin.hayes@openbet.com
Well we’ve captured the time for all the queries run for our long ticket (which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

Justin_Hayes1 · September 9, 2010, 10:11am

We’ve just tried a brand new webserver.

Intel(R) Xeon(R) CPU E5640 @ 2.67GHz Quad (with hyperthreading)
12GB RAM

Load time has dropped from 20-30s down to 11s. So throwing some newer hardware at it definitely improves things. Hopefully this will tide us over until RT3.10 which has the history rewrite in it.

Going to try the patches from that other slow ticket history thread as well to see if that brings it down further.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 9 Sep 2010, at 08:13, Justin Hayes wrote:

Aren’t those options just compressing the page to send out to the browser and caching the output?

We’re on an internal gigabit network so seems unlikely that would help. All our time goes on the server actually building the page to send out I think.

Can try it though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 12:45, Torsten Brumm wrote:
Hi Justin,
just created inside a RT Test VM (slow one with 500mb ram) a single ticket with around 60 replies and some comments. Tested the speed with different users

root user to open this ticket: around 26 sec → 870 single sql queries in around 4 sec! (Queries: SQL Log - Pastebin.com)

user with full access (take, own, modify etc): around same time and queries like root (Queries: SQL Log user with full access - Pastebin.com)

user with less rights (no take, no own, only showticket, seequeue): time around 15 sec and 600 sql queries in around 2 sec! (Queries: SQL Log user with less rights and no comments - Pastebin.com)

After this the apache starts to render the page from the results and push them to the browser. The page is for my few comments/replies already 206KB without any apache optimizations

After adding:
    SetOutputFilter DEFLATE
    SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$  no-gzip dont-vary
    SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary
    ExpiresActive On
    ExpiresByType text/css "A604800"

    ExpiresByType image/x-icon "A31536000"
    ExpiresByType image/gif "A604800"
    ExpiresByType image/jpg "A604800"
    ExpiresByType image/jpeg "A604800"

    ExpiresByType image/png "A604800"
    ExpiresByType application/x-javascript A3600
    Header set Cache-Control "must-revalidate"
to the rt vhost, the page load time goes down from 26 sec to 8 sec and from 206 kb to 10kb

you should try.

Torsten

2010/9/7 Justin Hayes justin.hayes@openbet.com
Well we’ve captured the time for all the queries run for our long ticket (which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n’t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de
RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Kenneth_Marshall · September 9, 2010, 12:50pm

One big win with enabling compression was that pages loaded in bigger
pieces and you have less problems with users trying to type in an
page that is unfinished with unpredictable results. With the DEFLATE
on, the page all decompresses on the fast client instead of dribbling
out from the server.

Cheers,
KenOn Thu, Sep 09, 2010 at 08:13:28AM +0100, Justin Hayes wrote:

Aren’t those options just compressing the page to send out to the browser and caching the output?

We’re on an internal gigabit network so seems unlikely that would help. All our time goes on the server actually building the page to send out I think.

Can try it though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 12:45, Torsten Brumm wrote:
Hi Justin,
just created inside a RT Test VM (slow one with 500mb ram) a single ticket with around 60 replies and some comments. Tested the speed with different users

root user to open this ticket: around 26 sec → 870 single sql queries in around 4 sec! (Queries: SQL Log - Pastebin.com)

user with full access (take, own, modify etc): around same time and queries like root (Queries: SQL Log user with full access - Pastebin.com)

user with less rights (no take, no own, only showticket, seequeue): time around 15 sec and 600 sql queries in around 2 sec! (Queries: SQL Log user with less rights and no comments - Pastebin.com)

After this the apache starts to render the page from the results and push them to the browser. The page is for my few comments/replies already 206KB without any apache optimizations

After adding:
    SetOutputFilter DEFLATE
    SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$  no-gzip dont-vary
    SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary
    ExpiresActive On
    ExpiresByType text/css "A604800"

    ExpiresByType image/x-icon "A31536000"
    ExpiresByType image/gif "A604800"
    ExpiresByType image/jpg "A604800"
    ExpiresByType image/jpeg "A604800"

    ExpiresByType image/png "A604800"
    ExpiresByType application/x-javascript A3600
    Header set Cache-Control "must-revalidate"
to the rt vhost, the page load time goes down from 26 sec to 8 sec and from 206 kb to 10kb

you should try.

Torsten

2010/9/7 Justin Hayes justin.hayes@openbet.com
Well we’ve captured the time for all the queries run for our long ticket (which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,?debug?); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n?t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Justin_Hayes1 · September 9, 2010, 2:18pm

I actually liked the incremental page load, as I could read the start of the ticket while the rest was loading, thus saving me a bit of time

However this seems to have stopped since installing 3.8.8…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 9 Sep 2010, at 13:50, Kenneth Marshall wrote:

One big win with enabling compression was that pages loaded in bigger
pieces and you have less problems with users trying to type in an
page that is unfinished with unpredictable results. With the DEFLATE
on, the page all decompresses on the fast client instead of dribbling
out from the server.

Cheers,
Ken

On Thu, Sep 09, 2010 at 08:13:28AM +0100, Justin Hayes wrote:
Aren’t those options just compressing the page to send out to the browser and caching the output?

We’re on an internal gigabit network so seems unlikely that would help. All our time goes on the server actually building the page to send out I think.

Can try it though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 12:45, Torsten Brumm wrote:
Hi Justin,
just created inside a RT Test VM (slow one with 500mb ram) a single ticket with around 60 replies and some comments. Tested the speed with different users

root user to open this ticket: around 26 sec → 870 single sql queries in around 4 sec! (Queries: SQL Log - Pastebin.com)

user with full access (take, own, modify etc): around same time and queries like root (Queries: SQL Log user with full access - Pastebin.com)

user with less rights (no take, no own, only showticket, seequeue): time around 15 sec and 600 sql queries in around 2 sec! (Queries: SQL Log user with less rights and no comments - Pastebin.com)

After this the apache starts to render the page from the results and push them to the browser. The page is for my few comments/replies already 206KB without any apache optimizations

After adding:
   SetOutputFilter DEFLATE
   SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$  no-gzip dont-vary
   SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary
   ExpiresActive On
   ExpiresByType text/css "A604800"

   ExpiresByType image/x-icon "A31536000"
   ExpiresByType image/gif "A604800"
   ExpiresByType image/jpg "A604800"
   ExpiresByType image/jpeg "A604800"

   ExpiresByType image/png "A604800"
   ExpiresByType application/x-javascript A3600
   Header set Cache-Control "must-revalidate"
to the rt vhost, the page load time goes down from 26 sec to 8 sec and from 206 kb to 10kb

you should try.

Torsten

2010/9/7 Justin Hayes justin.hayes@openbet.com
Well we’ve captured the time for all the queries run for our long ticket (which takes ~20secs to generate).

Total query time is 0.871493s

So it’s not the DB.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 11:13, Torsten Brumm wrote:

Hi Justin,
just found this threat, sounds interessting.

What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?

Think you should follow Raed’s hints first to log the queries generated with RT

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,?debug?); in your etc/RT_SiteConfig.
I’m sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can’t find in mysql-slow log.

Some more question regarding your hardware and setup.

One Server / quad core (hyper threating) → how many threats for Mysql/Postgresql? / 8 GB Ram

Hard Disk Setup? (logfiles and db storred on different HDD’s? Any I/O Problems?)

RT Rights Setup, does the user performance is faster or slower than the performance with root user?

Some more information?

We’re running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks.

Tob

2010/9/7 Justin Hayes justin.hayes@openbet.com
I think we’re just CPU bound. Roy’s webservers are 3.6ghz so quite a bit faster than ours. We’re going to try it on a faster server and that should drop our times. Guess we just wanted to explore all avenues before throwing hardware at the problem.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 7 Sep 2010, at 10:30, Justin Hayes wrote:

Tried Centos last night, and no difference at all.

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 20:49, Justin Hayes wrote:

Hi Ruslan,

Sorry looks like I shrunk the image too much. The thing I find odd is that there are others with similar hardware who don’t get the problem. It’ll be great if 3.10 fixes it for me, but I’d love to get to the bottom of it first. I’m pretty much positive it’s not a DB issue, as I’ve tried different sizes of DB, tried postgres AND mysql etc. I don’t think it’s apache as I’ve tried the built in webserver with RT and no change there either.

Currently trying to install RT on Centos given that Roy (who has kindly been helping me with details of his own setup) appears to have none of the same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu we’ve currently got live.

No idea if it’s going to have any effect though

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote:

Justin.

First of all, I can not read from the chart, but anyway history rendering has been worked on in a new code branch. Probably this code will be part of RT 3.10. Code at the moment is unstable, but eventually it wil be faster then the current version.

On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes justin.hayes@openbet.com wrote:
So far we’ve tried installing RT on different hardware, both 32 and 64bit versions of linux. RT is still very slow for long tickets. All the time is taken up by the perl/apache process maxing out a core of CPU.

We’ve even gone as far as trying to profile the code. We came up with this graph of where the time was going:

<TIMING.png>
We then tried to go further into those functions but can’t find a single smoking gun call that is taking all the time.

For example in a ticket that takes 22s to render approx 5 secs goes on these 2 lines:

File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8

my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } @attachments;

grep { ($->TransactionId == $Transaction->Id ) && ($trans_content->{$->Id} = $_) } @attachment_content;

Both are greps. Does this imply that perl itself is just slow?

IF so why would our perl be slow compared to other people’s? We’ve tried compiling it from source and that made no difference.

ATM we’re at a bit of a loss…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 1 Jul 2010, at 11:51, Raed El-Hames wrote:

Justin,

Do you use Transaction custom fields, if you do n?t ; try and comment out lines 70,71,72 from html/Ticket/Elements/ShowTransaction
% if ( $Transaction->CustomFieldValues->Count ) {
<& /Elements/ShowCustomFields, Object => $Transaction &>
% }
See if that improves things for you.
Some of our monitoring tickets can have up to 500 updates, such tickets use to take up to 20s to load, once I commented out the above lines, load time is now down to less than 5 seconds.

Regards;
Roy

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Justin Hayes

Sent: 01 July 2010 11:39
To: Kenneth Crocker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Slow Ticket History 3.8.8

We do Kenneth, but most tickets don’t have many file attachments, so I assume that’s not an issue?

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 17:54, Kenneth Crocker wrote:

Justin,

I didn’t see this mentioned and may have missed it, but are you displaying attachements inline? That might cut back on the I/O for History. Just a thought.

Kenn
LBNL

On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.com wrote:
As a test we’ve just created a long ticket in an empty RT DB and it’s very fast. So does look to be DB related - contrary to our earlier investigations.

I guess it must still access the DB resultset during the ticket rendering (which isn’t how we thought it would work).

Time to tune the hell out of mysql then…

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:53, Justin Hayes wrote:

Seem to be quite a few things to look at Jason. Need to figure out what they all mean first.

Justin

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 611M (Tables: 8)
[–] Data in InnoDB tables: 10G (Tables: 20)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: 39B)
[–] Reads / Writes: 98% / 2%
[–] Total buffers: 602.0M global + 134.8M per thread (150 max threads)
[!!] Maximum possible memory usage: 20.3G (262% of installed RAM)
[OK] Slow queries: 0% (229K/110M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M
[OK] Key buffer hit rate: 100.0% (84M cached / 7K reads)
[OK] Query cache efficiency: 71.4% (76M cached / 107M selects)
[!!] Query cache prunes per day: 661360
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 112714
[!!] Temporary tables created on disk: 33% (968K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 222K connections)
[OK] Table cache hit rate: 36% (318 open / 880 opened)
[OK] Open file limit used: 14% (166/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
[!!] InnoDB data size / buffer pool: 10.1G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 64M)
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

On 29 Jun 2010, at 15:22, Jason Doran wrote:

Hi,
If you are using mysqld have a look at “mysqltuner.pl” perl script (google)
This has fixed quickly many performance issues on both RT and other
web-based software we use. I run this every few weeks and apply suggested
changes and then simply restart mysqld when things are quite.

Regards,
Jason Doran
Computer Centre
NUI, Maynooth

On 29 Jun 2010, at 14:09, Justin Hayes wrote:

Hi everyone,

I’ve raised this before, but we’ve had another look at it and still can’t see how to improve things.

We put a lot of comments/replies in our tickets. Often there can be 50-100 entries in a ticket, mostly plain text. Loading such a ticket can take 10-20secs.

We don’t have any slow queries - all the time seems to be in the code rendering the history of the ticket.
We’ve had a go at stripping functions out of ShowHistory, ShowTransaction and ShowTransactionAttachmments but not had much success.

FWIW our RT runs on quad 3ghz Xeons with 8gb of ram.

I’d like to try and determine if we’re just slow, or if this is just how long RT takes. Maybe perl is just slow.

Can anyone shed any light on how long it takes them to render long tickets in their systems? If you look at the page source it gives you a value e.g.

Time to display: 24.996907

Can anyone share some numbers from theirs for longer tickets? It would be really appreciated.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
Best regards, Ruslan.

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de

–
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de
RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

Raed_El-Hames2 · September 9, 2010, 3:19pm

Justin;

You must have implemented the DEFLATE
The deflate stops the incremental load not RT … defalte works by compressing the page and sending the compressed page which the browser then decompress.

Roy