6 ms·

I used and liked Drupal a lot. The only thing bothers me to see enormous number of queries running even for a single page web site. I think it is a sacrifice fo

by bbayer 11y ago

I used and liked Drupal a lot. The only thing bothers me to see enormous number of queries running even for a single page web site. I think it is a sacrifice for the sake of creating all-purpose application.

jacquesm 11y ago
No, it's the price of a naive approach to maintaining a data model programmatically. This is a very weak point of drupal and the performance overhead it generates is incredible.
- aikah 11y ago
  > it's the price of a naive approach to maintaining a data model programmatically Are you talking about their node system ?
  - jacquesm 11y ago
    That's the least of the problems. Drupal creates tables willy-nilly and hits a very large number of them for a single page request.
    - rvense 11y ago
      I've seen a thousand request for a single page when logged in as administrator (since the menu structure for the admin interface comes from the database as well). I talk about this a lot and I must admit I rarely use words as diplomatic as naive...
      - jacquesm 11y ago
        > I talk about this a lot and I must admit I rarely use words as diplomatic as naive... That has to be the first time that I'm labeled 'diplomatic'. I have different words as well but I see no upside in using them, those who choose to pick drupal after many years of broken processes, lack of an upgrade path between major versions, abandoning large numbers of users on broken and unsupported code anybody that chooses drupal today really deserves what they get.
      - technion 11y ago
        To be fair, this is on par with, or better than, Wordpress.
    - rjknight 11y ago
      Much of this can be avoided by enabling caching. Running Drupal without entity or page caching is like running a desktop app in debug mode, or compiling without any optimisations.
      - jacquesm 11y ago
        Caching is not a substitute or fix for bad design.
        rjknight 11y ago
        So, let's look at the design. Drupal wants end-users (not limited to developers) to be able to create new types of entities, add fields to those entities and store complete revision histories for those entities as they are created and edited. This should be achievable within a GUI and without needing to edit code by hand. It also wants developers to be able to distribute code that give these end users new types of fields to use. It achieves this by creating two new tables for each additional field. One table stores just the current value of the field, and the other table stores the historical values of the field in previous revisions. An entity like a 'blog post' with three fields will therefore have a base table plus three additional tables for the current values, and a revisions table with three additional tables for the historical values. When loading an entity, these field values can be loaded via SQL joins, or (depending on the field type) via additional queries. As this is expensive, entities can be cached, and the next attempt to load the entity will pull the complete object from memcached, Redis etc., and this is how most production sites would do it. Obviously in the case of zero caching, where you have entities with many fields (say, 30) and where you need to load many entities, you can quickly end up with many queries - hundreds or even thousands! But almost all of those go away with caching enabled. Is this a bad design? It would certainly be possible to make the database schema line up better with the entities, which would reduce the number of queries - a single 'blog_posts' table could be queried, rather than hitting the 'node' table and then the additional field tables. But you would lose a lot of the end-user flexibility - the ability to define new entity types, add and remove fields or reconfigure those fields (changing their size or even their cardinality). Instead of adding new tables, you could add and remove columns from an existing table, but this brings other problems - your tables can become very large, and altering tables containing existing data is slow. Mutating a single table when you want to add/remove a field is a more dangerous tool to expose to an end-user than just adding/removing additional tables. If only developers can do these things, then yes, you would expect to design your tables for optimal performance. There's a large body of knowledge on SQL optimisation that a developer would be able to bring into play. But if this requires giving up the ability of end-users to modify entity types via the GUI, it no longer achieves the original design goals. Drupal trades off best practice database design against delivering certain tools to end-users, and mitigates the performance impact with a fairly robust caching system. To me, that's good design, given the constraints. You might argue that Drupal's design goals are wrong, or that those design goals make Drupal inappropriate for some use cases, but I don't think any of that makes it a fundamentally bad design. EDIT: fixed some grammar in the db schema description
    - dreamfactory2 11y ago
      That was a deliberate design choice for Drupal 7. The reasons were: - Loose coupling. Drupal has been becoming increasingly API-centric and storage engine agnostic. Focusing on the physical storage schema for a DB engine misses the point of the architecture. - Performance(!) Supports schema changes on existing large datasets (migrations are only starting to appear in Drupal 8, and elsewhere bring their own complexities when you want zero downtime). Unsurprisingly there was some gnashing of teeth when this 'table per field' approach was introduced and as a result it was decided to build denormalized read layers on top (materialised views and bundles). However, it turned out that they showed no perf improvement over Drupal's object caching whilst adding complexity to the design and were therefore abandoned. (The fact that this performs as well as materialised views speaks very positively of Drupal's architecture and code quality.) - Multiple revisions of any data. - Multilingual capabilities. - Support of tree type data models. https://www.drupal.org/node/1035804#comment-6860070 https://www.drupal.org/node/1035804#comment-6860070 On the whole I would call this a thoughtful and reasonable design choice, and one that is supported by real-world implementation on millions of sites at all scales.
  - monk_e_boy 11y ago
    You should make a view that searches some nodes for a custom field. Wow, it hits the database A LOT.
    - delta9 11y ago
      You shouldn't do that, you should use Solr

by bbayer 11y ago

jacquesm 11y ago

No, it's the price of a naive approach to maintaining a data model programmatically. This is a very weak point of drupal and the performance overhead it generates is incredible.

aikah 11y ago
> it's the price of a naive approach to maintaining a data model programmatically Are you talking about their node system ?
- jacquesm 11y ago
  That's the least of the problems. Drupal creates tables willy-nilly and hits a very large number of them for a single page request.
  - rvense 11y ago
    I've seen a thousand request for a single page when logged in as administrator (since the menu structure for the admin interface comes from the database as well). I talk about this a lot and I must admit I rarely use words as diplomatic as naive...
    - jacquesm 11y ago
      > I talk about this a lot and I must admit I rarely use words as diplomatic as naive... That has to be the first time that I'm labeled 'diplomatic'. I have different words as well but I see no upside in using them, those who choose to pick drupal after many years of broken processes, lack of an upgrade path between major versions, abandoning large numbers of users on broken and unsupported code anybody that chooses drupal today really deserves what they get.
    - technion 11y ago
      To be fair, this is on par with, or better than, Wordpress.
  - rjknight 11y ago
    Much of this can be avoided by enabling caching. Running Drupal without entity or page caching is like running a desktop app in debug mode, or compiling without any optimisations.
    - jacquesm 11y ago
      Caching is not a substitute or fix for bad design.
      - rjknight 11y ago
        So, let's look at the design. Drupal wants end-users (not limited to developers) to be able to create new types of entities, add fields to those entities and store complete revision histories for those entities as they are created and edited. This should be achievable within a GUI and without needing to edit code by hand. It also wants developers to be able to distribute code that give these end users new types of fields to use. It achieves this by creating two new tables for each additional field. One table stores just the current value of the field, and the other table stores the historical values of the field in previous revisions. An entity like a 'blog post' with three fields will therefore have a base table plus three additional tables for the current values, and a revisions table with three additional tables for the historical values. When loading an entity, these field values can be loaded via SQL joins, or (depending on the field type) via additional queries. As this is expensive, entities can be cached, and the next attempt to load the entity will pull the complete object from memcached, Redis etc., and this is how most production sites would do it. Obviously in the case of zero caching, where you have entities with many fields (say, 30) and where you need to load many entities, you can quickly end up with many queries - hundreds or even thousands! But almost all of those go away with caching enabled. Is this a bad design? It would certainly be possible to make the database schema line up better with the entities, which would reduce the number of queries - a single 'blog_posts' table could be queried, rather than hitting the 'node' table and then the additional field tables. But you would lose a lot of the end-user flexibility - the ability to define new entity types, add and remove fields or reconfigure those fields (changing their size or even their cardinality). Instead of adding new tables, you could add and remove columns from an existing table, but this brings other problems - your tables can become very large, and altering tables containing existing data is slow. Mutating a single table when you want to add/remove a field is a more dangerous tool to expose to an end-user than just adding/removing additional tables. If only developers can do these things, then yes, you would expect to design your tables for optimal performance. There's a large body of knowledge on SQL optimisation that a developer would be able to bring into play. But if this requires giving up the ability of end-users to modify entity types via the GUI, it no longer achieves the original design goals. Drupal trades off best practice database design against delivering certain tools to end-users, and mitigates the performance impact with a fairly robust caching system. To me, that's good design, given the constraints. You might argue that Drupal's design goals are wrong, or that those design goals make Drupal inappropriate for some use cases, but I don't think any of that makes it a fundamentally bad design. EDIT: fixed some grammar in the db schema description
  - dreamfactory2 11y ago
    That was a deliberate design choice for Drupal 7. The reasons were: - Loose coupling. Drupal has been becoming increasingly API-centric and storage engine agnostic. Focusing on the physical storage schema for a DB engine misses the point of the architecture. - Performance(!) Supports schema changes on existing large datasets (migrations are only starting to appear in Drupal 8, and elsewhere bring their own complexities when you want zero downtime). Unsurprisingly there was some gnashing of teeth when this 'table per field' approach was introduced and as a result it was decided to build denormalized read layers on top (materialised views and bundles). However, it turned out that they showed no perf improvement over Drupal's object caching whilst adding complexity to the design and were therefore abandoned. (The fact that this performs as well as materialised views speaks very positively of Drupal's architecture and code quality.) - Multiple revisions of any data. - Multilingual capabilities. - Support of tree type data models. https://www.drupal.org/node/1035804#comment-6860070 https://www.drupal.org/node/1035804#comment-6860070 On the whole I would call this a thoughtful and reasonable design choice, and one that is supported by real-world implementation on millions of sites at all scales.
- monk_e_boy 11y ago
  You should make a view that searches some nodes for a custom field. Wow, it hits the database A LOT.
  - delta9 11y ago
    You shouldn't do that, you should use Solr