Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 49556
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: gq1 [...] sanger.ac.uk
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: Foreign_key_info very slow for a large database
Date: Wed, 09 Sep 2009 14:05:03 +0100
To: "bug-DBD-mysql " [...] rt.cpan.org
From: Guoying Qi <gq1 [...] sanger.ac.uk>
Hi, there. I am using Fey::ORM to create table classes from MySQL(V5.067). Fey::Loader needs read all the database schema out (get columns, primary and foreign keys) and create classes for each table. I have 41 tables in this schema. This works fine against our test database without much data inside. But when I run it against our live database (100G data in total) it becomes very slow. This is caused by calling foreign_key_info for each table. Each query takes around 15 seconds. The query DBD::mysql generated for one table is listed below. I am not sure why this query takes so long against the database with large data but very quick against the database with less data. I am not sure this is the problem from MySQL or there is other simple way to get foreign key information. If you can give me any suggestion, I will really appreciate it. Thank you very much! Guoying Qi SELECT NULL AS PKTABLE_CAT, A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_CATALOG AS FKTABLE_CAT, A.TABLE_SCHEMA AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ, NULL AS UPDATE_RULE, NULL AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, NULL AS PK_NAME, NULL AS DEFERABILITY, NULL AS UNIQUE_OR_PRIMARY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, INFORMATION_SCHEMA.TABLE_CONSTRAINTS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL AND A.REFERENCED_TABLE_SCHEMA = 'npgqcp' AND A.REFERENCED_TABLE_NAME = 'lane_qc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.
Download (untitled) / with headers
text/plain 2.2k
Guoying Qi, I'm not sure why it would be slow, but I'm thinking that perhaps obtaining this information from information schema might be a better way. The only issue with this is that I would have to modify the driver to discern whether the server is less than or greater than or equal to version 5.0. Thank you! Patrick On Wed Sep 09 09:05:46 2009, gq1@sanger.ac.uk wrote: Show quoted text
> Hi, there. > > I am using Fey::ORM to create table classes from MySQL(V5.067). > Fey::Loader needs read all the database schema out (get columns, primary > and foreign keys) and create classes for each table. I have 41 tables in > this schema. > > This works fine against our test database without much data inside. > > But when I run it against our live database (100G data in total) it > becomes very slow. This is caused by calling foreign_key_info for each > table. Each query takes around 15 seconds. > > The query DBD::mysql generated for one table is listed below. I am not > sure why this query takes so long against the database with large data > but very quick against the database with less data. I am not sure this > is the problem from MySQL or there is other simple way to get foreign > key information. > > If you can give me any suggestion, I will really appreciate it. > > Thank you very much! > > Guoying Qi > > > > > SELECT NULL AS PKTABLE_CAT, > A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, > A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, > A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, > A.TABLE_CATALOG AS FKTABLE_CAT, > A.TABLE_SCHEMA AS FKTABLE_SCHEM, > A.TABLE_NAME AS FKTABLE_NAME, > A.COLUMN_NAME AS FKCOLUMN_NAME, > A.ORDINAL_POSITION AS KEY_SEQ, > NULL AS UPDATE_RULE, > NULL AS DELETE_RULE, > A.CONSTRAINT_NAME AS FK_NAME, > NULL AS PK_NAME, > NULL AS DEFERABILITY, > NULL AS UNIQUE_OR_PRIMARY > FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, > INFORMATION_SCHEMA.TABLE_CONSTRAINTS B > WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME > AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS > NOT NULL > AND A.REFERENCED_TABLE_SCHEMA = 'npgqcp' AND A.REFERENCED_TABLE_NAME = > 'lane_qc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION > >
Download (untitled) / with headers
text/plain 2.6k
To be clear, I mean use information_schema for "foreign_key_info". I'll have to look at the code. Any chance you could give me a data dump and test code, or is this proprietary? On Wed Sep 09 09:09:29 2009, CAPTTOFU wrote: Show quoted text
> Guoying Qi, > > I'm not sure why it would be slow, but I'm thinking that perhaps > obtaining this information from information schema might be a better > way. The only issue with this is that I would have to modify the driver > to discern whether the server is less than or greater than or equal to > version 5.0. > > Thank you! > > Patrick > > > On Wed Sep 09 09:05:46 2009, gq1@sanger.ac.uk wrote:
> > Hi, there. > > > > I am using Fey::ORM to create table classes from MySQL(V5.067). > > Fey::Loader needs read all the database schema out (get columns,
primary Show quoted text
> > and foreign keys) and create classes for each table. I have 41
tables in Show quoted text
> > this schema. > > > > This works fine against our test database without much data inside. > > > > But when I run it against our live database (100G data in total) it > > becomes very slow. This is caused by calling foreign_key_info for each > > table. Each query takes around 15 seconds. > > > > The query DBD::mysql generated for one table is listed below. I am not > > sure why this query takes so long against the database with large data > > but very quick against the database with less data. I am not sure this > > is the problem from MySQL or there is other simple way to get foreign > > key information. > > > > If you can give me any suggestion, I will really appreciate it. > > > > Thank you very much! > > > > Guoying Qi > > > > > > > > > > SELECT NULL AS PKTABLE_CAT, > > A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, > > A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, > > A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, > > A.TABLE_CATALOG AS FKTABLE_CAT, > > A.TABLE_SCHEMA AS FKTABLE_SCHEM, > > A.TABLE_NAME AS FKTABLE_NAME, > > A.COLUMN_NAME AS FKCOLUMN_NAME, > > A.ORDINAL_POSITION AS KEY_SEQ, > > NULL AS UPDATE_RULE, > > NULL AS DELETE_RULE, > > A.CONSTRAINT_NAME AS FK_NAME, > > NULL AS PK_NAME, > > NULL AS DEFERABILITY, > > NULL AS UNIQUE_OR_PRIMARY > > FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, > > INFORMATION_SCHEMA.TABLE_CONSTRAINTS B > > WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME > > AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS > > NOT NULL > > AND A.REFERENCED_TABLE_SCHEMA = 'npgqcp' AND A.REFERENCED_TABLE_NAME = > > 'lane_qc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION > > > >
> >
Subject: Re: [rt.cpan.org #49556] Foreign_key_info very slow for a large database
Date: Wed, 09 Sep 2009 15:07:35 +0100
To: bug-DBD-mysql [...] rt.cpan.org
From: Guoying Qi <gq1 [...] sanger.ac.uk>
Download (untitled) / with headers
text/plain 3.3k
I could give you our database schema but no problems to call foreign_key_info with small amount of data. Our live database is currently around 100G. I don't think you want this. When you have your new code, I could run my scripts to test it for you. Thanks. Patrick Galbraith via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=49556 > > > To be clear, I mean use information_schema for "foreign_key_info". I'll > have to look at the code. > > Any chance you could give me a data dump and test code, or is this > proprietary? > > On Wed Sep 09 09:09:29 2009, CAPTTOFU wrote: >
>> Guoying Qi, >> >> I'm not sure why it would be slow, but I'm thinking that perhaps >> obtaining this information from information schema might be a better >> way. The only issue with this is that I would have to modify the driver >> to discern whether the server is less than or greater than or equal to >> version 5.0. >> >> Thank you! >> >> Patrick >> >> >> On Wed Sep 09 09:05:46 2009, gq1@sanger.ac.uk wrote: >>
>>> Hi, there. >>> >>> I am using Fey::ORM to create table classes from MySQL(V5.067). >>> Fey::Loader needs read all the database schema out (get columns, >>>
> primary >
>>> and foreign keys) and create classes for each table. I have 41 >>>
> tables in >
>>> this schema. >>> >>> This works fine against our test database without much data inside. >>> >>> But when I run it against our live database (100G data in total) it >>> becomes very slow. This is caused by calling foreign_key_info for each >>> table. Each query takes around 15 seconds. >>> >>> The query DBD::mysql generated for one table is listed below. I am not >>> sure why this query takes so long against the database with large data >>> but very quick against the database with less data. I am not sure this >>> is the problem from MySQL or there is other simple way to get foreign >>> key information. >>> >>> If you can give me any suggestion, I will really appreciate it. >>> >>> Thank you very much! >>> >>> Guoying Qi >>> >>> >>> >>> >>> SELECT NULL AS PKTABLE_CAT, >>> A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, >>> A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, >>> A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, >>> A.TABLE_CATALOG AS FKTABLE_CAT, >>> A.TABLE_SCHEMA AS FKTABLE_SCHEM, >>> A.TABLE_NAME AS FKTABLE_NAME, >>> A.COLUMN_NAME AS FKCOLUMN_NAME, >>> A.ORDINAL_POSITION AS KEY_SEQ, >>> NULL AS UPDATE_RULE, >>> NULL AS DELETE_RULE, >>> A.CONSTRAINT_NAME AS FK_NAME, >>> NULL AS PK_NAME, >>> NULL AS DEFERABILITY, >>> NULL AS UNIQUE_OR_PRIMARY >>> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, >>> INFORMATION_SCHEMA.TABLE_CONSTRAINTS B >>> WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME >>> AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS >>> NOT NULL >>> AND A.REFERENCED_TABLE_SCHEMA = 'npgqcp' AND A.REFERENCED_TABLE_NAME = >>> 'lane_qc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION >>> >>> >>>
>>
> > > >
-- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.
Subject: Re: [rt.cpan.org #49556] Foreign_key_info very slow for a large database
Date: Fri, 27 Nov 2009 16:33:04 +0000
To: bug-DBD-mysql [...] rt.cpan.org
From: Guoying Qi <gq1 [...] sanger.ac.uk>
Download (untitled) / with headers
text/plain 17.5k

Message body is not shown because it is too large.

Download schema.txt
text/plain 45.2k

Message body is not shown because it is too large.

Download (untitled) / with headers
text/plain 430b
I just wanted to chime in here as well and note that I am also adversely affected by the same behavior and came to the same conclusion in tracking the performance issue down to foreign_key_info(). In my situation, some of these resulting queries are taking over ten minutes each. I'd be grateful for anything that could be done to improve the performance and would be happy to test any potential fix against my local environment.
Download (untitled) / with headers
text/plain 114b
This appears to be a known issue with MySQL 5. For more information, visit: http://bugs.mysql.com/bug.php?id=19588
Download (untitled) / with headers
text/plain 506b
Hi all, sorry I have been swamped writing a book and some family issues. This bug has nothing to do with the driver. The foreign key info comes from information schema. If that query that obtains that info is slow, that is what is slowing the DBI function. One way that could save you some headache is to use a cron job to updated memcached with that info about each table and obtain info like this from memcached. It's not like you will alter your tables often, so putting this info in a cache is ideal.
Download (untitled) / with headers
text/plain 1.2k
Bug appears to be related to the way mysql 5.0 walks the .frm files. On a large database with default settings these will cause a lot of opening and closing which was fixed in 5.1.23+ but not sure if it was back ported. The following settings from the example my-innodb-heavy-4G.cnf may help # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] table_open_cache = 2048 [mysqld_safe] # Increase the amount of open files allowed per process. Warning: Make # sure you have set the global system limit high enough! The high value # is required for a large number of opened tables open-files-limit = 8192 I am having no problems on 5.1.39-ndb-7.0.9 with 500 tables using information_schema but not tested on a low spec server. mysqld Ver 5.1.39-ndb-7.0.9-cluster-gpl-log for unknown-linux-gnu on x86_64 (MySQL Cluster Server (GPL)) Hope it helps On Fri Dec 04 09:57:42 2009, FHOXH wrote: Show quoted text
> This appears to be a known issue with MySQL 5. For more information, > visit: http://bugs.mysql.com/bug.php?id=19588
Download (untitled) / with headers
text/plain 219b
Hi all, I'm closing this bug since it's not an issue of the driver. Thanks to all who gave input on the issue. Also, please refer to the last comment pertaining to MySQL version 5.1.23 and greater by Patrick Mulvany.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.