[Dbix-class] Re: Horrendous Performance Problem

Dagfinn Ilmari Mannsåker ilmari at ilmari.org
Sat Sep 14 18:25:14 GMT 2013


Robert Inder <robert at interactive.co.uk> writes:

> 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.
[...]
> 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.

I'm surprised these queries take _that_ long, even with dozens of tables
and relationships. Could you try running the query from _table_fk_info
manually (both with and without the WHERE clause) in psql and see how
long it takes? Type \timing at the psql prompt to turn on timing
information.

On the schema created by the DBIx::Class::Schema::Loader test suite,
which has 54 tables and 39 constraints, it takes about 25ms for a
specific table, and 150ms without the where clause. This is on
PostgreSQL 9.2.4 on my laptop. Which verson of are you running?

> 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?

The newer version of DBIx::Class::Schema::Loader extracts more
information about foreign key constraints (specifically ON UPDATE/DELETE
and DEFERRABLE clauses), so it runs different queries. On closer
inspection (and by running the test suite), it seems like the
Pg-specific _table_fk_info function is redundant, and the generic one in
DBI.pm suffices. Can you try disabling the former (just change the name
of the sub in DBIx/Class/Schema/Loader/DBI/Pg.pm, so the generic one is
inherited) and see if that affects the performance?

-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.               - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.                      - Calle Dybedahl




More information about the DBIx-Class mailing list