DBM449 Lab 2 in MySQL – Complete Word Document with all steps – Instant Delivery – Perfect Solution

Lab Price = $15
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

I. OBJECTIVES
1. Understand and become familiar with fundamentals of DBMS performance optimization, operation of the query optimizer, and analysis and tuning commands: EXPLAIN, ANALYZE, and OPTIMIZE.

II. PARTS LIST
1. EDUPE Omnymbus Environment (https://devry.edupe.net:8300) and/or
2. MySQL (dev.mysql.com/downloads)

III. PROCEDURE, Part A—The EXPLAIN Command
By now you have set up either the Omnymbus environment or the MySQL Server Community environment or both. You may do the labs in this class on your own computer equipped with MySQL or you may use the MySQL environment hosted by the vendor Omnymbus. You may even use both.

Lab Procedure (common to both environments):

1. CHECKPOINT QUESTION: An essential component of modern relational database management systems is the query optimizer. Explain what the query optimizer is, and describe the decision making process the query optimizer uses in order to automatically generate efficient query execution. Based on your explanation, it should be clear to you that the query optimizer can only work with the tools that you, the database architect, provide. For example, the query optimizer cannot make use of an index that would dramatically speed query results, if you have not created the index. Nor can the query optimizer automatically inform you of this serious omission. However, there are other tools, such as the EXPLAIN, ANALYZE, and OPTIMIZE commands which will provide the information you need in order to detect and address the need for indexes, objects, or modifications in the data model which can dramatically improve DBMS performance.
2. Start/Connect to MySQL.
3. If you have modified or deleted the schema and data model used for Lab #1, re-run the SQL script from Lab #1 in order to create and populate the tables for this exercise.
4. Execute the following SQL query.

EXPLAIN
SELECT * FROM COURSE;

You should obtain a result similar to the following.

Figure 1
5. Take a screen shot that shows the results and paste that into the lab document.
6. Annotate the EXPLAIN output, in order to make a guide for yourself that you may reuse and consult for the remaining lab steps and also for your future use. Be sure to define what each of the fields denotes generally, as well as the implications of the specific results obtained. Be sure to include the results of this step (the annotated output) in your lab report.
7. CHECKPOINT QUESTIONS: This query performed a table scan. How can you tell this from the output generated? What is a table scan? What are the performance implications of a table scan? Record your answers to these questions in your lab report.
8. Execute the following SQL query.

EXPLAIN
SELECT * FROM COURSE
ORDER BY COURSE_CODE;

You should obtain a result similar to the following.

Figure 2
9. CHECKPOINT QUESTION: Explain each difference in Figure 2 when compared to Figure 1, why the element changed, and how to interpret its meaning. Record your answers to these questions in your lab report.
10. Carefully study, and then execute the following SQL query. Note that this table joins multiple tables in the WHERE clause.

EXPLAIN
SELECT COURSE_NAME, CLIENT_NAME, GRADE
FROM COURSE c, COURSE_ACTIVITY ca, CLIENT cl
WHERE c.COURSE_CODE = ca.COURSE_CODE AND ca.CLIENT_NO=cl.CLIENT_NO
ORDER BY COURSE_NAME, CLIENT_NAME;
11. Study and analyze your results carefully. Your understanding of the results will be essential, shortly. Copy and paste your results into your lab report.
12. The image below is the current (Lab #1) data model. Study it carefully, keeping in mind the EXPLAIN results you just obtained.
Figure 3
13. CHECKPOINT QUESTION: A careful analysis of both the EXPLAIN results and the data model should make it clear to you that there are several improvements/modifications that could and should be made to improve the performance of this (and many other) queries that are likely to be run on this schema. If the necessary changes are not yet clear to you, review what you have learned in previous database classes about normalization, indexes, relationships, and primary and foreign keys. List the most important changes that should be made to address the shortcomings of the current implementation of the schema in order to provide the greatest improvement in performance of this query, as indicated by the EXPLAIN output. Record your recommendations in your lab report.
14. Create the revised ERD diagram implementing your recommended changes. Paste a copy into your lab report.
15. Implement the DDL statements to affect your recommended modifications. Paste a copy into your lab report.
16. With your modifications to the data model completed, re-run the EXPLAIN command.

EXPLAIN
SELECT COURSE_NAME, CLIENT_NAME, GRADE
FROM COURSE c, COURSE_ACTIVITY ca, CLIENT cl
WHERE c.COURSE_CODE = ca.COURSE_CODE AND ca.CLIENT_NO=cl.CLIENT_NO
ORDER BY COURSE_NAME, CLIENT_NAME;

17. Paste the output from the EXPLAIN command into your lab report.
18. Compare and analyze the differences between the EXPLAIN results prior to, and after your modifications. Discuss how the latter output from the EXPLAIN command shows that your modifications will effectively improve query performance. Record your analysis and commentary in the lab report.

IV. PROCEDURE, Part B—The ANALYZE Command

1. CHECKPOINT QUESTION: One performance issue not addressed by tweaks to the data model or query optimizer is table fragmentation. What is table fragmentation? How does it occur? What are the implications of table fragmentation to database file size (and why?)? What are the implications of table fragmentation to query performance (and why)? Record your response in your lab report.
2. Create a new table with which to explore the phenomenon of table fragmentation, using the following SQL query.

CREATE TABLE BIG_TEST_TABLE(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, VAL INT) ENGINE = MYISAM;

3. We will need to insert a large number of rows in order to see in a really meaningful way the effects of fragmentation. Because it could be very inefficient and time consuming to upload a large amount of data if you are working with a remote database, create the following stored procedure, which can create the additional rows needed directly within the database engine.

DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
DECLARE i INT DEFAULT 100;

WHILE i < 10000 DO INSERT INTO BIG_TEST_TABLE (VAL) VALUES (i); SET i = i + 1; END WHILE; END$$ DELIMITER ; 4. Execute the prepare_data() stored procedure in order to create 10,000 rows on the test table (if you would like to experiment with a larger table, you may either modify the stored procedure, or call it multiple times. Be careful! In some test and lab environments, this procedure can take a LONG time to run!). Create a new table with which to explore the phenomenon of table fragmentation, using the following SQL query. CALL prepare_data(); Notes: If you are running this procedure using a remote lab or cloud provider, such as the DeVry lab, it can take a VERY long time to complete. You may want to start by reducing the number of rows the stored procedure creates to 100 or 1,000, until you have a good idea of how long it will take to complete. Also note that if the query runs very long, you may lose the connection to the remote database while the procedure is still running. This will NOT stop the execution of the procedure. You can check the progress of the procedure by running a query such as, SELECT COUNT(*) from BIG_TEST_TABLE; and examining the result. Run the query again to see that the stored procedure is still inserting rows. If the number of rows keeps increasing, the procedure is still running! 5. Compose and execute a query to count the number of rows in BIG_TEST_TABLE. Record the query and copy and paste the query result into your lab report. 6. Issue the following command in order to obtain useful statistics on the status of tables in the current schema. Copy and paste the result into your lab report. SHOW TABLE STATUS; 7. Notice that we can express the percentage of table fragmentation as: (DATA_FREE/DATA_LENGTH). We can refine the ration of fragmentation further by adding the space consumed by the INDEX to the denominator: (DATA_FREE/1024/1024)/((DATA_LENGTH/1024/1024)+(INDEX_LENGTH/1024/1024)). Use these observations to construct a query similar to the following. SELECT TABLE_SCHEMA, TABLE_NAME, DATA_FREE, (DATA_FREE/1024/1024)/((DATA_LENGTH/1024/1024)+(INDEX_LENGTH/1024/1024)) AS RATIO, (DATA_FREE/DATA_LENGTH) AS FRAG_PERCENT, INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "DBM449LAB1" AND TABLE_NAME = "BIG_TEST_TABLE"; 8. SELF-TEST: At this point, you have a newly created table, and no rows have been deleted. What percentage of fragmentation would you expect to obtain from running this query? You do not need to record your estimate—this self-test is to help you to check your understanding. 9. Run the query to determine the percentage fragmentation, and record the result in your lab report. The percentage fragmentation at this point should be zero, because no rows have been deleted (there is no wasted space). 10. Run the SHOW TABLE STATUS; statement again, and paste the result into your lab report. 11. Now, let’s cause the table to become badly fragmented, by dropping every other row. DELETE FROM BIG_TEST_TABLE WHERE mod(ID,2) = 0; 12. Run the SHOW TABLE STATUS; statement again, and paste the result into your lab report. 13. Run the query to show the percentage fragmentation again, and paste the result into your lab report. 14. CHECKPOINT QUESTION: Explain what has happened to cause table fragmentation. What are the implications of wasted file space in the database? Explain the mechanics of how fragmentation negatively impacts query performance (note: take it as a given that table fragmentation does degrade query performance; you are to explain the mechanics of query operation that cause a query to run more slowly on a fragmented table than on an optimized table). Record your response in your lab report. 15. Run the following command, and paste the result into your lab report: ANALYZE TABLE BIG_TEST_TABLE; 16. Run the SHOW TABLE STATUS; statement again, and paste the result into your lab report. 17. CHECKPOINT QUESTION: What was accomplished by running the ANALYZE TABLE command? Why is this important to database performance? Record your recommendations in your lab report. Be sure to read and research the differences of running this command using the MYISAM storage engine, and the InnoDB storage engine, as this will be of interest shortly. 18. CHECKPOINT QUESTION: Considering what you have read about the ANALYZE command and table locking, what are your recommendations about when and how often to run this command. Are there periods of time when you would avoid using this command? Explain. Record your response in your lab report. V. PROCEDURE, Part C—The OPTIMIZE Command 1. Run the following command, and paste the result into your lab report. OPTIMIZE TABLE BIG_TEST_TABLE; 2. Issue the following command in order to obtain useful statistics on the status of tables in the current schema. Copy and paste the result into your lab report. SHOW TABLE STATUS; 3. Run the query to show the percentage fragmentation again, and paste the result into your lab report. 4. CHECKPOINT QUESTION: After reading about the OPTIMIZE command, and reviewing your output from the two previous steps, explain what you have achieved, and describe the current state of the table from a performance perspective. Record your response in your lab report. 5. CHECKPOINT QUESTION: Considering what you have read about the OPTIMIZE command and table locking, what are your recommendations about when and how often to run this command. Are there periods of time when you would avoid using this command? Explain. Record your response in your lab report. VI. PROCEDURE, Part D—Comparing Storage Engines: MyISAM and InnoDB The MySQL DBMS provides a pluggable storage engine architecture. That is, the actual file structures and search and storage formats and programs that make-up the database’s primary storage and retrieval engine can be replaced. At first, you might think this silly, or at best, chaotic. In fact, this versatility makes a single database solution very flexible. For example, when configured to run the MEMORY storage engine, MySQL offers an exceedingly fast access, low latency solution useful for specific applications, and also useful for embedded device usage. For high-performance, scalable applications, one might select the MySQL CLUSTER storage engine. The benefit to this architecture is that, by-and-large, these different storage engines are transparent to users and developers. However, database analysts (DBAs) and data architects (DAs) need to be aware of differences in how to tune and manage different storage engine for maximum performance, as well as the characteristics of different storage engines, in order to select the best, most suitable engine for their intended purposes. In this procedure, you will repeat steps from the previous procedures using the InnoDB storage engine in place of the MyISAM storage engine, in order to become familiar with some of the fine distinctions between how these storage engines operate, and how they are tuned for performance. 6. Drop BIG_TEST_TABLE, and recreate it using the InnoDB storage engine. 7. Repeat the steps and observation recordings from Procedure, Part B, and Procedure Part C, omitting the CHECKPOINT QUESTIONS from those sections. NOTE: Also omit step IV.3, in which you created the stored procedure prepare_data(). 8. CHECKPOINT QUESTION: Why is it not necessary to drop and re-create the prepare_data() stored procedure, even though you will be using it again, this time on an InnoDB version of your table? How does this demonstrate the benefits of a pluggable storage engine architecture? Record your response in your lab report. 9. CHECKPOINT QUESTION: Study your lab report results for the current procedure carefully, and review your research and reading about the ANALYZE and OPTIMIZE commands and how they operate in the MyISAM and InnoDB environments. Explain why it is more difficult to determine the degree of fragmentation and to predict with accuracy the benefit that will be derived from optimizing the table. Be sure to touch on the differences in the way that InnoDB samples and updates key statistics in your explanation. Record your response in your lab report.   Laboratory Report DeVry University College of Engineering and Information Sciences Course Number: DBM449 Laboratory Number: 2 Laboratory Title: Performance Tuning and Diagnostics Note: There is no limit on how much information you will enter under the three topics below. It is important to be clear and complete with your comments. Like a scientist you are documenting your progress in this week’s lab experiment. Objectives: (In your own words what was this lab designed to accomplish? What was its purpose?) Results: (Discuss the steps you used to complete your lab. Were you successful? What did you learn? What were the results? Explain what you did to accomplish each step. You can include screen shots, code listings, etc. to clearly explain what you did. Be sure to record all results specifically directed by the lab procedure.) III.1: III.5: III.6: III.7: III.9: III.11: III.13: III.14: III.15: III.17: III.18: IV.1: IV.5: IV.6: IV.9: IV.10: IV.12: IV.13: IV.14: IV.15: IV.16: IV.17: IV.18: V.2: V.3: V.4: V.5: VI.7: VI.8: VI.9: Conclusions: (After completing this lab, in your own words, what conclusions can you draw from this experience?)

Relevant Material
Screenshots
Lab 2: Screenshot 2
Lab 2: Screenshot 2
Lab 2: Screenshot 1
Lab 2: Screenshot 1
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on support@iqrajavaid.com
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $15
Please feel free to send us your queries at: support@iqrajavaid.com

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply