How should I structure my database to gain maximum efficiently in this scenario?

Question

I'm developing a PHP script that analyzes the web traffic of my clients websites. By placing a link to a javascript on the clients website (think of Google Analyses), my script harvests information like: the visitors IP address, reference link, current page link, user agent, etc. Now my clients can view these statistics via a control panel that I have build. These clients can also adjust profile settings, set firewall rules, create support tickets and pay invoices.

Currently all the the traffic is stored in one table. You can imagine that this tabel would become very large as some my clients receive thousands of pageviews per day. Furthermore, all the traffic data of each client would be stored in the same table, creating a mess. This is the same for the firewall rules currently, and the invoice and support system. I'm looking for way to structure my database in a more organized way to hold large amounts of data of multiple users.

This is the first project that I'm developing that deals with so much data, and would like to hear suggestions and tips. I was thinking of using multiple databases to structure the data. The main database will store users data (email,pass,id,etc) admin/website settings. Than each client will have an unique database labeled prefix_userid, which carry tables holding their traffic, invoice, and support ticket data.

Would this be a solution, and would it slow down or speed up overall performances (that is spreading the data over muliple databases). I have a solid VPS, but would like to safe and be as effient as possible.

Answer 1

Sounds like you need a schema for each client.

i.e. you have the same set of tables you presently have but defined once per client.

Your client would only access the table belonging to their schema.

While this may make it easier to organize and track each client I doubt there would be much difference in performance. Writing 1GB to 20 tables vs. writing 20GB to 1 table is pretty much the same thing as far as the file system and hardware are concerned.

Answer 2

I think you should separate some things since you are talking about different applications (stats/firewalls/transactions) which you just by accident show in the same interface. It is not needed to implement back-end in the same way.

Likely the parts: invoices, firewall etc. will not have a huge load as I estimate based on your question.

Statistics surely do, and they need to be processed fast initially because they can slow down websites.

Now because you can separate stats out of the normal database you can think of the stats as a separate thing. Most statistics don't need a relational database (though it can work perfectly fine). You don't need transactions, locking etc. So select a storage method which fits, could be a flat mysql, mongodb, etc.

Also rethink: Why would you do this yourself, couldn't you use for example Google Analytics or Piwik and use their API to show the same results to the customer?

Building reports from it is something you can do later so don't try to do smart things when logging the view. Just keep that part simple. Then you will have the best chance of a fast response.

So don't see everything as the same, see each application separately and work up to the best solution for that issue.

About you idea of sharding (database per client) see:

http://highscalability.com/blog/2009/8/6/an-unorthodox-approach-to-database-design-the-coming-of-the.html

It's a lot of work to get it right so be sure you need it.