Suorituskyvyn monitorointi ja testiympäristön luonti © Jouni Huotari 2007-2010 (IIO30200 Tietokannan hallinta) ============================================================ http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/ ============================================================ Huom.: M-kirjaimella alkavat tehtävät/otsikot liittyvät MySQL:ään S-kirjaimella alkavat tehtävät/otsikot liittyvät SQL Serveriin O-kirjaimella alkavat tehtävät/otsikot liittyvät Oracleen Käynnistä ensin virtuaalikone, jossa on MySQL/Oracle/SQL Server MySQL: Suorituskyvyn monitorointi --------------------------------- 1. Käynnistä MySQL Administrator. 2. Rootin salasana on root66. 3. Katso Health-kohdasta esim. Traffic ja tutki myös muut välilehdet. Oracle: suorituskyvyn monitorointi ---------------------------------- 1. Käynnistä Oracle Enterprise Manager (Go To Database Home Page) 2. Kytkeydy instanssiin SYSTEM-tunnuksella (pw: root66). 3. Katso Usage Monitor -kohdasta (keskusmuisti jne.). 4. Katso myös Administration > Monitor > System Statistics ja Top SQL Lisätehtävä: vertaa Oraclen monitorointityökaluja SQL Serverin ja MySQL:n työkaluihin. SQL Server: suorituskyvyn monitorointi -------------------------------------- 1. Käynnistä Microsoft SQL Server Management Studio Express 2. Kytkeydy sa-tunnuksella (pw: root66). 3. Katso Management-kohdasta Activity Monitor (ja jätä se päälle) 4. Katso myös Databases ja hiiren oikeanpuol. nappi kannan päällä > Reports 5. Tee kysely ja suoritettuasi sen anna Query-valikosta käsky "Display Estimated Execution Plan" ============================================================================= Huom. ennen testiympäristön luontia sinun kannattaisi luoda itsellesi tunnus ja kirjautua DBMS:ään omalla tunnuksellasi. Voit samalla luoda TILAUS-tieto- kannan ja antaa itsellesi kaikki oikeudet ko. kantaan. ============================================================================= M - Testiympäristön luonti MySQL:ään ------------------------------------ 1. Luo Demox Oy:n taulut; aja skripti createDemoxOySQL_MySQL.sql esim. siten, että a) kopioi se http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/-kansiosta virtuaalikoneeseen (esim. C-aseman TEMP-kansioon ja anna sitten MySQL:n komentokehotteessa käsky source C:\TEMP\createDemoxOy_MySQL.sql) TAI b) käynnistä Query Browser omalla tunnuksellasi ja kytkeydy samalla TILAUS-kantaan, tee uusi skriptivälilehti ja liitä skripti em. tekstitiedostosta; lopuksi "Execute" Seuraavaksi luodaan ODBC-yhteys Oracle-kantaan ja luodaan testidata 2. Luo MySQL 3.51 ODBC-määritys TILAUS-kantaan. Anna ODBC-yhteyden nimeksi "tilaus". 3. Kopioi http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/DemoxGen04.xls virtuaalikoneeseen esim. C-aseman TEMP-kansioon. 4. Käynnistä DemoxGen04.xls. Salli makrot. 5. Muuta tarvittaessa Connection String: Provider=MSDASQL;DSN=tilaus 6. Luo aluksi 100 asiakasta ja tuotetta. Katso MySQL Query Browserilla hakuajat näytön alalaidasta. 7. Listaa kaikki asiakkaat (SELECT * FROM asiakas;). 8. Luo esim. 500000 asiakasta (ja pidä kahvitauko :) 9. Tee haku uudestaan: miten hakuaika muuttui? 10. Tee haku, joka listaa helsinkiläiset miespuoliset yhteyshenkilöt. SELECT astunnus, yhteyshlo FROM asiakas WHERE sukup = '1' AND postitmp = 'Helsinki' ORDER BY yhteyshlo; 11. Luo 3 tähden indeksi em. haulle. (Vinkki: indeksin nimi on i_postitmp_sukup_yhteyshlo_astunnus) 12. Tee edellinen haku uudestaan (voit muuttaa sukupuolen: 1 => 2). Tutki selitykset ja hakuajat. Mitä opit? ==================================================== VMware-testikoneessa saatiin seuraavat tulokset: MySQL 5.0 InnoDB-taulut, haku ennen indeksointia 1) 10000 asiakasta generoitiin n. 10 sekunnissa => hakuaika: 0,0156 s. (0,0387 s.); palautti 112 riviä 2) 100000 riviä generoitiin n. 2 minuutissa => hakuaika: 0,0367 s. (1,1231 s.); palautti 1285 riviä 3) 500000 riviä generoitiin n. 9 minuutissa => hakuaika: 0,1321 s. (4,1571 s.); palautti 6405 riviä Taulun koko: n. 100 Mt (keskim. n. 200 t / rivi) =================================================== => Luotiin 3 tähden indeksi (kesti n. 10 min.) ---------------------------------------------- Sama haku uudestaan => hakuaika: 0,3927 s. (0,0568 s.); palautti 6405 riviä ========================================================================== ////////////////////// Microsoft SQL Server 2008 \\\\\\\\\\\\\\\\\\\\\\\\\ ========================================================================== S - Testiympäristön luonti SQL Serveriin ---------------------------------------- 1. Luo Demox Oy:n taulut: aja skripti C_DemoxOy_MSSQL.sql esim. siten, että - kopioi sen sisältö SQL Server Management Studioon omaan kyselyvälilehteen ja suorita se - SQL Server 2008 sisältää oman Date-tietotyypin, joten sitä ei tarvitse enää luoda erikseen - SQL-skripti löytyy http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/-kansiosta 2. Jos kirjauduit omalla tunnuksellasi SQL Serveriin, käy välillä katsomassa sa:na esim. Activity Monitorista mitä tapahtuu. 3. Kopioi http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/DemoxGen04.xls virtuaalikoneeseen esim. C-aseman TEMP-kansioon. 4. Käynnistä DemoxGen04.xls. Salli makrot. 5. Muuta tarvittaessa Connection String, esim. SQL Server 2008:aan (Native Client OLE DB): Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword; Lisätietoa: http://www.connectionstrings.com/sql-server-2008 6. Luo aluksi 100 asiakasta ja tuotetta. 7. Listaa kaikki asiakkaat (SELECT * FROM asiakas;). 8. Luo esim. 200000 asiakasta (ja pidä pieni tauko :) 9. Tee haku uudestaan: miten hakuaika muuttui? 10. Tee haku, joka listaa helsinkiläiset miespuoliset yhteyshenkilöt. - lajittelu yhteyshenkilön nimen mukaan - listaukseen astunnus, yhteyshlo SELECT astunnus, yhteyshlo FROM asiakas WHERE sukup = 1 AND postitmp = 'Helsinki' ORDER BY yhteyshlo; 11. Luo 3 tähden indeksi em. haulle. (Vinkki: indeksin nimi on i_postitmp_sukup_yhteyshlo_astunnus) 12. Tyhjennä välimuisti: dbcc dropcleanbuffers 13. Tee edellinen haku uudestaan (ja tutki selitykset). Mitä opit? 14. Kokeile monimutkaisempia hakuja, liitä tauluja toisiinsa jne. Lue indeksointivinkit kirjasta "Tietokantojen suunnittelu ja indeksointi" =============================================== ////////////// Oracle 10g \\\\\\\\\\\\\\\\\\\\\ =============================================== O - Testiympäristön luonti Oracleen ----------------------------------- 1. Luo ensin tilaus-hakemisto oradatan alle, esim. md C:\oraclexe\oradata\tilaus Vaihtoehtoisesti voit luoda tilaus-tietokannan Configuration Assistant -ohjelmalla (ks. ohje: Oracle-instassin luonti). 2. Käynnistä SQL*Plus ja kirjaudu system-tunnuksella (salasana on root66). Jos kohdassa 1 loit tilaus-hakemiston, luo sinne uusi taulualue: CREATE TABLESPACE TILAUS LOGGING DATAFILE 'C:\oraclexe\oradata\tilaus\tilaus.dbf' SIZE 800M 3. Käy katsomassa Enterprise Managerista, kuinka mm. USER I/O on muuttunut. 4. Luo uusi käytäjä: CREATE USER Demox IDENTIFIED BY demox DEFAULT TABLESPACE TILAUS; 5. Anna perusoikeudet uudelle käyttäjälle: GRANT CONNECT, RESOURCE TO Demox; 6. Uusi käyttäjä kytkeytyy kantaan: CONN Demox/demox; 7. Laita ajastus päälle> SET TIMING ON 8. Luo PLAN_TABLE: @?/rdbms/admin/utlxplan.sql (= C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utlxplan.sql) 9. Laita jäljitys päälle + selitykset näkyviin (Oracle 8 ja Oracle 9): SET AUTOTRACE ON EXPLAIN STATISTICS => Oracle 10g2:ssa tulee virheilmoitus "SP2-0618: Istunnon tunnistetta ei löydy. Tarkista, että PLUSTRACE-rooli on käytössä SP2-0611: Virhe otettaessa käyttöön raporttia STATISTICS" 10. Luo Demox Oy:n taulut ajamalla skripti createDemoxOy_Oracle.sql esim. siten, että kopioi se ensin http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/-kansiosta virtuaalikoneeseen esim. C-aseman TEMP-kansioon ja anna sitten käsky start C:\TEMP\createDemoxOy_Oracle.sql 11. Lisää tiedot tilaus-tietokantaan ajamalla skripti insertDemoxOy_Asiakas.sql Palaa sitten SQL*Plussaaan ja katso, mitä Oracle kertoo kun teet pari hakua tietokantaan. 12. Listaa kaikki asiakkaat. SELECT * FROM asiakas; (Jos et antanut käskyä SET LIN 1000, niin saatat saada listauksena melko epäselvän rivijoukon :). Elapsed: 00:00:00.32 Execution Plan ---------------------------------------------------------- Plan hash value: 4165228809 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 52200 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| ASIAKAS | 100 | 52200 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ----------------------------------------------------- 584 recursive calls 0 db block gets 135 consistent gets 15 physical reads 0 redo size 41151 bytes sent via SQL*Net to client 446 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 100 rows processed Huom. Oraclessa optimoijan tekemän suunnitelman saa selville lauseella EXPLAIN PLAN: EXPLAIN PLAN SET STATEMENT_ID = 'Asiakashaku' INTO plan_table FOR SELECT * FROM asiakas; 13. Tee vielä monimutkaisempi haku; hae esim. asiakkaiden nimet, tilauspvm:t ja tilattujen tuotteiden määrät ja hinnat. Vinkki: kyselyn voit tehdä Accessilla ja liittää sen Oracleen (siivottuasi sitä hieman esim. tekstieditorilla). SELECT ASIAKAS.ASNIMI, MTILAUS.TILAUSPVM, TUOTE.TUOTENIMI, MTILAUSRIVI.KPL, TUOTE.HINTA, KPL*HINTA AS YHT FROM TUOTE INNER JOIN ((ASIAKAS INNER JOIN MTILAUS ON ASIAKAS.ASTUNNUS = MTILAUS.ASTUNNUS) INNER JOIN MTILAUSRIVI ON MTILAUS.TILAUSNRO = MTILAUSRIVI.TILAUSNRO) ON TUOTE.TUOTENRO = MTILAUSRIVI.TUOTENRO; 10000 rows selected. Elapsed: 00:00:11.51 Execution Plan ---------------------------------------------------------- Plan hash value: 348991559 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10000 | 1728K| 32 (7)| 00:00:01 | |* 1 | HASH JOIN | | 10000 | 1728K| 32 (7)| 00:00:01 | | 2 | TABLE ACCESS FULL | TUOTE | 304 | 17632 | 3 (0)| 00:00:01 | |* 3 | HASH JOIN | | 10000 | 1162K| 28 (4)| 00:00:01 | |* 4 | HASH JOIN | | 1000 | 80000 | 19 (6)| 00:00:01 | | 5 | TABLE ACCESS FULL| ASIAKAS | 100 | 4500 | 5 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL| MTILAUS | 1000 | 35000 | 13 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | MTILAUSRIVI | 10000 | 380K| 9 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("MTILAUSRIVI"."TUOTENRO"="TUOTE"."TUOTENRO") 3 - access("MTILAUSRIVI"."TILAUSNRO"="MTILAUS"."TILAUSNRO") 4 - access("MTILAUS"."ASTUNNUS"="ASIAKAS"."ASTUNNUS") Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 133 recursive calls 0 db block gets 905 consistent gets 0 physical reads 0 redo size 580154 bytes sent via SQL*Net to client 7706 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 10000 rows processed 14. Tee haku, joka listaa helsinkiläiset miespuoliset yhteyshenkilöt. SELECT astunnus, yhteyshlo FROM asiakas WHERE sukup = '1' AND postitmp = 'Helsinki' ORDER BY yhteyshlo; 15. Luo 3 tähden indeksi em. haulle. 16. Tee edellinen haku uudestaan (ja tutki selitykset). Mitä opit? Lisätehtävä: L1. Kopioi http://student.labranet.jamk.fi/~huojo/opetus/IIO30200/harj/DemoxGen04.xls virtuaalikoneeseen esim. C-aseman TEMP-kansioon. L2. Käynnistä DemoxGen04.xls. Salli makrot. L3. Muuta tarvittaessa Connection String: Provider=OraOLEDB.Oracle.1;Password=demox;Persist Security Info=True;User ID=Demox;Data Source=tilaus