Contrib.social

Slow database query

Open on Drupal.org →
Created on 19 May 2023, about 2 years ago

Problem/Motivation

Our directory listing pages were taking a very long time to load - anywhere from 6 seconds on a good day to 30+ seconds when under load.

I profiled the code and tracked most of that time down to this function call in MetadataEventSubscriber::setMetadata():

        $data = $this->createData($name, $fid, $file, $subdir_fid);
        $query = \Drupal::entityQuery('filebrowser_metadata_entity')
          ->condition('fid', $fid)
          ->condition('module', 'filebrowser')
          ->condition('name', $name);
        $entity_id = $query->execute();

Adding an index to the table reduced the page load time to around 2.5 seconds on a good day (not sure about a bad day yet), and the total database query time from around 4 seconds to just 235 ms (measured by WebProfiler).

CREATE INDEX fid_module_name USING BTREE ON filebrowser_metadata_entity (fid, module, name);

Steps to reproduce

Not sure exactly... We have 34,033 records in that particular table, so maybe just having a lot of files?

Proposed resolution

If adding the index automatically is an option, I would recommend it.

Otherwise I guess this is just for others who might have the same problem to try.

Thanks!

Remaining tasks

User interface changes

API changes

Data model changes

🐛 Bug report
Status

Active

Version

3.0

Component

Directory Listing Pages

Created by

🇬🇧United Kingdom mi-dave Oxford, England

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Status changed to Closed: outdated 19 days ago
  • Comment 19 days ago →
    🇳🇱Netherlands clivesj

    We had a problem with bloated tables, but this was resolved a long time ago.
    Maybe this is a legacy db that was not corrected yet?
    I will close this one, please re-open if or when needed

  • Comment 17 days ago →
    🇬🇧United Kingdom mi-dave Oxford, England

    Thanks for your input.

    Without knowing what the bloated tables problem was, or what would class as a legacy DB, I can't say for sure. The site is several years old for sure, but we've kept the module up-to-date and run all the database migrations.

    We have since added Memcache and moved to faster hardware, so I can't do a direct comparison with the original load times - but when I remove the index now, the page load time jumps from around 1 second up to 5 seconds (consistently).

    We currently have 52,473 rows in the table, of which 21,820 relate to the node I am testing on (10,661 files across 932 directories & subdirectories). We are using Filebrowser 3.1.4, Drupal 10.4.7 and MariaDB 10.6.21 (Ubuntu 22.04).

    I've re-opened the issue as you suggested - but if it's not affecting anyone else and isn't worth investigating further, I'm happy for it to be closed, as I have a solution that's been working well for us for the last 2 years.

  • Comment 16 days ago →
    🇳🇱Netherlands clivesj

    1 to 5 seconds is significant.
    I will put issue on my personal list for further investigation.
    Did you solve it within Filebrowser by a patch or did you do it otherwise?

  • Comment 16 days ago →
    🇬🇧United Kingdom mi-dave Oxford, England

    Thanks!
    I manually added the index to the MySQL table using the SQL above.

  • Comment 15 days ago →
    System Message
    • clivesj → committed 0581356a on 3.1.x 
      Issue #3361551 by mi-dave, clivesj: Slow database query
  • Comment 15 days ago →
    🇳🇱Netherlands clivesj

    I ran a test on a table with 100.000 rows and the performance gain is indeed enormous.
    It was an easy fix so I committed it to todays dev and also provide a patch here

  • Comment 15 days ago →
    🇳🇱Netherlands clivesj
  • Comment 4 days ago →
    vtrefny

    Hi, the database update fails for me after upgrading to 3.1.5 which seems to be related to this fix:

    Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "USING" LINE 1: CREATE INDEX fid_module_name USING BTREE ON filebrowser_meta... ^: CREATE INDEX fid_module_name USING BTREE ON filebrowser_metadata_entity (fid, module, name); Array ( ) in filebrowser_update_9106()

    Drupal 10.4.7, PHP 8.2.28 and PostgreSQL 15.13

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024