I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites.
Our table design looks like this:
db_sessions.tbl_sessions_2012 (2012 being current year, 2013 next year).
This is an InnoDB table, and has 9 indexes, 13 columns of generally varchar(varying) and INT, and 2 TEXT columns that record request URL and referrer URL. it looks like this:
CREATE TABLE `tbl_sessions_2012` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sessionid` varchar(255) NOT NULL,
`ipaddress` varchar(255) NOT NULL,
`geocode` varchar(255) NOT NULL DEFAULT '''',
`journalcode` varchar(255) NOT NULL,
`logintypeid` int(1) NOT NULL DEFAULT ''0'',
`accountid` int(11) NOT NULL DEFAULT ''0'',
`contactid` int(11) NOT NULL DEFAULT ''0'',
`organisationid` int(11) NOT NULL DEFAULT ''0'',
`accountcollectionid` int(11) NOT NULL DEFAULT ''0'',
`request` text,
`referer` text,
`isSSL` tinyint(1) NOT NULL DEFAULT ''0'',
`isLogout` tinyint(1) NOT NULL DEFAULT ''0'',
`actionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,
KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE,
KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE,
KEY `organisationaccountcollections` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE,
KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
KEY `accountcollection` (`accountcollectionid`,`journalcode`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3161119 DEFAULT CHARSET=utf8
We have two MySQL servers, one is running MySQL 5.5.24, and the other running 5.5.11 which stores all our other content. We are currently writing the session logging to our 5.5.24 which also acts as our dev database.
So at the moment, this single table is getting written to on every hit of the website, ~1.2m a month. We also want to be able to run queries on it, So have 2 views on the table depending on the login type. We only ever run queries on the views.
With this, we are getting slowdowns on the admin section of our sites, where we wish to display stats about who is logging in. None of us are DBAs, but we really need to improve the speed and efficency on this. Are there any recommended practices for writing to a session logging table and reading from it at the same time.
Could the table design be better. I went froma split monthly design to a single yearly design.
We run a query like this:
SELECT count(id) as total, accountid FROM vw_ContactAccounts_2012 s
WHERE 1 AND accountid IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
GROUP BY accountid
on our admin pages to show whether someone has session statistics available.
SHOW CREATE TABLE
) for one of the session tables. Also, give a few of the queries you're running that you notice slowdown with.EXPLAIN
before the select:EXPLAIN SELECT count(id)..
to get an idea of what MySQL is doing to process your query. Unless there is a very good reason for it, I wouldn't search foraccountid IN (unlimited id list)
. See my answer footnote for why.