The Problem The Tests Breakthroughs
Scaling MySQL writes through partitioning Philip Tellis /
[email protected]
ConFoo / 2010-03-10
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
$ finger philip
Philip Tellis
[email protected] bluesmoon.info @bluesmoon yahoo geek
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure Performance
Why not use a data warehouse?
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Our data DB infrastructure Performance
I like to get the most out of my hardware
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure 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 The Tests Breakthroughs
Our data DB infrastructure Performance
Test 1
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy Insights
Test 2 - Drop the secondary index
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy Insights
Test 3 - innodb_buffer_pool_size=1000
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy 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 The Tests Breakthroughs
Basic tests Going crazy 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 The Tests Breakthroughs
Basic tests Going crazy Insights
Test 4 - innodb_flush_log_at_trx_commit=2
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy Insights
Test 5 - innodb_max_dirty_pages_pct=60
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy Insights
Test 6 - Let’s try MyISAM
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy Insights
Test 7 - Inserts in a transaction
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Basic tests Going crazy 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 The Tests Breakthroughs
Basic tests Going crazy 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 The Tests Breakthroughs
Basic tests Going crazy 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 The Tests Breakthroughs
Bulk inserts Partitioning Long running test
Test 8 - Single bulk insert
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Bulk inserts Partitioning 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 The Tests Breakthroughs
Bulk inserts Partitioning Long running test
Test 9 - bulk inserts + partitioning
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Bulk inserts Partitioning 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 The Tests Breakthroughs
Bulk inserts Partitioning 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 The Tests Breakthroughs
Bulk inserts Partitioning Long running test
Test 10 - Ran for 7 days
ConFoo / 2010-03-10
Scaling MySQL writes through partitioning
The Problem The Tests Breakthroughs
Bulk inserts Partitioning 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
[email protected] 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