Subject: Re: [rt.cpan.org #49493] Fey loader and large database
<URL:
https://rt.cpan.org/Ticket/Display.html?id=49493 >
Fey::Loader should _only_ look at the schema definition. It never looks at
the data in the tables.
I have no idea why having a large database would make any difference here.
Can you do some further debugging on this? Maybe try to figure out where
exactly it's so slow in the Fey::Loader code? This may be specific to your
DBMS (you didn't mention which one!) and or your particular version of
that DBMS.
Unfortunately, I do not have any huge databases lying around to test with
;)
-----------------
Hi, Dave
Thank you very much for your quick response.
I am using MySQL 5.067. There are 41 tables in this database.
There is no problems for me to use Fey::Loader against our dev and test database. There is not much data in them.
I did a little bit debugging just now against our live database.
When I set DBI_TRACE level to 1, I can see the script went through each table very quickly at the beginning, doing three things for each table: primary_key_info, column_info, statistics_info.
And then keep trying to get foreign_key_info for each table. (each takes around 20 seconds, some steps may be longer, in total around 7-8 minutes). This foreign key info things mainly cause problems. Is there any way I can turn it off or make it quick?
I will do more test tomorrow.
Thanks for the help.
Regards,
Guoying Qi
<- foreign_key_info(undef, 'npgqcp', ...)= DBI::st=HASH(0xa29f120) at mysql.pm line 166
<- fetchrow_hashref= undef row-1 at DBI.pm line 299
<- DESTROY(DBI::st=HASH(a23c898))= undef at DBI.pm line 323
<- foreign_key_info(undef, 'npgqcp', ...)= DBI::st=HASH(0xa29f0e0) at mysql.pm line 166
<- fetchrow_hashref= undef row-1 at DBI.pm line 299
<- DESTROY(DBI::st=HASH(a29f180))= undef at DBI.pm line 323
<- foreign_key_info(undef, 'npgqcp', ...)= DBI::st=HASH(0xa23c708) at mysql.pm line 166
<- fetchrow_hashref= undef row-1 at DBI.pm line 299
<- DESTROY(DBI::st=HASH(a29f1f0))= undef at DBI.pm line 323
When I set to DBI_TRACE to 2, the log message like below.
<-- dbd_st_finish
<- fetchrow_hashref= undef row-1 at DBI.pm line 299
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x9f64458)~INNER)
Freeing 2 parameters, bind 0 fbind 0
<- DESTROY= undef at DBI.pm line 323
-> foreign_key_info for DBD::mysql::db (DBI::db=HASH(0x9f01970)~0x9f01ab0 undef 'npgqcp' 'lane_qc' undef undef undef)
-> dbd_st_prepare MYSQL_VERSION_ID 50051, SQL statement: 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 = ? AND A.REFERENCED_TABLE_NAME = ? ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION
Show quoted text >- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
Show quoted text>count_params statement 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 = ? AND A.REFERENCED_TABLE_NAME = ? ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION
<- dbd_st_prepare
-> dbd_st_execute for 09f64498
Show quoted text >- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 50051
Show quoted text>parse_params statement 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 = ? AND A.REFERENCED_TABLE_NAME = ? ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION
Binding parameters: 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
<- dbd_st_execute returning imp_sth->row_num 0
<- foreign_key_info= DBI::st=HASH(0x9f64198) at mysql.pm line 166
-> fetchrow_hashref for DBD::mysql::st (DBI::st=HASH(0x9f64198)~0x9f64288)
-> dbd_st_FETCH_attrib for 09f64498, key NAME_uc
-> dbd_st_FETCH_attrib for 09f64498, key NAME
-> dbd_st_fetch
dbd_st_fetch for 09f64498, chopblanks 0
dbd_st_fetch result set details
imp_sth->result=09fa7180
mysql_num_fields=15
mysql_num_rows=0
mysql_affected_rows=0
dbd_st_fetch for 09f64498, currow= 1
dbd_st_fetch, no more rows to fetch
--> dbd_st_finish
Show quoted text >- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
--
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.
