CS634 – Homework 5

Due Mon., Mar. 25

Transactions, Locking, more DBA work


Problem 1 Loading the bench table into your own mysql

See /var/local/cs634/setq for a copy of the Set Query Benchmark setup files for Oracle that were previously used to load the bench table on Oracle that you queried in homework 2. Also there is loadbench_mysql.sh for the mysql bulk load. Create a hw5 directory under your /var/local directory for your files for this assignment. Copy *.sql and *.sh from setq to your hw5 directory. Don't copy the huge data file.  Also copy ../mysql.sh to this directory so you can run your own mysql conveniently. Convert them to set up the bench table in your own mysql installation, in the database <username>db that you created in hw3. Use loadbench_mysql.sh for the bulk load.

Files: in order of use:

setq_cre.sql: create table (modified from Oracle case)
loadbench_mysql.sh: load the table from /var/local/cs634/setq/bench1M.dat (provided)
setq_index1.sql: build secondary indexes (modified from Oracle case)
analyze.sql: run analyze on bench (modified from Oracle case)
check_bench: get basic info on table from catalogs (modified from Oracle case)
scan.sql: sample table scan, with one row of output
queryK500K.sql: your query from hw2 that uses K500K (on column with index)
queryK250K.sql: your query from hw2 that uses K250K (on column without index)

Note: for my eoneil1 database,  I have

mysql --defaults-file=/home/eoneil/.my.eoneil1.cnf --verbose --port=3506

/home/eoneil/.my.eoneil1.cnf:   Keep .my.cnf for system-wide mysql use.

Problem 2

a. Run the timings of hw2 testing the effectiveness of the index. You can bring the server down and up again ("bounce" the server) to empty the buffer pool. Again use uptime to check that the system is not loaded. Specifically: bounce server, run queryK500K.sql, bounce server, run queryK250K.sql. Also try running scan.sql twice in a row without bouncing the server to see if it is faster the second time due to buffering some pages (bounce server, run scan.sql, run scan.sql again). Note that OS buffering is in use here, so bouncing the server does not remove all the buffering.

b. To make mysql more like Oracle, and avoid buffering data twice over, turn off the use of OS buffering by putting  innodb_flush_method = O_DIRECT in my.cnf (in the [mysqld] area).  Rerun the experiments of a after bouncing the server to set the new parameter. Now the buffer pool should be the only source of buffering, and it is too small (128MB) to hold the bench table.

c. Reconfigure the buffer pool size to 500MB and rerun the two-scans experiment. Explain how you increased the buffer size and the results of the experiment. Put the buffer pool size back to its original value when you're done.

Problem 3
Exercise 17.10,
which should refer to Figure 17.5, not 19.5.  Do parts, 1, 3, and 5.

Worked example of Schedules and 2PL locking, for next questions:

Recall the example from class 18: original sequence

R1(A) R2(A) R2(B) R1(B) W2(B) R2(B) W2(B) C1  C2

Running with strict 2PL, with T2 requesting a write lock for R(B) W(B):

The following sequence shown on the slide assumes the R1(B) executes after X2(B) but before the R2(B):

S1(A) R1(A) S2(A) R2(A) X2(B) <S1 (B)-blocked> R2(B) W2(B) C2  <S1 (B)-unblocked> R1(B) C1

But the simple interpretation of the above sequence is as follows, where T2 does the R2(B) just after getting the lock for it, so use this as a model:

S1(A) R1(A) S2(A) R2(A) X2(B) R2(B) <S1 (B)-blocked> W2(B) C2  <S1 (B)-unblocked> R1(B) C1


Problem 4

 R1(A) R2(B) W1(B) W2(C)R3(C)W3(A) C1 C2 C3


Problem 5

R2(X) W3(Y) R1(Y) W2(X) W1(X) W1(Y) W4(Y),W4(X) C1 C2 C3 C4


Problem 6

Run the following sequence under Strict 2PL. Show the waits-for-graph as the sequence of actions progresses.

W1(A) R2(B) W2(B) W3(C) R1(B) W1(B) W2(C) W3(A) C1 C2 C3 C4