Slow GET_LOCK queries on merged tickets

RT Users
1

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@Host: rt[rt] @ localhost []

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

SELECT GET_LOCK(‘Apache-Session-a196db0f41ab721840dba0b78f2dd36e’,
3600);

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

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

SELECT GET_LOCK(‘Apache-Session-a196db0f41ab721840dba0b78f2dd36e’,
3600);

2

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);

3

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…)

Torsten2008/11/7 Kenny Seever kseever@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);

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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

4

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,

KennyFrom: Torsten Brumm [mailto:torsten.brumm@googlemail.com]
Sent: Tuesday, November 11, 2008 10:11 AM
To: Kenny Seever
Cc: rt-users@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