Transcript Database performance cookbook

DATABASE
PERFORMANCE
COOKBOOK
SCENARIO
 Great news!




You’ve completed CSCI 6442 (congratulations!)
You have an MS from GWU (congratulations again!)
You’re working on a project using a DBMS (congrats)
And they think of you as a database guru (whoops!)
 And now—a programmer says “I’m having performance
problems.”
 The boss, of course, tells her to come see you!
 How to proceed?
 Is it time to buy one of those huge books on performance tuning?
WHERE TO LOOK











First, look at the basics. Don’t buy the book yet.
How loaded is the server?
Which DBMS is being used?
How does the application use SQL?
Identify the queries that are slow
Review the use of SQL—can SQL do more?
Look at the plan for each one
First—check for existence of indexes and their use in the plan
Then—general review of the query and the plan
Recommend changes to indexes and query as appropriate
Get performance test results right away
SERVER LOAD
 The problem may be at the server





Find that out first
Server may be overloaded
Server may not be administered effectively
Server may not have enough capacity
Sometimes test server does not have production capacity
 Often, adding server capacity is the cheapest way to improve
performance
 A server upgrade may cost a few thousand dollars
 Cost of a programmer to the company is over $1,000 per day
 You won’t make many software changes in ten person-days of work
but for $10,000 you can buy quite a server
DBMS CHOICE
 There are huge dif ferences in ef ficiency between database
systems
 If either the IBM, Sybase, Oracle or Microsoft family is being used,
you can work with it
 If MySQL is used, check that NODB is being used. Even then, you
have limited options for optimization
 If you’re going to need serious optimization, get away from
MySQL
 With MySQL you can get stuck with poor optimization options
 You’ll also gain many other useful features with a fully -featured
DBMS
APPLICATION USE OF SQL
 Do applications make full use of SQL?
 Applications may use SQL for put and get, do all data manipulation in
the application
 They may issue many SQL statements to get a result that you can
obtain with a single statement
 Often programmers will just copy another programmer’s SQL to get
access to the database, won’t understand a thing
 Make sure that the ability of SQL to do powerful,
sophisticated selections, and use that power for updates and
deletes, is being used
 Huge performance gains are available from fixing these problems
 These are very easy wins
IDENTIFY PROBLEM QUERIES
 Usually some SQL statements stand out as performance
problems
 If they’re all slow, the problem may be in the database server
or the application server
 If all SQL statements are slow, go back two charts and repeat
WHERE TO LOOK
 Start again with the basics
 Review the use of SQL in each problem query
 Can more function be added to the database and removed from the
application? This can improve performance
 Look at indexes
 Look at indexes and their use in the plan
 Test with additional indexes as indicated
 Adding indexes is often all that’s necessary to improve performance
 Now you need to buy a big book
 Buy one of those huge books about performance tuning for your DBMS
 Your DBMS may process the statement differently depending on how
written
 Test alternative formulations of the statement for performance
 Follow the book for additional help
 Recommend changes in SQL statement, indexes
WHERE TO LOOK
 Carry out and document results of performance tests on your
improvements right away
 Create and write down a quantitative record of what you
accomplished
TAKE THE CREDIT
 Share the results of the performance improvement, with due
modesty
 Use them constructively, such as to create a write -up on DBMS
performance for your project
 You are a hero, take the credit —but do it in a gracious way