Today we are going to talk about connecting to the database and
how we choose connect to the database.
In previous YouTube videos, we’ve shown
developers might connect to the database,
and how they would use
cursors and arrays, and things like that. But we are going to do a
topic that really should be thought about from both the system administrator
and the system architect. And it’s the number of connections that we make to the database
and how they connect to the database. Are
those connections permanent
or are they dynamic? This is a topic that my colleagues within the Real World Performance group
have been dealing with for
probably the best part of 20 years.
it’s done wrong in many cases, and it’s
usually done wrong
because of over-optimistic views
of how they think the database can just
absorbing more and more connections. The reality of the situation is
that there’s two things at stake here. One the database–you cannot add
an infinite number connections to the
the second thing is the number of
connections to the database should ideally
be static–should be the same number all
Unfortunately, today’s applications
tend to get built in middle tiers
and they tend to specify dynamic
connection pools. Very often it’s a
minimum number and then maximum number.
And there’s under…. people are under this illusion that basically
the applications servers will keep on
creating connections as required,
and they’ll potentially cut them back if the load goes off.
Well in fact this is actually the worst
thing possible to do.
In fact, one of my colleagues often defines this sort of architecture
as a “gun ready to go off.” You have the
potential to rapidly create a large
connections. We have the ability to
destabilize the entire database
And for this reason itself, um,
we actually got quite tired of talking to
people about this.
And for this reason we built demos and
built graphical screens to actually show
when the load comes on the system. So to
the challenges associated with both a
large connection pool and a
dynamic connection pool, what we’re going to do is we are going to simulate load coming on a system
in slow motion. So we can actually
watch and interrogate a system as the load comes up.
The reasoning being for that is that if we see in slow motion
we can actually see what is happening
and watch the system fall apart in
front of you rather than it all
happening in two seconds
and system just falling over and everyone
curious as to what has happened.
And this it becomes particularly
to understand what is happening and how
quickly these things
can run out of control. So and
we’re back using the Real World Performance demo screens
and you’ll notice we have basically
three windows down the middle which is
reporting what we’re doing. Now we have a
very stable system here,
running at this point in time. You can
notice that the response time from the
is two milliseconds. We’re running at
quite a modest transaction rate of just
under 2,000 transactions a second.
But let’s talk about what our user
community is doing. What we actually have
is nearly 15,000 individual Java threads
all playing effectively cards online.
basically pressing the button,
each thread, once every 10 seconds.
What we’ve done is we’ve created a
connection pool that can
literally go out of control and destabilize
The initial connection pool was a 144
connections to the database, but we’re going
let it grow
to 6,000 connections to the database
when the load comes on. Now if we look at the system at the moment you can see we’re
barely recording anything on the AWR
report or ASH reports.
The CPU utilization is extremely low.
And the other thing to notice is that
even though the
utilization on the system is extremely
low, we’ve already
extended the connection pool at over 200
connections to the database
up from 144, just because the
occasionally found that all their connections were busy, we allowed it to grow.
And with that in mind and I’m going to
force it to grow even more. And we are going to watch the system as we start to grow.
So I’m going to start doubling up the work load, so instead of everyone
pressing the button every 10 seconds,
we are now going to make it every five seconds.
And we’ll double this up.
And I’m going to continue to double this up until it gets quite
interesting and we’ve actually started to make the system yield.
At this point just doubling up the
workload, you can see that there’s just a
increment in the workload, the transaction
rate is doubling,
the response time hasn’t been impacted
we’re starting to see a little bit of climb on
the CPU utilization,
which we can see we’re barely even using
seven or eight
percent of the system this point in time. So let’s double it up
in another step.
And again, here we go doubled up the
workload again. And
at this point in time, we can see very
on the system. We’re seeing the
transaction rate literally
double with each step. We’re barely
response time. And we’re seeing the CPU getting proportional response
coming up as the workload comes up.
So, at this point in time, people will
start to think we’ve built a linear and
a scaleable system. The other thing to note
is that we are starting to see the connections to the database
and the size of the connection pool now is now approaching nearly 500, okay.
So we are really about four times bigger
nearly five times bigger than the
initial connection pool,
yet we’ve hardly stretch the machine at all.
So there’s a lot of connections that
actually aren’t doing anything
is 465 at this point in time, where we are not actually doing anything at all.
We’ve only got 16 active. And this is on a
with actually twenty four cores on. So
you can see we’re
not even fully utilizing the machine in
terms of sessions per
core at this point. But you can see, again,
that connection count is increasing just
because you get occasional
timing glitches where of all the 32
we have driving this application, all the
busy for one point in time and so we just
popped another connection.
So let’s double up the workload a little
bit more, and then we should start
getting into start seeing
load come onto the system a little bit
So we’re doubling it up now,
and we are starting to push the transaction
much higher. We are getting up into the
thousand transactions per second. Thing
to note here, you starting to see a much
noticeable increase in the CPU on the
and we’re approaching at this point 25
percent utilization on the system.
But again nothing to see in database on
not much to see of the user response time.
This is the point in time
where very often we see system architects and administrators
congratulating each other because they
think, “wow we’ve got this
scaleable system.” But it’s only
25 percent utilization of the database server. That’s
hardly the best utilization of hardware
software licenses. So let’s double up
and see where get to when we start. Hopefully, now that we’re going to get
to use 50 percent of the utilization on the
So we’re doubling up again and we can
again, a very immediate response. The
transaction rate is doubling.
We are seeing the CPU workload doubling. And we’re nearly at
50 percent utilization on the system.
Barely an increase on the system
response time. Again a very stable system.
The thing to note now is we really are
starting to grow a lot more connections to the
is the middleware is now getting to
situation. It is running at a higher rate
and the statistical chance that all the
connections used from one JVM
being in use is increasing all the time. So literally as I’ve been speaking, we’re seeing the
connections pop to
over nearly 2000 here. We are at 1900
as I spoke, before we were in the
and within a second or so, I imagine we are going to be
greater than 2000 fairly quickly. But you can see quite quickly that
you know, still at this point in time the
system is very predictable,
very linear, and everyone seeming great. And this is at the point where
we’re about fifty percent utilization on the system.
Now at fifty percent utilization on the
we can see this is a point where our
statistical chance of getting scheduled
is 2 in 1. As I crank up the
utilization on the system,
your chance of getting scheduled
is starting to get reduced. We are starting to not get scheduled all of the
time. We are going to start seeing more variation in the response time.
As the response time varies, we are more likely to have
more application servers waiting.
And they are more likely to initiate more
So you can see as we crank up the
we can more likely to basically get more
variable response time,
which is going to force the app servers
to throw more connections at the database,
which in its turn, are going to get more and more unpredictable response time,
and we’re get ourselves in what is known as a race condition. And this race condition we
very often call a connection
storm, when basically we’re initiating a
storm of connection requests but we are actually getting
nothing back. And remember what was shown in previous YouTubes
the actually logging on and off the database is quite an expensive operation.
So this just compounds this effect, and we start going into a spiral of degrading
So I’m going to not double the performance at this point in time.
Because as you can see it’s been quite well behaved. We’ve got over
2000 connections to the database at this
point. But what we’re going to do this we do
is we are going to do it in smaller increments.
So instead of doubling it, I’m just gonna
take it up to four hundred milliseconds, so
0.4 percent of a second between each mouse clicks.
some fairly serious card players. And
we are just going to jump the workload up a little bit more.
And you can see that basically, suddenly the small increment in work suddenly
quite an impact on the system, okay?
So almost like a 50 percent increase in the
workload, we saw almost
twice as much CPU. We see this disproportional response in CPU utilization
getting extremely busy. We’re not seeing
increase in the transaction rate, and
we’re now starting to see the
starting to become quite choppy. And we are starting to see
queuing in the middle tier machine. And we’re seeing extended database response times.
And for the first time, we are actually starting to see wait events
inside the database through the ASH
And some of you may have seen some of these
wait events in your life before. Latches on end queues,
row cache objects, latch free
enq:TX index contention, buffer busy waits.
Quite common wait events as and well, we getting to the situation where we’re starting to
oversubscribe the machine, but the CPU
is starting to get extremely busy.
And we’re starting to see more and more wait events. We are starting to see the CPU get busier
And you can see now that the database
that was at 2000 is now approaching 6000.
Remember we have a limit here of 6144.
So very quickly, we’re actually getting
to the situation where we are approaching our
And you’ll notice the number of active connections on the database
is massively oversubscribed, compared to
of cores on the system. It’s now telling me I have
6,000 active requests into the database
at this point in time. As a result you can
we’ve completely oversubscribed the machine so the response time
has gone out of control, the wait event
inside the database has gone
completely out of control. Just
looking at that you can see
enq:TX index contention, contention events
and the throughput is dropped right off.
And in fact what we’re starting to see
is the monitoring of the thing
is starting to become unpredictable. And
even in fact the CPU has dropped off
because this machine has now actually rendered itself completely unstable.
And at this point in time, we’ve
simulated a failure in the system.
And then sometimes it starts to recover and it’s erratically
you can see the trajectory sometimes
effectively what we’ve done is hang
And trying to use a keyboard and things like this
becomes impossible. So getting debugging
information on such a system
into this state becomes next to