[intro music]
[theme music]
What we're going to talk about here
is a prelude
to give you an idea of the importance of doing set-based processing,
rather than doing row-by-row processing. The challenge
we face is that historically
programmers have historically like to write things in
paragraphs or paragraph or chapters of code
that dealt with one row or one object at a time.
And this, whilst it's great to be choosing,
producing, functional
code relatively quickly and making meeting their objectives and in terms of
meeting the functional requirements of a program,
it inherently is results in serial programs
that will undoubtably never scale when the data volumes get large
or if you're trying to exploit parallel techniques
on big computers of the nature that
we deal with now at Oracle. So we have this challenge in the industry where
data set are getting bigger,
computers are getting bigger, computers are getting better at processing in
parallel.
Also the computers are less likely to be i/o starved as they were historically
when people wrote programs or went to school because
a whole generation of programmers have grown up thinking
i/o was bad and done everything to avoid it. And
now the game has changed. Data sets are
big, very often in the millions, or billions, or even trillions of rows.
Computers have got
up to hundreds and even thousands of CPUs
and i/o with technologies
like exit data and then memory computing, has largely been mitigated.
So it meant that things could run at CPU speed.
And so we need to start architecting code
to run at CPU speed and not just one CPU,
but to exploit all the CPUs.
But the challenge is in many cases just even on small computers,
the programs were done in an efficient manner. And this is what we want
to show in a very simple case of just how
we can make a serial program in fact more efficient than someone's attempt
doing parallel processing, because it was being done in the right place.
And what we're gonna do here is demonstrate loading 20 million rows.
And what I'm going to show is we're going to do this by a series of races. We're
going to race
two types of racers. There is racer one and there is racer two.
And racer one, he's using a dedicated connection to the database just one
connection
and bulk loading a thousand rows at a time.
The second racer is using
a classic Java connection pool, and he's having
16 connections to the database. Actually, he has a 128
individual Java threads within the application. And what we're gonna do is
we are going to race them
in sort of in front of you, and see who can load twenty million
rows first. As you can see,
both processes are starting up. It is very
apparent that if we actually look at the graph, you can see which racer is
pulling ahead.
It's the guy that is doing the array insert and using only one call.
If we actually look at what's going on,
within the computer, you can see the machine that's doing
one core is using one process and is very evenly loaded.
And if you look at the host that is
actually running, doing a pool of 16, you'll notice that we are getting more CPUs busy
but we've also got a lot of processes in wait states.
And if you actually look at the CPU, you can see the CPU utilization on the
threaded case is actually higher
than on the array case. If we actually now start looking at the
performance after the first race finishes, the first
single thread was able to load 20 million rows
in just over a minute. Now we're gonna stop the race in any second of
time when it's finished. And then let's see
what sort of time we were if we'd actually gone with the threading model
he would have taken us 22 middle,
22-minutes, to actually
load the same $20 million rows. So if a system architected
architected to use a connection pool and do everything one row at a time,
and now he used a Java pool, it'll probably blame the database and
say the database couldn't load 20 million rows
in an acceptable period of time. So you can see two different programming styles,
again all running on the same hardware. One works really well.
One doesn't work very well and is in fact 20 times slower.
So, in many cases, we will say,
"well, you know we didn't use up all
the hardware on the
other case on the threaded case, so we should
potentially give it some more connections to the database, so I'm going
increase the connections quite dramatic into the database I'm gonna give its 64
connections to the database,
and I'm gonna give 512
threads to the application server so there's no shortage in connections
threads or whatever head.
And I'm gonna run the race again and let's just see what happens.
And again, we're seeing similar characteristics.
We're throwing a lot more hardware as you can see popping up. Here we go.
But many of those processes were busy
are all in a wait state. They're not actually doing the inserts.
We've got no shortage of threads. We got no shortage of processing.
But you can see the threaded model because it's doing
things. Lots. One at a time. It isn't actually achieving any of its
objectives of trying to make things work.
Because basically we're funneling effectively
contention downstream to the database. Whereas the one that is running serial,
is running extremely efficient, is able actually to
load rows into the database much faster in this case. And in fact,
we're starting to see some interference where in fact threaded one is actually
slowing down the serial process.
But you can see that we've shaved a little bit of time
off the racer 2. We are going to get it to finish in about 30 minutes.
And racer 1 is still taking about a minute to load
20 million rows. So that didn't show just throwing resources at the problem,
it just showed that the programming model was worse.
If we start to index
the table that were inserting and making a much more real-world,
let's re-run the race and see what happens at this point in time.
As you can see, the anticipated times for the job is going to take a little bit longer
even the one with the racer 1 with the array insert. He's taking slightly longer
because we have to maintain that index.
But it gets more interesting if we actually look at the second case.
The threaded model with all those threads. And
you can see that were going to experience a lot more database contention
inside the database usually associated with TX index contention and buffer busy
waits as you can see here.
And this is basically just us creating a program that
builds inherent contention into the database at a later time.
And very often we hear people saying, "oh it's the database. It's
faulty. It cannot scale. It can't accommodate the workload."
Again, you can see a row-by-row programming paradigm
is the challenge we've got here. And can see that basically
whilst we are putting lots of threads in motion, most of them are stalled
because they're contending with each other. Whereas if we set up the workload
to work in batches,
we are not only saving code path, but we're eliminating contention
because there are so few threads active in the game.
So again we are going to reproduce that basically we need to learn how to deal with
this in sets and think about CPU efficiency,
and how to mitigate contention. This is a very simple case
and you can see again we're orders of magnitude faster
by doing it in using an array and set-base programming method
rather than single row at a time. And effectively
manual or home-grown or home-spun
parallelism.