Scaling MYSQL database for a multi site Drupal installation with 20,000 visitors per day

I have a multisite Drupal installation with about 20 urls pointing to the same code base and a common database. I have around 20,000 visitors visiting all sites daily which I hope would increase.

I am using 2 servers currently- one being the webserver Apache 2 on Linux Platform and the other is the database server- MYSQL.

Offlate my MYSQL server is being unable to handle the load and starts behaving erratically - sometimes issuing warnings, sometimes not returning data, sometimes timing out. I have tried all optimizing techniques but the problem has seemed to increase.

I would be very thankful if someone could suggest me a solution to the problem. Would doubling the capacity of server help or should I head towards using a cluster or cloud? Also would a clustered web tier with load balancer be useful or just using a clustered db with optimization is a viable solution?

I came across this site http://www.johnandcailin.com/blog/john/scaling-drupal-open-source-infrastructure-high-traffic-drupal-sites . However I am confused about which architecture to choose

@Fabian, I did try the boost module. The load on the Webserver ain't much, its the MySql server thats driving the site up the wall. I checeked the queries as well and removed the inefficient queries. Still the problem persists. Nevertheless, Ill try memcache. Is scaling of no use?

Loveleen Kaur 2010-09-15 08:54:16

You can always throw more hardware at the problem, but without good caching Drupal is just slow. Memcache is definitely the first thing you should try. And Boost completely bypasses the database for cached pages, but if your boost cache is invalidated to often it will slow down your site.

Fabian 2010-09-15 09:07:05

@Fabian, I have another problem. I have been also facing content access problem in my sites. I had posted a query on it and the suggested solution was to disable the cache because of of some mandatory modules like Domain access conflicting with the core cache mechanism. Would your suggested solution be still viable in this scenario?

Loveleen Kaur 2010-09-15 09:25:01

I don't know if they are compatible, you'll have to ask someone familiar with Domain access. Edit: I quickly googled and found http://drupal.org/node/778044, which seems to support the idea that the modules are generally compatible.

Fabian 2010-09-15 10:26:51

I did take care of the domain access settings for file inclusion to be placed at the bottom of the settings.php file. Thanks for all the help Fabian. I highly appreciate it!

Loveleen Kaur 2010-09-15 10:55:21

ansaurus

tags:

views:

answers:

Scaling MYSQL database for a multi site Drupal installation with 20,000 visitors per day

related questions