Section 9.1. The Database Engine as a Service Provider


9.1. The Database Engine as a Service Provider

You might be tempted to consider the DBMS as an intelligent and dedicated servant that rushes to forestall your slightest desire and bring data at the exact time when you need it. Reality is slightly less exalted than the intelligent servant model, and at times a DBMS looks closer to a waiter in a very busy restaurant. If you take your time to choose from the menu, chances are that the waiter will tell you "I'll let you choose, and I'll come back later to take your order" before disappearing for a long time. A DBMS is a service provider or, perhaps more precisely, a collection of service providers. The service is simply to execute some operation against the data, fetching it or updating itand the service may be requested by many concurrent sessions at the same time. It is only when each session queries efficiently that the DBMS can perform efficiently.

9.1.1. The Virtues of Indexes

Let's execute some fairly basic tests against a very simple table with three columns. The first two are integer columns (each populated with distinct values from 1 to 50,000), one being declared as the primary key and the second without an index. The third column (named label) is a text column consisting of random strings thirty to fifty characters long. If we generate random numbers between 1 and 50,000 and use these random numbers as query identifiers to return the label column, you might be surprised to discover that on any reasonably powerful machine, the following query:

     select label     from test_table     where indexed_column = random value 

as well as this one:

     select label     from test_table     where unindexed_column = random value 

provide virtually instant results. How is this possible? A query using an unindexed column should be much slower, surely? Actually, a 50,000-row table is rather small, and if it has as few columns as is the case in our example, the number of bytes to scan is not that enormous, and a modern machine can perform the full scan very rapidly. We indeed have, on one hand, a primary key index search, and on the other hand, a full-table scan. What's happening is that the difference between indexed and unindexed access is too small for a human to perceive.

To really test the benefit of an index, I have run our queries continuously for one minute, and then I have checked on how many queries I was able to process by unit of time. The result is reassuringly familiar: on the machine on which I ran the test, the query using the indexed column can be performed 5,000 times per second, while the query using the unindexed column can only be performed 25 times per second. A developer running single user tests may not really notice a difference, but there is one, and it is truly massive.

Even sub-second response times sometimes hide major performance issues. Don't trust unitary tests.

9.1.2. A Just-So Story

Continuing with the example from the preceding section, let's have a look at what may very well happen in practice. Suppose that instead of being a number, the key of our table happens to be a string of characters. During development, somebody notices that a query has unexpectedly returned the wrong result. A quick investigation shows that the key column contains both uppercase and lowercase characters. Under pressure to make a quick fix, a developer modifies the where clause in the query and applies an upper( ) function to the key columnthus forfeiting the index. The developer runs the query, the correct result set is returned, and anyone other than a native of the planet Krypton cannot possibly notice any significant difference in response time. All appears to be for the best, and we can ship the code to production.

Now we have hordes of users, all running our query again, again and again. Chapter 2 makes the point that in our programs we should not execute queries inside loops, whether they are cursor loops or the more traditional for or while constructs. Sadly, we very often find queries nested inside loops on the result set of other queries, and as a result, our query can be run at a pretty high rate, even without having tens of thousands of concurrent users. Let's see now what happens to our test table when we run the query at a high rate, with a set number of executions per unit of time, occurring at random intervals. When we execute our query at the relatively low rate of 500 per minute, everything appears normal whether we use the index or not, as you can see in Figure 9-1. All our queries complete in under 0.2 seconds, and nobody will complain.

Figure 9-1. Response time of a simple query against a 50,000-row table, low query rate


We actually have to increase our execution rate 10 times, to a relatively high rate of 5,000 executions per minute, to notice in Figure 9-2 that we may occasionally have a slow response when we use the unindexed column as key. This, however, affects only a very low percentage of our queries. In fact, 97% of them perform in 0.3 seconds or less.

But at 5,000 queries per minute, we are unaware that we are tottering on the brink of catastrophe. If we push the rate up to a very high 10,000 executions per minute, you can see in Figure 9-3 that a very significant proportion of the queries will execute noticeably more slowly, some taking as long as 4 seconds to complete. If in another test we run the queries that use the index at the same high rate, all queries execute imperturbably in 0.1 seconds or less.

Of course, when some queries that used to run fast start to take much longer, users are going to complain; and other users who, unprompted, would otherwise have noticed nothing will probably grumble as well, out of sympathy. The database is slowcan't it be tuned? Database administrators and system engineers will tweak parameters, gaining a few weeks of relief, until the evidence will finally impose itself, in all its glorious simplicity: we need a more powerful server.

Figure 9-2. Response time of a simple query against a 50,000---row table, high query rate


Figure 9-3. Response time of a simple query against a 50,000---row table, very high query rate


An increasing load may not cause performance problems, but may actually reveal them, suggesting program improvements as an alternative to upgrading the hardware.

9.1.3. Get in Line

One can take a fairly realistic view of a DBMS engine by imagining it to be like a post office staffed by a number of clerks serving customers with a wide array of requestsour queries. Obviously, a very big post office will have many counters open at the same time and will be able to serve several customers all at the same time. We may also imagine that young hypercaffeinated clerks will work faster than older, sedate, herbal-tea types. But we all know that what will make the biggest difference, especially at peak hours, is the requests actually presented by each customer. These will vary between the individual who has prepared the exact change to buy a stamp book and the one who inquires at length about the various rates at which to send a parcel to a remote country, involving the completion of customs forms, and so on. What is most irritating is of course when someone with a mildly complicated request spends several minutes looking for a purse when the moment for payment arrives. But fortunately, in post offices, you never encounter the case that is so frequent in real database applications: the man with 20 letters who joins the queue on 20 separate successive occasions, buying only one stamp in each visit to the counter. It is important to understand that there are two components that determine how quickly one is served at the counter:

  • The performance of, in our example, the clerk. In the case of a database application, this equates to a combination of database engine, hardware, and I/O subsystems .

  • The degree of complexity of the request itself, and to a large extent how the request is presented, its lucidity and clarity, such that the clerk can easily understand the request, and accordingly make a quick and complete answer.

In the database world, the first component is the domain of system engineers and database administrators. The second component belongs squarely within the business requirements and development arena. The more complicated the overall system, the more important becomes the collaboration between the different parties involved when you want to get the best out of your hardware and software investment.

With the post-office image in mind, we can understand what happened in our query test. What matters is the ratio of the number of customers arriving (e.g., the rate of execution of queries), to the average time required to answer the query. As long as the rate of arrival is low enough to enable everyone to find a free counter, nobody will complain. However, as soon as customers arrive faster than they can be serviced, queues will start to lengthen, just as much for the fast queries as for the slow ones.

There is a threshold effect, very similar to what one of Charles Dickens's characters says in David Copperfield:

Annual income twenty pounds, annual expenditure nineteen six, result happiness. Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.

This can easily be demonstrated by running our two queries simultaneously, the one using the indexed column and the other using the unindexed column, at a rate of 5,000 times per second. The compound result of Figure 9-4 is noticeably different from Figure 9-2, in which results were shown for the two queries running separately, not concurrently. As appears clearly from Figure 9-4, the performance of the fast query has deteriorated because of the simultaneous presence of slow queries.

Figure 9-4. Fast and slower queries running together, both at a high query rate


System performance crashes when statements arrive faster than they can be serviced; all queries are affected, not only slow ones.




The Art of SQL
The Art of SQL
ISBN: 0596008945
EAN: 2147483647
Year: N/A
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net