3

Last week I inherited a several-year old messy implementation of a mid-sized Drupal site. Short version: 1 NFS/mysql server, 3 Apache servers. Everything was overloaded and crawling along. The first thing I did was add a memcached server to the mix which cut the network traffic to the mysql server a lot. Next I got the code all running on local file systems using VCS and only used the NFS mounts for the files directory. Again, network traffic went down.

The CMS was actually an extended install of Pressflow dated at v6.20. I had the bright idea to update this to 6.26, which I did using the Pressflow 6.26 package. Other than a few conflicts with other modules that had to be resolved, this went pretty smoothly. The site works fine.

However, looking at the interface stats on the mysql server, since the upgrade the ethernet traffic is about 100% more to handle the same site load. It's been three days now and the pattern is the same. The network traffic hugely increased corresponding to the upgrade to 6.26. The metric I'm using here is bytes transferred in and out of the DB server. Unfortunately I don't have stats about number of queries run.

What changed between 6.20 and 6.26 that would cause this and is there anything I can do to mitigate it?

  • @Citricguy: Everything is in revision control and looking at the logs, there were zero changes to the apache configs, .htaccess or settings.php files during this period including the upgrade to 6.26. The load on memcached looks identical before and after the upgrade. – Caleb Sep 1 '12 at 9:20
  • 2
    Consider trying newrelic.com. It gives you live reports of the executed queries among many other things. Both a log of the slowest actual queries and the grouped by table. We recently used it successfully to track down a huge performance issue on a larger site. You can test it for free, then it costs per server/month. – Berdir Sep 2 '12 at 10:36
3

Apart from the NewRelic suggestion, which I put in a comment, consider reproducing this issue in a second environment, I assume you do have a staging/testing environment of the site, or at least a local installation. If not, you should get one ;)

Then you can install the devel.module, enable query logging and/or xhprof profiling. Then you can actually switch between the old version and the new one and compare the difference within Drupal. Devel's query logging will tell you exactly which queries are executed and how long they took.

  • 1
    We're onto something. The devel module's query log is showing about 10k queries being run per pageview. Me-thinks that's excessive. – Caleb Sep 8 '12 at 13:22
3

We had kind of the same experience some days ago when upgrading from latest Drupal 6.26 to Pressflow 6.26. The migration was done within a few hours and everything started smoothly but when the traffic came in we saw a 20% higher CPU load on average and significantly higher activity on the database server. This was quite surprising and we are still investigating the reasons. We already run memcache, XCache, CDN to reduce Apache and DB workload. After analysing slow-queries (from the MySQL slow query log) we found some ankers to start some optimization work but still it does not explain the increase in CPU and DB traffic for me. Additionally we will head towards Varnish to get requests away from the Apache.

2

Unfortunately, I think you have too many variables to really point to what could be going on. You upgraded core and also updated contrib modules(?), so the issue could really be with both. I also think that profiling based on net traffic is open to too many errors.

It also sounds like you have the files directory mounted on the same machine that machine that runs MySQL? You also are measuring your load based on network IF stats, which would be both MySQL traffic and NFS traffic? If this is the case, then your problem may not really be on the MySQL side, but rather with the NFS side.

Personally, this is what I would do.

Disable memcached, run your Drupal site for a few days, and then run mysqltuner. Tweak your my.ini as needed. This will get your MySQL instance at a good starting point. Then you can re-enable memcached.

Go through your Drupal settings and double check all of the performance related ones. Make sure JS and CSS aggregation is on (could be a major source of network traffic to the files dir), and make sure your caching rules make sense for your organization.

Look at your Apache config and check your caching rules. Drupal has URL cachebusting, so there is no real reason to not have aggressive caching turned on. Look at the .htaccess for HTML5 Boilerplate for some ideas. Implementing these has has drastic improvements on Drupal sites for me. In your case they would reduce requests for images/JS/CSS which would decrease load on your server.

Then go through your files directory, and see what it is being used for. If you dumped image cache, then traffic could be from that and will level off when all of your image derivatives get rebuilt. It could be from backup/migrate files not being compressed now. Check the other things in files, too. And then figure out a way to get files off of NFS.

As for the Drupal/Pressflow side, I can't think of anything that would account for major DB load. If you do think this is the issue, then you need to go through the CHANGELOGs for both Drupal and Pressflow. However, I think that Pressflow was just tracking Drupal changes at that point.

  • No, I split NFS off to a different server first. The traffic on that is a few megs an hour. The traffic to the mysql server rarely drops below 10Mb/s. I realize the if stats aren't a great metric, but we're talking an instant doubling from ~5Mb/s to a constant ~10Mb/s under the same user load so I feel like it's a solid indication something changed. – Caleb Sep 1 '12 at 13:24
  • There is next to zero traffic to the files dir. Most things that get served from files are also edge cached on a CDN so js/css/images etc are not really a load factor. The problem is with the Drupal->DB interaction somewhere. – Caleb Sep 1 '12 at 13:26
  • Thanks for the leads on tuning mysql and apache, I'll take a look. – Caleb Sep 1 '12 at 13:27
  • You still need to verify that the traffic is actual mysql traffic and not something else. – mpdonadio Sep 1 '12 at 13:47
  • The traffic is mysql. There are no other ports open. tcpdump confirms. I don't know the insides of Drupal that well but networking I know. – Caleb Sep 1 '12 at 13:49

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.