Posted by joblogs83 on December 8, 2009 at 9:49pm
Hi everyone,
I work on a Drupal 6.13 site that is current storing the details of 750k users, expecting to grow to over 1 million in the next 6 months. I'm wondering if anyone else has made a Drupal site scale to this level and if so, what issues (if any) you might have come across?
The biggest issues I'm noticing now include:
- Custom users > taxonomy term table (a basic structure of uid, tid, vid) using MyISAM has over 15 million rows. Simple querying is fine, but reporting is impossible.
- Core profile module has over 9 million rows and is pretty rubbish at storing and indexing it's contents.
To cater for reporting, my team is building a sub-system to put all the user profile data into a decent relational model. Otherwise our system grinds to a halt.
I'm also wondering whether it's worth investigating converting to InnoDB instead of MyISAM? The lack of transaction support + row level locking irks me greatly on an enterprise level system.
I guess my biggest interests here would be:
- Has anyone implemented a complex user profile to taxonomy module that scales well?
- How hard is it to convert to InnoDB with Drupal 6 and what are the risks?
Thanks!
Comments
There are several Drupal
There are several Drupal sites out there this size or larger. drupal.org is at ~660k users. And I'd be surprised if your user base is as active as the Drupal users.
Using something other than profile module sounds like a good idea. You probably need performance over the flexibility of adding fields in a few clicks.
InnoDB is probably advisable with a site of this size. Switching is fairly simple, but you need to tweak my.cnf. Search other posts in this group for more details. Note that even though InnoDB supports transactions, Drupal 6 doesn't have any (D7 will).
But I wouldn't advise anything more without first determining where your problems are. There's loads of different performance techniques. But if you don't know what the problem is, you can't prescribe surgery. I'd recommend doing some slow query analysis, running mysqltuner, analyzing the queries per page with the devel module and possibly doing some code profiling before you start performance optimizations.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
InnoDB
Converting the database to InnoDB is not that hard to do & I've had very good success with it as long as you configure your server correctly. Great post on this subject:
http://groups.drupal.org/node/18177
You might want to take it one step further and convert it to XtraDB; its the high performance fork of InnoDB. If your going this route, might as well use MariaDB, a fork of MySQL; it might also help with the joins due to it using a unique storage engine for internal temporary tables (what usually gets created when doing unoptimized joins, profile & taxonomy module might benefit).
As always running explain on sql statements that take a long time and seeing where you might be able to do, to improve performance is key. The menu_router rebuild is known for being slow; when I get some free time a work (end of the year) I'll be rolling a patch against core that should make it much better behaved.
http://groups.drupal.org/node/35102#comment-105086 my code from here would go in this issue: http://drupal.org/node/512962
Related issues for the menu_router table
http://drupal.org/node/356399
http://drupal.org/node/251792#comment-2125124
menu_router_build() patch
give this a shot
http://drupal.org/node/512962#comment-2452998
Replacement for profiles
http://drupal.org/project/content_profile is a good a substitute for profiles. As it uses standard nodes for 'profile' information if you are happy with node performance then this should suit.