高容量数据库性能测试-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倍性能优势)。