1 / 27

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. performance monitoring and tuning  salah satu tugas penting DBA

brooke
Download Presentation

DATABASE ADMINISTRATION

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 ADMINISTRATION Pertemuanke-5

  2. Performance Management source : Database Administrationthe complete guide to practices and procedureschapter 9by. Craig S. Mullins

  3. performance monitoring and tuning  salah satu tugas penting DBA • Komputer  bagaimana mendapatkan kinerja yang baik • Kinerja DBMS  memiliki reputasi yang buruk dalam hal performance • Konten materi : • Performance monitoring • Tuning • Perbedaan monitoring dan performance management

  4. Defining Performance • Organisasi pengguna IT  monitor dan tune performa IT infrastruktur • Infrastruktur IT meliputi • Server • Jaringan • Aplikasi • Database • Performance management yang digunakan masih reaktif • Contoh : tablespace yang sudah penuh

  5. Apa database performance ? Efisiensi ? • Konsep db performance  konsep supply and demand • User request informasi dari db • Dbms  supply informasi • Db performance  tingkat dimana DBMS mensupply informasi untuk permintaan users

  6. Lima faktor yang mempengaruhikinerjadatabase • workload, • throughput, • resources, • optimization, • and contention

  7. workload • Kombinasi dari  transaksi online, batch jobs, adhoc query, analisa data warehousing, dan perintah sistem dalam satu waktu • Dapat fluktuatif dari hari ke hari, jam ke jam, menit ke menit • terkadang dapat diprediksi (contoh : penggunaan internet paling banyak pada waktu istirahat) • Lebih sering tidak dapat diprediksi • Memiliki dampak besar pada performa db

  8. throughput • Kemampuan seluruh komputer untuk memproses data • Gabungan kecepatan I/O, CPU dan kemampuan paralel komputer, efisiensi OS dan system software

  9. Resource • Hardware dan software sebagai tempat untuk penampungan data sistem • Meliputi • Db kernel • Disk storage • RAM chips • Cache control • microcode

  10. optimatizion • Semua sistem dapat dilakukan optimasi • DBMS perlu optimasi khusus  dilakukan ke dalam internal DBMS • Banyak faktor yang perlu dioptimalkan  SQL query, db parameter

  11. contention • Permintaan yang tinggi  muncul contention • Contention  kondisi dimana dua atau lebih komponen dari kerja menggunakan sebuah resource dengan bertentangan • Contoh dua upate bersamaan pada satu data • Contention naik, througput naik

  12. Monitoring vs. Management • Butuh langkah proaktif selain langkah reaktif • Perubahan pada kode aplikasi  belum bisa dianggap langkah proaktif • Langkah proaktif  memperbaiki masalah sebelum menyelesaikan aplikasi • Dba sering menggunakan pendekatan reaktif

  13. Penggunaan event-drivent tools  untuk mendeteksi masalah kinerja secara otomatis • Manajemen performance != monitoring performance • Manajemen performance  combine monitoring performance + resolve masalah • Meliputi 3 komponen spesifik • Monitoring, analisis, dan koreksi • Monitoring  identifikasi masalah

  14. Figure 9-1. The components of performance management

  15. analisa • Komponen kedua dari performance management • Monitoring  dapat menghasilkan ribuan laporan masalah • Hasil monitoring tidak dapat digunakan untuk mengambil keputusan • Analisa  diperlukan untuk mencari penyebab utama dan cara mengatasi masalah • Hanya dapat dilakukan oleh tenaga ahli, seperti DBA

  16. Types of Performance Tuning • Aplikasi db memerlukan interkasi konstan antara beberapa resource komputer agar bekerja sesuai spesifkasinya • Tuning db dapat dipecah menjadi 3 komponen • System tuning • Db tuning • Aplikasi tuning • Ketiganya berhubungan

  17. 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.

  18. 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.

  19. 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.

  20. 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.

  21. 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.

  22. Performance Tuning Tools • Tools DB  efektif membantu mengatur performa DB • Beberapa vendor menyertakan beberapa tools untuk performance management • Tapi tidak terlalu bisa menangani tipe data yang sangat banyak • Tools DB dapat dikatagorikan 2 tuama • Performance management • Performance optimization

  23. Contoh tools • Performance monitor • Performance estimation • Capacity planning tools • SQL analysis dan tuning • Advisory tools augment SQL • System analysis dan tuning

  24. 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.

  25. Aturan tuning • Do not over-tune • Remain focused • Do not panic • Communicate clearly • Accept reality

  26. 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.

  27. Terimakasih

More Related