I’m thinking of installing Piwik, an open source alternative to Google Analytics. It’ll be used on popular websites, sites that could potentially generate millions of hits a day.
Piwik stores its information into a MySQL database. So I’m wondering, how stable would MySQL be doing millions upon millions of writes a day? Size of the database I’m not too concerned about. Everyday reports will be generated and the data flushed, but during the day the db will be hit hard with writes.
Anyone have architectural choices? Different db engine for mysql? Maybe MySQL isn’t up to this task?
Troll. Mysql backed by sufficient hardware is absolutely up to the task. My production instance handles about 2000 qps, split evenly between reads and writes. That’s about 87 million writes per day every day and we haven’t run into a performance wall. Mysql 5.x + CPU + ram + sufficiently fast disk + a stable os and a competent admin can handle millions of writes per day.
I work on a MySQL-backed analytics system, and I’ve looked at Piwik a little bit, though this was already after we’ve rolled our own. If you’re really concerned about your need to scale writes (that is you’re not guessing about your traffic numbers, you’d bet your money they will be big immediately) , then you should invest upfront in a sharding solution. Certainly MySQL can handle quite a bit of writing, but you may find serious I/O or lock contention occurs during Piwik’s nightly summarization. In particular, counting distinct unique user cookies can be very intensive. It’s much easier to spread the load to several databases than to try to optimize throughput on one giant database, especially if you don’t need to do any cross-site reporting and each sites data is completely sandboxed.
Personally, I don’t think Piwik’s using MySQL as a raw event log is the best design unless you really need to report on raw events, which you really don’t for web analytics. But at the same time, Piwik is a great alternative to rolling your own if analytics is not the main focus of your business.