Schema design for metered billing depending on account usage Sorry if this has been asked somewhere else, but so far I haven't found any questions like this, or can't find the words to search for.

TL;DR: What's the best way to implement counters in the database to track usage for billing purposes? Specifically when those counters will reach into the 10's or 100's of thousands?

I have a LAMP application that allows users to send out emails (Laravel, MySql).  I want to start charging them for "email usage" and imposing monthly sending limits, which if they exceed, I will charge them extra, rounding to the next thousand.

So from a database standpoint, how can I make this happen in a way that is stable and maintains integrity, while not burning up all my CPU with updating counters?  I also would like to record usage month by month for good record keeping.  Here is what I'm considering:

accounts:
    account_id
    company_name
    email_limit (counter integer, example might be 20,000)
    email_usage (counter integer, example might be 13,425)
    ...

users:
    user_id
    account_id
    email
    ...

email_history
    account_id
    period (date field or something to know which month it was)
    email_usage (the historical usage they used that month)
    email_limit (the historical limit they had that month)


In this scenario, my system would process, let's say 5000 emails at once, and increment the email_usage counter 5000 times.  On the other hand, I could just use an internal counter ($i) and when the process is complete, just add $i to the usage counter.  But what if there's an error during processing?  I could lose my counter half way through.

Another way I could think to do this would be to save batch operations, so when we process 5000 emails at once, we just record that:

accounts:
    account_id
    company_name
    ...

users:
    user_id
    account_id
    email
    ...

email_batches
    account_id
    date (date batch was processed)
    emails_sent (integer, total emails in batch)


That way, when it comes time to process billing each month, we just SUM(email_batches.emails_sent) WHERE MONTH(date) = [this_month] and process that.

We're going to be billing people based on this mythical usage tracking, so any advice from someone who's done it before would be appreciated.

UPDATE: My project's been put on hold for a short bit, but I will definitely mark an answer when I start working on it again (this is a high priority, so it won't be long).
I regularly handle tables with 250GB of data that needs "counting" (Example: https://phabricator.wikimedia.org/T124676).

Let me start by saying that MySQL is not a good place for these kind of queries- its focus is OLTP (in a nutshell, short reads and writes in parallel), not OLAP. There are multiple storage/computing models (such as column-based data stores, or datawarehouse solutions that allows map/reduce jobs in parallel) that would be better suiting for your task, so if this is a business-critical/very frequent part of your needs, I would suggest setting up a complementary service that allows easier/faster handling of these kind of tasks. The reason is that counting things on MySQL usually require "snapshoting" and reading all rows one by one, and that can only be done on a single thread.

Having said that, your needs may not merit a full-blown solution, and only make that specific need faster. You can implement analytics by implementing such functionality on regular MySQL objects. The implementation will depend on your requirements, and that is mainly: "freshness" of your data, its dynamic nature, concurrency and if it is append-only.


If your counter does not need to be 100% accurate, you can run counting jobs every X time. Make sure no more than 1 job is running at each time by locking on a particular resource. We do such things with special pages such as: https://en.wikipedia.org/wiki/Special:WantedPages
If data is not always modified, but it needs to be more or less up to date with the rows, you can start a background thread that gets notified when data changes. If it gets notified several times before it runs, it de-duplicates the job by updating it only once. We do such a thing for Category updates: https://en.wikipedia.org/wiki/Category:Contents
Both of the "counters" from the previous tables are stored on auxiliary tables.
If you need to have perfect counting, you need to cache it with a trigger-like execution. It doesn't need to be a on server side, but be part of the transaction that updates the row. This will be more costly, as it will increase the latency of each individual DML, but it may be interesting in some cases. In our model, user have (or at least, used to have) an edit counter that it is transactionally updated every time an edit is created or deleted.
If your data is append-only, you can avoid counting all data every time, and only count the most recent records. Store the last counted record and then the total/per month/yearly/etc. count on another column, per item. We use such a model for counting things like number of visits per article.


In general, with MySQL you are forced to count- whether you cache or precache that value is a question of handling caches, which is a very common problem (although as you may know, not a simple one).

One common mistake is to store a single counter field when there is a lot of concurrency- e.g. visits for a webpage. For such cases you can distribute your counters according to your structure (e.g. one per fronted) or just use some kind of hashing table so you do not have to serialize every single update. On actual counting, you can sum the much fewer counters, which will be easier than counting the individual values.

If you need to speed up counting, there are even frameworks such as Shard-query that could simulate the panellization of queries for MySQL to some extent.

The summary is that you have 3 options: make things consistent, make them fast or make them cheap, choose 2 :-)