MySQL Design For High Frequency Billing System

MySQL Design For High Frequency Billing System

I have a feeling I'm gonna get butchered for asking this question, but
here it goes...
I have 5 million daily subscriptions & expect/hope to have 50million in 12
months. I need to renew/bill these very, very quickly. I've tried every
permutation of index & looping I could think of, but its the SELECT
queries are still too slow. Perhaps my mistake is MySQL design, perhaps
its my daemon that uses MySQL, perhaps its simply because I'm using MySQL
- please let me know your thoughts and/or advice. Thanks!
** Subscription Table looks like this **
subscription_id (PK)
subscriber_id
service_id
add_date
current_period_start_date
current_period_end_date
next_bill_date (used to ensure two threads dont grab to bill at same time)
last_successful_bill_date
has_outstanding_balance
** Money-people-owe-me table looks like this *
id
subscription_id (UK)
outstanding_balance
next_bill_date
number_bill_attempts
(a fair amount of people dont always pay, I give them unpaid access for a
bit while I continue trying to bill, eventually I cut off their service
though)
** Billing Daemon looks like this **
Run multi-threaded on multiple machines, here is the master thread:
For each service
stuffToBill[] = SELECT stuff ORDER BY next_bill_date FOR UPDATE LIMIT XXX;
UPDATE stuff SET next_bill_date = later WHERE id IN (stuffToBill[ids])
COMMIT
Put them on a queue for billing workers
Running EXPLAIN shows that I'm using decent indexing, but nitty gritty of
the SQL combined with the fact that I am running the same daemon on
multiple servers makes it lock up / generally overload the I/O queue on my
DBM. DBM is quality hardware.
Thanks again for your advice!