I upgraded to 5.0.45-log via yum this morning and found no change to the behavior. Here's a snippet from mysqladmin processlist:
| 7 | hqadmin | localhost:45478 | hqdb | Query | 0 | Sending data | select alert0_.ID as ID116_, alert0_.VERSION_COL as VERSION2_116_, alert0_.CTIME as CTIME116_, alert |
The full query from SHOW FULL PROCESSLIST was:
select alert0_.ID as ID116_, alert0_.VERSION_COL as VERSION2_116_, alert0_.CTIME as CTIME116_, alert0_.FIXED as FIXED116_, alert0_.ALERT_DEFINITION_ID as ALERT5_116_, (select e.id from EAM_ESCALATION_STATE e where e.alert_id = alert0_.id and e.alert_type = -559038737) as formula2_, (select e.acknowledged_by from EAM_ESCALATION_STATE e where e.alert_id = alert0_.id and e.alert_type = -559038737) as formula3_ from EAM_ALERT alert0_ inner join EAM_ALERT_DEFINITION alertdefin1_ on alert0_.ALERT_DEFINITION_ID=alertdefin1_.ID inner join EAM_RESOURCE resource2_ on alertdefin1_.RESOURCE_ID=resource2_.ID where (resource2_.RESOURCE_TYPE_ID is not null) and (alert0_.CTIME between 1239893640000 and 1242312840000) and alertdefin1_.PRIORITY>=0 order by alert0_.CTIME DESC limit 5955, 5
What I find peculiar is that it seems to stick in "Sending data" state and the query time doesn't update. mysqld in top jumps to 95% CPU while the query is running. I checked my mysql-slow log and it doesn't show up there. (More disturbing is that I've had 3 slow queries on "select measurement_id, value ..." since upgradng to 5.0.45 ... *sigh*
If I run that query by hand, it returns in 0.03 seconds. I suppose that could be because the first query is slow and the 2nd is cached. But I've got a 4GB innodb buffer pool so presumably I have enough room to keep most of it cached...
I guess it's not clear to me if it's a MySQL problem or a HQ problem... but I'm definitely going to try to make sure no one tries to filter on "selected resources" for recent events until I figure it out 🙂