Database Reference
In-Depth Information
Chapter 8
Parallel Query Tuning
A good analogy for parallel processing is to take an example from our everyday life. It's that time of the year: the
holidays when people are anxiously waiting to exchange gifts. Several people need to be given gifts and to get the gifts
ready, the package needs to be packed, glued, and labeled. Once finished it's either delivered in person or delivered
to the post office for shipping. What if there was a method to break this entire processing into smaller pieces of work
where a different person does each piece of work? One person cuts the paper, another assembles the gifts for one
recipient, another cuts the adhesive tape, and finally one wraps up the gift and places the label. It would help in
two aspects: first, if more than one person does the entire work, that person is not bored doing all the gifts all alone;
second, when more people help in the process, the work will get done quicker. As the family network expands and
more gifts have to be given in a subsequent year, this would mean increasing the number of people to complete the
work sooner.
There is a limit to every activity; just increasing the number of people to help in the gift wrapping process may not
help. These additional people should have a balanced distribution of work so there is even flow from one person to
the other. If we have additional people to cut the adhesive tape, then one person may cut more than what can be used
immediately or may have to wait for others to complete their work so the tape cutter could apply the tape. In other
words, there could be bottlenecks in the process. Similarly, increasing the number of helpers through all stages could
complete the work sooner; however, after a certain stage, the throughput of these helpers could be reduced. Because
they could start talking and making stories in the process, their overall efficiency could be reduced.
What if one of the helpers took a restroom break, others will have to wait, and a backlog would be created. Ideally,
the remaining people can divide the work of the missing person and complete the work but at a much slower speed.
We could apply this metaphor to information technology. If there were a large number of users making requests
to the database simultaneously (or one user making a large request to retrieve a complex set of data) and there was
only one CPU to process the request(s), the user(s) would have to wait several hours or possibly days for the results.
(The length of the wait would be based on the complexity of the query, underlying database design, processing power
of the computer, the volume of data being retrieved, etc.). If there were an opportunity for the user(s) to execute
requests to the database in parallel, the request would speed up and complete much more quickly and efficiently.
Increasing the number of helpers for wrapping the gifts in our metaphor or processing of data across multiple
processors is possible because of the work can be divided among the additional resources, for example, lanes and
processors. This division of work into multiple, or asynchronous processing of a request is called a parallel activity or
“parallel processing.”
Parallel processing is the use of multiple processors to execute different parts of the same program
simultaneously and provides the opportunity to divide the workload and obtain the results using available computer
resources. The main goal of parallel processing is to reduce wall-clock time, that is, to “speed up” work. The scenarios
discussed earlier are examples of parallel processing, and both the metaphor example and the one of a large number
of users accessing data illustrate the goal of distributed processing.
In the example of the gift wrapping, the power and weakness of the parallel approach becomes visible by taking
it to the extreme. In other words, as the number of people/helpers is increased, there will be a characteristic speedup
curve, demonstrating how, up to a certain number of helpers, it is beneficial. However, anything over a certain point
probably will give a reduced or negative benefit.
 
Search WWH ::




Custom Search