[rt-users] RT2 and MySQL
Adam Morton
lists at adammorton.com
Wed May 22 14:17:35 EDT 2002
Here's the bad query, which has at least 1 more join than the others in the
list and is swapping to disk and locking up all the incoming ones.
> | 4253989 | rt_user | localhost | rt2 | Query | 1199 | Copying to
> tmp table | SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1,
> Watchers Watchers_3 LEFT JOIN Users
Workaround: Whatever you did that generates that query with the extra join
(20 minutes prior to running this processlist), don't do it.
A fix would involve figuring out how to do whatever is being done here more
efficiently, or tweaking MySQL so that it can handle this query without
swapping out to the tmp table (check tmp_table_size), or so that locking is
less of a problem (look at http://www.mysql.com/doc/T/a/Table_locking.html).
A 'show full processlist' will list the complete SQL statement which may
help you pin down what it is a little better. Or, you could turn on query
logging or slow query logging.
----- Original Message -----
From: "Joshua Mandelberger" <josh at pwebtech.com>
To: "Adam Morton" <lists at adammorton.com>; <rt-users at fsck.com>
Sent: Wednesday, May 22, 2002 1:36 PM
Subject: Re: [rt-users] RT2 and MySQL
> This is what it looks like.
>
+---------+---------+-----------+----------+---------+------+---------------
-------+--------------------------------------------------------------------
----------------------------------+
> | Id | User | Host | db | Command | Time |
> State |
> Info
> |
>
+---------+---------+-----------+----------+---------+------+---------------
-------+--------------------------------------------------------------------
----------------------------------+
> | 4253090 | rt_user | localhost | rt2 | Sleep | 1095
> | |
> NULL
> |
> | 4253105 | rt_user | localhost | rt2 | Query | 188 |
> Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
> ((main.Status = 'open')OR(main.Status = 'new')) AND |
> | 4253135 | rt_user | localhost | rt2 | Query | 1026 |
> Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
> ((main.Status = 'open')OR(main.Status = 'new')) AND |
> | 4253260 | rt_user | localhost | rt2 | Query | 928 |
> Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
> ((main.Status = 'open')OR(main.Status = 'new')) AND |
> | 4253385 | rt_user | localhost | rt2 | Query | 495 |
> Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
> ((main.Status = 'new')OR(main.Status = 'open')) AND |
> | 4253393 | rt_user | localhost | rt2 | Query | 921 |
> Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
> ((main.Status = 'open')OR(main.Status = 'new')) AND |
> | 4253417 | rt_user | localhost | rt2 | Query | 1096 |
> Locked | SELECT DISTINCT main.* FROM Tickets main, Watchers
> Watchers_1 LEFT JOIN Users as Users_2 ON Watch |
> | 4253428 | rt_user | localhost | rt2 | Query | 1096 |
> Locked | SELECT DISTINCT main.* FROM Tickets main, Watchers
> Watchers_1 LEFT JOIN Users as Users_2 ON Watch |
> | 4253989 | rt_user | localhost | rt2 | Query | 1199 | Copying to
> tmp table | SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1,
> Watchers Watchers_3 LEFT JOIN Users |
> | 4273904 | rt_user | localhost | rt2 | Query | 718 |
> Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
> ((main.Status = 'open')OR(main.Status = 'new')) AND |
> | 4331604 | rt_user | localhost | rt2 | Query | 1096 |
> Locked | UPDATE Tickets SET LastUpdatedBy='4366' WHERE
> id='12598' |
> | 4332533 | rt_user | localhost | rt2 | Query | 1084 |
> Locked | INSERT INTO Tickets (Creator, Due, Status,
> LastUpdatedBy, Resolved, Type, TimeWorked, LastUpdated, S |
> | 4335019 | rt_user | localhost | rt2 | Query | 719 |
> Locked | SELECT * FROM Tickets WHERE id =
> '12556' |
> | 4336413 | rt_user | localhost | rt2 | Query | 511 |
> Locked | INSERT INTO Tickets (Creator, Due, Status,
> LastUpdatedBy, Resolved, Type, TimeWorked, LastUpdated, S |
> | 4337214 | rt_user | localhost | rt2 | Query | 397 |
> Locked | INSERT INTO Tickets (Creator, Due, Status,
> LastUpdatedBy, Resolved, Type, TimeWorked, LastUpdated, S |
> | 4339696 | root | localhost | NULL | Query | 0 |
> NULL | show
> processlist
> |
> | 4339886 | root | localhost | vpopmail | Sleep |
> 0 | |
> NULL
> |
> | 4339887 | root | localhost | vpopmail | Sleep |
> 0 | | NULL
>
>
> when I say just stays put there, I mean it continually takes 99.9% of the
> CPU power of the box, or at least as much of it as it can get.
>
> To stop it we have to restart the mysql server.
>
> Sorry about the delayed response, the problem didn't occur again until
now.
>
> Josh
>
> At 03:49 PM 5/9/2002, Adam Morton wrote:
> >What does 'show processlist' in mySQL look like when this is happening?
> >
> >When you say 'just stay put there', do you mean forever, and you have to
> >restart mySQL or the server, or what? Or does it stop by itself at some
> >point?
> >
> >----- Original Message -----
> >From: "Joshua Mandelberger" <josh at pwebtech.com>
> >To: <rt-users at fsck.com>
> >Sent: Thursday, May 09, 2002 3:36 PM
> >Subject: [rt-users] RT2 and MySQL
> >
> >
> > > Hi, I've been on the list for quite some time now but up until now
have
> > > been lurking and reading. Lots of great information, a good deal of
which
> > > as been valuable to myself and the admins who over see our RT2
> >installation.
> > > I must also say its an awesome program, its improved the efficiency of
our
> > > tech support more than I can possibly describe.
> > >
> > > That having been said, we have been seeing some problems with our RT2
> > > database over the last week or two that is cause for some concern.
> > >
> > > Every so often, and it is intermittent but this problem occurs at
least
> > > once ever couple days, the MySQL thread for the RT2 database will
spike up
> > > to 85-99% of the servers CPU power and just stay put there. This
causes
> > > all the sessions in Tracker to move very slowly naturally.
> > >
> > > Is there anywhere to look for a way to pin down what is causing the
> > > problem, or some kind of tuning we can do on the database that might
> > > alleviate the problems we're having?
> > >
> > > Here is the present configuration.
> > >
> > > Dual P-III 800
> > > 768MB of Ram
> > > RedHat Linux version 6.1
> > > Running the 2.4.17 kernel
> > > mysql version 3.23.39
> > > RT version 2.0.13
> > >
> > > There are also around 11000-12000 tickets in our database if that
makes a
> > > difference.
> > >
> > > If there is anything else I can provide that would help with
information
> > > please let me know. Also if this has come up before (I hadn't seen
> > > anything like it) and there is already messages covering this, let me
know
> > > where in the archives to look. I went through them and tried to find
some
> > > answers but didn't see anything similar to what is happening. But I
could
> > > have missed them.
> > >
> > > Thanks,
> > >
> > > Joshua Mandelberger
> > > Pegasus Web Technologies
> > >
> > >
> > >
> > >
> > >
> > > _______________________________________________
> > > rt-users mailing list
> > > rt-users at lists.fsck.com
> > > http://lists.fsck.com/mailman/listinfo/rt-users
> > >
> > > Have you read the FAQ? The RT FAQ Manager lives at
http://fsck.com/rtfm
> > >
> >
> >
> >_______________________________________________
> >rt-users mailing list
> >rt-users at lists.fsck.com
> >http://lists.fsck.com/mailman/listinfo/rt-users
> >
> >Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
>
>
> _______________________________________________
> rt-users mailing list
> rt-users at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-users
>
> Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
>
More information about the rt-users
mailing list