What is the engine type of the table you are using here ?

On Wed, Dec 28, 2011 at 2:41 AM, umapathi b <umapathi.b@gmai...> wrote:

> On Wed, Dec 28, 2011 at 1:19 AM, umapathi b <umapathi.b@gmai...> wrote:
>
> > Hi All,
> >
> > Yesterday suddenly we got tens of slow query mails running on 10.10.90.57
> > ( houdb01 ) . When I inspected the show process list output ..there were
> > two queries which have been running for the longer time compared to
> others
> > ..
> > Plz find the attached file for inspection.
> >
> > 1) (SELECT UI.USER_ID FROM (user_info UI LEFT OUTER JOIN user_contact UC
> > ON UI.USER_ID = UC.USER_ID),course C WHERE UI.COURSE_ID = C.COURSE_ID AND
> > UI.DRIVERS_LICENSE LIKE '05105915%' AND C.PRODUCT_ID=25) UNION (SELECT
> > UI.USER_ID FROM (user_info UI LEFT OUTER JOIN user_contact UC  ON
> > UI.USER_ID = UC.USER_ID),course C WHERE UI.COURSE_ID = C.COURSE_ID AND
> > UI.LOGIN LIKE '05105915%' AND C.PRODUCT_ID=25)
> >
> > 2) UPDATE user_info SET TEST_INFO = '100000' WHERE USER_ID = 18921819
> >
> > These queries were running with the same time .i.e. each query started at
> > the same time ..and were running parallelly with the same no.of seconds .
> >
> > Then I killed the Select Query ( the first one ) assuming that it will
> > free other resources . But this didn't happen and the thread status was
> > showing as killed but not releasing others ..
> >
> > At this time there was not even any load on the server . In this regard ,
> > I re-started mysqld on 57 which resolved the issue . Infact , the queries
> > mentioned above were not even taking any time to be executed and they
> were
> > not slow queries .
> >
> > I inspected all the management,data node,mysql logs but could not find
> any
> > error/information wrt to this ..
> >
> > Can anybody clarify this , why this happened so ?
> > What measures are to be taken so that this kind of things are not
> repeated again ..
> >
> > Thanks,
> > Umapathi.
> >
> >
>



--
Thanks
Suresh Kuna
MySQL DBA