3

We are going to track user engagement (i.e. time spent on website, most viewed part/page of the system, etc).

I don't see Google Analytics / MixPanel being able to do this, since we have to analyze based on factors only present in OUR backend (such as users who go to specific school, users who are a specific type, etc) - not the general stuff like Country, Gender, etc.

I can think of a REALLY simple solution, but I am not sure if it is, serverwise, bad. You have a table that looks something like this:

CREATE TABLE `log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uri` int(11) DEFAULT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `user` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And then whenever a page is loaded, a row is added to the "log" table, with current timestamp, the user id and the uri.

Now I got the data I want, which is great. I can figure out when the user was last online and how many minutes people spend in average.

BUT. Is this bad for the server? It is going to add a row each page load, which is a lot of rows, if you have 500 or 1000 active users. But will it matter? What are your thoughts?

3
  • 2
    Have you load tested it? What is an 'active user'? Does that mean someone loading a page every second? or every minute? Are you logging all requests? or just pages (rather than images, javascript, css, etc...)?
    – user40980
    May 14, 2014 at 21:56
  • Hi Michael. I plan to log it from the header, meaning everywhere where user views a page, and then manually some places. Do you think a single MySQL insert every page will hurt?
    – FooBar
    May 19, 2014 at 21:02
  • Your simple solution is the best one - just make sure the log is written to a separate machine from the one used to show the website... and make sure the communication layer between those machines is fast (for example, your web server should send an entry to the log server without waiting for confirmation - use insert delayed for example). This way if there are performance problems, nothing bad happens - you'll just miss out on stats for a day or two until the problem is fixed. Mar 6, 2016 at 21:37

3 Answers 3

2

It is not necessarily bad for the server to log a line per page request. However, there are few things to consider here. First of all, I see you are using an AUTO_INCREMENT as a key. This will result in some performance loss as you are writing records to it.

You could replace the id with a GUID field and generate GUID's as you write lines.

Another optimization that you could to is to make this asynchronously. So the user requests a page, which you serve to the user, and while you do, you fire off a request, event, or whatever is suitable for your server side technology and does not block sending back the request, to actually write your logging.

Another thing to consider is, do you really need the data in a database immediately? It might be sufficient to just write to a log file first and import the data into a database at a later point in time.

5
  • Do you have a source for performance of auto increment keys being a performance problem (at least one not using the legacy backwards-compatibility mode that locks the table while inserting)?
    – Jules
    Mar 6, 2016 at 19:23
  • Also note that using rows to a large table with a uuid primary key can be slow (see kccoder.com/mysql/uuid-vs-int-insert-performance), probably due to the time taken to sort the new keys into the index and/or ensure that the entry is unique.
    – Jules
    Mar 6, 2016 at 19:29
  • @Jules auto increment is slow because it's necessary to avoid duplicates somehow, which is problematic in multithreaded situations (like a web server) and can easily result in a simple insert taking 5 minutes. You should instead use a "version 4" UUID - which is designed to be able to handle trillions of inserts per second spread across as many threads as you want (avoid "version 1" UUIDs since they have similar multithreading performance issues just like auto increment). stackoverflow.com/questions/2040240/… Mar 6, 2016 at 21:40
  • I've used mysql auto increment for just this application before, and have never seen insert times longer than milliseconds, much less minutes. There are 3 available options for duplication avoidance, and unless you use the older legacy system that locks the table, either of the other two should perform much better than the php code for uuid generation you link to. The default uses a spinlock to synchronise access to an in-memory cache of the next value. With any system having less than 20 processor threads, this should result in trivial delays.
    – Jules
    Mar 6, 2016 at 22:37
  • Above 20 threads, contention may become significant (I could foresee a case where contention latency here might amount to as much as 1ms) , but note that this is still much faster than the more complex task of inserting a string into a btree in the middle versus inserting an integer at the end (which is likely to result in a much larger proportion of cache hits versus misses). If this does become a problem, there's a lock-free implementation you can switch to (albeit at cost of compatibility).
    – Jules
    Mar 6, 2016 at 22:49
1

In a similar setup, one of the domain's I develop logs pageloads and other in-page events to a logging table on a SQL Server. It uses an auto-incrementing int-PK. Per Google Analytics (GA), we've definitely seen over 500 "active users." And we haven't seen any problems logging to the table.

The notable difference for us, I think, is that we actually wrap the GA logging and have the browser initiate a separate logging request "alongside" the GA request. That allows any the bottlenecks in the logger to stay out of the more important matter of actually building the page.

But, I can't say whether this is feasible for your application, which likely has very different server loads, traffic patterns, locking patterns, etc.. At the very surface of it, you're obviously using a different DMBS, and probably a different server-side stack ... I can't say what impact that'll have.

But, the basic logging you want certainly can be feasible if your server resources can handle it, and ideally if you keep it "out of the way" of your core application logic.

1

If you really think this will impose a heavy server load, consider logging everything to a NoSQL database like MongoDB (or possibly even a plain text/CSV file), then performing a bulk load operation during off-peak hours. If the load process is the only thing that writes to this table, then you can eliminate the auto-increment ID and pre-generate all your IDs before you insert.

This presumes you don't need real-time access to this data; if you do, then you could consider writing a data loader that would post the data in a controlled fashion (say only N rows/second, configurable). This would prevent the writer from swamping the database server.

Definitely load-test your per-page-load option first, though. That sounds like the most straightforward approach, don't go to a lot of work to address a performance issue that doesn't really exist.

1
  • Correction: if it does impose a heavy server load, then look into what can be changed (MongoDB would be one option, but never the first one). Never make assumptions about performance ... in many situations MongoDB is slower/more resource heavy than MySQL. Mar 6, 2016 at 21:54

Your Answer

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

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