3

I have a database that was running on a propitiatory system with a hosting company. The data is about 10 years old and mostly text. The database it too large aprox 300GB. We are trying to migrate the database to Drupal and I am wondering if this is a good idea and whether drupal/mysql could support such large databases or not. I have a few thoughts such as archiving the data and using caching tools such as varnish. Is this a good idea? Can drupal support/serve it? What impact could this have on performance?

Edit: We have already migrated the database but performance is a extremely bad. ~17 million nodes, and ~90 million comments. What could I optimize? what kind of infrastructure would you recommend? caching and architecture? Load balancing? Any hints or tips that could help run such big site with drupal is what I need. Case studies if anyone is aware of.

  • Does that 300GB database grow over time in that old system? Or is it "read only" until you finish the migration? – cherouvim Oct 9 '12 at 15:33
  • 3
    What sort of data are there in that 300GB database? And how much of that will you want to migrate? Sometimes DBs grow huge due to secondary tables such as log tables, cache tables or by heavy denormalization. – cherouvim Oct 9 '12 at 15:34
  • What is the source database? MySQL? Oracle? – cherouvim Oct 9 '12 at 15:35
  • 1
    @Cherouvim that's very true. Usually removing that junk stuff clears almost 80% of size. – AyeshK Oct 9 '12 at 15:40
  • 1
    D6 or D7? What version of MySQL is the target DB? What version of PHP? So you have migrated everything over and now you're wondering about performance, is that correct? – mikeytown2 Oct 9 '12 at 21:57
2

Drupal can support any size of a database. Although as a CMS, Drupal is fairly light, it's still a lot more resource hungry than a simple coded site. It will almost definitely require more hardware resources, which can partially be addressed by caching.

I hope I don't get boo'd for this, but I did see a fair number of large sites switch away from Drupal, such as Realself, which had 150,000 nodes, you can read the story here.

If you decide to stick to Drupal, I'd recommend the following:

  • Use caching. I personally use a combination of Memcache, Boost modules and Drupal's core caching modules. Memcache stores the recent & most used queries in memory for fast retrieval; in your case it would seem like a good choice. Boost is great for serving pages to anonymous people or to handle floods. It caches pages by generating a page once and serving the same page until it expires.

  • To increase the database performance, you can also host your database on a cloud, such as Google's BigQuery.

  • Last, but not least, give Pantheon or Acquia a try. Both of these companies employ some of Drupal's most talented people, providing scalable hosting and management of large Drupal sites. They also take care of all your caching needs, which will probably cost you less than hosting yourself.

2

The answer is Yes. First - because you are asking on Drupal Answers site. I would recommend you using configuration like this:

  • 0 Load balancer managing several servers
  • 1 Varnish (proxy cache)
  • 2 Nginx (php-fpm)
  • 3 Drupal
  • 4 MySQL (Master+Slave)

Also memcache can be used. The main problem will be when you will try to update/insert new record in DB. All read queries will be managed by caching-proxying systems. If you need text search - use ApacheSolr. Try to not views or panels, with such DB size you can have perfomance issues.

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