I have an application where each page load inserts information about the visit into a MariaDB table. There is an analytics hub where statistics from this data can be viewed.
In the beginning, this used to work well, but now the table is large enough (currently 650,000 rows), where performing SELECT queries on it with lots of filters tends to take a while, sometimes as long as 20 seconds. This in and of itself isn't an issue. The problem is that since all pages insert records into this table, whenever analytics are being retrieved, the whole site is basically down until the query finishes. At that point, everything else continues again.
This is admittedly not a great architecture for this purpose, but I would like to modify some things so that the analytics queries are not locking the table. By this, I mean simple modifications, not setting up a read replica and point the analytics queries at that.
Is it possible to change the queries so that the SELECT doesn't lock the table in any way that would prevent the INSERTs from succeeding. The INSERTs are all auto-increment, and I don't care if a little accuracy is sacrificed on the SELECT.
I tried moving this particular table into its own table, but that hasn't really helped in, since it is, in fact, this specific table which is the issue, not access to other tables in either database. I have tried some of the approaches such as in this answer, but they don't work because they are focused on SELECT. I don't care if the SELECT's are slow, but the INSERTS all queue up while a SELECT is being run and this is causing serious issues whenever this occurs.
Speeding up the query might help a little bit, but fundamentally I want to prevent the INSERTs from waiting on the SELECTs to complete.
Is this possible to do, without changing the database setup (e.g. using separate tables for SELECT/INSERT)? I am using MariaDB 10.3 with the InnoDB engine.
This is the structure of the table:
views | CREATE TABLE `views` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL DEFAULT current_timestamp(),
`userid` int(11) unsigned NOT NULL,
`sessionid` varchar(64) NOT NULL,
`ip` varchar(64) NOT NULL,
`agent` varchar(256) NOT NULL,
`domain` varchar(32) NOT NULL,
`pagebase` varchar(256) NOT NULL,
`pagefull` varchar(256) NOT NULL,
`hash` varchar(128) NOT NULL,
`ref` varchar(256) NOT NULL,
PRIMARY KEY (`id`),
KEY `userid` (`userid`),
KEY `pagebase` (`pagebase`),
KEY `pagebase_2` (`pagebase`,`domain`),
KEY `hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=930146 DEFAULT CHARSET=utf8mb4
Here is one of the faster queries (really), which takes 14 seconds to run:
SELECT domain, COUNT(DISTINCT(hash)) as d, COUNT(*) AS c FROM views GROUP BY domain ORDER BY d DESC;
Some of the much queries use lots of wildcard searches, which, even on indexed columns, perform quite badly.
Again, I have no issue with these queries taking a while, but whenever they are running, they block everything else, which is a serious issue.
