Dbix::Class slow response

Question

I have a DBIx::Class query that's taking too long to complete.

All SQL below were generated by DBIx::Class.

First scenario (DBIx Simple select):

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me ORDER BY event_time DESC LIMIT 10;

DBIx query time: 0.390221s (ok)

Second scenario (DBIx Simple select using where):

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me WHERE ( proto_id = 7 ) ORDER BY event_time DESC LIMIT 10;

DBIx query time: 29.27025s!! :(

Third scenario (Using pgadmin3 to run the query above):

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me WHERE ( proto_id = 7 ) ORDER BY event_time DESC LIMIT 10;

Pgadmin query time: 25ms (ok)

The same query is pretty fast using pgdamin.

Some info:

Catalyst 5.90091
DBIx::Class 0.082820 (latest)
Postgres 9.1
I did all tests on localhost, using Catalyst internal server.
I have no problems with any other table/column combination, it's specific with proto_id.
Database Schema automatically generated by DBIx::Class::Schema::Loader
proto_id definition:

"proto_id", { data_type => "smallint", is_foreign_key => 1, is_nullable => 0 },

Anybody have a clue why DBIx is taking so long to run this simple query?

Edit 1: Column is using index (btree).

Edit 2: This is a partitioned table, I'm checking if all the sub-tables have all the indexes, but still doesn't explain why the same query is slower on DBIx::Class.

Edit 3: I did a simple DBIx::Class script and I got the same results, just to make sure the problem is not the Catalyst Framework.

Edit 4: Using tcpdump I noticed postgres is taking too long to respond, still trying...

Edit 5: Using DBI with SQL seems pretty fast, I'm almost convinced this is a DBIx::Class problem.

You could check how long second scenario takes from mysql command line tool. This could isolate the problem to the database and not the software layer. — Kim Ryan, Jun 19 '15 at 00:13
You've tagged this as a perl question but you do not show any perl code. — Dondi Michael Stroma, Jun 19 '15 at 03:20
Good question about the index. Are the timings repeatable? Generally, the database will run the query in about the same regardless of the client that executed it, so I'd want to see the code where you did the timing. There might be other delays included in it. — tprocter, Jun 19 '15 at 11:27
hi, always around the same time, I'm getting the results from the catalyst debug. — Nilson Morais, Jun 19 '15 at 12:10
Query is generated by DBIx::Class: `$c->model('ov::Pf')->search({ -and => [@filters] },{ order_by => { -desc => 'event_time' } ,rows => $limit ,page => $page ,result_class => 'DBIx::Class::ResultClass::HashRefInflator' }) ];` This code generates the query. — Nilson Morais, Jun 19 '15 at 12:20

score 0 · Answer 1 · answered Jul 06 '15 at 20:00

After some tests, I found the problem:

When I do the query using DBI bind_param() (As DBIx::Class does) for some reason it became very slow.

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me WHERE ( proto_id = ? ) ORDER BY event_time DESC LIMIT ?;

my $sth = $dbh->prepare($sql);
$sth->bind_param(1, 80, { TYPE => SQL_INTEGER });
$sth->bind_param(2, 10, { TYPE => SQL_INTEGER });
$sth->execute();

So after some time searching CPAN I've noticed that my DBD::Pg was outdated (My bad). I downloaded the source from CPAN, compiled and the problem is gone. Must be some bug from older versions.

TL;DR: If you're having problems with DBI or DBIx::Class make sure your DBI database driver is updated.

Dbix::Class slow response

1 Answers1