[
http://mifosforge.jira.com/browse/MIFOS-4043?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=61181#action_61181
]
johnwoodlock commented on MIFOS-4043:
-------------------------------------
Jeff, this is actually a mysql problem with its information_schema database
(which is its dictionary database) and not dependent on database size (as far
as I've been able to determine) which is why you get it on a tiny volume (as do
I).
The upgrade conditionally adds 13 foreign keys via a temporary stored procedure
if a certain query count is 0. That query count is the problem. Anyhow, 2
changes to the query seem to fix this.
1. don't do a join.... just use from tableA, tableB syntax
2. use the schema() function everywhere you can
(http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
has relevant hints)
Its so long since I've done a java change I'm not sure which version(s) to
update.... Is there a possibility Buddy or Vivek (or someone else if not
possible) can implement the query segment replacement below after having had a
review of it?
Alternatively, there is a good chance that the 'cost' is always about 25 mins
regardless of db size and you might decide to bite it (although it is annoying
to wait so long) but you'd have to upgrade a large dataset like enda or gk or
even secdep to confirm that.
Finally, I'm on mysql version 5.1.45-community running on windows vista. And
there is some possibility that this mysql poor performance is version/platform
dependent.
Replace:
select
COUNT(*) into @fkCount
from
information_schema.table_constraints c
join information_schema.key_column_usage u
on (
c.CONSTRAINT_NAME = u.CONSTRAINT_NAME and
c.TABLE_SCHEMA = u.TABLE_SCHEMA and
c.CONSTRAINT_SCHEMA = u.CONSTRAINT_SCHEMA
)
where
c.constraint_schema=SCHEMA() and
c.TABLE_SCHEMA=SCHEMA() and
c.constraint_type=''foreign key'' and
With:
select
COUNT(*) into @fkCount
from
information_schema.table_constraints c,
information_schema.key_column_usage u
where
c.constraint_schema=SCHEMA() and
c.TABLE_SCHEMA=SCHEMA() and
c.constraint_type=''foreign key'' and
c.CONSTRAINT_NAME = u.CONSTRAINT_NAME and
u.constraint_schema=SCHEMA() and
u.TABLE_SCHEMA=SCHEMA() and
> Performance issue with DB upgrade 1279140399
> --------------------------------------------
>
> Key: MIFOS-4043
> URL: http://mifosforge.jira.com/browse/MIFOS-4043
> Project: mifos
> Issue Type: Bug
> Components: Database
> Affects Versions: Release E
> Reporter: jbrewster
> Assignee: johnwoodlock
> Priority: Critical
> Fix For: Release E - Iteration 10, Release E
>
> Attachments: sdemodata.zip, upgrade1279140399.sql
>
>
> Database upgrade with timestamp 1279140399 taking over 25 minutes on my local
> machine when upgrading a small data set. (data set attached). Reminded me
> that Sungard had the same issue during an upgrade test last week (different
> data)
> - Why is this taking so long?
> - Can we show progress for long (+1 minute) upgrades?
> - Will this upgrade be so slow that users won't have time to upgrade?
> Attaching data set used for upgrade. On console I see:
> applying upgrade with timestamp: 1277565300
> applying upgrade with timestamp: 1277565388
> applying upgrade with timestamp: 1278542119
> applying upgrade with timestamp: 1278542138
> applying upgrade with timestamp: 1278542152
> applying upgrade with timestamp: 1278542171
> applying upgrade with timestamp: 1279140399 <------- waiting for this upgrade
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://mifosforge.jira.com/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Nokia and AT&T present the 2010 Calling All Innovators-North America contest
Create new apps & games for the Nokia N8 for consumers in U.S. and Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
http://p.sf.net/sfu/nokia-dev2dev
_______________________________________________
Mifos-issues mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-issues
