Re: Long Fetch times
On Mar 14, 2010, at 6:51 AM, Jeff Schmitz wrote: I'm still re-running the stress tests (they take awhile) but from some preliminary testing it's looking like the problem was not having enough indexes, although I'm not 100% sure why this problem decided to manifest now, maybe just the nature of my stress tests which I recently upgraded. Probably. The change in data made the query optimizer change how it thought it should optimize the query. Adding the indexes Chuck suggested seems to have solved it. I probably need to do an entire analysis on where to add more indexes. Yes. I consider this a mandatory step before deploying an application, or any changes to an application. Even if the individual queries are not slow, if they are inefficient they can bog the system down under load. Chuck On Mar 13, 2010, at 10:49 PM, Chuck Hill wrote: Notice that of those queries, only _two_ take a significant amount of time. That is symptomatic of not having indexes that FB can use to optimize them. SELECT whatever FROM "t_combo_team" t0 INNER JOIN "t_entry" T1 ON t0."entryID" = T1."id" INNER JOIN "t_pool" T2 ON T1."poolID" = T2."id" Those columns should all have indexes, but check to be sure WHERE (T2."c_pool_type" = 0 AND T2."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE) c_pool_type should probably be indexes Using LIKE when not needed will take longer c_name should probably be indexed SELECT whatever FROM "t_team_popup" t0 INNER JOIN "t_combo_team" T1 ON t0."comboTeamID" = T1."id" INNER JOIN "t_entry" T2 ON T1."entryID" = T2."id" INNER JOIN "t_pool" T3 ON T2."poolID" = T3."id" WHERE (T3."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T3."c_pool_type" = 0) See above comments for this too. Chuck On Mar 13, 2010, at 8:22 PM, Jeff Schmitz wrote: A little more info. Here's the actual code that is doing the long running fetch, followed by the sql generated by the code. Note there there isn't all that much data in the databaes, and the fetch itself finds no matches... EOQualifier poolQual = Pool.POOL_TYPE.eq((int) poolType).and( Pool.NAME.likeInsensitive(name)); ERXFetchSpecification poolFetch = new ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null); NSArray keyPaths = new NSArray(new String[] {Pool.ENTRIES_KEY , Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY, Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY + "." + ComboTeam.TEAM_POPUPS_KEY, Pool.ENTRIES_KEY + "." + Entry.ENTRY_ADMIN_KEY}); poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths); Pool pool = (Pool) ec.objectsWithFetchSpecification(poolFetch).lastObject(); return pool; Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_admin_email", t0."c_admin_msg", t0."c_admin_pw", t0."c_calculating_can_i_wins", t0."c_can_i_win_index", t0."c_can_i_wins_calculated", t0."c_early_bird_paid", t0."c_entry_limit", t0."id", t0."c_name", t0."c_paid", t0."c_password", t0."c_pool_type", t0."c_results_index", t0."c_seed_type", t0."c_spare_string", t0."c_stand_alone", t0."c_ud_bonus", t0."c_updating_results", t0."c_weighted" FROM "t_pool" t0 WHERE (t0."c_pool_type" = 0 AND t0."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_games", t0."c_groups", t0."id", t0."c_last_save", t0."c_name", t0."c_password", t0."c_picks_confirmed", t0."poolID", t0."c_real", t0."spare_bool", t0."spare_int", t0."spare_str", t0."c_still_alive_first", t0."c_still_alive_second", t0."c_still_alive_third", t0."c_tie_breaker", t0."userID" FROM "t_entry" t0 INNER JOIN "t_pool" T1 ON t0."poolID" = T1."id" WHERE (T1."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T1."c_pool_type" = 0)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 2 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge
Re: Long Fetch times
I'm still re-running the stress tests (they take awhile) but from some preliminary testing it's looking like the problem was not having enough indexes, although I'm not 100% sure why this problem decided to manifest now, maybe just the nature of my stress tests which I recently upgraded. Adding the indexes Chuck suggested seems to have solved it. I probably need to do an entire analysis on where to add more indexes. THANKS for all your help here. Jeff On Mar 13, 2010, at 10:49 PM, Chuck Hill wrote: Notice that of those queries, only _two_ take a significant amount of time. That is symptomatic of not having indexes that FB can use to optimize them. SELECT whatever FROM "t_combo_team" t0 INNER JOIN "t_entry" T1 ON t0."entryID" = T1."id" INNER JOIN "t_pool" T2 ON T1."poolID" = T2."id" Those columns should all have indexes, but check to be sure WHERE (T2."c_pool_type" = 0 AND T2."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE) c_pool_type should probably be indexes Using LIKE when not needed will take longer c_name should probably be indexed SELECT whatever FROM "t_team_popup" t0 INNER JOIN "t_combo_team" T1 ON t0."comboTeamID" = T1."id" INNER JOIN "t_entry" T2 ON T1."entryID" = T2."id" INNER JOIN "t_pool" T3 ON T2."poolID" = T3."id" WHERE (T3."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T3."c_pool_type" = 0) See above comments for this too. Chuck On Mar 13, 2010, at 8:22 PM, Jeff Schmitz wrote: A little more info. Here's the actual code that is doing the long running fetch, followed by the sql generated by the code. Note there there isn't all that much data in the databaes, and the fetch itself finds no matches... EOQualifier poolQual = Pool.POOL_TYPE.eq((int) poolType).and( Pool.NAME.likeInsensitive(name)); ERXFetchSpecification poolFetch = new ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null); NSArray keyPaths = new NSArray(new String[] {Pool.ENTRIES_KEY , Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY, Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY + "." + ComboTeam.TEAM_POPUPS_KEY, Pool.ENTRIES_KEY + "." + Entry.ENTRY_ADMIN_KEY}); poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths); Pool pool = (Pool) ec.objectsWithFetchSpecification(poolFetch).lastObject(); return pool; Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_admin_email", t0."c_admin_msg", t0."c_admin_pw", t0."c_calculating_can_i_wins", t0."c_can_i_win_index", t0."c_can_i_wins_calculated", t0."c_early_bird_paid", t0."c_entry_limit", t0."id", t0."c_name", t0."c_paid", t0."c_password", t0."c_pool_type", t0."c_results_index", t0."c_seed_type", t0."c_spare_string", t0."c_stand_alone", t0."c_ud_bonus", t0."c_updating_results", t0."c_weighted" FROM "t_pool" t0 WHERE (t0."c_pool_type" = 0 AND t0."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_games", t0."c_groups", t0."id", t0."c_last_save", t0."c_name", t0."c_password", t0."c_picks_confirmed", t0."poolID", t0."c_real", t0."spare_bool", t0."spare_int", t0."spare_str", t0."c_still_alive_first", t0."c_still_alive_second", t0."c_still_alive_third", t0."c_tie_breaker", t0."userID" FROM "t_entry" t0 INNER JOIN "t_pool" T1 ON t0."poolID" = T1."id" WHERE (T1."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T1."c_pool_type" = 0)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 2 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."entryID", t0."c_game", t0."c_group", t0."id", t0."c_items", t0."c_score", t0."c_selected_item", t0."c_selected_weight" FROM "t_combo_team" t0 INNER JOIN "t_entry" T1 ON t0."e
Re: Long Fetch times
Hi Jeff, Just few questions to nail the problem: 1) When you execute the queries logged in your earlier directly in the database, does it have same performance/ same time to execute as you experience in the WO. 2) Try rebuilding table indices. 3) Just see if any data is corrupted in your database, there by it consumes so much time to retrieve data. 4) Might be your disk has slowed down and is not timely responding... time to replace with new disk ;-) See if any of the above nails out your issue and helps solve the problem... if so, just post back your experiences!!! Best Regards, Shravan Kumar. M Fw: Re: Long Fetch times Re: Long Fetch times Sun Mar 14 09:52:41 2010 From: "Jeff Schmitz" To: "Gaastra Dennis - WO Lists" A little more info. Here's the actual code that is doing the long running fetch, followed by the sql generated by the code. Note there there isn't all that much data in the databaes, and the fetch itself finds no matches... EOQualifier poolQual = Pool.POOL_TYPE.eq((int) poolType).and( Pool.NAME.likeInsensitive(name)); ERXFetchSpecification poolFetch = new ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null); NSArray keyPaths = newNSArray(newString[] {Pool.ENTRIES_KEY, Pool.ENTRIES_KEY+ "."+ Entry.COMBO_TEAMS_KEY, Pool.ENTRIES_KEY+ "."+ Entry.COMBO_TEAMS_KEY+ "."+ ComboTeam.TEAM_POPUPS_KEY, Pool.ENTRIES_KEY+ "."+ Entry.ENTRY_ADMIN_KEY}); poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths); Pool pool = (Pool) ec.objectsWithFetchSpecification(poolFetch).lastObject(); return pool; Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 2 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 126 row(s) processed Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 315 row(s) processed Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 2 row(s) processed Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction FileIO64 calcPickInfo - Time to prefetch entries: 41.847 Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: Mar 1
Re: Long Fetch times
Hi Jeff, As Chuck also said; please do a flat-file backup, create an empty database, and run the script to restore from the flat-file. This will give you a fresh database. Two more thinks to consider: 4) Disk fragmentation; maybe re-format the partition holding the DB. 5) Memory... 6) Research your Queries are you are doing already. Greetings, Dennis. On 2010-03-13, at 7:54 PM, Jeff Schmitz wrote: > Thanks Dennis, >comments below... > > On Mar 13, 2010, at 8:59 PM, Gaastra Dennis - WO Lists wrote: > >> Some things coming to my mind: >> >> 1) Are you using a lot of indices and/or compound indices? Sometimes when >> you have large tables, loading those indices the first time, takes a while. >> So there is a fine balance between too many and not enough indices; we have >> noticed with FB. As such, after every server restart, we "warm up" the >> database to get it going. > I don't have a lot of indices, just the default and one or two others. The > thing is, once it's gone, it's gone. I can restart, restore from live backup > (haven't tried flat files), reboot, doesn't matter, after a certain, sudden > point any fetch takes on the order of minutes, even to return no data. > >> >> 2) If your DB is too fragmented, consider writing it to a flat-file, and >> restoring it; as shown in the FB docs. > Would flat-file maybe work better than from live backup? > >> >> 3) How is your underlying storage medium doing? Enough free disk space? >> Consider deploying on SSDs. > Should be plenty. Honestly, it doesn't take that much data in the database > to get this to happen. A live backup gives a file on the order of 100 > megabytes. > > >> >> With Kind Regards, >> >> Dennis Gaastra, >> Chief Technology Officer, >> WEBAPPZ® Systems, Inc. >> >> >> >> >> >> On 2010-03-13, at 4:44 PM, Jeff Schmitz wrote: >> >>> While running some stress tests I seem to be able to get my database >>> (Frontbase) in a state where fetch times take an inordinate amount of time >>> (e.g. fetches that return no rows take a minute), and once in that state, >>> even a reboot of the machine won't fix the problem. Is there anyway to >>> recover such a database? I'll be perusing the Frontbase for any ideas, but >>> from experience, is such behavior symptomatic of any particular problem? >>> I've been running several years and haven't until now seen such behavior. >>> >>> Thanks, >>> Jeff >>> ___ >>> Do not post admin requests to the list. They will be ignored. >>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>> Help/Unsubscribe/Update your Subscription: >>> http://lists.apple.com/mailman/options/webobjects-dev/webobjects_lists%40webappz.com >>> >>> This email sent to webobjects_li...@webappz.com >> > ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
The one thing that is different about the data added is they have similar names (e.g. pool names, entry names), so your comment about not using LIKE might be a part of it since I assume finding the right name is harder when there's a lot of similar ones. On Mar 13, 2010, at 10:53 PM, Chuck Hill wrote: Let's keep this on the list. On Mar 13, 2010, at 8:46 PM, Jeff Schmitz wrote: On Mar 13, 2010, at 10:41 PM, Chuck Hill wrote: On Mar 13, 2010, at 4:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), Has your stress testing added any data to the database? It sounds like you have exposed a lack of indexes to optimize your queries, or have queries that don't optimize well. some, but not all that much. Much less than I've processed fine in the past. Has it added different data? More rows to some tables than is normal. It just takes a few rows to go from "fine" to "disaster". and once in that state, even a reboot of the machine won't fix the problem. Are the apps and the database on the same machine? Is there any load on the machine? Are the stress tests still running? Yes, everything is simple. Single app, all on the same machine. Same result on 3 different machines. Is there anyway to recover such a database? If you have actually damaged the database (which I doubt), no. I'd do a flat file export and re-import it into a new database to see. I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. It just sounds like you have added enough data that your queries are no longer sufficiently performant. I'm guessing (hoping) it's an indexing issue. That is my assumption so far. I'd try "optimize database" and do a flat file export and import. If that fixes it, you might just have index fragmentation. Chuck -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
Let's keep this on the list. On Mar 13, 2010, at 8:46 PM, Jeff Schmitz wrote: On Mar 13, 2010, at 10:41 PM, Chuck Hill wrote: On Mar 13, 2010, at 4:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), Has your stress testing added any data to the database? It sounds like you have exposed a lack of indexes to optimize your queries, or have queries that don't optimize well. some, but not all that much. Much less than I've processed fine in the past. Has it added different data? More rows to some tables than is normal. It just takes a few rows to go from "fine" to "disaster". and once in that state, even a reboot of the machine won't fix the problem. Are the apps and the database on the same machine? Is there any load on the machine? Are the stress tests still running? Yes, everything is simple. Single app, all on the same machine. Same result on 3 different machines. Is there anyway to recover such a database? If you have actually damaged the database (which I doubt), no. I'd do a flat file export and re-import it into a new database to see. I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. It just sounds like you have added enough data that your queries are no longer sufficiently performant. I'm guessing (hoping) it's an indexing issue. That is my assumption so far. I'd try "optimize database" and do a flat file export and import. If that fixes it, you might just have index fragmentation. Chuck -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
Notice that of those queries, only _two_ take a significant amount of time. That is symptomatic of not having indexes that FB can use to optimize them. SELECT whatever FROM "t_combo_team" t0 INNER JOIN "t_entry" T1 ON t0."entryID" = T1."id" INNER JOIN "t_pool" T2 ON T1."poolID" = T2."id" Those columns should all have indexes, but check to be sure WHERE (T2."c_pool_type" = 0 AND T2."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE) c_pool_type should probably be indexes Using LIKE when not needed will take longer c_name should probably be indexed SELECT whatever FROM "t_team_popup" t0 INNER JOIN "t_combo_team" T1 ON t0."comboTeamID" = T1."id" INNER JOIN "t_entry" T2 ON T1."entryID" = T2."id" INNER JOIN "t_pool" T3 ON T2."poolID" = T3."id" WHERE (T3."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T3."c_pool_type" = 0) See above comments for this too. Chuck On Mar 13, 2010, at 8:22 PM, Jeff Schmitz wrote: A little more info. Here's the actual code that is doing the long running fetch, followed by the sql generated by the code. Note there there isn't all that much data in the databaes, and the fetch itself finds no matches... EOQualifier poolQual = Pool.POOL_TYPE.eq((int) poolType).and( Pool.NAME.likeInsensitive(name)); ERXFetchSpecification poolFetch = new ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null); NSArray keyPaths = new NSArray(new String[] {Pool.ENTRIES_KEY , Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY, Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY + "." + ComboTeam.TEAM_POPUPS_KEY, Pool.ENTRIES_KEY + "." + Entry.ENTRY_ADMIN_KEY}); poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths); Pool pool = (Pool) ec.objectsWithFetchSpecification(poolFetch).lastObject(); return pool; Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_admin_email", t0."c_admin_msg", t0."c_admin_pw", t0."c_calculating_can_i_wins", t0."c_can_i_win_index", t0."c_can_i_wins_calculated", t0."c_early_bird_paid", t0."c_entry_limit", t0."id", t0."c_name", t0."c_paid", t0."c_password", t0."c_pool_type", t0."c_results_index", t0."c_seed_type", t0."c_spare_string", t0."c_stand_alone", t0."c_ud_bonus", t0."c_updating_results", t0."c_weighted" FROM "t_pool" t0 WHERE (t0."c_pool_type" = 0 AND t0."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_games", t0."c_groups", t0."id", t0."c_last_save", t0."c_name", t0."c_password", t0."c_picks_confirmed", t0."poolID", t0."c_real", t0."spare_bool", t0."spare_int", t0."spare_str", t0."c_still_alive_first", t0."c_still_alive_second", t0."c_still_alive_third", t0."c_tie_breaker", t0."userID" FROM "t_entry" t0 INNER JOIN "t_pool" T1 ON t0."poolID" = T1."id" WHERE (T1."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T1."c_pool_type" = 0)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 2 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."entryID", t0."c_game", t0."c_group", t0."id", t0."c_items", t0."c_score", t0."c_selected_item", t0."c_selected_weight" FROM "t_combo_team" t0 INNER JOIN "t_entry" T1 ON t0."entryID" = T1."id" INNER JOIN "t_pool" T2 ON T1."poolID" = T2."id" WHERE (T2."c_pool_type" = 0 AND T2."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE)" withBindings: > Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 126 row(s) processed Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Inter
Re: Long Fetch times
On Mar 13, 2010, at 7:54 PM, Jeff Schmitz wrote: Thanks Dennis, comments below... On Mar 13, 2010, at 8:59 PM, Gaastra Dennis - WO Lists wrote: Some things coming to my mind: 1) Are you using a lot of indices and/or compound indices? Sometimes when you have large tables, loading those indices the first time, takes a while. So there is a fine balance between too many and not enough indices; we have noticed with FB. As such, after every server restart, we "warm up" the database to get it going. I don't have a lot of indices, just the default and one or two others. That sounds like "not near enough". The thing is, once it's gone, it's gone. I can restart, restore from live backup (haven't tried flat files), reboot, doesn't matter, after a certain, sudden point any fetch takes on the order of minutes, even to return no data. It is not how much data it returns, it is how much data it has to sift though first. 2) If your DB is too fragmented, consider writing it to a flat- file, and restoring it; as shown in the FB docs. Would flat-file maybe work better than from live backup? Yes. But if you problem is lack of indexes / too much data, that won't help. Also try running optimize database; in the SQL pane of FrontBaseManager. Chuck 3) How is your underlying storage medium doing? Enough free disk space? Consider deploying on SSDs. Should be plenty. Honestly, it doesn't take that much data in the database to get this to happen. A live backup gives a file on the order of 100 megabytes. With Kind Regards, Dennis Gaastra, Chief Technology Officer, WEBAPPZ® Systems, Inc. On 2010-03-13, at 4:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), and once in that state, even a reboot of the machine won't fix the problem. Is there anyway to recover such a database? I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. Thanks, Jeff ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/webobjects_lists%40webappz.com This email sent to webobjects_li...@webappz.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net This email sent to ch...@global-village.net -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
On Mar 13, 2010, at 6:40 PM, Jeff Schmitz wrote: btw, I have tried backing up (live backup) and restoring to no avail. If there is a problem in the database, that just copies the problem and restores it. Chuck Jeff On Mar 13, 2010, at 6:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), and once in that state, even a reboot of the machine won't fix the problem. Is there anyway to recover such a database? I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. Thanks, Jeff ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/jeffandmonica%40mac.com This email sent to jeffandmon...@mac.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net This email sent to ch...@global-village.net -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
On Mar 13, 2010, at 4:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), Has your stress testing added any data to the database? It sounds like you have exposed a lack of indexes to optimize your queries, or have queries that don't optimize well. and once in that state, even a reboot of the machine won't fix the problem. Are the apps and the database on the same machine? Is there any load on the machine? Are the stress tests still running? Is there anyway to recover such a database? If you have actually damaged the database (which I doubt), no. I'd do a flat file export and re-import it into a new database to see. I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. It just sounds like you have added enough data that your queries are no longer sufficiently performant. Chuck -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
A little more info. Here's the actual code that is doing the long running fetch, followed by the sql generated by the code. Note there there isn't all that much data in the databaes, and the fetch itself finds no matches... EOQualifier poolQual = Pool.POOL_TYPE.eq((int) poolType).and( Pool.NAME.likeInsensitive(name)); ERXFetchSpecification poolFetch = new ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null); NSArray keyPaths = new NSArray(new String[] {Pool.ENTRIES_KEY , Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY, Pool.ENTRIES_KEY + "." + Entry.COMBO_TEAMS_KEY + "." + ComboTeam.TEAM_POPUPS_KEY, Pool.ENTRIES_KEY + "." + Entry.ENTRY_ADMIN_KEY}); poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths); Pool pool = (Pool) ec.objectsWithFetchSpecification(poolFetch).lastObject(); return pool; Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_admin_email", t0."c_admin_msg", t0."c_admin_pw", t0."c_calculating_can_i_wins", t0."c_can_i_win_index", t0."c_can_i_wins_calculated", t0."c_early_bird_paid", t0."c_entry_limit", t0."id", t0."c_name", t0."c_paid", t0."c_password", t0."c_pool_type", t0."c_results_index", t0."c_seed_type", t0."c_spare_string", t0."c_stand_alone", t0."c_ud_bonus", t0."c_updating_results", t0."c_weighted" FROM "t_pool" t0 WHERE (t0."c_pool_type" = 0 AND t0."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 1 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_games", t0."c_groups", t0."id", t0."c_last_save", t0."c_name", t0."c_password", t0."c_picks_confirmed", t0."poolID", t0."c_real", t0."spare_bool", t0."spare_int", t0."spare_str", t0."c_still_alive_first", t0."c_still_alive_second", t0."c_still_alive_third", t0."c_tie_breaker", t0."userID" FROM "t_entry" t0 INNER JOIN "t_pool" T1 ON t0."poolID" = T1."id" WHERE (T1."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T1."c_pool_type" = 0)" withBindings: > Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 2 row(s) processed Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:15:58 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."entryID", t0."c_game", t0."c_group", t0."id", t0."c_items", t0."c_score", t0."c_selected_item", t0."c_selected_weight" FROM "t_combo_team" t0 INNER JOIN "t_entry" T1 ON t0."entryID" = T1."id" INNER JOIN "t_pool" T2 ON T1."poolID" = T2."id" WHERE (T2."c_pool_type" = 0 AND T2."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE)" withBindings: > Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 126 row(s) processed Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:07 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_combo_index", t0."comboTeamID", t0."c_group", t0."id", t0."c_pool_type", t0."c_seed", t0."c_team_id", t0."c_team_pos" FROM "t_team_popup" t0 INNER JOIN "t_combo_team" T1 ON t0."comboTeamID" = T1."id" INNER JOIN "t_entry" T2 ON T1."entryID" = T2."id" INNER JOIN "t_pool" T3 ON T2."poolID" = T3."id" WHERE (T3."c_name" LIKE '0HOGANS HOOPSTERS' COLLATE INFORMATION_SCHEMA.CASE_INSENSITIVE AND T3."c_pool_type" = 0)" withBindings: > Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - 315 row(s) processed Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Commit Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - === Begin Internal Transaction Mar 13 22:16:39 netBrackets[] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog - evaluateExpression: "SELECT t0."c_admin_ok", t0."id", t0."c_late_pick" FROM "t_entry_admin" t0 INNER JOIN "t_ent
Re: Long Fetch times
Thanks Dennis, comments below... On Mar 13, 2010, at 8:59 PM, Gaastra Dennis - WO Lists wrote: Some things coming to my mind: 1) Are you using a lot of indices and/or compound indices? Sometimes when you have large tables, loading those indices the first time, takes a while. So there is a fine balance between too many and not enough indices; we have noticed with FB. As such, after every server restart, we "warm up" the database to get it going. I don't have a lot of indices, just the default and one or two others. The thing is, once it's gone, it's gone. I can restart, restore from live backup (haven't tried flat files), reboot, doesn't matter, after a certain, sudden point any fetch takes on the order of minutes, even to return no data. 2) If your DB is too fragmented, consider writing it to a flat-file, and restoring it; as shown in the FB docs. Would flat-file maybe work better than from live backup? 3) How is your underlying storage medium doing? Enough free disk space? Consider deploying on SSDs. Should be plenty. Honestly, it doesn't take that much data in the database to get this to happen. A live backup gives a file on the order of 100 megabytes. With Kind Regards, Dennis Gaastra, Chief Technology Officer, WEBAPPZ® Systems, Inc. On 2010-03-13, at 4:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), and once in that state, even a reboot of the machine won't fix the problem. Is there anyway to recover such a database? I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. Thanks, Jeff ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/webobjects_lists%40webappz.com This email sent to webobjects_li...@webappz.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
Some things coming to my mind: 1) Are you using a lot of indices and/or compound indices? Sometimes when you have large tables, loading those indices the first time, takes a while. So there is a fine balance between too many and not enough indices; we have noticed with FB. As such, after every server restart, we "warm up" the database to get it going. 2) If your DB is too fragmented, consider writing it to a flat-file, and restoring it; as shown in the FB docs. 3) How is your underlying storage medium doing? Enough free disk space? Consider deploying on SSDs. With Kind Regards, Dennis Gaastra, Chief Technology Officer, WEBAPPZ® Systems, Inc. On 2010-03-13, at 4:44 PM, Jeff Schmitz wrote: > While running some stress tests I seem to be able to get my database > (Frontbase) in a state where fetch times take an inordinate amount of time > (e.g. fetches that return no rows take a minute), and once in that state, > even a reboot of the machine won't fix the problem. Is there anyway to > recover such a database? I'll be perusing the Frontbase for any ideas, but > from experience, is such behavior symptomatic of any particular problem? > I've been running several years and haven't until now seen such behavior. > > Thanks, > Jeff > ___ > Do not post admin requests to the list. They will be ignored. > Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) > Help/Unsubscribe/Update your Subscription: > http://lists.apple.com/mailman/options/webobjects-dev/webobjects_lists%40webappz.com > > This email sent to webobjects_li...@webappz.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Long Fetch times
btw, I have tried backing up (live backup) and restoring to no avail. Jeff On Mar 13, 2010, at 6:44 PM, Jeff Schmitz wrote: While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), and once in that state, even a reboot of the machine won't fix the problem. Is there anyway to recover such a database? I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. Thanks, Jeff ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/jeffandmonica%40mac.com This email sent to jeffandmon...@mac.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Long Fetch times
While running some stress tests I seem to be able to get my database (Frontbase) in a state where fetch times take an inordinate amount of time (e.g. fetches that return no rows take a minute), and once in that state, even a reboot of the machine won't fix the problem. Is there anyway to recover such a database? I'll be perusing the Frontbase for any ideas, but from experience, is such behavior symptomatic of any particular problem? I've been running several years and haven't until now seen such behavior. Thanks, Jeff ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com