Transcript Slide 1

ASNApalooza 2007
Maximum velocity: How to get the
most performance from your AVR
apps
Paying attention to just a few details
can make your programs faster and
more efficient
by Roger Pence
ASNA Education Director
1
ASNApalooza 2007
Overview of topics
• Use connection pooling to improve file IO
performance
• Using the singleton DB pattern
• Beware shared DB’s
• Using threading to improve performance
2
ASNApalooza 2007
The proper use of
connection pooling
No single thing will more improve Web
application performance that proper use of
connection pooling
ASNApalooza 2007
What is connection pooling?
• Connection pooling is DataGate’s ability to
“reuse” database server jobs between
stateless IO requests
• Connection pooling applies mostly to stateless
Web applications or Web services.
• Rarely is connection pooling helpful to
Windows-based programs
– It’s not likely to hurt anything, but connection
pooling is especially suited for stateless apps
ASNApalooza 2007
Connection pooling: the System i
• Effective connection pooling is especially
important if the System i is your database
platform
• Without connection pooling, a new OS/400 job
must be created for each request. This can take 310 seconds—depending on System i load and
configuration
• With connection pooling, an OS/400 job is made
available to a request in a matter of milliseconds
Connection pooling: SQL Server
ASNApalooza 2007
• Although instancing a database connection isn’t
quite the performance drain on SQL Server that it
is on the System i, it’s still a drain
• It may also positively effect SQL Server licensing
– Because you are in effect also pooling licensing (for
CAL-based licenses)
– Connection pooling keeps any one connection is play
for the minimum amount of time
• This discussion is primarily System i-centric, but
everything said here about connection pooling
also applies to DataGate for SQL Server
ASNApalooza 2007
Enabling connection pooling
• Connection pooling can be enabled statically
through database name attributes
• Or set dynamically through changing the
database object’s PoolingTimeOut property
prior to connecting that object
Enabling connection pooling statically
ASNApalooza 2007
• Connection pooling is enabled with the
DataGate Database Manager’s “Work with
Database Names”panel
ASNApalooza 2007
Enabling connection pooling
dynamically
• Connection pooling can also be controlled by
setting the database object’s PoolingTimeout
value.
• Any value greater than zero sets the connection
pooling timeout value (in minutes); zero disables
connection pooling
ASNApalooza 2007
What’s a good time-out value?
• That depends.
• For many shops, something in the range of 20
minutes or so works well
• However, we have customers that use much
larger values if the latency between pages is
extreme
– Shop floor browser-based applications for
example
ASNApalooza 2007
How does connection pooling it work?
• Red zone= inactive pooled jobs
• Green zone = active jobs
• At this point, application has no jobs pooled
Blue and pink: cool and warm
ASNApalooza 2007
• For purposes of this discussion:
– Blue users get new, previously unpooled jobs.
These uses wait 3-7 seconds for their page.
– Blue jobs are new, previously unpooled jobs.
– Pink users get a pooled job. These users wait just a
few milliseconds for their page.
– Pink jobs are previously pooled jobs.
Think of it this way, blue users/jobs are cool;
pink users/jobs are warm!
A user request occurs
ASNApalooza 2007
• DataGate first looks in the red job zone for a
previously pooled job. There isn’t one. DataGate
starts a new one.
ASNApalooza 2007
The job is now pooled
• When the server is done servicing the user’s request,
the job is “moved” to the pooled zone
• Pooled jobs have no open files, no activity, and
consume very few OS/400 resources
Same user requests a second page
ASNApalooza 2007
• Because there is a job in the pooled zone, that job is
made available in just a few milliseconds
The job is put back in the pool
ASNApalooza 2007
• When the server is done servicing the user’s request,
the job is “moved” to the pooled zone again
Two users request a page within 1 MS
of each other
ASNApalooza 2007
• The first user gets the previously pooled job
• The second user has to wait for OS/400 to create a
new job
Now two jobs are pooled
ASNApalooza 2007
• Two jobs are pooled
• With two jobs pooled, the two users are both
guaranteed a pooled job
Three users request a page within a
couple of MS of each other
ASNApalooza 2007
• Two user get a pooled job; the third user waits for a new job
• For most Web apps, jobs aren’t unique to a user, so the two
pooled jobs are avaiable to the first two users—independent
of who previously used the two pooled jobs
Now three jobs are pooled
ASNApalooza 2007
• The inactive job pool is now somewhat populated
• Lots of users can come and go through these
three jobs
ASNApalooza 2007
The benefit of a populated pooled
zone
• As the pooled zone gets populated, it less
likely that new jobs will be needed
• After get five or six pooled, what are the
chances that five or six users will each request
a page within a few hundred or so
milliseconds of each other?
• Of course, it could happen. And if it does, a
new job is added and the pooled zone grows
by one
ASNApalooza 2007
20 or 30 to one!
• ASNA benchmarks show that you can expect
at least 20 or 30 users able to do “normal”
work using a single pooled job
• As the load grows, so grows the pooled zone
• But pooled jobs have a very low impact on the
System i
Lots of users busy with few jobs
22
How does the pooled zone get cleared
out
• By job timeout value
ASNApalooza 2007
– First in, first out
• For example with no activity, a job drops out of the
pooled zone when its timeout value expires
ASNApalooza 2007
How to tell if it’s working
• Performance is a very good way to tell if
connection pooling is working
• However, another wau to ensure it’s working
is to test you’re app, through several pages,
knowing only one user is using it
• If, after using the app for a while you have
more than one OS/400 job active, something
is amiss
24
Something is wrong here!
There are three jobs for a
application with a single user!
ASNApalooza 2007
Something else to watch for
• It’s possible to be using connection pooling
correctly and still get more than one job active
• This can happen when a Web page uses one
or more secondary classes where each of
which establishes its own DB connection
• This topic is out of the scope of this
presentation, but if you think this is happening
to you, ask me offline for a copy of the
Singleton DB pattern explanation
26
ASNApalooza 2007
The rules of connection pooling
• Enable it either statically or dynamically
• The next point is very important!
• Each page must following this cycle:
– Connect the DB
– Open files
– Do work
– Close files
– Disconnect the DB
27
ASNApalooza 2007
Your app must disconnect for every
page!
• A job gets moved back to the pooled zone
when it’s disconnect
• If you don’t disconnect, you aren’t pooling
jobs!
• The Page’s Unload event is a good place to put
your closes and disconnect
28
Consider doing this:
• Use Close *All to close all files
ASNApalooza 2007
– This gracefully closes all open files
– Doesn’t choke if none are opened
• Then disconnect
29
ASNApalooza 2007
Don’t forget to close files!
• If you disconnect but don’t close files, you
cause lots of files
to be opened!
• Close *All
• then disconnect
30
ASNApalooza 2007
Remember this!
• If you don’t close files and disconnect the DB
object before the page goes out of scope, you
are not using connection pooling!
• The same advice applies to Web methods.
Close files and disconnect the DB after every
Web method call
31
ASNApalooza 2007
Your app must disconnect for every
page!
• A job gets moved back to the pooled zone
when it’s disconnected
• If you don’t disconnect, it’s an orphan active
job
32
ASNApalooza 2007
Pooled connection scope
• Pooled connections are uniquely identified by
all of the properties of a database name.
• Thus, if each user is signing on to your Web
app with her user profile and password, you
are negating the scalable benefits of
connection pooling
– You are however, letting users get their jobs back
quickly
33
ASNApalooza 2007
Pooled connection scope
• Some shops can’t use a single database name
for all users (ie, a single user ID and
password). Thanks SOX.
• Consider using three or database names to
group users (perhaps by security or function)
• This way you’re at least getting some
scalability by letting each group share pooled
jobs
34
ASNApalooza 2007
Limiting database platform jobs
• When you instance a class that has a DB
object, and that class connects, that class gets
its own job on the database platform
• This means that it’s very easy for any one
program (Windows or Web) to have several
jobs in play for that one program
• This… is not good!
35
Limiting database platform jobs
ASNApalooza 2007
• Note the following discussion applies to
Windows and Web AVR apps, but not to
Monarch-generated apps
36
An example with two classes
ASNApalooza 2007
• Two classes, two DBs, two connects = two jobs
This is especially
troubling on the
System i.
Scalability goes
out the window
if you aren’t
careful!
37
ASNApalooza 2007
The solution: the singleton DB pattern
• A parent class
passes its job
to its children,
the children to
their children
• Results in just
one job for the
instance of the
program
38
ASNApalooza 2007
The bad scenario
• Here both
classes get
their job
• This
effects
Windows
and Web
programs
39
To limit the database platform one job, the parent
class needs to pass its DB object around to its
children (and them to their children if they have any.
40
ASNApalooza 2007
There are just a few rules
• The parent, top level class, must pass its DB
object on
• The parent must disconnect the DB
• Child can never disconnect
– Others might want the job
• Children can connection the DB, but should
check to see if it needs to be
– Others might have previously connected it
41
ASNApalooza 2007
The singleton DB pattern is welldocumented
• The downloads for Palooza (associated with
this session) includes a detailed description of
the singleton DB pattern
• Factor it into all of your programs!
• If you don’t, you are risking scalability on your
database server
42
ASNApalooza 2007
Beware shared DB connections!
• Do not share DB connections in Web
applications
• This forces all users to wait on a single thread
for DB connections
• This is guaranteed to make you stay up late at
night
43
ASNApalooza 2007
Consider (carefully) if using threading
can help your application performance
• Spinning off certain processes can help
application performance
• One place where using threading is effective is
to cause Web services to fire off a thread (to
start a long-running process) and quickly
return
• Here is an abbreviated example…
Spinning off the SubmitPrint process on its own thread.
ASNApalooza 2007
In summary
• Use connection pooling. Properly!
• Dispatch the KeyList where it makes sense
• Use SetRange/ReadRange instead of
SETLL/READE
• Populate lists from read-only files
• Don’t persist record locks longer than
necessary
46