managing a growing database in the long run (10 years from now)

Question

I'm close to finishing-up a WEB app (PHP + mysql website portal), which will start slowly, as in uploading few pictures and inserting few rows in main database table, but soon, and over few years, I expect it to grow steadily and even logaritmically at some point.

By my Excel calculations, at some point, the database table data + indexes (innoDB) will overgrow the size of hosted pictures by a factor of 10, but in the beginning it will be the other way round.

So basically my portal hosts pictures (medium size jpegs let's say) and uses MySql InnoDB database tables.

My question to you all admins and professionals is about my MySql Database.

I'm thinking in advance so I don't get caught unprepared :

When my database will grow more and even get 1.2TB or 2TB or more....how can I deal with that ? Can I say split it on 2 or more dedicated machines so searches will still be fast and getting data from this big database ? Or shall I do 1 server and pump it up with a lot of cheap 7200 rpm sata ? or what can be the solution to a growing database.

My first configuration for this web-app is a VPS - SSD hosting with a 4ghz processor, 3GB of ram and 2x60 GB SSD raid 1.

thanks for your answers,

Answer 1

Here's some generic suggestions...

• Don't store the images themselves inside the database (it's unclear if that's your plan). This will cause you huge scaling problems. Store them in a filesystem, or a non-relational database if you must.
• The database should be a separate server from the app.
• Ideally you should have enough RAM to hold your working data set. Make sure you tune the database in advance (innodb_buffer_pool_size, innodb_log_file_size, etc).
• Put your mysql data on the fastest disks you can get. SSD or 10k+ SATA/SAS. Make sure the RAID controller has a battery backup and write caching.
• RAID-10 for your mysql data volume.
• Put mysql's binlogs (and possibly relay logs, and innodb transaction logs) on a separate RAID array, RAID-1 should be fine.

• Have a slave server for read-only queries, to take some load from the master. Your app will have to differentiate RW and RO queries. Set 'read_only=1' on that slave to prevent unfortunate accidents.

• As your dataset grows, you may need to consider horizontal sharding across multiple servers. MySQL doesn't provide a mechanism for this, so your app will need to.

• There's no reason you can't have a single 2TB database, but you will probably find performance unacceptable for data that doesn't fit in the buffer pool.
• Don't go overboard on indexing. Additional indexes beyond the primary introduce additional overhead (not to say you should have none). There may be good opportunities to use a composite index (multiple columns) instead of 2 separate ones.

Hope this helps,

Answer 2

There is a saying, premature optimization is the root of all evil. My recommendation is to keep an eye on emerging problems but not worry about the long-term implications. Focus on keeping your database as good as it can be and let the future attend to itself.

As far as storing images in the database, there are significant pros and cons here. I haven't done much of this with MySQL, but on PostgreSQL there are both some very nice aspects (atomic backups ftw!) of this and some really annoying pitfalls (argh, memory usage turning base 64-representations of bytea's into binary again!) so be careful, pay attention to tradeoffs, and be willing to reconsider both ways.

The next point is that in a few years, your application will probably be somewhat different than it is today. Usage patterns will likely be different too. Trying to solve problems today that you think you may see someday is a good way to add complexity that will make it harder to solve the actual problems you encounter tomorrow.

Once you start beginning to struggle it is a good idea to have a good idea of what your choices are. These include:

• Vertical scaling, focusing on more RAM, faster hard drives, more powerful servers, etc.

• Horizontal scaling and sharding, replication, connection pooling, and the like.

• Database abstraction through some sort of intermediary which can cache frequently used data (such as a NoSQL db, or memcached).

These are your main options. Every one though adds significant complexity and it is impossible to know now which ones will have the best tradeoff for your use case.

If you are worried about scaling in the short to mid term, the single most important thing you can do is learn about options and tools, and play around with those you can in a safe and sane environment.