Relational Databases

After today you should...

• understand the importance of relational databases in architecting scalable Internet systems

Relational Databases

After today you should...

• understand the importance of relational databases in architecting scalable Internet systems

• understand how to design with concurrency in mind

Relational Databases

After today you should...

• understand the importance of relational databases in architecting scalable Internet systems

• understand how to design with concurrency in mind

• have a basic idea regarding how to speed up your data layer

Relational Databases

After today you should...

• understand the importance of relational databases in architecting scalable Internet systems

• understand how to design with concurrency in mind

• have a basic idea regarding how to speed up your data layer

  • determining where to start

Relational Databases

After today you should...

• understand the importance of relational databases in architecting scalable Internet systems

• understand how to design with concurrency in mind

• have a basic idea regarding how to speed up your data layer

  • determining where to start

  • understanding which rails-specific techniques to use

Relational Databases

After today you should...

• understand the importance of relational databases in architecting scalable Internet systems

• understand how to design with concurrency in mind

• have a basic idea regarding how to speed up your data layer

  • determining where to start

  • understanding which rails-specific techniques to use

  • being able to identify the source of slowness in an SQL query

Stateful Requests from Clients

Application Server Needs

• Data that is shared between requests and servers

Application Server Needs

• Data that is shared between requests and servers

• Fast access to data

Application Server Needs

• Data that is shared between requests and servers

• Fast access to data

• High availability of shared data

Application Server Needs

• Data that is shared between requests and servers

• Fast access to data

• High availability of shared data

• Intuitive utilization of the data layer

if david.balance > 100
  david.withdrawal(100)
  mary.deposit(100)
end

Data Layer Options

There are quite a few existing solutions for a stable data layer.

Relational (SQL)

• MySQL (MariaDB, Percona)
• PostgreSQL
• Oracle
• MS SQL

Data Layer Options

There are quite a few existing solutions for a stable data layer.

Relational (SQL)

• MySQL (MariaDB, Percona)
• PostgreSQL
• Oracle
• MS SQL

Non-relational (NoSQL)

• Cassandra
• MongoDB
• Redis

SQL v. NoSQL

SQL v. NoSQL

Relational Databases...

• are a general-purpose persistence layer
• offer more features
• have a limited ability to scale horizontally

SQL v. NoSQL

Relational Databases...

• are a general-purpose persistence layer
• offer more features
• have a limited ability to scale horizontally

Non-relational Databases...

• often are more specialized
• require more from the application layer
• are better at scaling horizontally

SQL v. NoSQL

Relational Databases...

• are a general-purpose persistence layer
• offer more features
• have a limited ability to scale horizontally

Non-relational Databases...

• often are more specialized
• require more from the application layer
• are better at scaling horizontally

If your needs fit within an RDBMS's (relational database management system) ability to scale, they tend to be the best. If your scaling needs exceed RDBMS capabilities, go non-relational.

Database Outline

Today we will discuss:

• Relational databases
  • Concurrency control
  • SQL query analysis

Database Outline

Today we will discuss:

• Relational databases
  • Concurrency control
  • SQL query analysis

Later we will discuss:

• Scaling options for relational databases:
  • Sharding
  • Service Oriented Architectures (SOA)
  • Distinguishing reads from writes
• A survey of NoSQL options

Database Transactions

Transactions are a database concept that allows a system to guarantee certain semantic properties.

Transactions provide control over concurrency.

Having rigorously defined guarantees means we can build correct systems using these databases.

Database ACID Properties - Refresher

Database ACID Properties - Refresher

Atomicity

• Complete everything, or do nothing
• No partial application of a transaction

Database ACID Properties - Refresher

Atomicity

• Complete everything, or do nothing
• No partial application of a transaction

Consistency

• The database should be consistent both at the beginning and end of a transaction
• Consistency is defined by the integrity constraints (e.g., foreign keys, NOT NULL, etc.)

Database ACID Properties - Refresher

Atomicity

• Complete everything, or do nothing
• No partial application of a transaction

Consistency

• The database should be consistent both at the beginning and end of a transaction
• Consistency is defined by the integrity constraints (e.g., foreign keys, NOT NULL, etc.)

Isolation

• A transaction should not see the effects of other uncommitted transactions

Database ACID Properties - Refresher

Atomicity

• Complete everything, or do nothing
• No partial application of a transaction

Consistency

• The database should be consistent both at the beginning and end of a transaction
• Consistency is defined by the integrity constraints (e.g., foreign keys, NOT NULL, etc.)

Isolation

• A transaction should not see the effects of other uncommitted transactions

Durability

• Once committed, the transaction's effects should not disappear

Overlapping Concerns

Atomicity and Durability are related and are generally provided by journaling.

Consistency and Isolation are provided by concurrency control (usually implemented via locking).

Overlapping Concerns

Atomicity and Durability are related and are generally provided by journaling.

Consistency and Isolation are provided by concurrency control (usually implemented via locking).

• Questions?

Definition: Schedule

A schedule is an abstract model used to describe the execution of transactions that run in a database.

Time goes top to bottom.

Conflicting Actions

Two actions are said to be in conflict if:

Conflicting Actions

Two actions are said to be in conflict if:

• the actions belong to different transactions

Conflicting Actions

Two actions are said to be in conflict if:

• the actions belong to different transactions

• at least one of the actions is a write operation

Conflicting Actions

Two actions are said to be in conflict if:

• the actions belong to different transactions

• at least one of the actions is a write operation

• the actions access the same object (read or write)

Conflicting Actions

Two actions are said to be in conflict if:

• the actions belong to different transactions

• at least one of the actions is a write operation

• the actions access the same object (read or write)

Conflicting Actions

Non-Conflicting Examples

All reads

Non-Conflicting Examples

All reads

Write to different object

Question

Can we blindly execute transactions in parallel?

Question

Can we blindly execute transactions in parallel?

Answer

No

• Dirty Read Problem

• Incorrect Summary Problem

Dirty Read Problem

A transaction (T2) reads a value written by another transaction (T1) that is later rolled back*.

The result of the T2 transaction will put the database in an incorrect state.

Incorrect Summary Problem

A transaction (T1) computes a summary over the values of all the instances of a repeated data-item. While that occurs, another transaction (T2) updates some instances of data-item.

The resulting summary will not reflect a correct result for any deterministic order of the transactions (T1 then T2, or T2 then T1).

Schedule Types

A schedule is serial if

• the transactions are executed non-interleaved
• one transaction finishes before the next one starts

Schedule Types

A schedule is serial if

• the transactions are executed non-interleaved
• one transaction finishes before the next one starts

Two schedules are conflict equivalent if

• they involve the same set of transactions
• every pair of conflicting actions are ordered in the same way

Schedule Types

A schedule is serial if

• the transactions are executed non-interleaved
• one transaction finishes before the next one starts

Two schedules are conflict equivalent if

• they involve the same set of transactions
• every pair of conflicting actions are ordered in the same way

A schedule is conflict serializable if

• the schedule is conflict equivalent to a serial schedule

Schedule Types

A schedule is serial if

• the transactions are executed non-interleaved
• one transaction finishes before the next one starts

Two schedules are conflict equivalent if

• they involve the same set of transactions
• every pair of conflicting actions are ordered in the same way

A schedule is conflict serializable if

• the schedule is conflict equivalent to a serial schedule

A schedule is recoverable if

• transactions commit only after all transactions whose changes they read, commit

Not Conflict Serializable

Why Serializable?

Serial Schedule

Having a serial schedule is important for consistent results. For example it is good when you are keeping track of your bank balance in the database.

A serial execution of transactions is safe but slow.

Most general purpose relational databases default to employing conflict-serializable and recoverable schedules.

How do these RDBMS employ conflict-serializable and recoverable schedules?

Database Locks

In order to implement a database whose schedules are both conflict serializable and recoverable, locks are used.

• A lock is a system object associated with a shared resource such as a data item, a row, or a page in memory.

• A database lock may need to be acquired by a transaction before accessing the object.

• Locks prevent undesired, incorrect, or inconsistent operations on shared resources by concurrent transactions.

Two Types of Database Locks

Write-lock

• Blocks writes and reads

• Also called exclusive lock

Two Types of Database Locks

Write-lock

• Blocks writes and reads

• Also called exclusive lock

Read-lock

• Blocks writes

• Also called shared lock (other reads can happen concurrently)

Two-Phase Locking

Two-phase locking (2PL) is a concurrency control method that guarantees serializability.

The two phases are:

• Acquire Locks

• Release Locks

Two-Phase Locking

Two-phase locking (2PL) is a concurrency control method that guarantees serializability.

The two phases are:

• Acquire Locks

• Release Locks

Two-phase locking Protocol

Two-Phase Locking

Two-phase locking (2PL) is a concurrency control method that guarantees serializability.

The two phases are:

• Acquire Locks

• Release Locks

Two-phase locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

Two-Phase Locking

Two-phase locking (2PL) is a concurrency control method that guarantees serializability.

The two phases are:

• Acquire Locks

• Release Locks

Two-phase locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

• Each transaction must obtain an exclusive lock on an object before writing.

Two-Phase Locking

Two-phase locking (2PL) is a concurrency control method that guarantees serializability.

The two phases are:

• Acquire Locks

• Release Locks

Two-phase locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

• Each transaction must obtain an exclusive lock on an object before writing.

• If a transaction holds an exclusive lock on an object, no other transaction can obtain any lock on that object.

Two-Phase Locking

Two-phase locking (2PL) is a concurrency control method that guarantees serializability.

The two phases are:

• Acquire Locks

• Release Locks

Two-phase locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

• Each transaction must obtain an exclusive lock on an object before writing.

• If a transaction holds an exclusive lock on an object, no other transaction can obtain any lock on that object.

• A transaction cannot request additional locks once it releases any locks.

Two-Phase Locking: Potential Issue

During the second phase locks can be released as soon as they are no longer needed. This permits other transactions to obtain those locks.

Two-Phase Locking: Potential Issue

During the second phase locks can be released as soon as they are no longer needed. This permits other transactions to obtain those locks.

What happens to T2 when T1 cancels the transaction?

Strong Strict Two-Phase Locking

2PL can result in cascading rollbacks.

SS2PL allows only conflict serializable schedules.

Strong Strict Two-Phase Locking

2PL can result in cascading rollbacks.

SS2PL allows only conflict serializable schedules.

Strong Strict Two-Phase Locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

• Each transaction must obtain an exclusive lock on an object before writing.

• If a transaction holds an exclusive lock on an object, no other transaction can obtain any lock on that object.

Strong Strict Two-Phase Locking

2PL can result in cascading rollbacks.

SS2PL allows only conflict serializable schedules.

Strong Strict Two-Phase Locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

• Each transaction must obtain an exclusive lock on an object before writing.

• If a transaction holds an exclusive lock on an object, no other transaction can obtain any lock on that object.

• All locks held by a transaction are released when the transaction completes.

This approach avoids the problem with cascading rollbacks.

Strong Strict Two-Phase Locking

2PL can result in cascading rollbacks.

SS2PL allows only conflict serializable schedules.

Strong Strict Two-Phase Locking Protocol

• Each transaction must obtain a shared lock on an object before reading.

• Each transaction must obtain an exclusive lock on an object before writing.

• If a transaction holds an exclusive lock on an object, no other transaction can obtain any lock on that object.

• All locks held by a transaction are released when the transaction completes.

This approach avoids the problem with cascading rollbacks.

What is the downside to SS2PL?

Concurrency Control in Rails

Concurrency Control in Rails

Rails uses two types of concurrency control:

Optimistic Locking

Assume that database modifications are going to succeed. Throw an exception when they do not.

Concurrency Control in Rails

Rails uses two types of concurrency control:

Optimistic Locking

Assume that database modifications are going to succeed. Throw an exception when they do not.

Pessimistic Locking

Ensure that database modifications will succeed by explicitly avoiding conflict.

Optimistic Locking in Rails

Any ActiveRecord model will automatically utilize optimistic locking if an integer lock_version field is added to the object's table.

Whenever such an ActiveRecord object is read from the database, that object contains its associated lock_version.

When an update for the object occurs, Rails compares the object's lock_version to the most recent one in the database.

Optimistic Locking in Rails

Any ActiveRecord model will automatically utilize optimistic locking if an integer lock_version field is added to the object's table.

Whenever such an ActiveRecord object is read from the database, that object contains its associated lock_version.

When an update for the object occurs, Rails compares the object's lock_version to the most recent one in the database.

If they differ a StaleObjectException is thrown.

Otherwise, the data is written to the database, and the lock_version value is incremented.

Optimistic Locking in Rails

Any ActiveRecord model will automatically utilize optimistic locking if an integer lock_version field is added to the object's table.

Whenever such an ActiveRecord object is read from the database, that object contains its associated lock_version.

When an update for the object occurs, Rails compares the object's lock_version to the most recent one in the database.

If they differ a StaleObjectException is thrown.

Otherwise, the data is written to the database, and the lock_version value is incremented.

This optimistic locking is an application-level construct. The database does nothing more than storing the lock_version values.

https://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html

Optimistic Locking Example

c1 = Person.find(1)
c1.name = "X Æ A-12"
c2 = Person.find(1)
c2.gender = "X-Ash-A-12"
c1.save!  # Succeeds
c2.save!  # throws StaleObjectException

rails g migration AddLockVersionToPeople lock_version:integer

Optimistic Locking Example

c1 = Person.find(1)
c1.name = "X Æ A-12"
c2 = Person.find(1)
c2.gender = "X-Ash-A-12"
c1.save!  # Succeeds
c2.save!  # throws StaleObjectException

rails g migration AddLockVersionToPeople lock_version:integer

Strengths

• Predictable performance
• Lightweight

Optimistic Locking Example

c1 = Person.find(1)
c1.name = "X Æ A-12"
c2 = Person.find(1)
c2.gender = "X-Ash-A-12"
c1.save!  # Succeeds
c2.save!  # throws StaleObjectException

rails g migration AddLockVersionToPeople lock_version:integer

Strengths

• Predictable performance
• Lightweight

Weaknesses

• Have to write error handling code
• (or) errors will propagate to your users

Pessimistic Locking in Rails

Easily done by calling lock along with ActiveRecord find.

Whenever an ActiveRecord object is read from the database with that option an exclusive lock is acquired for the object.

While this lock is held, the database prevents others from obtaining the lock, reading from, and writing to the object. The others are blocked until the object is unlocked.

Implemented using the SELECT FOR UPDATE SQL.

https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html

Pessimistic Locking Example

Request 1

transaction do
  person = Person.lock.find(1)
  person.name = "X Æ A-12"
  person.save!  # works fine
end

Request 2

transaction do
  person = Person.lock.find(1)
  person.name = "X-Ash-A-12"
  person.save!  # works fine
end

This approach works great, yet it is not commonly used.

What could go wrong?

Pessimistic Locking Issue: Blocking

transaction do
  person = Person.lock.find(1)
  person.name = "X Æ A-12"
  my_long_procedure
  person.save!
end

Request 2

transaction do
  person = Person.lock.find(1)
  person.name = "X-Ash-A-12"
  my_long_procedure
  person.save!
end

Pessimistic Locking Issue: Deadlock

transaction do
  family = Family.lock.find(3)
  family.count += 1
  family.save!
  person = Person.lock.find(1)
  person.name = "X Æ A-12"
  person.save!
end

Request 2

transaction do
  person = Person.lock.find(1)
  person.name = "X-Ash-A-12"
  person.save!
  family = Family.lock.find(3)
  family.count += 1
  family.save!
end

Pessimistic Locking: Summary

Strengths

• Failed transactions are incredibly rare or nonexistent

Pessimistic Locking: Summary

Strengths

• Failed transactions are incredibly rare or nonexistent

Weaknesses

• Have to worry about deadlocks and avoiding them

• Performance is less predictable

Next Step: Finding DB Bottlenecks

We now have a Rails app connected to PostgreSQL and it is slower than you'd like.

You think the bottleneck may be the database.

How do we find out?

Demo App Database Optimizations

Demo App ActiveRecord Time

Demo App Investigation

First Step: Find out what Rails is doing.

In development mode, Rails will output the SQL it generates and executes to the application server log.

To (temporarily) enable debugging in production mode, change config/environments/production.rb to contain:

config.log_level = :debug

Submission Index Controller and View

Controller

class SubmissionsController < ApplicationController
  def index
    @submissions = Submission.all
  end
end

View

<% @submissions.each do |submission| %>
  <tr>
    <td><%= link_to(submission.title, submission.url) %></td>
    <td><%= submission.url %></td>
    <td><%= submission.community.name %></td>
    <td>
      <%= link_to("#{submission.comments.size} comments",
                  submission, class: 'btn btn-primary btn-xs') %>
    </td>
  </tr>
<% end %>

Generated SQL Statements

Processing by SubmissionsController#index as HTML
Submission Load (0.5ms)  SELECT `submissions`.* FROM `submissions`
Community Load (0.3ms)  SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 1
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1  [["id", 1]]
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 2
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1  [["id", 1]]
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 3
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1  [["id", 1]]
...
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 20 LIMIT 1  [["id", 20]]
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 400

Generated SQL Statements

Processing by SubmissionsController#index as HTML
Submission Load (0.5ms)  SELECT `submissions`.* FROM `submissions`
Community Load (0.3ms)  SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 1
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1  [["id", 1]]
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 2
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1  [["id", 1]]
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 3
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 1 LIMIT 1  [["id", 1]]
...
SELECT  `communities`.* FROM `communities` WHERE `communities`.`id` = 20 LIMIT 1  [["id", 20]]
SELECT COUNT(*) FROM `comments` WHERE `comments`.`submission_id` = 400

That is a lot of SELECT queries!

Number of Queries Bottleneck

We are issuing a ton of SELECT queries. The overhead associated with each is slowing us down.

How can we fix this problem?

Number of Queries Bottleneck

We are issuing a ton of SELECT queries. The overhead associated with each is slowing us down.

How can we fix this problem?

Issue fewer queries.

• Do not ask for the community each time

• Do not ask for the number of comments each time

Reducing (N+1) Queries in Rails

(Before) Without includes

class SubmissionsController < ApplicationController
  def index
    @submissions = Submission.all
  end
end

Reducing (N+1) Queries in Rails

(Before) Without includes

class SubmissionsController < ApplicationController
  def index
    @submissions = Submission.all
  end
end

(After) With includes

class SubmissionsController < ApplicationController
  def index
    @submissions = Submission.includes(:comments)
                             .includes(:community).all
  end
end

Reducing (N+1) Queries in Rails

(Before) Without includes

class SubmissionsController < ApplicationController
  def index
    @submissions = Submission.all
  end
end

(After) With includes

class SubmissionsController < ApplicationController
  def index
    @submissions = Submission.includes(:comments)
                             .includes(:community).all
  end
end

Result

Result: ActiveRecord 39.6ms
Submission Load (0.9ms)  SELECT `submissions`.* FROM `submissions`
Comment Load (38.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`submission_id` IN (1, 2,... 399, 400)
Community Load (0.4ms)  SELECT `communities`.* FROM `communities` WHERE `communities`.`id` IN (1, 2, ...19, 20)

SQL Explain

Sometimes things are still slow even when the number of queries is minimized.

SQL provides an EXPLAIN statement that can be used to analyze individual queries.

When a query starts with EXPLAIN...

• the query is not actually executed

• the produced output will help us identify potential improvements

• e.g. sequential vs index scan, startup vs total cost

https://www.postgresql.org/docs/9.1/sql-explain.html

SQL Optimizations

There are three primary ways to optimize SQL queries:

SQL Optimizations

There are three primary ways to optimize SQL queries:

• Add or modify indexes

SQL Optimizations

There are three primary ways to optimize SQL queries:

• Add or modify indexes

• Modify the table structure

SQL Optimizations

There are three primary ways to optimize SQL queries:

• Add or modify indexes

• Modify the table structure

• Directly optimize the query

SQL Indexes

What is an index?

SQL Indexes

What is an index?

An index is a fast, ordered, compact structure (often B-tree) for identifying row locations.

SQL Indexes

What is an index?

An index is a fast, ordered, compact structure (often B-tree) for identifying row locations.

When an index is provided on a column that is to be filtered (searching for a particular item), the database is able to quickly find that information.

SQL Indexes

What is an index?

An index is a fast, ordered, compact structure (often B-tree) for identifying row locations.

When an index is provided on a column that is to be filtered (searching for a particular item), the database is able to quickly find that information.

Indexes can exist on a single column, or across multiple columns. Multi-column indexes are useful when filtering on two columns (e.g., CS classes that are not full).

Adding Indexes in Rails

To add an index on the name field of the Product table, create a migration containing:

class AddNameIndexProducts < ActiveRecord::Migration
  def change
    add_index :products, :name
  end
end

Related: Foreign Keys

By default, when dealing with relationships between ActiveRecord objects, Rails will validate the constraints in the application layer.

For example, an Employee object should have a Company that they work for.

Assuming the relationship is defined properly, Rails will enforce that when creating an Employee, the associated Company exists.

Many databases, have built-in support for enforcing such constraints. With rails, one can also take advantage of the database's foreign key support via add_foreign_key.

class AddForeignKeyToOrders < ActiveRecord::Migration
  def change
    add_foreign_key :employees, :companies
  end
end

Optimize the Table Structure

Indexes work best when they can be kept in memory.

Sometimes changing the field type, or index length can provide significant memory savings.

If appropriate some options are:

• Reduce the length of a VARCHAR index if appropriate

• Use a smaller unsigned integer type

• Use an integer or enum field for statuses rather than a text-based value

Directly Optimize the Query

Before

explain select count(*) from txns where parent_id - 1600 = 16340;
select_type: SIMPLE
table: txns
type: index
key: index_txns_on_reverse_txn_id
rows: 439186
Extra: Using where; Using index

Directly Optimize the Query

Before

explain select count(*) from txns where parent_id - 1600 = 16340;
select_type: SIMPLE
table: txns
type: index
key: index_txns_on_reverse_txn_id
rows: 439186
Extra: Using where; Using index

After

explain select count(*) from txns where parent_id = 16340 + 1600
select_type: SIMPLE
table: txns
type: const
key: index_txns_on_reverse_txn_id
rows: 1
Extra: Using index

Summary

• understand the importance of relational databases in architecting scalable Internet systems

• understand how to design with concurrency in mind

• have a basic idea regarding how to speed up the data layer

  • determine where to start

  • understand which rails-specific techniques to use

  • identify the source of slowness in an SQL query