[Building Sakai] SiteStats slow updates

[Steve Swinsburg]

Thats a lot of indexes. And the same column is being indexes multiple times. Is there some other FK relationship there?
Too many indexes slow down updates and inserts. What are the queries on that table? Are all of those indexes used?


On 23/03/2013, at 1:51 AM, Matthew Buckett <matthew.buckett at it.ox.ac.uk> wrote:

> 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
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"