Bug #49556 for DBD-mysql: Foreign_key_info very slow for a large database

Wed Sep 09 09:05:46 2009 gq1 [...] sanger.ac.uk - Ticket created

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.

Wed Sep 09 09:09:29 2009 CAPTTOFU [...] cpan.org - Correspondence added

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

Wed Sep 09 09:09:29 2009 The RT System itself - Status changed from 'new' to 'open'

Wed Sep 09 09:43:37 2009 CAPTTOFU [...] cpan.org - Correspondence added

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

> >

Wed Sep 09 10:07:57 2009 gq1 [...] sanger.ac.uk - Correspondence added

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>

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.

Fri Nov 27 11:33:29 2009 gq1 [...] sanger.ac.uk - Correspondence added

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>

Message body is not shown because it is too large.

Tue Dec 01 10:06:29 2009 FHOXH [...] cpan.org - Correspondence added

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.

Fri Dec 04 09:57:42 2009 FHOXH [...] cpan.org - Correspondence added

This appears to be a known issue with MySQL 5. For more information, visit: http://bugs.mysql.com/bug.php?id=19588

Fri Dec 04 15:15:46 2009 CAPTTOFU [...] cpan.org - Correspondence added

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.

Thu Feb 11 09:54:31 2010 paddy [...] firedrake.org - Correspondence added

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

Wed Apr 14 10:57:49 2010 CAPTTOFU [...] cpan.org - Correspondence added

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.

Wed Apr 14 10:57:51 2010 CAPTTOFU [...] cpan.org - Status changed from 'open' to 'resolved'