Database Performance
For a DBA the dreaded phone call is “my report is running very slowly”. For a start, what is mean by slowly? What is the user used to? Then there is the problem of how you establish where the problem is—is it hardware related? Or Network related? At the S
- PDF / 1,452,557 Bytes
- 42 Pages / 441 x 666 pts Page_size
- 97 Downloads / 218 Views
11
What the reader will learn: • that the different physical and logical aspects of database performance make tuning a complex task • that optimising for read and write performance is not the same thing and the this can cause conflicts when tuning • that database tuning is an ongoing requirement in an active OLTP system • that there are several types of index, each aimed at returning specific kinds of data more rapidly • that disk operations are amongst the slowest element of any database operation The examples we deal with in this chapter are primarily based on Client/Server RDBMS technology. Of course, as we saw in Chap. 5, other types of database technology do exist, and do claim to bring high performance in certain scenarios. The worked examples and diagrams rely heavily on Oracle. We have used Oracle 11g. However, much of what is covered holds true for versions back to 8i. Many architectural diagrams will hold true in principle for SqlServer, MySQL and most other true Client/Server databases.
11.1
What Do We Mean by Performance?
For users—very important stakeholders in any system—database performance is often simply measured in terms of how quickly data returns to their application. And they will often intuitively know when that is “too slow” despite not having timed the process. Returning data can be a very important element of a database system’s requirements. In a Decision Support System (DSS), for example, the system may well spend more than 90 % of its processing time serving out results sets and only ever have data loads occasionally. OLTP systems, on the other hand are often writing new rows, or updating existing ones, whilst relatively less frequently answering queries. Heavy use of indexing to speed query output will be very likely to slow inserts and updates (since the system has more information to store). The need, therefore, whether your system is read- or write-intensive is a very good starting point. P. Lake, P. Crowther, Concise Guide to Databases, Undergraduate Topics in Computer Science, DOI 10.1007/978-1-4471-5601-7_11, © Springer-Verlag London 2013
241
242
11
Database Performance
Fig. 11.1 Elements which influence database performance
So performance is not only about the speed with which queries are answered. As we look further at the building blocks of RDBMS we will need to come to a more rounded view. Performance is about ensuring you use all the system resources optimally in the process of meeting the application’s requirements, at the least cost. Modern database systems are typically built on several layers of technologies. When running a SQL query, the physical parts of the host server can be just as important in determining how quickly we get the result report runs as any of the processes within the RDBMS itself (see Fig. 11.1). We can divide the elements that might impact database performance into the following broad, interrelated categories: Physical layer, such as disks and RAM Operating System (OS) Database Server processes Schema level: Data types, location and volumes
Data Loading...
