I am doing a migration of a community website over to Drupal and have come up against a problem that does not seem solvable. The amount of users and nodes is too large for the Watchdog and the Cache queries. It seems that upon logging on that the creation of the user menu to cache and the watchdog exceeds the MySQL max_allowed_packets. Thus the watchdog table cannot be locked. The same applies to the cache table.
Besides sitting a tinkering with MySQL is this something that has been addressed in later versions of Drupal? Does 5.x or 6.x suffer from the same failing?
This is a critical thing because we have several communitites that are larger and have between 10,000-100,000 users and a potential for millions of nodes. I need to assemble some information concerning this situation before we are too deep into the project to jump to something else.
Comments
It seems that setting max
It seems that setting max packets allowed takes care of some of the table writing problems but the read time for 70,000 nodes is still too high.
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
subscribing
subscribing
subcribing
subcribing
subcribing
N.Mehrabany
Baruzh web design & programming
Nima
Can't be
This can't be a general problem. Drupal.org has 160K users and 160K nodes (or thereabouts), and does not suffer from these symptoms. Even on 4.7 we were above 100K for each, and did not have that.
So, it has to be something specific to your server, or your Drupal config.
You say "upon logging on that the creation of the user menu to cache and the watchdog exceeds the MySQL max_allowed_packets". This is too cryptic: how many items are in the menu? What is the watchdog trying to log?
Is this a dedicated server where you can increase the max_allowed_packets parameter? If so, go ahead and do that and see what happens.
--
Drupal development and customization: 2bits.com
Personal: Baheyeldin.com
--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba
As mentioned in the second
As mentioned in the second post max_allowed_packet was changed. We set it to 16m which solved only art of the problem.
The page execution times are erractic. If we reduce the amount of nodes and users to 1000 users and 1000 nodes then things operate fine. At about 25,000 nodes there is a significant slow down. Reading seems to be okay after a few clicks and using caching. But doing a delete, update or insert will give an incredible 83879.24 ms. This is on a development server with no network load and only the dev team surfing the pages.
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
Menu inc timing out
Another problem is that increasing the database allowed packets allows the menu to cache but then the menu.inc script times out at 60 seconds. This leads us to believe the menu creation is the reason for the high page load times
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
We had the same problem
for a few days essentially grinding development to a halt and having people tear their hair out. It always seems to be be related to some caching issue...in our case it was mainly Views caching that was the culprit. As you've already done...the max_packet_allowed setting in the MySQL config needs to be increased...perhaps even more that the 16MB...I think I set mine to 64...
That fixed it for me, but other people still had problems. We finally got rid of the errors by increasing the memory allocation for PHP in php.ini as well. Now, I'm using XAMPP, and there was some confusion which php.ini file...there are at least 2 in a general install, one under PHP and another under Apache...they needed both changing.
ourbrisbane.com
Taxonomy and Language
Thanksfor the tip. We upped from the standard 1mb on the servers to 16mb and that seems to be enough for now. I spent the last few hours tracing modules and found that taxonomy.module is responsible for half of the heavy page loads. i18n is partially a problem as it causes recaching on language switching.
Things seem to be speeding up slowly as the cache is moved around. But now we are having problems with the logout. Logging out takes 2 minutes sometimes.
I hate to say it. But I am glad that we are not the only ones having these problems :)
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
Something to look out for if
Something to look out for if you are using i18n and taxonomy. The code checks and rechecks for other languages even though a language has already been choosen. So if you are in Swedish and reload the page it goes through the entire vocabulary table and checks for English even though you have not switched. With thousands of vocabularies this creates a bit database hit.
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
Inefficient code and queries
I found two huge bottlenecks. Tagadelic.module is not something you want to use on a large site. First is uses hook_menu and taxonomy together which causes thousands of extra queries to run on certain pages. In our case it was the node form. If a user went to edit a node there where 7000 queries thrown by tagadelic alone. I rewrote tagadelic so that there is no hook_menu used and simplified the PHP code for weights which was increasing page execution time by running through this incredibly large loop sequence.
The second thing is in core. The taxonomy_term_count_nodes is a monster. I changed all code to just run a simple mysql count and went from 780ms/80ms cached to 65ms/13ms cached.
Sadly a lot of the things that make a site slow are still in the development release of Drupal 6. To top it off the database schema seems to be getting worse than ever.
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
help wanted
if you aren;t already, please submit bugs and even patches. things get better when people participate.
Same problem
I found a bottleneck: replacing taxonomy_term count_nodes() in taxonomy_dhtml.
I did not bother submitting a patch to core because I am not sure how this function is used elsewhere (other than taxonomy_dhtml module).
If you found the same, then there should be at least a disclaimer or comment in the code against using it with many terms/nodes.
--
Drupal development and customization: 2bits.com
Personal: Baheyeldin.com
--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba
Block Cache
What would have been the effect of using user specific (as opposed to page specific) Block Cache version of the taxonomy_dhtml menu block and setting the refresh time to 3600 seconds?
--- Ade Atobatele
Ade Atobatele
Share changes?
Hiveminds,
I'd be interested to check out the changes you made to tagadelic and the taxonomy_term_count_nodes if you're up for sharing them...
Thanks,
jonathan
Picali.com beta released
We just released a beta site using all my code changes. I have been so tired while working on this that I have not been able to do any other writing. But it seems that the pressure is off a bit and so I will be documenting the websites modules and changes. Then I will take from that documentation and write up some articles with code on Hiveminds and possibly Drupal.st.
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige
code to speed up tagadelic?
Hiveminds,
I was wondering if you might have time to share you code about tagadelic. Thank you.
I no longer work there but I
I no longer work there but I will wee if I have any snippets on a usb or dvd somewhere.
PHP Developers | Drupal Developers | PHP CMS code | Svensk Drupal Utvecklare
thank you, hiveminds
very much appreciate it.