[rt-users] Slow GET_LOCK queries on merged tickets

[Torsten Brumm]

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/20081111/a502a167/attachment.htm>