Scaling MySQL to 700 million inserts per day
In this technical retrospective (2010), @bluesmoon shares practical architectural lessons learned from running, scaling, or migrating production software under lean constraints.
Core Principles for Independent Builders:
Defaulting to simpler architectures (such as monolithic frameworks, SQLite, or managed VPS) prevents early engineering bankruptcy.
Evaluating true compute and egress costs reveals that high cloud bills can often be slashed by 80%+ through judicious re-platforming.
Code you write at 2 AM as a solo engineer must be easy to debug six months later without complex microservice dependencies.
Community Debate Themes:
HN engineers debated the tradeoffs between cloud convenience and raw server economics, database indexing strategies, and resisting premature architectural complexity.
CATEGORY Frugal Engineering →
HISTORICAL ERA Web 2.0 Genesis (2007–2013)
COMMUNITY SCORE ★ 39 points
TOTAL DISCUSSIONS 28 comments
A couple of points for those thinking about doing something similar:
"Since InnoDB stores the table in the primary key, I decided that rather than use an auto_increment column, I'd cover several columns with the primary key to guarantee uniqueness. This had the added advantage that if the same record was inserted more than once, it would not result in duplicates."
The 'correct' way to deal with this in MySQL is using the auto_increment_increment.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Of course, the real difficulty with mutli-master setups split across data centres isn't ensuring uniqueness of primary keys, it's ensuring data-integrity under a split-brain scenario, i.e. where one server can't reach the other, but users can reach one or the other. UPDATEs and DELETEs to rows can then become extremely difficult to merge back together. This wasn't a problem for this application, but as others have commented, this use case probably wasn't best suited for an RDBMS anyway.
This article was mis-titled: The title should be "how to get poor performance by using a completely inappropriate tool".
Quoth the article: "Several million records a day need to be written to a table. These records are then read out once at the end of the day, summarised and then very rarely touched again."
This isn't a job for a database. This is a job for a log file. You might want to use a database to store the summarized data, of course; but bulk data which is written, never updated, and quite likely only ever read once? That's practically the definition of a log file.
I wonder why they used a database at all: "These records are then read out once at the end of the day, summarised and then very rarely touched again."
And later on they end up writing them to a text file and bulk inserting them in 10K batches. Wouldn't it be easier to just write them all to a file then summarize at the end of the day? (It also says they do a DROP TABLE on each day's data.)
Why no discussion of the disk drives supporting this database? The write performance dropped to 150 when the disk starts getting pounded as, up to that point, everything was in a memory buffer. Does the temporal partition spread the file over multiple drives? What's the capacity and theoretical throughput of the drives?
Interesting use of tables, as previously commented, but we at room.ly surely appreciative of your post as we are currently on a PHP/MySQL diet and looking to reach critical mass soon after a few updates