I am using a LAMP environment. I have around 10 sites all running off the same install of Drupal. Right now each one has its own unique database and users. I am looking to merge the users.
I am thinking if I were to somehow combine all the databases into 1 that would be pretty sloppy. It seems like having close to 1,000 tables in a database would be a bad thing, or would it be?
I am thinking the best way to handle this would be to have a database for each site that carries the node info and system info and all that (my current setup). I then think having a single database for the sole purpose of shared items such as user info and sessions and all that would be a clean way to approach this.
If I went this route I would be connecting to 2 different databases for each site. Is this a good way to go about it, and if so is that even possible with Drupal?
Any thoughts on this would be much appreciated.
Thanks,
Quinton Figueroa
Comments
I do it that way
I would think having multiple databases would be more of a hassle than multiple tables, especially if you want to make changes across all sites (like adding node types, vocabularies, roles across all sites at once).
I have just two sites currently sharing the same code base and database, but I plan on adding about 10 more shortly. The number of tables will increase significantly since many can't be shared just by their nature. I'm using SQL Server, which can handle a ton of tables, so there really is no limit and I would assume the same for MySQL. I haven't thought about it too much, but I'm wondering if the number of tables could be limited by creating a single lookup table that would point various nodes, etc to the correct site rather than creating individual tables like site1_node, site2_node, etc. However, it is nice to be able to just highlight and delete all tables with a particular prefix and delete them, thus deleting that site so you can rebuild it individually and easily.
But what about +40 sites?
Hello, My team has jumped into Drupal and is resolving how to acheive our use case requirements. We are an international not-for-profit organization with +40 public websites and a shared login environment. The OG_sites module looked good until we began calculating the number of tables involved five years from now (?5000?). We want this to be sustainable and manageable, so we are looking at the OG family of modules and then comparing that to what comes with OG_sites. Why use OG_sites if we don't need to?
We are wondering if the OG family can achieve what we need without using OG_sites. For example, does the OG Family have, without OG_site:
- OG specific domains (making it look like a separate site): Yes
- OG specifc theming: Yes
- OG specific block visibility: Yes
- OG specific node permissions: Yes
- OG specific user roles: Yes
- OG specific menus: in dev, og_menus
- OG specifc views: in dev, og_views
- OG subgroups: Yes
- table_cache or max_connections issues on db: No
What have I missed? I am wondering is OG the direction that Drupal multisite is going for those Drupal projects which need a single table prefix, single database, and single Drupal install, but many Drupal sites? It's my impression that it is.
For a more detailed evaluation of OG vs. OG_sites as they relate to our needs, follow this link to an attached Open Office spreadsheet.
http://www.oxfordinform.com/iofcproject/node/75
I may be having delusions of grandeur about the OG family's future -- your experience is welcome.
cheers, John
og, og-sites, multisites
Sharing code is easy (multi-site). Sharing users is do-able (couple modules can help with single-sign on). Separate databases is advisable. Sharing content is messy (to be avoided).
I think OG is a viable path. If you want to share content and users this is really the only solution right now. So you have one site with x number of themes.
There is no clear path for sharing content across sites using Drupal. Lots are working on solutions to sharing content across sites and one attempt is OG-Sites. But I feel OG-sites is flawed by design and definitely is not ready for a production site. The author of that OG-sites has no plans for future development.
If you don't need to share content but do need to share users there is a proven path for this. Sharing users between databases is easy with prefixing. You can even have separate databases and share user related tables across databases. Instead of a ridiculous number of tables in one database, you end up with one database for each site and one more for the shared tables. This is manageable in my opinion.
If you have a limit on the number of databases you can make, I think you should be looking for a new host or account type instead of forcing 40+ sites into one limited hosting account.
related topics:
http://groups.drupal.org/node/4210#comment-18059
http://groups.drupal.org/node/4210#comment-18076
Regards,
Michael Hofmockel
Open Source || Open Access || Open Mind
Total amount of data is small.
Thanks for you insights mhofmockel -- it's very helpful. You've made a valid point about separate databases being advisable under a true multisite. However, what if the total amount of data involved is less than 20MB (excluding images) and no more than 20,000 rows in any given table, even with all 40+ 'themes' running? Would computing power keep up with that? Or is it Drupal's database efficiency that becomes the concern?
At this time, there are a few hundred groups in our current cms, but of those only about 40 groups would have individual theming and so forth in Drupal, and that may grow but not exponentially. These are mainly 'brochure' type websites, and behind that are working groups, mostly staff or dedicated volunteers (not a very 'public' constituency, not yet anyway). You can see what I mean here http://www.iofc.org, or http://www.caux.ch, or http://www.in.iofc.org/
I suppose we want to make it sustainable either way, even with a 'quadrupaling' of our membership/content.
compartmentalize for the humans
I recommend multiple database for the humans (admins) not for the code. Humans do better with more compartments and the code doesn't really care.
I don't think there will be any difference in computation resources consumed with one database compared to many databases. If anything it may be faster as any one database query does not have to enumerate all the tables across all your sites just the relevant tables.
Regards,
Michael Hofmockel
Open Source || Open Access || Open Mind
If one is serious about groups having domains...
If one is serious about groups having their own domains, then separate databases does seem to provide a more longterm solution. And as you say, it's the sharing of nodes that becomes messy. Unfortunately, I do not come at this with much experience of this mess -- I am still in the research phase of a new project and new to Drupal -- and so I go by my experience of what is happening in my trials and what others are saying (repeatedly).
If I now prefer separate databases (keeping it human is logical), then where should I focus my attention to see that improvement? I wonder if anyone is interested in re-writing the OG_sites module to support separate databases and proper node/content sharing, or is there another way?
Is the new Domain Access module part of the answer to this? http://drupal.org/project/domain