0

We currently have a Drupal 7 portal collecting research data for multiple studies (set as individual content types), for multiple sub-projects (individual content type set as Inline-Entity-Form), across multiple clinical sites (set as taxonomy). Please refer the pic below for the current structure.

We have used node access, field permissions and taxonomy access control contrib modules for configuring permissions.

With rapidly increasing number of projects and clinical sites, we have ended up having a lot of content types in a single database, and the portal performance is getting less efficient.

We are planning to migrate to Drupal 8 very soon and wanted some suggestions on whether to use multisite options or domains, use single database or multiple database?

We also have a custom LIMS module for our laboratory management, that is used across the studies and sites. Meaning, we have few serial/integer fields that is unique across the studies and want to retain this uniqueness. Any suggestions on keeping the uniqueness of these fields/IDs if we want to go multisite-multi-db way?

In the later stage, we also want to add an Intranet website to be accessed within the organization.

enter image description here

Edit: I am thinking of multisite / multidb only because of the performance issues it may cause with single database holding so many content types and IEF fields (ex: Study_1 has 15 IEF fields to enable different projects within that study, and each IEF form has between 50 - 80 fields!). Otherwise I would prefer working with a single database and may be use Domains?

Content access wise, we want to separate out the access primarily Study-wise, and site-wise for Studies with sites.

  • "we have ended up having a lot of content types in a single database, and the portal performance is getting less efficient." How many content types are there? Are the performance problems the reason you want to migrate to Drupal 8, or would you still want to migrate if you didn't have performance problems? Have you identified the views/queries that are causing the problems? I guess what I'm getting at is, if you are at all interested in fixing/optimizing your Drupal 7 site instead of migrating it to Drupal 8. – runswithscissors Jan 3 at 16:38
  • @runswithscissors, Hi, we have now 50 content types already and we are expecting few more projects in the next 5 months and an extra ~15 content types needs to be added. We want to migrate to Drupal 8 anyways as Drupal 7 is reaching its EOL. The number of projects/content types are only going to increase. We notice the portal loads very slow sometimes - when there are multiple (10 to 15) users using the portal. – Maddy Jan 5 at 2:21
  • If you weren't having issues with performance, would you still be thinking about using multi-site / multi-database? If so, could you please update your question with the reasons why; the other problems it would fix and/or the added benefits/advantages it would have. If you wouldn't consider multi-sites if you weren't having performance issues, then whether you can fix those issues under a single site would determine the answer to your question. – runswithscissors Jan 5 at 4:59
  • If you went with multisites, what would constitute a new website: a Study, Project, or Site? Your sketch suggests that Study is the main entity, but Project A is in all 4 Sites under Study 1, and twice under Study 4 (which has no Sites?). If a Project belongs to (or has) multiple Sites/Studies, then you can't have separate databases without duplication. This would increase admin overhead, and lead to data inconsistency: a field in website 1 for Project A is "foo", but in website 2 it's "bar". Please clarify how Studies, Projects and Sites relate to each other, and which can be shared. – runswithscissors Jan 5 at 6:13
  • @runswithscissors, have updated the question for your 1st comment. – Maddy Jan 6 at 1:42
1

Few points about Drupal 7.

  • 15 content types / up to 80 fields should not cause performance issues, although (as per previous answer) it can be bad project architecture: fields or views. More likely, setup (database configuration, server configurations , or caching) is the cause. To find the cause I would recommend to connect profiling tool like blackfire.io or newrelic.com to dig deep into what is slowing down the performance. Another technique I would try: create trial account on Acquia or Platform.sh and see if you are still getting the same issue on their dev environment.
  • In regards to multisite - it is a tradeoff as you might still get the same issues (in case they are cause by setup), but you also need to think about overhead of user management and upgrading each site.

Based on your requirements Drupal 8 and webform seems like appropriate solution:

  • webform module in Drupal 8 is completely new module to the one in Drupal 7. It will fit the requirements described in the issue. It has less database overhead due to flat result structure (as opposed to Drupal nodes that record each field into separate table). You won't require to create new Content Type for each study. Webform has larger selection of widgets, granular access control (both forms and fields) and views integration for data reporting among other things.
  • Drupal 8 architecture from ground up was for larger systems (both structurally and from data perspective). Even though Drupal 7 probably not the cause of the slowness in your case, your project would still benefit from more modern architecture.

In conclusion,

  • first, find the source of performance degradation
  • I don't think multisite is the right way to go based on your requirements
  • I'd recommend to switch to Drupal 8 and leverage webform module based on your requirements.
  • Drupal 8 would be a good investment as you would eventually need to migrate to version the next version of Drupal.
1

I am thinking of multisite / multidb only because of the performance issues

If that is the case, then only people who have very detailed information about your site and your organization can answer your question. It boils down to comparing the resources necessary to identify and fix the performance issues, to the increased/increasing long-term resources necessary to maintain multisite/multidb. "Resources" is not just about money, it's also about time taken away from other potential projects, the relative likelihood and severity of disruptions caused by bugs/errors, etc. These things are very specific to your site/problem and your organization.

Most of the time, the right answer would be to fix the problem, and not try to bypass/band-aid it. However, there can be circumstances that doing something the "right" way, or even a "good" way, would be so cost-prohibitive for an organization, that you have no choice but to do it the "acceptable" way.

Let's say the problem is how you have structured/modeled the data on your Drupal 7 site (your description makes this a possibility). Truly fixing it may require building a fundamentally new (incompatible) structure/model in a Drupal 8 site, and writing custom modules/programs to manipulate and import the data from Drupal 7. You probably have some idea about whether your organization has the resources and know-how to pull that off with minimal disruption. If you're thinking, "sure, no problem, we can do/pay-for that", then multisite isn't a viable option, in my opinion. Especially since you have data that needs to be shared and consistent among all the sites (Project A, LIMS, and eventually an Intranet).

So, if you haven't done it already, you need to figure out exactly what is causing the performance issues. Then you can look at what it would take to fix them, and decide where to go from there.

There are some things you may be able to do now to help with your performance problems, and alleviate some of the pressure/urgency while you find the root cause and come up with a strategy. Implementing memcached was mentioned in the comments. If the database is currently on the same server as your website, then moving it may help; this is much easier if your site is on AWS and you move the database to RDS (or the Azure/GoogleCloud equivalent). Aggregating JavaScript/CSS files and/or using a CDN may also help. But, just because something helps with your current symptoms, does not necessarily mean you have fixed the actual problem; the symptoms can reappear, with a vengeance, when your site grows beyond a certain point.

Edit:

Study_1 has 15 entity reference fields to enable different projects within that study, and each entity ref form has between 50 - 80 fields!

I'd like to point something out about multisite/multidb: Just because your sites/databases are logically separated, does not mean that your performance issues will go away.

Let's say that you migrate to multisite, and later you realize all your performance issues are because of Study 1. If the tables/data for the "Study 1" Site is using the same physical/virtual database server as your other sites, it's possible that you will still have performance issues. Putting Study 1's database on another server may help the other sites, but it increases your cost significantly, and Study 1 still has performance issues. You can upgrade your database server to handle the load, but that increases costs as well, and you can't upgrade it each time a new study comes along with a structure like Study 1.

  • Hi,thanks so much for your time understanding the scenario and for the detailed suggestions, really appreciate it. Like I mentioned before, we want to migrate to Drupal 8 only because Drupal 7 is reaching its EOL soon. We will definitely look for the caching methods for immediate performance boost, until we work in the background to get Drupal 8 instance ready. PS: database server is in a different dedicated server and is hosted in house. – Maddy Jan 6 at 6:33
  • We are bit worried about the future no of projects that are coming and no of CCKs and entity references that needs to be added to support those projects with the current structure and so were curious to know if there were any other approach in D8 to our scenario. Like you mentioned, we will have to think through all the requirements before deciding on any approach. -- Keeping performance issue aside, what do you think could be a better structure? – Maddy Jan 6 at 6:34
  • @Maddy Only someone with a thorough understanding of your data and organization can give you advice on whether, or how, to change your data structure. It seems odd to me that you would need a different content-type for each individual study and each individual project. I would normally expect to see one content-type called "Study", and each individual study would be a node (just like the Article content-type has individual article nodes). But I have zero knowledge about your organization or the field that it works in. – runswithscissors Jan 6 at 6:50
  • no worries, thanks again for the valuable suggestions. – Maddy Jan 6 at 7:18
  • You're welcome. I'm sorry I couldn't give you more specific advice. This actually seems like a very interesting project, and has the potential to be made into a great case-study. – runswithscissors Jan 6 at 23:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.