[Dbix-class] Horrendous Performance Problem

Robert Inder robert at interactive.co.uk
Wed Sep 11 12:01:15 GMT 2013


I have two very similar installations of a system that uses DBIx::Class to
access a
fairly small PostgreSQL database -- dozens of tables,each with hundreds
(not thousands)
of rows, albeit with a dozens of foreign key constraints between them.

The two installations have vastly  different performance in some
situations.  In particular,
dropping tables in/via DBIx::Class::Migration takes a bit under 10 seconds
on one, and
7 minutes on the other!

I'm thinking the difference must be down to either CPAN module differences,
or  some subtle difference in the database configurations which is
affecting how
DBIx::Class::Migration is working.  Since the bad performance is coming
from the installation with some slightly newer packages, I really need to
track this down,
and  I'm hoping that someone who knows about how Migration works can save me
hour of grubbing around inside DBIx::Class::Migration...

Turning on PostgreSQL logging reveals that both systems start out with
an identical, fairly complex query on pg_catalog, followed by a series of
queries of the form
           SELECT * from "public".TABLE WHERE (1 =3D 0)
which (since "WHERE (1 =3D 0) is never going to return anything) I'm guessi=
ng
is some kind of configuration or permissions checking.

But then, the two systems diverge, and generate completely different SQL.

The system with the slightly older version of DBIx::Class (0.08202)
generates
a sequence of queries against tables in "pg_catalogue", including
"pg_catalogue.pg_constraint.  These seem to come from DBD/Pg.pm.

This same module is present on the other system, but there, the newer
version of
DBIx::Class (0.08203) generates a series of completely different queries
against
tables in INFORMATION_SCHEMA.  These seem to be coming coming from
a function "_table_fk_info()" in /DBIx/Class/Schema/Loader/DBI/Pg.pm
(version
0.07033), and it is these queries that are taking several minutes to run.

The version of /DBIx/Class/Schema/Loader/DBI/Pg.pm on the "fast" system is
0.07015:
it does not have a "_table_fk_info" function, and makes no mention of
INFORMATION_SCHEMA.

Can anyone shed any light on what is going on?  Why the two installations
are going about things in completely different ways?

Is the system with the newer modules trying to use a new feature that is
horrendously
slow?  And if so, is there something I can do (e.g to the database) to make
it faster?

Or have I got some kind of subtle CPAN version incompatibility?

Or what?


Robert.

-- =

Robert Inder,                                    0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than
words
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130911/d49=
a19dd/attachment.htm


More information about the DBIx-Class mailing list