The Problem

The Tests
Breakthroughs

Scaling MySQL writes through partitioning

Philip Tellis / philip@bluesmoon.info

ConFoo / 2010-03-10

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem
The Tests
Breakthroughs

$ finger philip

Philip Tellis
philip@bluesmoon.info
bluesmoon.info
@bluesmoon
yahoo
geek

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Web requests

Millions of beacons from a web page

No response required
Can be batch processed
Very small amounts of data loss is acceptable

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Large volume

2000 requests/second on most days

up to 8000 requests/second on some days
200MM requests/day
Some data is fake or abusive

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Access patterns

Lots of writes throughout the day

One huge read at the end of the day
Summarise data and throw out the details
Many reads of summary data over several months

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Why not use a data warehouse?

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

I like to get the most out of my hardware

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Hardware setup

MySQL 5.1
Multi-master replication in two colos, 1 remote slave per
master
Only one master writable at any point of time
4GB RAM (later 16GB), Big disk with RAID 10

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

DB config

innodb_buffer_pool_size=2078M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_max_dirty_pages_pct=90
innodb_doublewrite=1, innodb_support_xa=1
sync_binlog=0
key_buffer_size=32M, myisam_sort_buffer_size=512k
transaction_isolation=REPEATABLE-READ

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Data setup

Each row 120bytes

+ InnoDB overhead
innodb_file_per_table so we can see how the table grows
No Autoincrement fields
PRIMARY KEY derived from data + one other index

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Test requirements

Insert records until the system breaks down

Find out why it broke down
Find out how to make it not break down
Find out how fast we can insert records (must be >2000 i/s)

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Test requirements

Insert records until the system breaks down

Find out why it broke down
Find out how to make it not break down
Find out how fast we can insert records (must be >2000 i/s)

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Test requirements

Insert records until the system breaks down

Find out why it broke down
Find out how to make it not break down
Find out how fast we can insert records (must be >2000 i/s)

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Test requirements

Insert records until the system breaks down

Find out why it broke down
Find out how to make it not break down
Find out how fast we can insert records (must be >2000 i/s)

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

How I tested

Insertion script measured insertion speed v/s number of

records
Number of records roughly translates to table size
On DB box we measure disk performance and table size

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Our data
The Tests DB infrastructure
Breakthroughs Performance

Test 1

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Test 2 - Drop the secondary index

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Test 3 - innodb_buffer_pool_size=1000

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Realisation

Max table size directly proportional to

innodb_buffer_pool_size
Extra index reduces insertion rate
Extra index reduces max table size
Possible solution: increase RAM and
innodb_buffer_pool_size
But this only postpones the problem

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Realisation

Max table size directly proportional to

innodb_buffer_pool_size
Extra index reduces insertion rate
Extra index reduces max table size
Possible solution: increase RAM and
innodb_buffer_pool_size
But this only postpones the problem

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Test 4 - innodb_flush_log_at_trx_commit=2

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Test 5 - innodb_max_dirty_pages_pct=60

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Test 6 - Let’s try MyISAM

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Test 7 - Inserts in a transaction

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Other stuff we tried

innodb_doublewrite=0 - no effect
Server side prepared statements - no effect
transaction_isolation=READ-COMMITTED - no effect
innodb_support_xa=0 - 12% increase in insertion rate
Combination of the best options - negligible effect

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

What we knew at this point

Sticking with InnoDB

We need a large buffer pool
We need to drop extra indices
flush_log_at_trx_commit=2 is good enough
Transactions are good

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Basic tests
The Tests Going crazy
Breakthroughs Insights

Our big problem

Insert rate was barely reaching the rate of incoming data!

Still breaks down before getting a day’s worth of data

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

Test 8 - Single bulk insert

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

Bulk insert specifications

40,000 records in one insert statement

Use INSERT IGNORE
4-6 seconds per statement
PRIMARY KEY drops duplicates
We still have a breakdown when we cross the buffer pool

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

Test 9 - bulk inserts + partitioning

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

What happened?

Split the table into partitions

Each partition < 0.5 x innodb_buffer_pool_size
current and next partition fit in memory at any time
Partition key is based on incoming data and not on
SELECTs

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

Schema

CREATE TABLE (
...
) PARTITION BY RANGE( ( time DIV 3600 ) MOD 24 ) (
Partition p0 values less than (2),
Partition p1 values less than (4),
...
Partition p10 values less than (22),
Partition p11 values less than (24)
);

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

Test 10 - Ran for 7 days

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem Bulk inserts
The Tests Partitioning
Breakthroughs Long running test

Still running

Terabytes of data
around 8500 inserts per second
Potentially 700+ MM inserts per day

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem
The Tests
Breakthroughs

Summary

Bulk inserts push up your insert rate

Partitioning lets you insert more records
Partition based on incoming data key for fast inserts
http://tech.bluesmoon.info/2009/09/scaling-writes-in-mysql.html

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem
The Tests
Breakthroughs

Thanks, Merci

ConFoo organisers
Exceptional Performance team @ Yahoo!
Monitoring team @ Yahoo!
MySQL Geeks at Yahoo!

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem
The Tests
Breakthroughs

contact me

Philip Tellis
philip@bluesmoon.info
bluesmoon.info
@bluesmoon
yahoo
geek

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning

 The Problem
The Tests
Breakthroughs

Photo credits

Disused warehouse on Huddersfield Broad Canal / by TDR1

http://www.flickr.com/photos/tdr1/3578203727/
Hardware store dog / by sstrudeau
http://www.flickr.com/photos/sstrudeau/330379020/
North Dakota, Broken Down Van / by mattdente
http://www.flickr.com/photos/mattdente/46944898/
One red tree / by EssjayNZ
http://www.flickr.com/photos/essjay/155223631/
The Leaning Tree / by stage88
http://www.flickr.com/photos/stage88/3179612722/

ConFoo / 2010-03-10 Scaling MySQL writes through partitioning