Thanks David,
Yes, from reading the Apple docs and wiki and prior discussions,
I'm using pre-fetching pretty extensively, and have things pretty well
tuned in that regard I think. Some of my pre-fetches are quite large
(up to 6-7 seconds under good circumstances) but they do help bring
down the overall processing time considerably. What's interesting is
that under load, the prefetches themselves start to take longer and
longer until they start taking over 10 times longer to complete. I
would have thought during the pre-fetch the DB would be locked, so I
was surprised to see this, but then I'm no DB expert by a long shot.
Anyway, here's the prefetch code used by the background task:
ERXFetchSpecification poolFetch = new
ERXFetchSpecification(_Pool.ENTITY_NAME, poolQual, null);
NSArray <String> 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_SCORE_KEY,
Pool.ENTRIES_KEY + "." + Entry.ENTRY_ADMIN_KEY,
Pool.ENTRIES_KEY + "." + Entry.PLACE_INFOS_KEY});
poolFetch.setPrefetchingRelationshipKeyPaths(keyPaths);
poolFetch.setRefreshesRefetchedObjects(false);
Pool pool = (Pool)
ec.objectsWithFetchSpecification(poolFetch).lastObject();
A typical fetch might involve the following number of rows:
1 Pool---100>>Entry----63>>ComboTeams----2>>TeamPopups
Entry----1>EntryScore
Entry----1>EntryAdmin
Entry----1>PlaceInfos
Note that this is run on a brand new EO and OBS stack, so I set
refreshesRefetchedObjects to false. One other phenomenon I've noticed
is that running two instances of my app also tends to kill
performance. I do have -WOAllowsConcurrentRequestHandling YES set in
the javamonitor config.
On Mar 31, 2009, at 4:55 AM, David Avendasora wrote:
On Mar 30, 2009, at 8:04 PM, Jeff Schmitz wrote:
Hello,
This is a very timely thread for me, and very interesting blog
entry. In my app, I get hit several times a day with the perfect
storm of having to deal with extremely high peak traffic from
users(mostly, but not all, read only) at the exact time that I have
to run a background thread that needs to rip through the database,
reading and updating a huge amount of data. The changes made by
the background task need to be made available to the users ASAP
(that's why they are there, they want their data and they want it
now!). After suffering through two of these peaks where the app
response and the background thread both got slower and slower until
they finally ground to a halt and required a reboot of the server,
I decided to put the app in maintenance mode (relegating users to
the main page) for the 20 or so minutes it takes the background
thread to complete, and then let everyone in. Doing it this way,
the background thread really rips through its calculations, and
then the server has no problem serving the high peak traffic with
the background thread out of the way. However I really don't like
having to lock people out several times a day at peak traffic. I
was wondering if there were some type of architecture that could be
used where this kind of case could be handled more smoothly, or
perhaps this is a sign of something I'm doing wrong at a lower
level. Note that it seems like the real problems occur when I have
two threads with two different OSC stacks (the background thread
has its own) hitting the same database at the same time. Could it
be something as simple as putting a delay in my background task?
Note that it is doing a lot of in memory calculations, so it's not
like it's constantly hitting the DB, although it does hit it pretty
hard I think. Would fetching raw rows in the background task
really make that much of a difference? It's still hitting (and
locking) the database, right? Anyway, just wondering if there are
others dealing with this type of situation and how they handle it?
The first thing I'd do is turn on SQL Logging so you can see just
what the app is doing with the database. If you're using Wonder (and
you should be) just put this in your properties file:
log4j.logger.er.transaction.adaptor.EOAdaptorDebugEnabled=DEBUG
Because EOF does not load any data it doesn't need, you will end up
with thousands of little SQL queries that return exactly one row of
a table. You can change this behavior by using a Fetch Specification
that prefetches related objects.
Below is an example where I want to load all the CustomerOrders in
the database into an array. I know that I'm going to be stepping
through this array and will be using the related customer() and
customerOrderItems() for each CustomerOrder as well. I should go to
the database and get them all together with the fewest possible SQL
queries.
(Note, I'm using the most verbose way of doing this just to make it
clear)
EOFetchSpecification fs = new EOFetchSpecification();
fs.setEntityName(CustomerOrder.ENTITY_NAME);
// Prefetching setup starts here.
NSMutableArray<String> keyPaths = new NSMutableArray<String>();
keyPaths.addObject(CustomerOrder.CUSTOMER_ORDER_ITEMS_KEY);
keyPaths.addObject(CustomerOrder.CUSTOMER);
fs.setPrefetchingRelationshipKeyPaths(keyPaths.immutableClone());
// Prefetching setup ends here, the FetchSpecification will now
fetch all the related objects too.
fs.setRefreshesRefetchedObjects(true);
NSArray<CustomerOrder> customerOrders =
ec.objectsWithFetchSpecification(fs);
for (CustomerOrder customerOrder : customerOrders) {
if (log.isDebugEnabled()) {
/**
* Without prefetching, EOF will have to go back to the DB at this
point
* to load the Customer.
*/
log.debug(customerOrder.customer() + customerOrder);
}
/**
* Without Prefetching at this point EOF has only the faults for all
the customerOrderItems,
* which is enough to set up the iteration.
*/
for (CustomerOrderItem customerOrderItem :
customerOrder.customerOrderItems()) {
if (log.isDebugEnabled()) {
/**
* Without prefetching, EOF will have to go back to the DB at this
point
* to load the CustomerOrderItem. Yes, One. At. A. Time.
Yikes!
*/
log.debug(" - " + customerOrderItem());
}
}
}
In this example, I'm telling EOF to fetch and cache the related
customer() and customerOrderItems() at the same time it fetches the
CustomerOrders because I know that I'm going to be using them right
away. This makes the initial fetch take longer, but it keeps EOF
from having to go back and hit the database once for _every_
Customer and CustomerOrderItem on each CustomerOrder. Since we have
hundreds of current CustomerOrders and thousands of
CustomerOrderItems, this could have been thousands of very small SQL
queries being run as I stepped through the array of CusotmerOrders.
Depending upon your database setup (is it on the same machine or
across the network? Are the fields you are fetching on indexed?)
this can make a huge impact.
Prefetching works great if you know you are going to be needing the
information. EOF can't make that kind of judgement call on it's own.
I hope this helps.
Dave
Thanks,
Jeff
On Mar 29, 2009, at 3:26 PM, Guido Neitzer wrote:
On 29. Mar. 2009, at 13:08 , Ren, Kevin wrote:
I think it's about EOF professing.
But if you have both, that's great.
First of all: What EXACTLY is your goal?
There are several ways of dealing with concurrency:
1. Switch on concurrent request handling with the property:
-DWOAllowsConcurrentRequestHandling=true
Note that this was set through JavaMonitor with the -D property
notation.
Using concurrent request handling has several implications (see
EOF part).
2. Use more instances. This might be the least painful way in
regard of locking issues, but the most painful in regard of data
freshness.
3. Define your bottlenecks better.
4. Use multiple EOF connections to the database (Wonder has ways
of doing this automatically).
As soon as you have concurrent request handling you need to deal
with the following:
- Data freshness
- Caching
- Locking
- Valid data (Which write wins? Dealing with freshness again.)
You get most of this for free if you use ProjectWonder, which I
highly recommend. You need to use correct locking of your editing
contexts (see ERXEC from Wonder or MultiECLockManager, search on
Google for more information).
If you just enable concurrent request handling, EOF will still
stay single threaded for one instance as long as you don't use for
example a new object store coordinator for long running
transactions.
Be aware that all this will bring you into dead locking and data
freshness hell as long as you don't really know, what you're doing
OR as long as you don't use the "make me and the gods of EOF happy
features" from Wonder.
cug
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/jeffandmonica%40mac.com
This email sent to [email protected]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/webobjects%40avendasora.com
This email sent to [email protected]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
