[Building Sakai] SiteStats slow updates

[Matthew Buckett]

On Sat, Mar 23, 2013 at 8:59 PM, Steve Swinsburg
<steve.swinsburg at gmail.com> wrote:
> 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?

I haven't looked through the indexes in sitestats but at a glance they
look reasonable. There doesn't seem to be a nice way in MySQL to as it
to report on index usage without logging all queries.

Ok, so it turns out that MySQL doesn't include the time waiting on
another transaction in the lock time. As we have 3 worker nodes and
sitestats updates large numbers of sites in one transaction these
updates end up blocking each other. In this sitestats code there isn't
any reason to use one large transaction other than the possible
performance improvement from doing so. So I'm going to look at using
smaller transactions.

My dump of the sitestats table wasn't good (Eclipse DTP), but they are
the standard indexes created by SiteStats:

CREATE TABLE `SST_EVENTS` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `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(20) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `SST_EVENTS_SITE_ID_IX` (`SITE_ID`),
  KEY `SST_EVENTS_USER_ID_IX` (`USER_ID`),
  KEY `SST_EVENTS_EVENT_ID_IX` (`EVENT_ID`),
  KEY `SST_EVENTS_DATE_IX` (`EVENT_DATE`),
  KEY `SST_EVENTS_SITEEVENTUSER_ID_IX` (`SITE_ID`,`EVENT_ID`,`USER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6902948 DEFAULT CHARSET=utf8;


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