Hello-
I have a community site that relies heavily on Domain Access and Date/Calendar along with some custom Panels and of course, a lot of CCK/Views.
I switched my site over to a "high performance" host back in July using Pressflow, Aegir and NGinx that claimed to handle everything for high performance sites... and I'm being booted for using too many resources.
My site is live but has relatively few "real" visitors but a lot of spider traffic and tons of nodes. (I have a lot of repeating events far into the future.)
When they first started accusing me of abuse, I ran Devel and found the Mini Calendar query (and most of the other Calendar generating queries) was the biggest offender, even when I set it to cache for 6 hours. Although it is a fairly critical feature, I dropped it... and a few weeks later, they are booting me because "something is wrong with your database and it's using too many resources."
I don't know what's wrong with the site. There must be something bad going on, but I'm not doing anything out of the ordinary as far as I know. I guess I will be returning to Apache hosting, but I want to be able to diagnose the "abuse" and fix it if possible.
Modules Installed:
Add another 6.x-1.6
Admin links 6.x-1.8
Administration menu 6.x-3.0-alpha4
AdSense 6.x-1.3
Advanced Forum 6.x-2.0-alpha3
Advanced help 6.x-1.2
Alinks 6.x-1.0-rc1
Author Pane 6.x-2.0-rc1
Auto Assign Role 6.x-1.2
Autoload 6.x-1.4
BeautyTips 6.x-2.x-dev (2010-Sep-10)
Better Formats 6.x-2.x-dev (2010-Jul-11)
Block Theme 6.x-1.0-beta1
Cache 6.x-1.x-dev (2009-Oct-08)
Calendar 6.x-2.2
CAPTCHA 6.x-2.3-rc2
CCK Fieldgroup Tabs 6.x-1.2
Chaos tool suite 6.x-1.x-dev (2010-Sep-10)
Comment Notify 6.x-1.x-dev (2010-Jul-11)
Computed Field 6.x-1.x-dev (2010-Jul-11)
Content Construction Kit (CCK) 6.x-3.x-dev (2010-Aug-25)
Custom Breadcrumbs 6.x-2.x-dev (2010-Aug-23)
Date 6.x-2.6
Devel 6.x-1.22
DHTML Menu 6.x-4.x-dev (2010-Jul-11)
Domain Access 6.x-2.5
Domain Actions 6.x-1.x-dev (2010-Jul-11)
Domain Blocks 6.x-1.3
Domain Taxonomy 6.x-1.0-rc1
Email Field 6.x-1.2
Fancy Login 6.x-1.5
Feeds 6.x-1.0-beta5
Flag 6.x-2.0-beta3
Flag Note 6.x-2.x-dev (2010-Jul-11)
Global Redirect 6.x-1.3-alpha1
GMap Module 6.x-1.x-dev (2010-Sep-09)
Google Analytics 6.x-3.x-dev (2010-Sep-19)
ImageAPI 6.x-1.8
ImageCache 6.x-2.0-beta10
IMCE 6.x-2.x-dev (2010-Aug-29)
IMCE Wysiwyg bridge 6.x-1.x-dev (2010-Jul-11)
jQuery plugins 6.x-1.10
jQuery UI 6.x-1.4
Keys 6.x-2.0
Libraries API 6.x-1.x-dev (2010-Jul-11)
Link 6.x-2.9
Location 6.x-3.x-dev (2010-Jul-11)
Location Feeds 6.x-1.3
Masquerade 6.x-1.4
Mass Change 6.x-1.0-rc
Mime Mail 6.x-1.0-alpha6
Node clone 6.x-1.2
Node comments 6.x-2.x-dev (2010-Jul-19)
Node Convert 6.x-1.6
Node Reference URL Widget 6.x-1.6
Panels 6.x-3.x-dev (2010-Sep-10)
Pathauto 6.x-1.4
PHPMailer 6.x-3.0
Privatemsg 6.x-2.x-dev (2010-Sep-08)
Protect Critical Users 6.x-1.1
Publish Content 6.x-1.4
Quick Tabs 6.x-2.x-dev (2010-Jul-11)
Quote 6.x-1.1
Read More Link (Drupal 6 and earlier) 6.x-5.0-rc7
Redirect 403 to User Login 6.x-1.2
Rules 6.x-1.1
Scheduler 6.x-1.7
Smileys 6.x-1.x-dev (2010-Jul-11)
SunMailer Newsletter 6.x-1.6
Superfish 6.x-1.6
Tab Tamer 6.x-1.0
Tabs (jQuery UI tabs) 6.x-1.x-dev (2010-Jul-11)
Tabs panel style 6.x-1.0-rc6
Taxonomy Menu 6.x-2.9
Token 6.x-1.14
Token Profile 6.x-1.0-beta1
Transliteration 6.x-3.x-dev (2010-Jul-23)
User Points 6.x-1.x-dev (2010-Jul-15)
User points Nodes and Comments 6.x-1.x-dev (2010-Jul-11)
User Stats 6.x-1.x-dev (2010-Jul-11)
Views 6.x-2.11
Views Bulk Operations (VBO) 6.x-1.9
Views content cache 6.x-2.2
Views Custom Field 6.x-1.x-dev (2010-Jul-11)
Websnapr Field 6.x-1.2
Wysiwyg 6.x-2.1
So, that's about 90 modules. A few are installed but not being used, even. But there's nothing custom or unusual.
Outside of the standard Advanced Forum, Advanced Profile Kit and Calendar views, I have less than a dozen active views.
Devel average on my home page reports:
Executed 476 queries in 579.94 milliseconds. Page execution time was 1576.04 ms.
Executed 533 queries in 889.15 milliseconds. Page execution time was 2757.68 ms.
Executed 453 queries in 920.93 milliseconds. Page execution time was 2042.33 ms.
That is from loading the home page, then refreshing it twice. Memory used at: devel_init()=1.72 MB, devel_shutdown()=33.14 MB.
path_alias_cache_lookup_path is called for every taxonomy term in my menu and makes up a vast number of the queries on the page... I have a fairly intricate directory listed by taxonomy terms in the secondary menu... on every page. Probably around 280-320 of the queries are path_alias_cache_lookup_path ones.
The next most frequent queries are UPDATE cache_views queries... even when the page is just being refreshed.
Any help appreciated; I've been reading and trying things for weeks and I'm not even sure how to diagnose the issue, other than I'm being told my site is "abusive." If more details are needed, I'm happy to provide them.
Thanks!!
Comments
I have a fairly intricate
That is bad.
What you can do is wrap this in some custom code that places the info in cache using cache_set() and then retrieves it using cache_get(), and keep this in the cache for say 15 minutes or so.
Then you use memcache, so you are not using the disk at all.
There is some code here that you can use as a guide (under "Solutions").
http://2bits.com/articles/case-study-views-quicktabs-and-templatephp.html
That is odd.
But if nothing else, memcache would help here, since it will avoid hitting the disk on updating that cache bin.
Another thing to do is to enable caching for all or most of the views you are using.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
Hmnn... it's all supposed to
Hmnn... it's all supposed to be super-cached everything... that's how they push the service. The whole we-manage-and-optimize-everything. But it looks like most of the queries are putting things into the cache everytime the page is accessed. But I'm not entirely sure what I'm seeing or what it means. But I keep trying to figure it out!
I don't mind caching the menu for up to a week or even a month at a time- it rarely changes. Do you think I would be better off to just hardcode the menu items via add menu and update them manually? Or would I still have the path_alias_cache_lookup_path queries even with a hand input menu system?
Thanks sooooo much for your help- at least I have a place to start now.
Just another oddity that I
Just another oddity that I don't understand- my Pressflow install shows that Memcache is installed.
Memcache extension 3.0.3
It doesn't seem to be utilized though? I'm not sure how to check that. Thanks again!
For memcache to work you
For memcache to work you need:
The memcache module is not enabled like regular Drupal modules. There is a memcache_admin module that is enabled like regular modules, but it's only to show stats and is optional. The actual memcache module is enabled through settings.php configuration.
Installation instructions
Further information on installing and configuring drupal memcache is available here: http://drupal.org/project/memcache
Thanks for the help. I don't
Thanks for the help. I don't have access to settings.php or anything else that doesn't live in the sites/ folder because I'm on an Aegir platform. No shell access either. Just as well for me to get back onto an Apache VPS where I have some control again.
settings.php does live in the
settings.php does live in the sites/[domain name]/ folder.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I said that wrong... yes, it
I said that wrong... yes, it does, but in my case it references a global document that handles the caching directives, site settings, etc.
aegir/config/includes/global.inc
My local document only handles the minimum database connect info and php settings.
The global document is where the host (I assume) has put the various cache settings. I guess I could try putting in more cache settings and seeing what happens, but they are now anxious for me to get off their server so it seems pointless to mess with their settings. Once I get moved back to a VPS I control, I'll dig a little deeper.
I'm still curious about the path_alias_cache thing though- I'm on Pressflow, I have the path alias cache turned on. When I turn it off, I get the same number of path queries taking about the same amount of time, they just use _drupal_lookup_path_direct instead.
I may be thinking about it wrong, but I thought the cache would save it so it didn't require a query for every menu item on every page load... if it doesn't, I'm not sure I understand why bother?
It almost sounds like things
It almost sounds like things are not being either written to, or retrieved from the cache correctly. Thus causing everything to regenerate on every page load.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I actually just had the same
I actually just had the same problem. It appears Pressflow was choosing Drupal's hook_lookup_path() over Path Alias Cache because of their weighting. I changed the weight of Path Alias Cache to -1 and my number of queries was reduced substantially.
The implementation that chooses which hook_lookup_path() to use is pretty rudimentary and prone to issues -
<?phpif (!isset($hook_module)) {
$modules = module_implements('lookup_path');
if (count($modules) > 0) {
$hook_module = $modules[0];
} else {
$hook_module = FALSE;
}
}
?>
module named drupal?
Do you have a module named drupal?
<?php$modules = module_implements('lookup_path');
print_r($modules);
?>
doesn't contain drupal_lookup_path on my box. What does it look like on yours? Better yet what does this output on your box
<?php$list = module_list(FALSE, TRUE, FALSE);
print_r($list);
?>
Is drupal in that list?
I've now been told that this
I've now been told that this is normal with Devel enabled. So if Devel writes to the cache with every page load, how do you ever get a benchmark or idea of what the real page load times are? Is there a different tool I should be using?
Thanks again for all the help!!
For uid 1 only
If you have only one admin on the site (uid 1), then devel being enabled will only come into play for requests for that user.
It should not affect traffic from other users, let alone anonymous.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
Are you on a shared host?
Are you on a shared host? Consider switching to VPS or even dedicated hosting. This way you'll get guaranteed resources and no claims of abuse.
-Natalie
Friendlydrupal.com - Drupal Video Tutorials
Yes, already on a VPS.
Yes, already on a VPS. Turned out the Calendar module was the culprit, generating huge overhead queries. I had to turn it off and have had no issues since.
I had simply used the default Calendar views, substituting a CCK date value but the resource usage was up to 60-90 seconds per query. Apparently, setting caching on the Calendar causes issues with the cache_views_data table.
http://drupal.org/node/282845
Then it doesn't make sense.
Then it doesn't make sense. You can't use more resources than allocated, that's the whole point of virtualization. Either they're lying or they're doing something wrong.
-Natalie
Friendlydrupal.com - Drupal Video Tutorials
That's only true for CPU and
That's only true for CPU and memory. You can be causing excessive disk I/O.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
My money is still on memory
My money is still on memory and CPU. Perhaps they're overselling.
-Natalie
Friendlydrupal.com - Drupal Video Tutorials
Apparently Aegir isn't a true
Apparently Aegir isn't a true VPS system? They sell it as a VPS, but it seems to be a shared environment.
No, it's a hosting
No, it's a hosting platform.
If you got a VPS, then you should have VPS control panel and should be able to access your server as root. Otherwise, you're on a shared hosting.
-Natalie
Friendlydrupal.com - Drupal Video Tutorials
Patch to core module
Here's a solution (patch) to the core module.
custom-path_alias_cache-cache.patch
It's been in production for the last ~4 months without operational issue.
David.
Similar
Patch is similar to this idea of mine
http://drupal.org/node/1327720
Do you have an issue where this patch is talked about?