高容量数据库性能测试-mysql

高容量数据库性能测试
耿红杰2010-10-25 测试环境说明
OS ：CentOS 5.5 X86
MySQL：5.1.50 ，ha_innodb_plugin
CPU：Intel(R) Xeon(R) E5504 @ 2.00GHz
MEM：1G （1G swap）
Disk：20G
f
innodb_thread_concurrency=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=384M
default-table-type=InnoDB
init_connect='SET autocommit=0'
binlog_format=MIXED
log-bin=/disk2/mysql/binlog/using
query_cache_size=128M
测试目的
1.myisam和innodb引擎对于性能的影响，采用2000w的数据进行写入和查询测试
2.200000000数据的查询性能测试
3.myisam 引擎的分区功能
测试步骤1
1.create table
CREATE TABLE `innodb` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`adress` varchar(45) DEFAULT NULL,
`markert` varchar(45) DEFAULT NULL,
`tel` varchar(45) DEFAULT NULL,
`base` varchar(45) DEFAULT NULL,
`log` varchar(45) DEFAULT NULL,
`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.load data into table
while i < 20000001 do
INSERT INTO `innodb`
(`name`, `adress`, `markert`, `tel`, `base`, `log`)
VALUES (
'play10100101010101010101010101',
'abcdefghijklmnopqrstuvwxyz01234567890',
'abcdefghijklmnopqrstuvwxyz01234567890',
'0086-27-87654321-99888',
'abcdefghijklmnopqrstuvwxyz01234567890',
'abcdefghijklmnopqrstuvwxyz01234567890'
);
set i = i+1;
end while;
3.query data from table
Q1: select count(*) from myisam;
Q2: select min(time) from myisam;
Q3: select max(time) from myisam;
Q4: select count(*) from myisam where time = '2010-10-21 14:20:51';
Q5: select count(*) from innodb where id between 10010000 and 10020000;
测试步骤2
1.MyISAM分区表
CREATE TABLE `play_sum_p` (
`play_sum_id` int(10) unsigned NOT NULL ,
`play_name` varchar(45) DEFAULT NULL,
`play_date` date DEFAULT NULL,
`player_id` int(11) NOT NULL DEFAULT '0',
`content_id` int(11) NOT NULL DEFAULT '0',
`play_completed` int(11) DEFAULT NULL,
`play_aborted` int(11) DEFAULT NULL,
`play_unknown` int(11) DEFAULT NULL,
KEY `player_id_idx` (`player_id`,`content_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (day(play_date))
( PARTITION p1 V ALUES LESS THAN (11) DATA DIRECTORY= '/disk2/mysql/p01' INDEX DIRECTORY =
'/disk2/mysql/p01',
PARTITION p2 V ALUES LESS THAN (21) DATA DIRECTORY= '/disk2/mysql/p02' INDEX DIRECTORY =
'/disk2/mysql/p02',
PARTITION p3 V ALUES LESS THAN (32) DATA DIRECTORY= '/disk2/mysql/p03' INDEX DIRECTORY =
'/disk2/mysql/p03'
);
测试结论
1.对于playlog这样大数据量的日志数据，“一次写入，几次查询，零次更新，无事务支持”的特性，
选择MyISAM是比较好的选择。

测试结果说明，MyISAM在性能上明显优于InnoDB(2倍性能优势)。

2.对于大数据量的数据，数据维护也会“与众不同”，对于MyISAM表采用分区表（partition）可以
简化数据的导出和迁移（add|drop partition），简化数据的备份和恢复。

3.是否采用分区表主要是基于管理的需要，在存储设备没有物理分离的情况下，对性能并无明显影
响。

4.对于1G内存的系统，系统的iowait较高，采用2G内存和分区功能，系统的iowait有显著的降低
（iowait：70% 6%），该数据可能有其他系统的扰动。

5.对于数据的查询，索引设计是关键。

不能使用索引的查询性能与数据量关系很大，2GB的数据量
扫描约60秒。

根据项目设计，600GB（一个月的明细数据）的数据扫描可能需要5小时。

对于索引，同样看索引的数据量大小。

对于高性能的IO设备，扫描性能会高一些（这个根据不同的硬件设备可能有不同）。

6.在硬盘容量超过一半时，写性能就会明显下降，最大达到40%
7.基于测试环境的结果进行推算，生产系统在8*CPU 16GB Memory 的情况下；10000 display的数
据处理可以控制在1小时之内。

建议
1.根据业务需求，需要事务支持，所以选择innodb引擎。

2.playsum可以不设计主键（Primary Key），可以设计1-2个索引来加速查询性能。

3.playlog可以不设计主键，基于管理和维护的要求，单独设计一个database来存储playlog（灵活备
份）。

4.对于日期字段，若不参加运算可以用varchar，例如reportdate
5.根据业务需求，每天的数据量约为90,000,000条数据，容量约20G；以此推算一个月的数据量约
2,700,000,000条数据，600G（缺省的mysql支持的记录数为2^32，可以采用--with-big-tables 编译为(2^32)^2）。

6.mysql可能会采取Master-slave replication模式来部署，master作写入，slave仅作查询；该模式同
时可以提供mysql数据库的高可用。

7.所有的性能和结构设计基于整个项目的需求。