Ask HN: Developing Web Apps around SQL tables that have millions of rows

Review your backups and contingency plans (and measure your restore times).

Usually you will want a mysql.conf for production mode, and a different one for full restore (with different parameters). A full restore of a so big database with the mysql defaults, can take looooooooong.

As an anecdote, in the most big mysql system I've ever touched, there was a "performance problem", when I got the credentials and started to review, 3 different sysadmins, had scheduled 3 different full backups daily, using dumps. Some of them at office hours.

Yes, this is just plainly stupid, but it's an example of real life.

On a big database system, backups (and restores) is something who is convenient to design with care, to monitor, and to measure/adapt periodically.

If you go to master/slave(s) usually you can make backups from some slave, without disrupt the master operations.

Also, you maybe interested in play with things like mysqlproxy to split reads/writes, so you can have writes going to master, but reads spread across multiple slaves.

I assume you're already using 64bits, and that you know already about mysql tuning variables.

As a BI developer, I'd also like to second the idea of being able to split the table up and changing your architecture. Ideally, you would leave your production database untouched and have a replicated database to work with. The replicated database will of course still probably be really massive, so you will want to create tables specifically for querying (attempts to simulate a data mart) where you only grab the data that you need for querying. Another idea would be to have cron jobs pre-query at say midnight so when your users hit the database, they are fetching pre-fetched data.

Also, if it's rising exponentially, I would have a look to see if all that data is really what you need. It's great to collect every single piece of data if you can but if you can't cover the costs to do that and there's no added value to all that data, it doesn't make sense to keep everything.

1) Optimize data type usage

2) Optimize indexing - removing redundant indexes, adding new indexes, changing existing indexes

3) Optimize storage - get your SAN/disks in order

4) Partition the data in such a way that you serve queries and users quickly

If you do the above, you should easily be able to scale any decent hardware into 10x where you are now. That will buy you some time to then build a data warehouse and backend support/reporting systems. At some point you're going to be in a spot where you either have to invest $5m+ in hardware to keep serving your current data, or you're going to have to start archiving some of this data to a data warehouse.

Relational, key/value, document, columnar, graph. Within each of those types there are specific implmentations (mysql, postgres, oracle, redis, riak, neo4j, titan, cassandra, mongodb, couchdb, etc), each with their own pros/cons.

Within the relational database realm, make sure you understand: when and when not to normalize/denormalize, clustered/non-clustered indexes, btree/rtree/hash indexes, schema design, storage engines, partitioning, master/slave, replication, caching strategies, execution plan optimization, etc..

In my opinion, it's not the amount of data that is the bigger issue, but the amount of reads/writes coming into the system. 100+ million rows with just 1 thread reading and writing to it really isn't a big deal, technically. If you have load issues, you might need to shard the data, do bulk inserts by buffering the data, increase the memory, cache commonly read rows in memory, etc.