1 / 14

Database Performance Tuning

Database Performance Tuning. Optimization Methods. DBMS Architecture. All data in a database are stored in data files. Data files are stored in file groups or table spaces. To work with the data, it is first moved to data/buffer cache (in RAM).

william-blevins
Download Presentation

Database Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Performance Tuning Optimization Methods

  2. DBMS Architecture • All data in a database are stored in data files. • Data files are stored in file groups or table spaces. • To work with the data, it is first moved to data/buffer cache (in RAM). • SQL cache stores the most recently executed statements and functions.

  3. Database Architecture • Moving data from data files (permanent storage) to cache requires in I/O request. • The majority of performance-tuning activities focus on minimizing the number of I/O requests.

  4. Design Choices • Make your data as small as possible • Declare columns not null (saves 1 bit per column) • Keep primary keys as short as possible • Create only the indexes that you need. Use indexes when you search using a combination of columns.

  5. Database Statistics/Troubleshooting • Use the ANALYZE TABLE command to gather statistics on your database. • Use the CHECK TABLE command to check for corrupt tables resulting from not being closed properly. • Use the REPAIR TABLE to fix any corrupt tables found with the CHECK TABLE command

  6. Database Statistics/Troubleshooting • Use the OPTIMIZE table command to recover unused space caused by fragmented files.

  7. Optimizing Queries • Use the EXPLAIN command BEFORE your SELECT query to see exactly how the query is working • Create indexes to speed up data retrieval • MySQL keeps row data (tables) and index data in separate files • An index is an ordered set of values that contains the index key and pointers. • The pointers are the row IDs for the actual table rows

  8. Indexes • If there is no index, the DBMS will perform a full table scan when executing a query.

  9. Indexes and Query Optimization (continued) CREATE INDEX STATE_NDX ON CUSTOMER(CUS_STATE);

  10. Example using Premiere Products CREATE INDEX CREDIT_LIIMIT ON CUSTOMER (CREDIT_LIMIT); CREATE INDEX REP_NUM ON CUSTOMER (REP_NUM);

  11. When do I need to create an Index? • When an indexed column appears by itself in a search criteria of a WHERE or HAVING clause. • When an indexed column appears by itself in a GROUP BY or ORDER BY clause. • When the data sparsity on the indexed column is high (High number of different values that a column could have.)

  12. Can I “SEE” an index file? • SHOW INDEX FROM tablename;

  13. Deleting Indexes • DROP INDEX indexname FROM tablename;

  14. Database Optimization Exercise • Use the car dealership project 2 database to complete the exercise. • Completed exercise due on Wednesday, November 19 (Same day as exam 2)

More Related