1

We have something similar to Google Analytics, but decided to not use something that's already available (but didn't fit our needs exactly), and instead created our own "mini-analytics".

Now, this was all easy and fun, but as it grows, the architecture either wasn't designed properly, or the wrong tools were used to solve the problem.

The problem lies with queries that look like the following: "Get all user sessions that have the following events: login, chrome browser version 58, and profile_view".

Currently this hits the following tables:

CREATE TABLE `logins` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `login_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `browsers` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `browser_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `profile_views` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `profile_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

Some notes:

  • All the mediumblob columns are JSON objects, but we haven't upgraded to MySQL 5.7.8 yet.
  • All tables have the same columns and indexes.
  • Each table contains in between several million and several billion rows.

One of the problems I seem to have is that I can't limit the inner queries (if using them), and joins also don't appear to work.

What I wonder is mostly: can this be efficiently solved using a SQL solution, or would this lean more towards one of the NoSQL (for example, a graph database) solutions?

-- EDIT:

Queries are built up using a loop that concatenates subqueries in the following manner:

For a single table (e.g. "sessions that have a profile view after timestamp x"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `profile_views`
    WHERE
        `timestamp` > x
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

Two tables (e.g. "Sessions that have a profile view and login"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `logins`
    WHERE
        `timestamp` >= x
    AND
        `session_id` IN (
        SELECT
            `session_id`
        FROM
            `profile_views`
        WHERE
            `timestamp` >= x
    )
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

I'm looking into joins, but at the moment they appear to return different results, for example something like the following:

SELECT DISTINCT
    `A`.`session_id`
FROM
    `logins` `A`
INNER JOIN
    `profile_views` `B`
ON
    `B`.`session_id` = `A`.`session_id`
WHERE
    `A`.`timestamp` > x
ORDER BY
    `session_id` DESC
LIMIT 100;
  • 1
    You probably should have persisted the actual values contained in the JSON in their own columns, or used an EAV model – Philᵀᴹ Mar 14 '17 at 13:17
  • 1
    Or even if not all the values in the JSON blob, at least the more common ones to search for. – Jonathan Fite Mar 14 '17 at 13:21
  • The values aren't even the biggest issue, the query runs rather slow even when looking at the existence of session_ids in a few tables. As in: I want 100 session_ids that appear in these 3 (or any arbitrary amount of) tables. – Aeveus Mar 14 '17 at 13:48
  • 1
    A question for you, and I realize it may be too late to turn back now. Why are you using MySQL for your analytics DB? You could use something more lightweight like influx which is built for this kind of purpose. influxdata.com – JPeck89 Mar 14 '17 at 14:15
  • Show your indexes, show your queries. – bishop Mar 14 '17 at 14:51
1

Your query using joins looks ok but it will help speed up the queries if you convert the timestamp_idx key to include session_id. This way the database engine won't have to to another/second lookup/sort and on tables with millions of rows, it will have significant improvement in response times.

ALTER TABLE <table name> ADD KEY ix_covering(`timestamp`, `session_id`,`request_id`);

Drop both indexes on all tables AFTER you have tested that the new index as per above does improve the performance.

Also, instead of

SELECT DISTINCT
    `A`.`session_id`

try

SELECT `A`.`session_id`
FROM `logins` `A`
INNER JOIN `profile_views` `B` ON `B`.`session_id` = `A`.`session_id`
WHERE `A`.`timestamp` > x
GROUP BY `A`.`session_id`
ORDER BY `session_id` DESC
LIMIT 100;
  • The timestamp isn't that much of an issue (I mostly used this to demonstrate a where clause in the query). Usually the queries pertain all events, with the timestamps being retrieved later on. For the second suggestion: The GROUP BY does run faster, but returns a different set of results. – Aeveus Mar 16 '17 at 6:13
1

(Too many questions/comments to clutter the Comments)

"Get all user sessions that have the following events: login, chrome browser version 58, and profile_view"

What is a "user session"? (I see no user_id.)

Where do you find "ver 58"? If it is buried inside JSON, that is a serious flaw. If you need to search for it, it needs to be in a separate (perhaps indexed) column. It could also be left in the JSON for simplicity, along with all the fru-fru.

Why are ther 3 separate tables? Doesn't a user (1) login with a (2) browser and (3) hit his profile. So, can't there be 1 row (with a couple more columns) in 1 table instead of 1 row in each of 3 tables?

If 1 row in 3 tables, and if they have different timestamps, how would you ever tie them together? Or is that what the session_id is?

You really need a PRIMARY KEY. Perhaps (session_id, request_id) is unique? If so, think about promoting it to PK.

InnoDB is the only engine to use for virtually all uses.

What other queries will be applied? Keep in mind that non-relational databases tend to scan all the data. You won't like the performance when scanning a billion rows, even in a single table, much less 3 tables.

  • A user session is a certain timeframe a user spent on the application; it starts when you visit the application and ends when you exit. Versions are indeed stored in JSON, but luckily that's not all too hard of a requirement; being able to search for them would only be a bonus. The reason for separate tables is so we can easily define new events (e.g. HoveredOverAvatarAndClicked; silly but just an example) and remove events that aren't needed. It used to be a really big table with a type instead of separate events, but deleting events was really expensive. Will add PK to test. – Aeveus Mar 16 '17 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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