[intro music]
[theme music]
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 how
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.
And basically
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 keep on
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 database. And
the second thing is the number of connections to the database should ideally
be static--should be the same number all time.
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
number
connections. We have the ability to basically
destabilize the entire database environment.
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 we
built graphical screens to actually show what happens
when the load comes on the system. So to demonstrate the
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 important
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
applications
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.
And they're
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 system.
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 application servers
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
tiny
increment in the workload, the transaction rate is doubling,
the response time hasn't been impacted it all,
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 linear behavior
on the system. We're seeing the transaction rate literally
double with each step. We're barely impacting
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
in fact
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 machine here
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 JVMs that
we have driving this application, all the connections are
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 more seriously.
So we're doubling it up now,
and we are starting to push the transaction rate
much higher. We are getting up into the 15-16
thousand transactions per second. Thing to note here, you starting to see a much
more
noticeable increase in the CPU on the system,
and we're approaching at this point 25 percent utilization on the system.
But again nothing to see in database on ASH report,
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 scaleable to
25 percent utilization of the database server. That's
hardly the best utilization of hardware resources,
software licenses. So let's double up again
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 machine.
So we're doubling up again and we can see,
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
database,
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 hundreds,
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 system,
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 immediately
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 connections.
So you can see as we crank up the utilization
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
performance.
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.
So
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
caused
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 proportional
increase in the transaction rate, and we're now starting to see the
transaction rate
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 reports.
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 busier.
And you can see now that the database connection pool
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
connection limits.
And you'll notice the number of active connections on the database
is massively oversubscribed, compared to the number
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 actually see
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 actually
simulated a failure in the system.
And then sometimes it starts to recover and it's erratically
you can see the trajectory sometimes bounce-back, but
effectively what we've done is hang the system.
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 impossible.