Working With HUGE Database

#1
[eluser]nagolpj[/eluser]
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.

#2
[eluser]WanWizard[/eluser]
3 million records sounds a lot, but it isn't. I have a community forum running where one of the tables currently hold 26,042,383 records, and it serves 1,554,955 post messages to 121263 unique visitors last month.
This database runs on a dedicated Dell R200 with an Intel® Xeon® X3210 CPU, 4Gb memory and 2 SAS disks in RAID1. Without problems.

I think you have to seriously look at the sizing of your server, and possibly the MySQL configuration.

p.s. Naturely, if I start making structural changes to the database, I have to plan ahead, because it will max out the CPU for several minutes. Some maintenance window is needed for operations like this...

#3
[eluser]nagolpj[/eluser]
I think I'm going to give http://aws.amazon.com/rds a try (even though it basically shut the Internet down a few weeks ago). And we have 3 million listings but over 10 million listing photos in a separate table. I don't know all the hardwares specs but I think Amazon seems to be a good solution for now. Thanks.

#4
[eluser]mdvaldosta[/eluser]
Could you break that up into several databases? Honestly That'd be the best way to handle it. I once had a 4 gig database with 300k tables and it was a bitch to manage. If something broke, which happened about once a month, I'd end up having to do constant repairs that took several hours to complete and sometimes had to be ran a couple times. It was a nightmare. Splitting it off in a dozen databases fixed things up right as rain. No more crashes, no more long nights to fix db errors.

*edit - this wasn't on a dedicated db server, and it was several years ago when a quad core w/ 4 gigs of memory was pretty good. The issues were mostly related to memory useage and I'm sure with someone more experienced with tuning could have gotten a bit better. Nevertheless, it kept getting worse until I broke the db up. It's either that, or more power.

PSS - not a pissing contest, but I was also serving 40k uniques per day and 10mil 'post' messages. All on one server. Again, smaller dbs are better when that's possible. If you're going to be searching through all 300k records for one query - that'll be tough to do. But if it's a bunch of localized listings then you could probably get around to separating them by state or agent/company or whatever.

#5
[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.

#6
[eluser]gh0st[/eluser]
Make sure you are using the INDEX property in MySQL; especially on tables where there are joins like Junction tables or Joined tables.

Using INDEX can increase the speed of MySQL a lot.

If all else fails, perhaps look at using MongoDB; but that would be a lot of re-coding, learning new stuff, prototyping and stress testing.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.