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,

  • 2
    In a few years MySQL will have changed, your web host will have different pricing, HDD prices will have changed and CPUs will have changed. It is impossible to predict what will be best at that point. – Stu Feb 27 '13 at 17:43

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,

  • thanks for the excellent advices. For the beginning it's definitely safe to keep images (folder tree) and DB (mySql InnoDB tables) on one server, it has 60GB ssd Raid 1 and 3 GB ram with pretty powerful processor. For low income hosting it's best what I got. But in the long run, I think I will need. Q : Why raid 10 for mysql volume ? THe hosting has raid 1, and since it's SSD good qual Intel i guess, it's super fast. In the long run i'll probably switch to raid 1 with 7200 rpm standard hdd, since it encases more space and it's not expensive. or maybe ssd on my own server.. – Adrian Tanase Feb 28 '13 at 10:35
  • RAID-10 will get you more throughput than RAID-1. You may not need this for SSDs, however it also gives you an opportunity to scale, since you can add additional pairs of drives to a RAID-10 set. Your dataset may outgrow the relatively-small size of SSDs currently available; if you can't get a large enough array with RAID-1 you'll have to go to 10. – Jonathan Nicol Mar 1 '13 at 18:16

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.

  • i considered using InnoDB for row-locking (not table). The site uses a lot of AJAX small updates (like the Like button on Facebook) and also not very frequent regular INSERTS, and a lot of READS. Pictures are being kept in the tree folder, and database is only text and numbers used to display stuff on the frontpage. I also have 2 separate admins, which will bring a lot of traffic and also the browsing through the stuff they define, on the frontpage which will bring a lot of reads on the httpd and mysql side. – Adrian Tanase Feb 28 '13 at 10:29
  • 1
    I think the big thing is that it is important to become familiar with the sorts of solutions rather than worry about optimizing future problems away now. There's a good chance that in 10 years the problems you are facing are not the ones you thought you'd be facing then and solutions that complicate your code make it harder to solve new problems. I don't see anything wrong with what you are doing. – Chris Travers Feb 28 '13 at 10:39
  • i see....this is my very ideea too, start prospecting on solutions, not just worrying about hdd space, db size etc. but I didn't know how to put it right in my question. I need to know before i launch the website if, at some point in the future, I can migrate to other optimised solutions, because site will build up and everything needs to be flexible in adapting to optimized solutions. thanks :) – Adrian Tanase Feb 28 '13 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.