36

I've read the following phrase on a website:

Instead of adding new fields to a content type, adding existing fields is a better option to reduce the system’s complexity and to improve scalability.

And some doubts arise.

In the system we are developing, we have the possibility to reuse a field across 3 or 4 content types but instead of improving scalability as the quoted phrase says, I'm afraid it will diminish it, because the field's table would faster become a bottleneck (at least that's my reasoning in this case, as all values of that field together, would be a couple millions per year and that would make the table too big). Do you agree?

How many rows would be a sensible maximum to aim for when architecting? That way we could decide when to reuse fields and when to create new ones (even though the chance to reuse is there).

Improve this question
2
  • 6
    I would love to see answers backed up with actual metrics.
    – mpdonadio ♦
    Commented Dec 4, 2011 at 2:06
  • Think we've gathered very constructive and informative comments around this question. However, I'll wait one or two days before marking as answered, as something inside me insists that keeping that one or two most heavy fields separate (despite they could be reused) could be a good idea :) ...specially knowing those fileds could easily grow by 5, 10 or 20 million items per year.
    – rafamd
    Commented Dec 7, 2011 at 0:36

9 Answers 9

Reset to default
25

The amount of data in a field usually isn't a problem. If you're worried about that, look into alternative field storage plugins or write your own. For example MongoDB, which can deal with pretty much anything you put into it. It is for example used on http://examiner.com.

A real problem however is the number of fields you have. Because currently in Drupal 7, the complete field configuration of all fields, no matter if they're loaded or not, is fetched from the cache on every single request.

I've seen sites with 250+ fields, where loading and unserializing the field configuration takes 13MB+ memory.

Edit: The field info cache has been improved (see http://drupal.org/node/1040790 for details) with Drupal 7.22, only the fields of bundles that are displayed on a certain page are loaded from the cache and they're separate cache entries. That only works if there are no wrong API calls that request instances across multiple bundles.

Improve this answer
5
  • Hi Berdir, thanks for your answer. I didn't know about that overhead for the number of fields. So, we should try to reuse as much as possible, but still, shouldn't we try to split those we know are the most heavy ones ? I don't know much about mongo and the like but is it really that they don't care about the size of a group they have to query ? thanks !
    – rafamd
    Commented Dec 4, 2011 at 20:25
  • I actually don't know. Depends, I guess. Doing a test as MPD suggested might not be a bad idea. You could even compare it very low-level directly in Mysql. Create two tables with the same layout and indexes as the field data tables, write 10m (make sure to actually use different values for the entity_id) rows into one and 5m into the second. Then compare write performance and read performance (based on the entity_id aka an index). I suspect that the read performance will be almost equal thanks to the index but write performance could make a difference.
    – Berdir
    Commented Dec 7, 2011 at 14:18
  • That said, having a handful fields more or less will not really make a difference so if you feel more comfortable that way, that shouldn't be a problem.
    – Berdir
    Commented Dec 7, 2011 at 14:21
  • Writes are the tricky part, hence my recommendation about doing a test. What may be counterintuitive is the fact that MySQL drops cached entries based on table and not row (the last time I checked). I am unsure which would be more of an impact, the memory overhead of multiple fields and tables or cache-misses from writes to the same table. It is surely traffic / use dependent, though. Systems with multiple caches (Drupal cache, APC opcode, APC user, MySQL query cache, memcached, varnish, etc) makes gut-based decisions very difficult without profiling.
    – mpdonadio ♦
    Commented Dec 8, 2011 at 2:35
  • this is no longer the case: drupal.org/node/1040790
    – jackbravo
    Commented May 15, 2013 at 0:42
13

I totaly agree with berdir. Here are my experiences with a project with millions of rows and 30-40 fields on some node types.

  1. The number of rows in a field table isn't a big problem for the read performance, as all fields are fetched by primary key.
  2. The number of fields per node type can quickly grow into big performance problems when writing new nodes. Having 30+ fields for one node type results into 60+ INSERT statements when you create a new node. This takes seconds to complete. If you're users creating a lot of data this will hit your performance. Bulk inserts of 1000 nodes will take almost an hour. If you have to update 100'000 nodes, this is a big problem.
  3. If you think the number of fields problem is going to hit you, you should seriously think about writing your own field storage or just don't use fields. (You can still make your node working with views with some extra effort.)
  4. A word about MongoDB. It's a very interesting project and I hope it's making it into the olymp of the big DBs. Unfortunately compared to the maturity of MySql or PgSql it's a baby. Be prepared to deal with a very young product.
Improve this answer
1
  • Hi @BetaRide, thanks for your insight. About 2), we are already trying to minimize number of fields per content type and that's not exactly what we are discussing here. The real deal is: should I blindly reuse fields whenever possible or should I try to (at least) keep the most heavy one or two separate (even though they could easily be the same eg: they actually have the same name, etc). Yeah, mongo should be our last alternative for now :)
    – rafamd
    Commented Dec 5, 2011 at 17:30
6

If you are really worried about what will happen, then I think a simulation is in order.

Get an account at Rackspace Cloud, Amazon, Linode, or anywhere else you can easily spin up a VPS. Make two identical instances. Install Drupal on each. Create some dummy content types, and set up the fields one way in one system, and other other way in the other. Use the devel module to create a boatload of content. Adjust performance settings to make sure Drupal is caching as needed. Run mysqltuner and adjust MySQL on each per recomendations. Double check PHP and APC settings so that you aren't hitting swap and that you aren't churning the APC cache.

Once you get a good baseline configuration for each, start simulating traffic (both normal visitors and admin updates) with wget and drush, and then profile.

Simulations are never perfect, but they can get you going in the right direction.

Improve this answer
3

another tip: having a lots of fields will cause problems with many different modules as well. The Token GUI for instance will make your browser lag for minutes if you try to edit url aliases for example. This behavior can be seen on all the pages where token will be loaded and displayed (including devel - dpm() etc.)

There is no performance benefit in splitting this data across multiple tables when using InnoDB (MyISAM is different because of table locking). So - if you know you will have a lot of similar content types with similar fields (which configurations will be also the same, maybe differ in labeling only) reuse your fields!

It might also ease up template creation because of similar node attributes.

Improve this answer
2

Just sharing my story, we are using Drupal Commerce and have about 40 fields in our product variations (Sku) and then another 460 (yes, crazy) in our Product Display. We had some product comparison views that would look at all of these fields. Without caching, some page loads could take up to a minute!

However, it did work. If you did use caching and Varnish, the user wait time wasn't that bad.

The main problem that we ran into with so many fields is with Display Suite, as that would become very slow (sometime non-responsive) if we tried to re-arrange or move a field around.

Luckily, we decided to re-factor our products a little bit so we can hopefully get our max number of fields down into the 200-250 range for our most complex products (we are in scientific instrumentation, so complex measurements and specs are needed).

Improve this answer
2

One issue with scalability in fields in the use of indexes on every single table field in each field in the table created. The primary key clustered index is a composite of most of the fields, then it created separate indexes on each field individual. The indexes create a ton of overhead writes for the database, and in most cases are never used.

Improve this answer
0

It's an interesting question. I've thought about this before, sometimes re-using a field can be convenient to not have loads of similar fields 'lying around' but it does seem silly to have a certain content type having to select from a big load of data that we know isn't meant to be returned in the result.

I'd need a bit more info on the project to advise on best practice for scaling. What is the expected traffic, how many of those users to be logged in etc? For instance if all traffic except that of your admin user(s) is unauthenticated and anonymously cached

Improve this answer
2
  • Hi @drupaljoe, thanks for your reply. The expected traffic is difficult to estimate, because it's a brand new site. It's being developed with lots of care and we expect some sort of success, so let's say we manage to have some couple hundred concurrent users (most of them authenticated). That's exactly what I was thinking, querying that huge table must be a pain, so maybe we should architect to reuse those fields that won't grow too much and keep separate those that are going to hold more data. What could be considered too much ? 1 million ? 100 million ? 300 million ? ...
    – rafamd
    Commented Dec 4, 2011 at 19:49
  • I think the comments from the other two about how it shouldn't matter too much because the selects are on the primary key are good points. I guess I'd say just go with it for now but make sure you've done some reading about your options for the future, mongo for fields etc. You can't always second guess everything about the future of your site
    – joevallender
    Commented Dec 5, 2011 at 10:42
0

I've so far always been re-using fields but am now considering to use unique fields per node type for a new project. I actually want to keep everything nicely separeted (fields, views, rules, contexts, etc) for each entity bundle. So it raised the question of scalability which led me here. I'm comforted by Berdir's edit ( The field info cache has been improved (see http://drupal.org/node/1040790 for details) with Drupal 7.22, only the fields of bundles that are displayed on a certain page are loaded from the cache and they're separate cache entries. That only works if there are no wrong API calls that request instances across multiple bundles).

I just want to point out that there's a very interesting module that I've been using for months on multiple, complex sites.: https://www.drupal.org/project/render_cache. It's one of those hidden gems in my opinion.

As it says on the project page, the comments part is actually being used on D. O. itself.

So, with all that in mind, would it turn the consensus in favor of separate fields? The caveat being mentioned about DS is still a bummer, though. It's super annoying the way it saves via ajax instead of, for instance, how the core block administration interface handles re-ordering. I feel it's a ds issue, though...

Improve this answer
-4

As per my suggestion Using same fields in separate content type is good idea. Because it will improve your site performance. In Drupal 7, When you are using select operation that time, Using same fields in content type is really Useful to your Drupal7 site.

Improve this answer
2
  • 1
    In Drupal 7, they started using Doctrine ORM...no they didn't. Drupal 8 doesn't even use Doctrine
    – Clive ♦
    Commented Jul 30, 2013 at 11:43
  • "Doctrine always returns the object from all mapped data", is also a false statement. Objects can be annotated to indicate to doctrine that the default behavior is not suitable. Not that that is terribly relevant, given that, as Clive says, Drupal doesn't use Doctrine.
    – Letharion
    Commented Jul 30, 2013 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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