[rt-users] Slow GET_LOCK queries on merged tickets

[Kenny Seever]

Thanks for the response Torsten.
 
I gave that a try, but unfortunately it didn’t speed it up any.   We luckily had only merged a couple of times so once we get them closed hopefully we won’t touch them again for a while.
 
Thanks again,
 
Kenny 
 
From: Torsten Brumm [mailto:torsten.brumm at googlemail.com] 
Sent: Tuesday, November 11, 2008 10:11 AM
To: Kenny Seever
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] Slow GET_LOCK queries on merged tickets
 
Hi Kenny,
we had exactly the same problem. i'm not sure if our solution is the best, but it helped us to get rid of this.

We changed the following: (and again, i'm not sure if it is a good idea, but it fixes the problem)

grab up from /usr/lib/perl5/site_perl/5.8.xxx/Apache/Session/Lock the file MySQL.pm and find the line with my GET_LOCK, its around line 53. It uses default 3600 sec for lock timeout. we changed this to 1sec.

     50     #MySQL requires a timeout on the lock operation.  There is no option
     51     #to simply wait forever.  So we'll wait for a hour.
     52
     53     my $sth = $self->{dbh}->prepare_cached(q{SELECT GET_LOCK(?, 1)}, {}, 1);

And again, and hopefully some of the DB/PERL Gurus have a better idea, this fixed our problem (for over one year) but i'm not sure which site effects it has (we discovered nothing so far....)

Torsten
2008/11/7 Kenny Seever <kseever at innonetkc.com>
Hello everyone.   I was looking through the archives and came across the following.  It looks like we are having the same problem and I was wondering if a solution was ever found.
 
Thanks in advance,
 
Kenny Seever
Innovative Networks, Inc.
 
 
 
Original Message…:
 
Hi all, 



I managed to track down a problem with merged tickets being slow to 
display. I have enabled the slow queries log on Mysql (5.0.51a) and get 
the following in the log files. I searched over the RT list archives and 
I found note of setting low_priority_updates to 1 on mysql for this. 
This however makes no difference. Does someone perhaps have an answer 
for this problem? 



The server is a test setup I made to test the migration of 3.6.6 RT 
installation to 3.8.1. The problem was there in 3.6.6 when I imported 
the production database from a mysql dump the production server also 
running rt 3.6.6 is fine (not slow on merged tickets). The difference 
between the systems is that the production server is a Redhat Enterprise 
4 server (MySQL-server-community-5.0.51a-0.rhel4) and the test system is 
a newer Fedora 9 (mysql-server-5.0.51a-1.fc9). The fedora 9 installation 
installed all required modules from CPAN during installation. After 
migrating it to 3.8.1 the problem remains. It looks like it slightly 
faster but not much. Database has been updated from mysql 4.0 to 4.1 
followed by the database update script. All is working fine accept for 
the slow queries on merged tickets and the GET_LOCK reports in the slow 
query log. 



I've cleared the session table. This currently makes no difference. It 
had about 1100 records in it before I cleared it. 



Regards, 



Ton Hoogstraten 



SELECT GET_LOCK('Apache-Session-7dee36ca13e864c568b1659d2fa18751', 
3600); 

# Time: 080822 14:36:01 

# User[at]Host: rt[rt] @ localhost [] 

# Query_time: 110 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 

SELECT GET_LOCK('Apache-Session-a196db0f41ab721840dba0b78f2dd36e', 
3600); 

# User[at]Host: rt[rt] @ localhost [] 

# Query_time: 80 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 

SELECT GET_LOCK('Apache-Session-a196db0f41ab721840dba0b78f2dd36e', 
3600);

_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales at bestpractical.com


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



-- 
MFG

Torsten Brumm

http://www.torsten-brumm.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20081112/2723e5af/attachment.htm>