Drupal taking too much resources, so provider not supporting
Well, looks like my ISP has told me they can no longer support my web site - because Drupal is too expensive in terms of mysql resource usages. They have put in limits to the amount of time queries take, and while I can access my site and navigate, looking at the administration page which displays logs now fails, and there are also intermittent failures when adding/updating nodes.
What fails 100% of time time is the log display - these error messages are thrown up - now, this is a new change at my Hosting Provider, so not sure if other packages are also impacted, but my small, low traffic site (< 1000 page views per day) is causing Drupal mysql queries to take too long - is this a Drupal problem? Or are the limits at my provider too low? In any case, since this is a hobby site, I'll try to look for another provider, http://www.hostmonster.com/ looks good, if anyone has Drupal running correctly on it, let me know.
Current provider errors:
Warning: Lost connection to MySQL server during query query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (2, 'php', 'Server shutdown in progress\nquery: SELECT DISTINCT(type) FROM watchdog ORDER BY type in ../drupal/includes/database.mysql.inc on line 121.', 2, '', 'http://...../drupal/admin', 'http://...../drupal/user/whome', 'IP address', 1174575880) in ../drupal/includes/database.mysql.inc on line 121
Warning: Lost connection to MySQL server during query query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (2, 'php', 'Server shutdown in progress\nquery: SELECT w.*, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY w.wid DESC LIMIT 0, 50 in ../drupal/includes/database.mysql.inc on line 121.', 2, '', 'http://../drupal/admin', 'http://../drupal/user/whome', 'IP address', 1174575893) in ../drupal/includes/database.mysql.inc on line 121
That can happen
Here's a thread with a similar question. They have 197 queries on a page. I don't know what's typical in terms of # of queries per page for modern CMS's and frameworks, but it seems that we sure get fat and happy with fast bandwidth nowadays. :)
I'd be really curious what's a typical number of queries for Joomla and xoops and other systems out there.
The database issue stung us on 1and1. Couldn't hack it. However they run their database server on a separate server, not localhost, which has other pros and cons. We ended up taking the site to Networkredux and things are pretty damn fast and happy there. And this is just for a beta site with no users yet.
So, to actually try and help answer your question, could you limit the log entries to only store for 1 hour and see if that limits the admin db queries. On second thought that might not really help, but maybe a start...
Good luck,
Jason
I'm very curious what kind
I'm very curious what kind of "pressure" my site gives to my server. How can I found out about the number of MySQL queries? Is there a setting somewhere to show this, or a special parameter, or what exactly?
Ludo
install the devel.module it
install the devel.module it will produce all of this kind of information.
Yes, watchdog was a problem
Thanks for the tip - reduced watchdog to 12 hours (3 days would still fail), and now seems to handle the load fine - it has only 20 messages in the log.
But - looks like this is a known issue,and watchdog is a required module, and in drupal core, so is this a bug that should be fixed for 4.7?
This issue makes it a real problem to start small, for small sites to use Drupal, since I suspect all low-priced hosting providers will setup limits, and when it starts taking over 15 seconds to display the log, it looks like a real problem with Drupal that should be fixable....
And even for sites with high traffic, given the poor performance of watchdog, even 1 hour may be too much in the log for the site to handle - the mysql queries to get that data and paginate it will kill that site... may need watchdog to be able to be set for #entries instead of a time? That way the db impact can be limited irrespective of server load.
This was only one of my problems, this watchdog display page failed all the time, but I also have intermitent failures when I submit a node... oh well.... I guess Drupal is not really suited to low-resource sites...
why was watchdog table so large?
In my database of size 176.3MiB, watchdog table was 172.7MiB.
I have cron running regularly, is there a problem in cron that the watchdog table was not being truncated? Or is it normal to have so much huge overhead, and need to do manually run mysql optimize command as mentioned in the Drupal performance pages?
Anyway - I ran optimize on the watchdog table, it went down to 3.6MiB
Queries for the logs are now lightning fast - from over 2 secs down to < 100ms for the log page.
So, if you have a small site, and have performance problems, especially in admin pages, look at the watchdog table size and run optimize if it is very large.
strange
That seems freakin' large. One of our sites keeps logs for 2 weeks, and the watchdog table is currently about 2.8megs. It's a moderate traffic site, looks like some activity and an entry every couple minutes or so.
Could it be something with your table engine on the mysql database? Aren't some engines more efficient (innodb vs myisam)? I'm not sure off the top of my head.
I believe there's a db maintenance module that can run things like optimize on your db with your cron. Could be worth checking out.
Jason
i got the same problem
my site got suspended on several web hosting sites. my site has 1000-3000 visitor daily and only have several modules on it. the biggest joke is when i install a new fresh drupal and submit some contents, several hours after it the site display the suspended page.
i cant take it anymore. now my site down again and i dont know what to do.
i think i should find drupal alternatives for now. i really like drupal but not with the to much resource taking. i hope it will got changes on drupal 6 :)
---
Drupal can certainly be heavy on the database. It may benefit you to seek out a host who is not so limiting with their mysql resources. The more modules you use the more db querries that are needed to make your site work.
In most cases, the cheaper the host, the more restricting the host is. A host who is not willing to work with you to help beyond shutting down your site, is probably not a very good host. Rather than just pulling the plug on you , they could offer you some help or information on exactly what the problem is that is being caused.
Using page cache helps, using block cache.module helps, what steps have you taken to lessen the amount of resources being used ?