SQL Tuning to Address Job & Task Performance

Thu, 23 Apr 2009 18:28:08 Z

Like others I see posting here, we are experiencing performance problems on our Windows HPC 2008 cluster with task management in jobs that include 100's of tasks. I see from those other posts that MSFT is working to address these performance problems. Will that come in the form of a service pack or other? When can we expect some help?

As for right now, I'd like to ask about what we can do to tune/optimize the SQL database. To give some background, our application architecture has a workflow web service that is directing work on our cluster. This workflow web service is using the .NET Scheduler API to add tasks to existing jobs.

Here's what we're doing to squeeze better performance out of the scheduling and some questions around each.

1) We've found that it takes seconds to establish a connection to scheduler so we're implementing a connection pool to manage ISchedulerJob instances

2) We've run SQL Profiler on the CCPClusterService database and see many operations that are taking 10+ secs so we're taking standard database administration steps to seperate data, indexes, and log files as well as run maintenance plans on the database. Can we do any of the following?

- use x64 SQL Server?

- use SQL 2008?

- offload SQL load on to another box? I know HPC Head Node wants a COMPUTECLUSTER named instance but can it be remote?

- tweak fill factor values to limit index fragmentation?

- modify indexing? does MSFT have any revised index guidance?

I appreciate any feedback from the community and will share any practices that we come up with.

Thanks!

Luke

SQL Tuning to Address Job & Task Performance

Fri, 24 Apr 2009 06:24:04 Z

Surly, it would be a huge help for us to get more information. What types of jobs are you submitting? How are you submitting them (code sample would be great!)? How long do you expect it to take vs. how long is it actually taking?

More information on how to configure SQL with HPC is available here: http://go.microsoft.com/fwlink/?LinkId=137791

Thanks!
Josh

-Josh

SQL Tuning to Address Job & Task Performance

Fri, 24 Apr 2009 18:00:50 Z

Hi Josh,

Thanks for your reply and the pointer to that doc. I reviewed it quickly and will go back in more detail. From what I see, it is not possible to use a remote SQL database. If that is true then it is a real flaw in the architecture that I hope you guys can address in next release, because most everyone has $'s committed to a robust SQL server with sophisticated storage backing it before HPC comes into the picture. Allow us to use that infrastructure as the SQL backend for the head node too. Can't you just expose a connection string somewhere?

We've got a 16 node, 128 core cluster that is running image processing on very large images - several gigabytes. We've got a >25 step hierarchical process that we run on each image and it is totally data-driven i.e. what we find in the image drives what the next step in the process is. A job represents processing of one image and the tasks are dynamically determined and populated via workflow web service. We are not using MPI yet so all of our parallelism is achieved thru scheduler - dynamically subdividing the image into processing units and executing each of those units as a task. The EXE run by each task calls back to the workflow web service (WS) when complete, tells WS what it did, the WS loads up the ISchedulerJob from HPC via IScheduler connection, determines if all sibling tasks are complete, and if so then adds the next task in the sequence.

A couple odds and ends:

- I said in my original email that we are pooling ISchedulerJob instances but I meant IScheduler instances

- As a rule of thumb, our tasks will not take less than 30 seconds. However, we could have 100+ tasks complete within a couple seconds of each other. Each of them would call back to the web service and cause a lookup of the job on the head node. The last one to finish would cause another 100+ tasks to be added to the job and this happens one-by-one because there is no way to throw a collection of tasks over with one call that we know of. This all should occur in a couple seconds (1-5). I think API support for N tasks would really solve this issue because you could do one roundtrip to the database instead of N.

- Our approach is partially complicated by struggles to make Task Dependencies work. My recollection is that we couldn't get these to work w/o a pre-existing Job Template. Again, our process is quite dynamic so the pre-constructed job template isn't flexible enough. Are there known bugs in this area of Task Dependencies?

I'll ask my engineer responsible for these features to post code examples of our task addition and example of the task dependency problems we're experiencing.

Thanks!

Luke

SQL Tuning to Address Job & Task Performance

Fri, 24 Apr 2009 18:20:57 Z

Josh,

One other thing, it would be EXTREMELY helpful to us if the task that failed was differentiated from all the others that are cancelled. When one task fails it causes the others to be cancelled but they also show up as in Failed state in the Cluster/Job Manager's Job view. This forces us to hunt through everyone of them currently.

-Luke

SQL Tuning to Address Job & Task Performance

Fri, 24 Apr 2009 20:00:09 Z

Josh,

Below is a code example where you can't get two tasks to converge back into one task. See code for more notes.

-Scott

//// UNIT TEST SET #1 OUTPUT

Took 749 ms to connect to devhead

Job 5024

Adding tasks - Level A