Movie Swap/Presentation

CS290F Fall 2006 - UCSB Computer Science - Thorsten von Eicken

Contents 1 Demo 2 Testing Methodology 2.1 Database Size 2.2 Choosing test data 2.3 Assuring the test 2.4 Critical Paths 3 Optimization of SQL Queries 3.1 Creating indexes 3.1.1 Optimization 3.2 Using forien key directly 3.3 Using Number of Owners 3.4 Graphs 4 Scaling 4.1 Session rate changed 4.2 From 1 to 3 servers 4.3 Optimizing 4.3.1 Optimization 1 4.3.1.1 Path 1, Add before 4.3.1.2 Path 1, Add after 4.3.2 Optimization 2 4.4 From 3 to 5 4.5 Memcached for sessions 4.6 Graphs

Demo

SEE: Movie Swap Website

Testing Methodology

Database Size

We have run the tests using a data base with 1000 users, 10000 ownerships (10 for each user and one for each movie), 10000 movies, 1000 potential swaps rows. The data has been created automatically using a small program and it is inserted into DB with each new test.

Choosing test data

We have chosen the test data so that they do not affect each other.

Assuring the test

For each test we do the following to make sure that the test emulates real life as much as we can.

• We run each path twice manually from the web.
  
• Then we go and check the DB data to make sure that the test really did what we expect.
  
• After finishing the test we go and check the DB again to see if the data we expected to be added, deleted, or updated is there.

Critical Paths

• Path1 => Login -> Search catalog -> Add a movie to MyLibrary -> Logout

This path represents a user adding a movie to his library. A movie can not be added more than once bu we have relaxed this condition during the test.

• Path2 => Login -> Search available movies -> Propose a movie swap -> Show owners -> Choose one -> Show my library -> Choose my movie to exchange for -> Add to swaps basket -> Checkout -> Logout

This path represents a user who wants to propose a swap to another user. A user chooses a movie from the available ones for swapping, choses one of the movie owners to swap with, then a movie from his library in exchange. The swap will be added to the basket and then he checks out. This does not mean that the swap is done but rather it is proposed and it will be either accepted (path 3) or rejected.

Optimization of SQL Queries

Creating indexes

SHOW FIELDS FROM users  

SELECT * FROM users WHERE (users.`name` = 'muath' AND users.`hashed_password` = **********) LIMIT 1  

Optimization

Add index on user name and password.

CREATE INDEX `users_name_index` ON users (`name`, `hashed_password`) 

Using forien key directly

• Some of the optimizations here are due to using foreign keys directly instead of using Active Record objects.
• instead of potentialswaps.ownership.user we say potentialswaps.user1_id

Using Number of Owners

• Instead of going over all the ownerships table counting number of owners for a movie we use this counter.

Graphs

Scaling

Session rate changed

• This is to give realistic results.

From 1 to 3 servers

• We have scaled from 1 to 3 servers.

Optimizing

• We have optimized the code again

Optimization 1

Path 1, Add before

 /login/login think=2.0
       /stylesheets/layout_1_2.css
       /stylesheets/depot.css
       /images/Banner.png
 /login/login method=POST contents='name=foo&password=barbar' think=2.0
 /user
 /user/library think=1.0
 /catalog
 /catalog/search think=5.0
 /catalog/search method=POST contents='qstring=cars'
 /catalog/result think=5.0
 /user/add/8864?user_id=1 method=POST
 /user/index
 /user/library think=2.0
 /login/logout
 /login/login

Path 1, Add after

4 pages has been omitted.

/login/login think=2.0
       /stylesheets/layout_1_2.css
       /stylesheets/depot.css
       /images/Banner.png
/login/login method=POST contents='name=foo&password=barbar' think=2.0
/user/library think=1.0
/catalog/result think=5.0
/catalog/result method=POST contents='qstring=cars' think=5.0
/user/add/8864?user_id=1 method=POST
/user/library think=2.0
/login/logout
/login/login

Optimization 2

• SQL statement before

SELECT count(*) AS count_all FROM ownerships WHERE (user_id = 2)
SELECT * FROM ownerships WHERE (user_id = 2) LIMIT 0, 5
SELECT * FROM movies WHERE (movies.id = 1) LIMIT 1
SELECT * FROM movies WHERE (movies.id = 2) LIMIT 1
SELECT * FROM movies WHERE (movies.id = 18) LIMIT 1
SELECT * FROM movies WHERE (movies.id = 20) LIMIT 1
SELECT * FROM movies WHERE (movies.id = 45) LIMIT 1

• SQL statement after

SELECT COUNT(DISTINCT ownerships.id) FROM ownerships LEFT OUTER JOIN movies ON movies.id = ownerships.movie_id WHERE (user_id = 1)
SELECT ownerships.`id` AS t0_r0, ownerships.`movie_id` AS t0_r1, ownerships.`user_id` AS t0_r2, ownerships.`pref` AS t0_r3, movies.`id` AS t1_r0, movies.`asin` AS t1_r1, movies.`title` AS t1_r2, movies.`amazonUrl` AS t1_r3, movies.`audienceRating` AS t1_r4, movies.`description` AS t1_r5, movies.`smallImageUrl` AS t1_r6, movies.`largeImageUrl` AS t1_r7, movies.`num_of_owners` AS t1_r8 FROM ownerships LEFT OUTER JOIN movies ON movies.id = ownerships.movie_id WHERE (user_id = 1) LIMIT 0, 5

From 3 to 5

• we scaled from 3 to 5.

• The throughput went down on 5 servers and response time goes up?? DB bottleneck when we have paginate helper method)
• 3 Servers:
  • catalog/available_movies_result=> queries movies table + session + paginate over all the table

Completed in 0.32282 (3 reqs/sec) | Rendering: 0.00413 (1%) | DB: 0.31588 (97%) | 200 OK [1]
Completed in 0.56407 (1 reqs/sec) | Rendering: 0.00495 (0%) | DB: 0.55731 (98%) | 200 OK [2]

• 5 Servers
  • catalog/available_movies_result: => queries movies table + session + paginate over the all movies

Completed in 0.75342 (1 reqs/sec) | Rendering: 0.00586 (0%) | DB: 0.74560 (98%) | 200 OK [3]
Completed in 0.88054 (1 reqs/sec) | Rendering: 0.00437 (0%) | DB: 0.87412 (99%) | 200 OK [4]

Memcached for sessions

• We implemented memcached to store session data only.

Graphs