0

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.

1 Answer 1

0

Plan A: Use a lower "transaction_isolation".

Plan B: Build and maintain Summary Table(s) for the analytics queries to use. They will be faster and not block the writes. http://mysql.rjweb.org/doc.php/summarytables

4
  • Is A different from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;? I tried that already and it didn't work Aug 12, 2021 at 11:33
  • @InterLinked - Well, on to plan B. I might have further detailed advice if you provide SHOW CREATE TABLE and the SELECT doing the summarization.
    – Rick James
    Aug 12, 2021 at 17:21
  • There are multiple SELECT queries that run, and all of them have lots of WHERE clauses (WHERE a AND b AND c... etc.). Summary tables don't really help since the most important stuff is the most recent stuff, so they'd need to be constantly re-generated. Aug 12, 2021 at 18:14
  • @InterLinked - Start a new Question with just one of the slowest queries. Provide SHOW CREATE TABLE and the SELECT. I may (or may not) be able to show how to speed it up with (or maybe without) a Summary table.
    – Rick James
    Aug 12, 2021 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.