Computer Science II

Lab - Connection Pooling

School of Computing
College of Engineering
University of Nebraska-Lincoln
University of Nebraska-Omaha

This lab demonstrates utilizing parallel computing as well as connection pooling to speed up the persistence of records to a database.

This lab is part of the PDC@UNL project which aims to introduce Parallel and Distributed Computing (PDC) topics and exercises in early computing education.

Overview

Prior to lab you should read/review the following resources.

• Read the wikipedia article on Connection Pool

Lab Objectives & Topics

Following the lab, you should be able to:

• Recognize the efficiency improvements of using connection pooling

Peer Programming Pair-Up

To encourage collaboration and a team environment, labs will be structured in a pair programming setup. At the start of each lab, you may be randomly paired up with another student by a lab instructor. One of you will be designated the driver and the other the navigator.

The navigator will be responsible for reading the instructions and telling the driver what is to be done. The driver will be in charge of the keyboard and workstation. Both driver and navigator are responsible for suggesting fixes and solutions together. Neither the navigator nor the driver is "in charge."
Beyond your immediate pairing, you are encouraged to help and interact and with other pairs in the lab.

Each week you should try to alternate: if you were a driver last week, be a navigator next, etc. Resolve any issues (you were both drivers last week) within your pair. Ask the lab instructor to resolve issues only when you cannot come to a consensus.

Because of the peer programming setup of labs, it is absolutely essential that you complete any pre-lab activities and familiarize yourself with the handouts prior to coming to lab. Failure to do so will negatively impact your ability to collaborate and work with others which may mean that you will not be able to complete the lab.

1. Getting Started

Clone this project code for this lab from GitHub in Eclipse using the URL: https://github.com/cbourke/PDCPooling. Refer to Lab 1.0 for instructions on how to clone a project from GitHub.

1.1 Setup

1. You'll need to install the database (available in sql/database.sql) in a network connected SQL database.
2. Update pdc.database.Config to provide your database's URL, username and password

2. Running in Serial

We have provided code that generates random data to insert into the database provided. There is a serial version, pdc.serial.SerialPersistor that will generate and persist a number of records. However, it runs in serial (one operation after the other, but only one operation at a time) and also opens/closes a connection for each operation. This can be extremely inefficient in a real system. To demonstrate just how inefficient run the program for 1000 records.

1. Open pdc.Demo and create a run configuration (run it once to create one)
2. Modify the run configuration with the following command line arguments: --demoType serial --numberOfRecords 1000
3. Record how long it took to insert all the records.

3. Running in Parallel

3.1 Completing the Code

We have provided most of the code that implements a connection pool. The connection pool code can be found in pdc.parallel.ConnectionPool. It uses a BlockingQueue to store connections. A blocking queue is a thread-safe version of the FIFO queue that you are familiar with. However, you interact with it differently:

• To enqueue an element, you use the put() method which will block the thread attempting to put something in the queue until space becomes available.
• To dequeue an element, you use the take() method which will block the thread attempting to dequeue until an element becomes available. This interaction ensures that threads retrieving or returning a database connection do not interfere with each other.

Instructions

The code in ConnectionPool is incomplete. You'll need to complete the following:

• getConnection() method which should retrieve and return a connection from the connections pool
• putConnection() method which should return the connection back to the pool In addition, you'll need to modify the PersistorWorker class to use these two methods. Remember: we don't want to create or close connections, we want to check them out from the pool and return them to the pool when we are done with them so they can be reused by other threads.

3.2 Running the Code

1. Go back to pdc.Demo
2. Modify the run configuration with the following command line arguments: --demoType parallel --numberOfThreads 4 --numberOfRecords 1000
3. Record how long it took to insert all the records.
4. Run different scenarios: if you increase the number of threads what does it do to the performance?

4. Testing, Submitting & Grading

• Test your programs using the provided JUnit test suite(s). Fix any errors and completely debug your programs.
• Submit the following files through webhandin:
  • ConnectionPool.java
  • PersistorWorker.java
• Run the grader and verify the output to complete your lab.