Slow Ticket History 3.8.8

Justin_Hayes1 · June 29, 2010, 1:09pm

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

Charles_Johnson · June 29, 2010, 2:14pm

Quite outside of the speed of RT, what database are you using as a
back-end? How long has it been since you did some care and feeding of
your database? How big are your tables? If you are using mysql or
postgres have you used their tools to examine your database,
especially the indexes and statistics? This may be an issue with
indexes needing to be rebuilt, or asking the database to gather new
statistics for the queries your are running.

Cheers–

CharlesOn Jun 29, 2010, at 8:09 AM, 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

Charles Johnson, Vanderbilt University
Advanced Computing Center for Research & Education
Mailing Address: Peabody #34, 230 Appleton Place, Nashville, TN 37203
Shipping Address: 1231 18th Avenue South, Hill Center, Suite 143,
Nashville, TN 37212
Office: 615-343-4134
Cell: 615-478-5743
Fax: 615-343-7216
charles.johnson@accre.vanderbilt.edu

Justin_Hayes1 · June 29, 2010, 2:18pm

Hi Charles,

But as far as we can tell from our profiling the time is spent in the perl rendering the ticket, not in the query used to retrieve the ticket and attachments in the first place (which happens once at the start I believe). The loop that renders the ticket history and each attachment takes anything up to 20+ seconds.

So I’m not sure how the DB can be an issue here? Unless I’m wrong about it doing 1 big query at the start and there are loads of little queries inside the loop.

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 29 Jun 2010, at 15:14, Charles Johnson wrote:

Quite outside of the speed of RT, what database are you using as a back-end? How long has it been since you did some care and feeding of your database? How big are your tables? If you are using mysql or postgres have you used their tools to examine your database, especially the indexes and statistics? This may be an issue with indexes needing to be rebuilt, or asking the database to gather new statistics for the queries your are running.

Cheers–

Charles

On Jun 29, 2010, at 8:09 AM, 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

–
Charles Johnson, Vanderbilt University
Advanced Computing Center for Research & Education
Mailing Address: Peabody #34, 230 Appleton Place, Nashville, TN 37203
Shipping Address: 1231 18th Avenue South, Hill Center, Suite 143, Nashville, TN 37212
Office: 615-343-4134
Cell: 615-478-5743
Fax: 615-343-7216
charles.johnson@accre.vanderbilt.edu

Jason_Doran · June 29, 2010, 2:22pm

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, MaynoothOn 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

Charles_Johnson · June 29, 2010, 2:28pm

Justin, thank you for the kindness of your reply. I didn’t intend to
send you chasing rabbits, so to speak. Just offering something to add
to the checklist of items to be crossed off before digging too deeply
into the perl code. If you are convinced that mysql is performing at
its best then please ignore my noise!

Cheers–

CharlesOn Jun 29, 2010, at 9:18 AM, Justin Hayes wrote:

Hi Charles,

But as far as we can tell from our profiling the time is spent in
the perl rendering the ticket, not in the query used to retrieve the
ticket and attachments in the first place (which happens once at the
start I believe). The loop that renders the ticket history and each
attachment takes anything up to 20+ seconds.

So I’m not sure how the DB can be an issue here? Unless I’m wrong
about it doing 1 big query at the start and there are loads of
little queries inside the loop.

Cheers,

Justin

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

On 29 Jun 2010, at 15:14, Charles Johnson wrote:

Quite outside of the speed of RT, what database are you using as a
back-end? How long has it been since you did some care and feeding
of your database? How big are your tables? If you are using mysql
or postgres have you used their tools to examine your database,
especially the indexes and statistics? This may be an issue with
indexes needing to be rebuilt, or asking the database to gather new
statistics for the queries your are running.

Cheers–

Charles

On Jun 29, 2010, at 8:09 AM, 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

–
Charles Johnson, Vanderbilt University
Advanced Computing Center for Research & Education
Mailing Address: Peabody #34, 230 Appleton Place, Nashville, TN
37203
Shipping Address: 1231 18th Avenue South, Hill Center, Suite 143,
Nashville, TN 37212
Office: 615-343-4134
Cell: 615-478-5743
Fax: 615-343-7216
charles.johnson@accre.vanderbilt.edu

Charles Johnson, Vanderbilt University
Advanced Computing Center for Research & Education
Mailing Address: Peabody #34, 230 Appleton Place, Nashville, TN 37203
Shipping Address: 1231 18th Avenue South, Hill Center, Suite 143,
Nashville, TN 37212
Office: 615-343-4134
Cell: 615-478-5743
Fax: 615-343-7216

Justin_Hayes1 · June 29, 2010, 2:28pm

Thanks Jason - I’ll give that a go as well. We have got a large DB (~10gb) so keeping it tuned is definitely important.

However as stated the time seems to be lost in code, after I’d have thought it had run the query for the ticket (though I may be wrong in that assumption).

Cheers,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 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

Justin_Hayes1 · June 29, 2010, 2:52pm

Hehe thanks Charles.

I think you might have a point anyway. Running that script recommended by Jason has thrown up a few things that need tweaking with mysql.

Not sure what some of them mean yet, and not sure if it’ll help, but we’ll see.

I appreciate you helping believe me!

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 29 Jun 2010, at 15:28, Charles Johnson wrote:

Justin, thank you for the kindness of your reply. I didn’t intend to send you chasing rabbits, so to speak. Just offering something to add to the checklist of items to be crossed off before digging too deeply into the perl code. If you are convinced that mysql is performing at its best then please ignore my noise!

Cheers–

Charles
On Jun 29, 2010, at 9:18 AM, Justin Hayes wrote:

Hi Charles,

But as far as we can tell from our profiling the time is spent in the perl rendering the ticket, not in the query used to retrieve the ticket and attachments in the first place (which happens once at the start I believe). The loop that renders the ticket history and each attachment takes anything up to 20+ seconds.

So I’m not sure how the DB can be an issue here? Unless I’m wrong about it doing 1 big query at the start and there are loads of little queries inside the loop.

Cheers,

Justin

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

On 29 Jun 2010, at 15:14, Charles Johnson wrote:

Quite outside of the speed of RT, what database are you using as a back-end? How long has it been since you did some care and feeding of your database? How big are your tables? If you are using mysql or postgres have you used their tools to examine your database, especially the indexes and statistics? This may be an issue with indexes needing to be rebuilt, or asking the database to gather new statistics for the queries your are running.

Cheers–

Charles

On Jun 29, 2010, at 8:09 AM, 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

–
Charles Johnson, Vanderbilt University
Advanced Computing Center for Research & Education
Mailing Address: Peabody #34, 230 Appleton Place, Nashville, TN 37203
Shipping Address: 1231 18th Avenue South, Hill Center, Suite 143, Nashville, TN 37212
Office: 615-343-4134
Cell: 615-478-5743
Fax: 615-343-7216
charles.johnson@accre.vanderbilt.edu

–
Charles Johnson, Vanderbilt University
Advanced Computing Center for Research & Education
Mailing Address: Peabody #34, 230 Appleton Place, Nashville, TN 37203
Shipping Address: 1231 18th Avenue South, Hill Center, Suite 143, Nashville, TN 37212
Office: 615-343-4134
Cell: 615-478-5743
Fax: 615-343-7216

Foggi_Nicola · June 29, 2010, 2:49pm

I did just have a problem with 3.8.8 and one of the indexes (didn’t have the problem on 3.8.6 or below). It turned out to be the index GROUPS2 on the Groups table… dropped the index, and performance was back to normal (pre 3.8.8)

NicolaFrom: rt-users-bounces@lists.bestpractical.com on behalf of Justin Hayes
Sent: Tue 6/29/2010 9:28 AM
To: Jason Doran
Cc: rt Users
Subject: Re: [rt-users] Slow Ticket History 3.8.8

Thanks Jason - I’ll give that a go as well. We have got a large DB (~10gb) so keeping it tuned is definitely important.

However as stated the time seems to be lost in code, after I’d have thought it had run the query for the ticket (though I may be wrong in that assumption).

Cheers,

Justin

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

Justin_Hayes1 · June 29, 2010, 2:53pm

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.comOn 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

Justin_Hayes1 · June 29, 2010, 2:59pm

Hi Nicola,

I’ve just tried upgrading from 3.8.4 which I’ve been on for ages, on the hope that it would be better, but isn’t.

So I don’t think it’s something that has either been introduced or fixed by 3.8.8. I’ll take a look at that index though.

Thanks,

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 29 Jun 2010, at 15:49, Foggi, Nicola wrote:

I did just have a problem with 3.8.8 and one of the indexes (didn’t have the problem on 3.8.6 or below). It turned out to be the index GROUPS2 on the Groups table… dropped the index, and performance was back to normal (pre 3.8.8)

Nicola

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com on behalf of Justin Hayes
Sent: Tue 6/29/2010 9:28 AM
To: Jason Doran
Cc: rt Users
Subject: Re: [rt-users] Slow Ticket History 3.8.8

Thanks Jason - I’ll give that a go as well. We have got a large DB (~10gb) so keeping it tuned is definitely important.

However as stated the time seems to be lost in code, after I’d have thought it had run the query for the ticket (though I may be wrong in that assumption).

Cheers,

Justin

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

Justin_Hayes1 · June 29, 2010, 3:04pm

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.comOn 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

Kenn_Crocker · June 29, 2010, 4:54pm

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
LBNLOn Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes justin.hayes@openbet.comwrote:

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

Kim_Pedersen · June 30, 2010, 7:55am

Hi Justin,

Our RT instance is much smaller than yours, but we had very similar
problems.

Optimizing MySQL using the output from the tuner script made nearly all
the speed issues go away.

The one remaining issue we had with slow queries was resolved after the
upgrade to 3.8.8

Your key buffer size looks like overkill - probably want to reduce that,
and you have many joins done without indexes - that’s a performance killer.

Your InnoDB memory allocation looks like it needs to be increasing.

Overall I’d say your database performance is skewed towards ISAM
performance and not InnoDB (Which is what RT3 uses by default)

KimOn 2010-06-29 10: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_Hayes1 · July 1, 2010, 10:39am

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.comOn 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

Justin_Hayes1 · July 1, 2010, 10:42am

Thanks a lot for this response Kim.

r.e. the joins without indexes. How can we work out which indexes we need? I’d have thought this would cause slow queries but we’re not seeing any…

We’re trying to tune on our test server before making any changes to live, however the hardware etc is different so not sure it it’s going to be that useful an excercise.

This is what we’re not getting on test - we still get long ticket load times and we’ve done a lot of the recommended tuning:

[OK] Logged in using credentials passed on the command line

-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.75-0ubuntu10.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 131M (Tables: 15)
[–] Data in InnoDB tables: 10G (Tables: 81)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[–] Up for: 3m 44s (9K q [41.304 qps], 73 conn, TX: 17M, RX: 2M)
[–] Reads / Writes: 99% / 1%
[–] Total buffers: 11.0G global + 3.5M per thread (150 max threads)
[!!] Maximum possible memory usage: 11.5G (149% of installed RAM)
[OK] Slow queries: 0% (20/9K)
[OK] Highest usage of available connections: 9% (14/150)
[OK] Key buffer size / total MyISAM indexes: 3.0G/4.6M
[OK] Key buffer hit rate: 97.8% (453 cached / 10 reads)
[OK] Query cache efficiency: 89.0% (8K cached / 9K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 22 sorts)
[OK] Temporary tables created on disk: 14% (28 on disk / 195 total)
[OK] Thread cache hit rate: 80% (14 created / 73 connections)
[OK] Table cache hit rate: 95% (121 open / 127 opened)
[OK] Open file limit used: 7% (80/1K)
[OK] Table locks acquired immediately: 100% (773 immediate / 773 locks)
[!!] InnoDB data size / buffer pool: 10.7G/6.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
innodb_buffer_pool_size (>= 10G)

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 30 Jun 2010, at 08:55, Kim Pedersen wrote:

Hi Justin,

Our RT instance is much smaller than yours, but we had very similar problems.

Optimizing MySQL using the output from the tuner script made nearly all the speed issues go away.

The one remaining issue we had with slow queries was resolved after the upgrade to 3.8.8

Your key buffer size looks like overkill - probably want to reduce that, and you have many joins done without indexes - that’s a performance killer.

Your InnoDB memory allocation looks like it needs to be increasing.

Overall I’d say your database performance is skewed towards ISAM performance and not InnoDB (Which is what RT3 uses by default)

Kim

On 2010-06-29 10: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)

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

Justin_Hayes1 · July 1, 2010, 10:59am

I’d heard of this before and thought I’d removed them. Just checked on live and they’re still there.

However removing them seems to have made no difference. Still taking 20+seconds for a ticket with 130 comments/replies (+ other status changes etc).

Cheers,

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

Justin_Hayes1 · July 1, 2010, 1:30pm

Hi Roy,

We’ve logged the SQL statements on the test server - virtually every query is .000s of a second. Yet the ticket we’re using on there still takes 12s to render.

So the queries seem ok. However I know that having a huge ticket in a small DB is much faster (say 2s). So it seems to be DB or server related, I just don’t know how…

Thanks,

Justin

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

Hi Justin;

Our database is ~ 59G, the attachments table is ~ 40G.
In our setup , the web server is different hardware to the db servers , and the db servers are 2 * dual core 2.6 AMD processors with 16G Ram,

A stupid question, but did you restart apache after you commented out the ShowTransaction lines ?
What version of RT by the way?
Do you use UseSQLForACLChecks?? I found it slows things a bit as it tries to build long joins with cached group members and groups etc.

In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
Then try to load the ticket, then from your rt.log grab some of the sql statements generated and try executing them on the sql server via sql console, and see if any are slow , for any slow queries try an explain and see what indexes are used.

Something else I also try is to put debug lines with timestamps within the html page itself and see which component taking the longest time, then do the same within the slowest component breaking it into various parts to determine which bit of code is taking the longest time etc etc.

Feel free to mail me again if you need further help.

Regards;
Roy

From: Justin Hayes [mailto:justin.hayes@openbet.com]
Sent: 01 July 2010 12:08
To: Raed El-Hames
Subject: Re: [rt-users] Slow Ticket History 3.8.8

Hi Raed,

How big is your DB? We’ve got about 10gb in Attachments. If we put a really long ticket in an empty DB performance is excellent.

So something is wrong with the server/DB, we just don’t know what.

Cheers,

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

Kenneth_Marshall · July 1, 2010, 1:36pm

Hi Roy,

We’ve logged the SQL statements on the test server - virtually every query is .000s of a second. Yet the ticket we’re using on there still takes 12s to render.

So the queries seem ok. However I know that having a huge ticket in a small DB is much faster (say 2s). So it seems to be DB or server related, I just don’t know how…

Thanks,

Justin

Wild conjecture: The time is spent grabbing the attachments from
the database. For the small database, all of them are available
in just a few reads. For the large database, each attachment or
update will need to have its own seek/read and will not typically
be cached.

Cheers,
Ken

alberto.villanueva · July 1, 2010, 1:42pm

Have you read next links? Maybe you could be good for your server.

http://wiki.bestpractical.com/view/PerformanceTuning
http://wiki.bestpractical.com/view/CleanupSessions

Good luck!

Hi Roy,

We’ve logged the SQL statements on the test server - virtually every
query is .000s of a second. Yet the ticket we’re using on there still
takes 12s to render.

So the queries seem ok. However I know that having a huge ticket in a
small DB is much faster (say 2s). So it seems to be DB or server
related, I just don’t know how…

Thanks,

Justin

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

Hi Justin;
Our database is ~ 59G, the attachments table is ~ 40G.
In our setup , the web server is different hardware to the db servers
, and the db servers are 2 * dual core 2.6 AMD processors with 16G Ram,
A stupid question, but did you restart apache after you commented out
the ShowTransaction lines ?
What version of RT by the way?
Do you use UseSQLForACLChecks?? I found it slows things a bit as it
tries to build long joins with cached group members and groups etc.
In terms of debug; if you have not done this yet enable
DBIx-SearchBuilder StatementLog
Set($StatementLog,ï¿½debugï¿½); in your etc/RT_SiteConfig.
Then try to load the ticket, then from your rt.log grab some of the
sql statements generated and try executing them on the sql server via
sql console, and see if any are slow , for any slow queries try an
explain and see what indexes are used.
Something else I also try is to put debug lines with timestamps within
the html page itself and see which component taking the longest time,
then do the same within the slowest component breaking it into various
parts to determine which bit of code is taking the longest time etc etc.
Feel free to mail me again if you need further help.
Regards;
Roy
From: Justin Hayes [mailto:justin.hayes@openbet.com]
Sent: 01 July 2010 12:08
To: Raed El-Hames
Subject: Re: [rt-users] Slow Ticket History 3.8.8
Hi Raed,
How big is your DB? We’ve got about 10gb in Attachments. If we put a
really long ticket in an empty DB performance is excellent.
So something is wrong with the server/DB, we just don’t know what.
Cheers,
Justin

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

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
[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
mailto: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 mailto:justin.hayes@openbet.com

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

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 mailto:justin.hayes@openbet.com

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 mailto:justin.hayes@openbet.com

Hi,
If you are using mysqld have a look at “mysqltuner.pl
http://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

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 mailto:justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
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.bestpractical.com/

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
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

Alberto Villanueva
Industria

ALTRAN

C/Campezo, 1, Edificio 1, Planta 4
28022 Madrid, Spain
Tel : + 34 91 550 41 00
Fax: + 34 91 415 61 53

www.altran.es

Antes de imprimir este mensaje, asegï¿½rate de que es necesario. Proteger
el medio ambiente estï¿½ tambiï¿½n en tu mano.

En cumplimiento de la Ley Orgï¿½nica 15/1999, con fecha 13 de diciembre,
de Protecciï¿½n de Datos de Carï¿½cter Personal, y la Ley 34/2002, con fecha
11 de julio, de Servicios de la Sociedad de la Informaciï¿½n y de comercio
electrï¿½nico, le comunicamos que su direcciï¿½n de correo electrï¿½nico forma
parte de un fichero del que es responsable Altran Espaï¿½a, y que
garantiza la confidencialidad y seguridad de sus datos. Tiene usted
derecho al acceso, rectificaciï¿½n y cancelaciï¿½n de sus datos en los
tï¿½rminos establecidos en la Ley Orgï¿½nica 15/1999 de Protecciï¿½n de Datos
de Carï¿½cter Personal y demï¿½s normativa concordante, dirigiï¿½ndose a
nuestra direcciï¿½n anteriormente seï¿½alada o por medio de correo
electrï¿½nico: comunicacion@altran.es mailto:comunicacion@altran.es.

AVISO LEGAL: Este mensaje, junto con cualquier fichero adjunto, estï¿½
dirigido a su destinatario y es confidencial. Cualquier distribuciï¿½n,
uso o reproducciï¿½n sin consentimiento del remitente estï¿½ estrictamente
prohibido. Si ha recibido este mensaje por error, por favor proceda a
ponerlo en conocimiento del remitente por e-mail y a borrarlo de su
sistema sin realizar copias.

Justin_Hayes1 · July 1, 2010, 1:55pm

Thanks Alberto. Will look at those as well.

Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.comOn 1 Jul 2010, at 14:42, Alberto Villanueva wrote:

Have you read next links? Maybe you could be good for your server.

PerformanceTuning - Request Tracker Wiki
CleanupSessions - Request Tracker Wiki

Good luck!

Hi Roy,

We’ve logged the SQL statements on the test server - virtually every
query is .000s of a second. Yet the ticket we’re using on there still
takes 12s to render.

So the queries seem ok. However I know that having a huge ticket in a
small DB is much faster (say 2s). So it seems to be DB or server
related, I just don’t know how…

Thanks,

Justin

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

On 1 Jul 2010, at 12:28, Raed El-Hames wrote:

Hi Justin;
Our database is ~ 59G, the attachments table is ~ 40G.
In our setup , the web server is different hardware to the db servers
, and the db servers are 2 * dual core 2.6 AMD processors with 16G Ram,
A stupid question, but did you restart apache after you commented out
the ShowTransaction lines ?
What version of RT by the way?
Do you use UseSQLForACLChecks?? I found it slows things a bit as it
tries to build long joins with cached group members and groups etc.
In terms of debug; if you have not done this yet enable
DBIx-SearchBuilder StatementLog
Set($StatementLog,’debug’); in your etc/RT_SiteConfig.
Then try to load the ticket, then from your rt.log grab some of the
sql statements generated and try executing them on the sql server via
sql console, and see if any are slow , for any slow queries try an
explain and see what indexes are used.
Something else I also try is to put debug lines with timestamps within
the html page itself and see which component taking the longest time,
then do the same within the slowest component breaking it into various
parts to determine which bit of code is taking the longest time etc etc.
Feel free to mail me again if you need further help.
Regards;
Roy
From: Justin Hayes [mailto:justin.hayes@openbet.com]
Sent: 01 July 2010 12:08
To: Raed El-Hames
Subject: Re: [rt-users] Slow Ticket History 3.8.8
Hi Raed,
How big is your DB? We’ve got about 10gb in Attachments. If we put a
really long ticket in an empty DB performance is excellent.
So something is wrong with the server/DB, we just don’t know what.
Cheers,
Justin

Justin Hayes
OpenBet Support Manager
justin.hayes@openbet.com mailto: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
[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
mailto: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 mailto: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 mailto: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 mailto: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 mailto: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
http://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 mailto:justin.hayes@openbet.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
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.bestpractical.com/

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
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

–
Alberto Villanueva
Industria

ALTRAN

C/Campezo, 1, Edificio 1, Planta 4
28022 Madrid, Spain
Tel : + 34 91 550 41 00
Fax: + 34 91 415 61 53

www.altran.es

Antes de imprimir este mensaje, asegúrate de que es necesario. Proteger
el medio ambiente está también en tu mano.

En cumplimiento de la Ley Orgánica 15/1999, con fecha 13 de diciembre,
de Protección de Datos de Carácter Personal, y la Ley 34/2002, con fecha
11 de julio, de Servicios de la Sociedad de la Información y de comercio
electrónico, le comunicamos que su dirección de correo electrónico forma
parte de un fichero del que es responsable Altran España, y que
garantiza la confidencialidad y seguridad de sus datos. Tiene usted
derecho al acceso, rectificación y cancelación de sus datos en los
términos establecidos en la Ley Orgánica 15/1999 de Protección de Datos
de Carácter Personal y demás normativa concordante, dirigiéndose a
nuestra dirección anteriormente señalada o por medio de correo
electrónico: comunicacion@altran.es mailto:comunicacion@altran.es.

AVISO LEGAL: Este mensaje, junto con cualquier fichero adjunto, está
dirigido a su destinatario y es confidencial. Cualquier distribución,
uso o reproducción sin consentimiento del remitente está estrictamente
prohibido. Si ha recibido este mensaje por error, por favor proceda a
ponerlo en conocimiento del remitente por e-mail y a borrarlo de su
sistema sin realizar copias.

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