[eluser]n0xie[/eluser]
[quote author="nagolpj" date="1304990644"]I have a site with over 3 million real estate listings and the MySQL database is HUGE. I was wondering if anyone could guide me in managing such a large DB. I just crashed the site today by modifying a field type in the table. Not good. Is there any such thing as cloud DB hosting. I'm thinking about hacking up CI code to allow insert and update query to post to a second DB simultaneously as method for backing up.[/quote]
Are you running a memory cache (like Memcached) in front of your persistent store (MySQL) ? If the listings won't change much you might be better served just setting up a Memcached cluster with a long TTL, essentially minimising the number of requests to your persistent store. This will have the added benefit that whenever you make a change to your MySQL schema, most of your site will still be 'up' and only a few edge case queries will fail. (if you set it up right anyway).
You might also want to consider migrating (some) of your data to NoSQL. Estate listings are typical use cases for key/value stores. Another option is looking at MySQL clustering/sharding.
There are dozens of options. You might think that 3 million is a 'big number' but MySQL can handle far more than that.
