I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row
insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.
OK, let’s start off with the configuration details.
Configuration
First of all let me describe the EC2 instance type that I used.
EC2 Configuration
I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.
High-Memory Quadruple Extra Large Instance
68.4 GB of memory
26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute Units each)
1690 GB of instance storage
64-bit platform
I/O Performance: High
API name: m2.4xlarge
As for the IO configuration I chose 8 x 200G EBS volumes in software RAID 10.
Now let’s come to the MySQL configuration.
MySQL Configuration
I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used:
## InnoDB options
innodb_buffer_pool_size = 55G
innodb_log_file_size = 1G
innodb_log_files_in_group = 4
innodb_buffer_pool_instances = 4
innodb_adaptive_flushing = 1
innodb_adaptive_flushing_method = estimate
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_max_dirty_pages_pct = 50
innodb_io_capacity = 800
innodb_read_io_threads = 8
innodb_write_io_threads = 4
innodb_file_per_table = 1
## Disabling query cache
query_cache_size = 0
query_cache_type = 0
You can see that the buffer pool is sized at 55G and I am using 4 buffer pool instances to reduce the contention caused by buffer pool mutexes. Another important configuration that I am using is that I am using “estimate” flushing method available only on Percona Server. The “estimate” method reduces
the impact of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload.
OK, so that was all about the configuration of the EC2 instance and MySQL.
Now as far as the benchmark itself is concerned, I made no code changes to iiBench, and used the version available here. But I changed the table to use range partitioning. I defined a partitioning scheme such that every partition would hold 100 million rows.
Table Structure
The table structure of the table with no secondary indexes is as follows:
CREATE TABLE `purchases_noindex` (
`transactionid` int(11) NOT NULL AUTO_INCREMENT,
`dateandtime` datetime DEFAULT NULL,
`cashregisterid` int(11) NOT NULL,
`customerid` int(11) NOT NULL,
`productid` int(11) NOT NULL,
`price` float NOT NULL,
PRIMARY KEY (`transactionid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (transactionid)
(PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (200000000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (300000000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (400000000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (500000000) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (600000000) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (700000000) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (800000000) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (900000000) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
While the structure of the table with secondary indexes is as follows:
CREATE TABLE `purchases_index` (
`transactionid` int(11) NOT NULL AUTO_INCREMENT,
`dateandtime` datetime DEFAULT NULL,
`cashregisterid` int(11) NOT NULL,
`customerid` int(11) NOT NULL,
`productid` int(11) NOT NULL,
`price` float NOT NULL,
PRIMARY KEY (`transactionid`),
KEY `marketsegment` (`price`,`customerid`),
KEY `registersegment` (`cashregisterid`,`price`,`customerid`),
KEY `pdc` (`price`,`dateandtime`,`customerid`)
) ENGINE=InnoDB AUTO_INCREMENT=11073789 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (transactionid)
(PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (200000000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (300000000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (400000000) ENGINE = InnoDB,
PARTITION p4 V
Truncated by Planet PHP, read more at the original (another 3858 bytes)
