[Building Sakai] SiteStats slow updates

[Matthew Buckett]

We're seeing some slow queries against our MySQL database, this is
from the slow query log. The only slow queries are from site stats
doing updates on the SST_EVENTS table.

# Time: 130320  9:47:49
# Query_time: 6.013975  Lock_time: 0.000065 Rows_sent: 0  Rows_examined: 1
SET timestamp=1363772869;
update SST_EVENTS set USER_ID='?', SITE_ID='info',
EVENT_ID='content.read', EVENT_DATE='2013-03-20', EVENT_COUNT=10605
where ID=6857853;

The table is a few columns and several indexes and according to the
slow query log it didn't do a scan and used the index as we have
"Rows_examined: 1", the lock time is very low so I'm left wondering
why this query took so long. We do have lots of updates happening
against this table and in particular this row, but the slow queries
pretty much always for this row (>90%).


CREATE TABLE SST_EVENTS (
        ID BIGINT NOT NULL,
        USER_ID VARCHAR(99) NOT NULL,
        SITE_ID VARCHAR(99) NOT NULL,
        EVENT_ID VARCHAR(32) NOT NULL,
        EVENT_DATE DATE NOT NULL,
        EVENT_COUNT BIGINT NOT NULL,
        PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE INDEX SST_EVENTS_EVENT_ID_IX ON SST_EVENTS (EVENT_ID ASC);

CREATE INDEX SST_EVENTS_DATE_IX ON SST_EVENTS (EVENT_DATE ASC);

CREATE INDEX SST_EVENTS_SITEEVENTUSER_ID_IX ON SST_EVENTS (EVENT_ID ASC);

CREATE INDEX SST_EVENTS_SITEEVENTUSER_ID_IX ON SST_EVENTS (SITE_ID ASC);

CREATE INDEX SST_EVENTS_SITEEVENTUSER_ID_IX ON SST_EVENTS (USER_ID ASC);

CREATE INDEX SST_EVENTS_USER_ID_IX ON SST_EVENTS (USER_ID ASC);

CREATE INDEX SST_EVENTS_SITE_ID_IX ON SST_EVENTS (SITE_ID ASC);

-- 
  Matthew Buckett, VLE Developer, IT Services, University of Oxford