Problem/Motivation

Using the "information_schema" tables in PostgreSQL is very slow. @Mixologic who works for the Drupal Association has pointed out in #2847855-19: Incorrect statement class caused by race condition when closing and opening the connection.
PostgreSQL uses the default implementation for the method Schema::fieldExists(). And the default implementation uses the "information_schema" tables.

Proposed resolution

To speed PostgreSQL up it would be the best to create a specific PostgreSQL implementation of Schema::fieldexists().

Remaining tasks

  • Do research (Mixologic) - DONE
  • Write a patch (daffie) - DONE
  • Review patch
  • Performance review

User interface changes

None

API changes

Table field exists information is gathered in a different way with potentially some differences for PostgreSQL. This does not seem to have any affect on tests.

Data model changes

None.

For the committer

Can Mixologic also get commit credits for being the one that came with the problem.

CommentFileSizeAuthor
#7 drupal-2850684-7.patch911 bytesRoSk0
#3 2850684-3.patch1.2 KBdaffie
#2 2850684-1.patch1.22 KBdaffie
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

daffie created an issue. See original summary.

daffie’s picture

daffie CreditAttribution: daffie commented
Status: Active » Needs review
FileSize
2850684-1.patch1.22 KB
daffie’s picture

daffie CreditAttribution: daffie commented
FileSize
2850684-3.patch1.2 KB

:schema.:table will become 'public'.'node' and not what we need it to be like:'public.node'.

daffie’s picture

daffie CreditAttribution: daffie commented

The testbot finishes with this patch a lot faster:

  1. PostgreSQL + PHP 5.5 is 10 minutes faster
  2. PostgreSQL + PHP 5.6 is 4 minutes faster
  3. PostgreSQL + PHP 7 is 8 minutes faster
daffie’s picture

daffie CreditAttribution: daffie commented

The method Schema::findTables() has the same problem as Schema::fieldExists(), it uses the "information_schema" tables. The method Schema::findTables is only used twice in the test Drupal\KernelTests\Core\Database\SchemaTest. Does anybody have any idea how much this method is used in contrib modules and third party modules. Shall we do the same for Schema::findTables or does nobody uses the method.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

RoSk0’s picture

RoSk0
he/him
Primary language Russian
Location Wellington
CreditAttribution: RoSk0 at Catalyst IT commented
FileSize
drupal-2850684-7.patch911 bytes

Re-rolled #3.

Lets take a look is it still adding that much in performance terms.

RoSk0’s picture

RoSk0
he/him
Primary language Russian
Location Wellington
CreditAttribution: RoSk0 at Catalyst IT commented
Status: Needs review » Reviewed & tested by the community

11 minutes comparing to https://dispatcher.drupalci.org/job/drupal_patches/36889/. That's pretty awesome!
I will be bold

daffie’s picture

daffie CreditAttribution: daffie commented

+1 for RTBC

mradcliffe’s picture

mradcliffe
he/him
Primary language English
CreditAttribution: mradcliffe at Kosada commented

Yeah, this patch looks like a good improvement. Awesome.

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
CreditAttribution: alexpott as a volunteer and commented
Issue tags: +Needs followup

There should be a followup to discuss #5 and the Schema::findTables() method.

daffie’s picture

daffie CreditAttribution: daffie commented
Issue tags: -Needs followup

Created the followup: #2921855: Default Database Schema::findTables() is slow for PostgreSQL.

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
CreditAttribution: alexpott as a volunteer and commented

Looks like we have a reliable segmentation fault on PHP5.5 andPostgres :( https://www.drupal.org/pift-ci-job/805466

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
CreditAttribution: alexpott as a volunteer and commented

Created #2921862: Segfault on PHP5.5 and PostgreSQL

mradcliffe’s picture

mradcliffe
he/him
Primary language English
CreditAttribution: mradcliffe at Kosada commented

Yeah.

I saw it earlier, and re-ran the test, but it's reliably seg faulting. :(

Looks like an OOM error during PHP shutdown or garbage collection? I tried looking in the PHP stack trace where fieldExists could be getting called that, but that turned into a bigger rabbit hole than I expected for the time I could put into it. It might not be in the stack trace at all, but a side effect of some other query.

It's probably happening in setUp because that's the most db intensive thing with regard to schema changes.

  • catch committed f73e805 on 8.5.x 
    Issue #2850684 by daffie, RoSk0: Default Database Schema::fieldExists()...
catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch at Third and Grove commented
Status: Reviewed & tested by the community » Fixed

8.5.x is back to green with postgres and successfully retested this patch.

Committed f73e805 and pushed to 8.5.x. Thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.