Transcript database administration

DATABASE ADMINISTRATION
Pertemuan ke-5
Performance Management
source :
Database Administration
the complete guide to practices and procedures
chapter 9
by. Craig S. Mullins
• When non-DBAs think about what it is that a DBA does,
performance monitoring and tuning are quite frequently
the first tasks that come to mind.
• Almost anyone who has come in contact with a computer
has experienced some type of performance problem.
Moreover, relational database systems have a notorious
reputation (mostly undeserved) for poor performance.
• This chapter, as well as the following three, will discuss
performance monitoring, tuning, and management within
the context of database administration.
• This chapter defines performance, discusses the difference
between performance monitoring and performance
management, looks at managing service levels, and defines
three specific subsets of database performance management.
Defining Performance
• Most organizations monitor and tune the
performance of their IT infrastructure.
• This infrastructure encompasses servers, networks,
applications, desktops, and databases.
• However, the performance management steps
taken are usually reactive.
• A user calls with a response-time problem: A
tablespace runs out of disk storage space in
which to expand. The batch window extends into
the day. Someone submitted a "query from hell"
that just won't stop running.
• Those of you in the trenches can relate—you've
been there, done that.
• All of this discussion is useful, but it begs the question:
Just what do we mean by database performance? You
need a firm definition of database performance before
you can plan for efficiency.
• Think, for a moment, of database performance using
the familiar concepts of supply and demand.
• Users request information from the database.
• The DBMS supplies information to those requesting it.
• The rate at which the DBMS supplies the demand for
information can be termed "database performance"
However, this definition captures database performance
only in a most simplistic form.
• Five factors influence database
performance:
–
–
–
–
–
workload,
throughput,
resources,
optimization,
and contention
• The workload is a combination of online
transactions, batch jobs, ad hoc queries, data
warehousing analysis, and system commands
directed through the system at any given time.
• Workload can fluctuate drastically from day to day,
hour to hour, and even minute to minute.
• Sometimes workload is predictable (such as
heavy month-end processing of payroll, or very light
access after 7:00 P.M., when most users have left
for the day), whereas workload is very unpredictable
at other times.
• The overall workload has a major impact on
database performance.
• Throughput defines the overall capability of
the computer to process data.
• It is a composite of I/O speed, CPU speed,
parallel capabilities of the machine, and the
efficiency of the operating system and system
software.
• resources of the system is the hardware and
software tools at the disposal of the system.
• Examples of resources include the database
kernel, disk storage devices, random access
memory chips, cache controllers, and
microcode.
• The fourth defining element of database
performance is optimization.
• All types of systems can be optimized, but
relational databases are unique in that query
optimization is primarily accomplished internal to
the DBMS.
• However, many other factors need to be
optimized (such as SQL formulation and
database parameters) to enable the database
optimizer to create the most efficient access
paths
• When the demand (workload) for a particular
resource is high, contention can result.
• Contention is the condition where two or
more components of the workload are
attempting to use a single resource in a
conflicting way (e.g., dual updates to the
same piece of data).
• As contention increases, throughput
decreases.
Monitoring vs. Management
• Even many of the supposedly proactive steps taken
against completed production applications might be
considered reactive.
• A change to a completed application that requires code to be
rewritten cannot reasonably be considered proactive.
• A proactive approach would have involved correcting the
problem before completing the application
• Unfortunately, the DBA usually attacks performance in a
reactive manner.
• A user calls with a response time problem. A database runs out
of space. The batch window extends into the day.
• The problem has happened and now it needs to be remedied.
Such activity is purely reactive.
• Some event-driven tools can be used to make
performance tuning easier by automatically taking
predefined actions when prespecified alerts are triggered.
• This is the first step toward performance management.
• Managing performance differs from monitoring
performance because it combines monitoring with a detailed
plan for resolving problems when they arise.
• Performance management consists of three specific
components that need to be performed in conjunction with
each other: monitoring, analysis, and correction, as shown
in . Figure 9-1
• Monitoring is the first component of performance
management. It consists of scanning the environment,
reviewing the output of instrumentation facilities, and generally
watching the system as it runs. Monitoring is the process of
identifying problems.
Figure 9-1. The components of
performance management
• Analysis is the second component of
performance management.
• A monitoring task can generate hundreds or
thousands of messages, or reams and reams
of paper reports.
• A monitor collects the pertinent information for
making performance tuning and optimization
decisions, but it is essentially dumb.
• A monitor cannot independently make decisions
based on the information it has collected.
• This requires analysis—and analysis typically is
performed by a skilled technician like a DBA.
Service-Level Management
• Service-level management (SLM) is the
"disciplined, proactive methodology and
procedures used to ensure that adequate
levels of service are delivered to all IT users
in accordance with business priorities and at
acceptable cost."
• In order to effectively manage service levels, a
business must prioritize its application and
identify the amount of time, effort, and capital
that can be expended delivering service for
those applications
• A service level is a measure of operational
behavior.
• SLM ensures that applications behave accordingly
by applying resources to those applications based
on their importance to the organization.
• Depending on the needs of the organization,
SLM can focus on availability, performance, or
both.
• In terms of availability, the service level might be
defined as "99.95% uptime from 9:00 A.M. to 10:00
P.M. on weekdays." Of course, a service level can
be more specific, stating "average response time for
transactions will be two seconds or less for
workloads of 500 or fewer users."
•
•
•
•
•
For a service-level agreement (SLA) to be successful, all parties
involved must agree on stated objectives for availability and
performance.
The end users must be satisfied with the performance of their
applications, and the DBAs and technicians must be content with their
ability to manage the system to the objectives.
Compromise is essential to reach a useful SLA.
In practice, though, many organizations do not institutionalize SLM.
When new applications are delivered, there may be vague
requirements and promises of subsecond response time, but the
prioritization and budgeting required to assure such service levels are
rarely tackled unless the IT function is outsourced.
Internal IT organizations are loath to sign SLAs because any SLA
worth pursuing will be difficult to achieve. Furthermore, once the
difficulties of negotiating an SLA are completed, the business could
very well turn around and outsource the SLA to a lower-cost provider
than the internal IT group.
•
•
•
•
•
•
•
The failure of SLM within most businesses lies with both IT organizations and
business users.
The business users frequently desire better service but are not willing to make the
effort to prioritize their needs correctly or to pay additional cash to achieve better
service.
Another potential problem with SLM is the context of the service being discussed.
Most IT professionals view service levels on an element-by-element basis.
In other words, the DBA views performance based on the DBMS, the SA views
performance based on the operating system or the transaction processing system,
and so on.
SLM properly views service for an entire application. However, it can be difficult to
assign responsibility within the typical IT structure.
IT usually operates as a group of silos that do not work together very well.
Frequently, the application teams operate independently from the DBAs, who
operate independently from the SAs, as shown in Figure 9-3.
When an application team has staffed an application DBA function, that team may
not communicate effectively with the corporate DBA silo. These fractured silos make
cooperation toward a common application service level difficult.
Figure 9-3. IT silos in a fractured environment
• To achieve end-to-end SLM, these silos need to be broken
down. The various departments within the IT infrastructure
need to communicate effectively and cooperate with one
another.
• Failing this, end-to-end SLM will be difficult, if not impossible,
to implement.
• SLM is a beneficial practice: A robust SLM discipline makes
performance management predictable. SLM manages the
expectations of all involved.
• Without an SLA, how will the DBA and the end users know
whether an application is performing adequately? Not every
application can, or needs to, deliver subsecond response time.
• Without an SLA, business users and DBAs may have different
expectations, resulting in unsatisfied business executives and
frustrated DBAs. Not a good situation.
• With SLM in place, DBAs can adjust
resources by applying them to the most
mission-critical applications as defined in the
SLA.
• Costs will be controlled and capital will be
expended on the portions of the business
that are most important to the business.
Types of Performance Tuning
• A database application requires constant
interaction between disparate computing
resources in order to operate efficiently and
according to specifications.
• Realistically, though, the tuning of a database
application can be broken down into three
components: system tuning, database tuning,
and application tuning.
• Indeed, all these areas are related, and certain
aspects of tuning require an integrated
approach. However, for clarity, we'll discuss
these areas separately.
System Tuning
• System tuning occurs at the highest level
and has the greatest impact on the overall
health of database applications because
every application depends on the system.
• For the purposes of this discussion, we will
define the system as comprising the DBMS itself
and all of the related components on which it
relies.
• No amount of tuning is going to help a
database or application when the server it is
running on is short on resources or
improperly installed.
• The DBMS can and must be tuned to assure
optimum performance.
• The way in which the DBMS software is installed, its
memory, disk, CPU, other resources, and any
configuration options can impact database
application performance.
• The other systems software with which the DBMS
interacts includes the operating system, networking
software, message queueing systems, middleware,
and transaction processors.
• System tuning comprises installation,
configuration, and integration issues, as well as
ensuring connectivity of the software to the
DBMS and database applications.
Database Tuning
• Performance can be impacted by the
physical design of the database, including
normalization, disk storage, number of
tables, index design, and use of DDL and its
associated parameters.
• The physical location of database files on disk
systems will have an impact on the performance
of applications accessing the data.
• As more data is stored on the same disk device,
the possibility of performance degradation
increases.
• However, design is not the only component of database
performance. The organization of the database will
change over time.
• As data is inserted, updated, and deleted from the
database, the efficiency of the database will degrade.
Moreover, the files that hold the data may need to
expand as more data is added.
• Perhaps additional files, or file extents, will need to be
allocated.
• Both disorganization and file growth can degrade
performance.
• Indexes also need to be monitored, analyzed, and tuned
to optimize data access and to ensure that they are not
having a negative impact on data modification.
Application Tuning
• The application itself must be designed appropriately and
monitored for efficiency.
• Most experts agree that as much as 75% of performance
problems are caused by improperly coded applications.
• SQL is the primary culprit; coding efficient SQL statements can
be complicated.
• Developers need to be taught how to properly formulate,
monitor, and tune SQL statements.
• However, not all application problems are due to improperly
coded SQL.
• The host language application code in which the SQL has been
embedded may be causing the problem.
• For example, Java, COBOL, C++, or Visual Basic code may be
inefficient, causing database application performance to suffer.
Performance Tuning Tools
• Database tools are helpful to effectively manage
database performance.
• Some DBMS vendors provide embedded options and
bundled tools to address database performance
management.
• However, these tools are frequently insufficient for largescale or heavily used database applications.
• Fortunately, many third-party tools will effectively
manage the performance of mission-critical database
applications.
• Tools that enable DBAs to tune databases fall into two
major categories: performance management and
performance optimization.
• Many different types of performance management
tools are available.
•
•
•
•
•
•
Performance monitors enable DBAs and performance analysts to gauge the
performance of applications accessing databases in one (or more) of three
ways: real time, near time (intervals), or based on historical trends. The more
advanced performance monitors are agent-based.
Performance estimation tools provide predictive performance estimation for
entire programs and SQL statements based on access paths, operating
environment, and a rules or inference engine.
Capacity planning tools enable DBAs to analyze the current environment and
database design and perform "what-if" scenarios on both.
SQL analysis and tuning tools provide graphical and/or textual descriptions of
query access paths as determined by the relational optimizer. These tools can
execute against single SQL statements or entire programs.
Advisory tools augment SQL analysis and tuning tools by providing a
knowledge base that provides tips on how to reformulate SQL for optimal
performance. Advanced tools may automatically change the SQL (on request)
based on the coding tips in the knowledge base.
System analysis and tuning tools enable the DBA to view and change
database and system parameters using a graphical interface (e.g., cache
and/or bufferpool tuning, log sizing).
• In the performance optimization category, several tools
can be used to tune databases.
– Reorganization tools automate the process of rebuilding
optimally organized databases. Databases can cause
performance problems due to their internal organization (e.g.,
fragmentation, row ordering, storage allocation).
– Compression tools enable DBAs to minimize the amount of disk
storage used by databases, thereby reducing overall disk
utilization and, possibly, elapsed query/program execution time,
because fewer I/Os may be required. (Caution: Compression
tools can also increase CPU consumption due to the overhead of
their compress/decompress algorithms.)
– Sorting tools can be used to sort data prior to loading databases
to ensure that rows will be in a predetermined sequence.
Additionally, sorting tools can be used in place of ORDER BY or
GROUP BY SQL. Retrieving rows from a relational database is
sometimes more efficient using SQL and ORDER BY rather than
SQL alone followed by a standalone sort of the SQL results set.
• The DBA will often need to use these tools in
conjunction with one another—integrated and
accessible from a central management console. This
enables the DBA to perform core performance-oriented
and database administration tasks from a single platform
• Many DBMS vendors provide solutions to manage
their databases only; for example, Oracle provides
Oracle Enterprise Manager and Sybase provides SQL
Central for this purpose. Third-party vendors provide
more robust options that act across heterogeneous
environments such as multiple different database servers
or operating systems.
• In general, it is wise to use the DBMS vendor solution
only if your shop has a single DBMS. Organizations with
multiple DBMS engines running across multiple operating
systems should investigate the third-party tool vendors.
DBMS Performance Basics
• We have defined database performance and
discussed it from a high level.
• Before we delve into the specifics of system,
database, and application performance, let's
examine some rules of thumb for achieving
your DBMS-related performance goals.
•
•
Do not over-tune. Most DBAs are more than happy to roll up their
sleeves and get their hands dirty with the minute technical details of
the DBMS. Sometimes this is required. However, as a DBA, you
should always keep in mind the business objectives of the databases
and applications you manage. It is wise to manage performance
based on the expectations and budget of the business users. Even
though it might be an interesting intellectual challenge for you to finetune a query to its best performance, doing so may take too much
time away from your other duties. It is best to stop tuning when
performance reaches a predefined service level for which the
business users are willing to pay.
Remain focused. As a DBA, you should understand the goal for each
task you perform and remain focused on it. This is important because
the DBMS is complex, and when you are tuning one area, you might
find problems in another. If so, it is best to document what you found
for later and continue with the tuning task at hand. Furthermore, by
jumping around trying to tune multiple things at once, you will have no
idea of each task's impact on the environment.
• Do not panic. The DBA is expected to know everything about
the DBMS he manages. However, this is an unreasonable
expectation. "I don't know, but I'll find out" is one of the most
important phrases in your communications arsenal. A good
DBA knows where to look for answers and who to call for help.
• Communicate clearly. Communication is key to assuring
properly tuned, high-performance database systems. The DBA
must be at the center of that communication, coordinating
discussions and workload between the business users,
programmers, managers, and SAs. Furthermore, the world of
IT in general, and database technology in particular,
sometimes uses a language all its own. Many similar and
confusing terms are thrown about, and folks are expected to
understand what they mean. Be sure to clearly define even
basic terms so that you're all speaking the same language.
• Accept reality. Many organizations talk about being
proactive but in reality have very little interest in
stopping performance problems before they happen.
Yet, every organization is interested in fixing
performance problems after the problems occur.
This can be a frustrating environment for the DBA,
who would rather set up preventative maintenance
for the DBMS environment. Alas, this requires
budget, time, and effort—all of which are in short
supply for strapped IT organizations. As a DBA, you
must sometimes be content to accept reality and
deal with problems as they occur—even when you
know there are better ways of tackling performance
management.
Summary
• Applications that access relational databases
are only as good as the performance they
achieve.
• The wise organization will implement a
comprehensive performance monitoring,
tuning, and management environment that
consists of policies, procedures, and
integrated performance management tools
and utilities.
Terima kasih