queues, pools, caches

52
Queues, Pools, and Caches Presented by: Gwen shapira, Senior consultant

Upload: chen-gwen-shapira

Post on 26-Jan-2015

114 views

Category:

Technology


1 download

DESCRIPTION

Transaction processing systems are generally considered easier to scale than data warehouses. Relational databases were designed for this type of workload, and there are no esoteric hardware requirements. Mostly, it is just matter of normalizing to the right degree and getting the indexes right. The major challenge in these systems is their extreme concurrency, which means that small temporary slowdowns can escalate to major issues very quickly.In this presentation, Gwen Shapira will explain how application developers and DBAs can work together to built a scalable and stable OLTP system - using application queues, connection pools and strategic use of caches in different layers of the system.

TRANSCRIPT

Page 1: Queues, Pools, Caches

Queues, Pools, and CachesPresented by: Gwen shapira, Senior consultant

Page 2: Queues, Pools, Caches

© 2009/2010 Pythian 2

About Myself

• 13 years with a pager• Oracle ACE• Oak table member• Senior consultant for Pythian• @gwenshap• http://www.pythian.com/news/author/

shapira/• [email protected]

Page 3: Queues, Pools, Caches

© 2009/2010 Pythian 3

Recognized Leader:

• Global industry-leader in remote database administration services and consulting for Oracle, Oracle Applications, MySQL and SQL Server

• Work with over 150 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments

Expertise:

• One of the world’s largest concentrations of dedicated, full-time DBA expertise. Employ 7 Oracle ACEs/ACE Directors

• Hold 7 Specializations under Oracle Platinum Partner program, including Oracle Exadata, Oracle GoldenGate & Oracle RAC

Global Reach & Scalability:

• 24/7/365 global remote support for DBA and consulting, systems administration, special projects or emergency response

Why Pythian

Page 4: Queues, Pools, Caches

© 2009/2010 Pythian

Page 5: Queues, Pools, Caches

© 2009/2010 Pythian 5

WHY?

Page 6: Queues, Pools, Caches

© 2009/2010 Pythian 6

OLTP:High ConcurrencyLow LatencyLow Variance

Page 7: Queues, Pools, Caches

© 2009/2010 Pythian 7

Page 8: Queues, Pools, Caches

© 2009/2010 Pythian 8

Our mission:

Use modern application design to control database concurrency to its maximum throughput, lower latency and make the system more predictable.

Page 9: Queues, Pools, Caches

© 2009/2010 Pythian 9

Nobody expects modern application design!

Page 10: Queues, Pools, Caches

© 2009/2010 Pythian 10

Our Chief weapons are:Connection PoolsQueuesCaches

And fanatical monitoringAnd ruthless capacity planningAnd nice red uniforms!

Page 11: Queues, Pools, Caches

Connection Pools

11

Page 12: Queues, Pools, Caches

© 2009/2010 Pythian

The Problem

Opening a database connection is high latency operation.

OLTP systems can't afford this latency for every user request

Page 13: Queues, Pools, Caches

© 2009/2010 Pythian

The Solution

Page 14: Queues, Pools, Caches

© 2009/2010 Pythian 14

Application Business Layer

Application Data Layer

DataSource

JNDI

JDBC Driver

Connection Pool

DataSource Interface

Page 15: Queues, Pools, Caches

© 2009/2010 Pythian 15

New Problems

Slow Resp. Time

Run out of connectio

ns

Add More!

CPU + Latch

contention

Page 16: Queues, Pools, Caches

© 2009/2010 Pythian 16

5000 connectionsin pool…

But only 12 cores?

?

Page 17: Queues, Pools, Caches

© 2009/2010 Pythian 17

Page 18: Queues, Pools, Caches

© 2009/2010 Pythian 18

Page 19: Queues, Pools, Caches

© 2009/2010 Pythian 19

And that’s not all…

How long does it take to open 5000 connections to the database?

Page 20: Queues, Pools, Caches

© 2009/2010 Pythian 20

New Solutions

1.Load test 2.Limit connection pool3.????4.Low latency!!!

Page 21: Queues, Pools, Caches

© 2009/2010 Pythian

Objection!

1. But I don’t use all connections at once

2. Our connection pool grows and shrinks automatically

Page 22: Queues, Pools, Caches

© 2009/2010 Pythian 22

"Dynamic connection pools are a loaded gun pointed at your system. Feeling lucky, punk?"

Graham Wood, Oracle

Page 23: Queues, Pools, Caches

© 2009/2010 Pythian

Objection!

Problem: We can’t load test

Solution: Guesstimate1. How many cores are available?2. How much time is spent squatting a

connection without running database queries?

3. How much workload is IO-bound?

Page 24: Queues, Pools, Caches

© 2009/2010 Pythian

Objection!

Problem: We have 5000 application servers

Solution: Data Layer1. Separate servers running data layer2. Fewer servers3. Load balance based on capacity

Page 25: Queues, Pools, Caches

Queues

25

Page 26: Queues, Pools, Caches

© 2009/2010 Pythian

The Problem

We have more user requests than database connections

Page 27: Queues, Pools, Caches

© 2009/2010 Pythian 27

What do we do?1. “Your call is important to us…”

2. Show them static content

3. Distract them with funny cat photos

4. Prioritize them

5. Acknowledge them and handle the request later

6. Jump them to the head of line

7. Tell them how long the wait is

Page 28: Queues, Pools, Caches

© 2009/2010 Pythian 28

Solution – Message QueueMsg 1

Msg 2

Msg 3

.

.

.

Msg N

Page 29: Queues, Pools, Caches

© 2009/2010 Pythian 29

Application Business Layer

Application Data Layer

DataSource

JNDI

JDBC DriverConnection

Pool

DataSource Interface

Message

Queue

Page 30: Queues, Pools, Caches

© 2009/2010 Pythian 30

New ProblemsStuff developers say about message queues:

1. It is impossible to reliably monitor queues2. Queues are not necessary if you do proper

capacity planning3. Message queues are unnecessarily

complicated.

Page 31: Queues, Pools, Caches

© 2009/2010 Pythian 31

Page 32: Queues, Pools, Caches

© 2009/2010 Pythian 32

Do Monitor:

1. Service time2. Arrival rate3. Queue size4. Utilization

Page 33: Queues, Pools, Caches

© 2009/2010 Pythian 33

Capacity Planning

Myth: With proper capacity planning, queues are not necessary

Fact: Over-provisioning is not proper capacity planningFact: Queue theory is capacity planning tool.Fact: Introduction of a few well defined and well understood queues will help capacity planning.

Page 34: Queues, Pools, Caches

© 2009/2010 Pythian 34

Complex Systems

1. Queues are simple2. Enterprise message

queues are complex3. Match solution to

problem requirements

Page 35: Queues, Pools, Caches

Caches

35

Page 36: Queues, Pools, Caches

© 2009/2010 Pythian

The Problem

OLTP scales best when working set is cached in RAM

RDBMS have limited memory scalability

Page 37: Queues, Pools, Caches

© 2009/2010 Pythian 37

The Solution - Memcached

App

Memcached

App App App

App App

Memcached

Memcached

Memcached

Page 38: Queues, Pools, Caches

© 2009/2010 Pythian 38

How is it awesome?1. Less DB access2. Less disk access3. Distributed4. Simple KV store5. “Free” memory6. Latency and availability resilience

Page 39: Queues, Pools, Caches

© 2009/2010 Pythian 39

Amazon ElastiCacheMemcached cluster of any size in Amazon cloud

Unfortunately only accessible from EC2

9 cents per node per hour!

Page 40: Queues, Pools, Caches

© 2009/2010 Pythian 40

Linear Scalability?

Page 41: Queues, Pools, Caches

© 2009/2010 Pythian 41

More Numbers1. 0.007ms latency on my desktop2. 2ms latency on cloud3. 60K gets a second4. All from the smallest possible servers at

38 cents per hour.

Page 42: Queues, Pools, Caches

© 2009/2010 Pythian 42

Application Business Layer

Application Data Layer

DataSource

JNDI

JDBC Driver

Connection Pool

DataSource Interface

Message

QueueMemcache

d

Page 43: Queues, Pools, Caches

© 2009/2010 Pythian 43

New Problems• Does not apply automatically• How to use it effectively?• How to monitor it?• How big?

Page 44: Queues, Pools, Caches

© 2009/2010 Pythian 44

Use Case - Selectfunction get_username(int userid) {

/* first try the cache */

name = memcached_fetch("username:" + userid);

if (!name) {

/* not found : request database */

name = db_select("SELECT username FROM users WHERE userid = ?", userid);

/* then store in cache until next get */

memcached_add("username:" + userid, username);

}

return data;

}

Page 45: Queues, Pools, Caches

© 2009/2010 Pythian 45

Use Case - Updatefunction update_username(int userid, string username) {

/* first update database */

result = db_execute("Update users set username=? WHERE userid=?", userid,username);

if (result) {

/* database update successful: update cache */

memcached_set("username:" + userid, username);

}

Page 46: Queues, Pools, Caches

© 2009/2010 Pythian 46

Usage Advice1. Use the ASH2. More memory, fewer cores3. DB is for durable writes4. Warm-up the cache5. Store nulls6. Updates are tricky7. Backward compatible schema

Page 47: Queues, Pools, Caches

© 2009/2010 Pythian 47

How Big?

Cluster: As big as you canNode: Not too big to fail.

Page 48: Queues, Pools, Caches

© 2009/2010 Pythian 48

What will we gain by adding 1G cache?

1. You can’t calculate

2. Log all cache hits and misses, by key

3. Or sample4. Run cache

simulator5. Predict avg.

latency

Page 49: Queues, Pools, Caches

© 2009/2010 Pythian 49

1ms * 0.95 + 5ms * 0.05= 1.2ms

Page 50: Queues, Pools, Caches

© 2009/2010 Pythian 50

Monitor1. Number of items, gets,

sets and misses2. Number of evictions and

eviction time.3. Low hit rate and high

eviction rate?4. Swapping5. Average response time6. Number of connections

Page 51: Queues, Pools, Caches

© 2009/2010 Pythian 51

Reminder:

1.Use Connection Pools

2.Limit the number of connections

3.Use queues to handle the excessive load

4.Use caches to make everything faster

Page 52: Queues, Pools, Caches

© 2009/2010 Pythian

Thank you and Q&A

52

http://www.pythian.com/news/

http://www.facebook.com/pages/The-Pythian-Group/

http://twitter.com/pythian

http://www.linkedin.com/company/pythian

1-866-PYTHIAN

[email protected]

To contact us…

To follow us…