高容量數(shù)據(jù)庫(kù)性能測(cè)試-mysql

1、高容量數(shù)據(jù)庫(kù)性能測(cè)試耿紅杰 2010-10-25測(cè)試環(huán)境說明OS ： CentOS 5.5 X86MySQL： 5.1.50 ， ha_innodb_pluginCPU：Intel(R) Xeon(R) E5504 2.00GHzMEM：1G （1G swap）Disk：20GMfinnodb_thread_concurrency=2innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=384Mdefault-table-type=InnoDBinit_connect=SET autocommit=0binlog_format=MIXE

2、Dlog-bin=/disk2/mysql/binlog/usingquery_cache_size=128M測(cè)試目的1. myisam和innodb引擎對(duì)于性能的影響，采用2000w的數(shù)據(jù)進(jìn)行寫入和查詢測(cè)試2. 200000000數(shù)據(jù)的查詢性能測(cè)試3. myisam 引擎的分區(qū)功能測(cè)試步驟11. create tableCREATE TABLE innodb (id int(11) unsigned NOT NULL AUTO_INCREMENT,name varchar(45) DEFAULT NULL,adress varchar(45) DEFAULT NULL,markert var

3、char(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 tablewhile i 20000001 doINSERT INTO innodb(na

4、me, 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

5、tableQ1: 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;測(cè)試步驟21. MyISAM分區(qū)表CREATE TABLE play_sum_p (play_sum_id int(10)

6、 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,con

7、tent_id) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION BY RANGE (day(play_date)( PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY= /disk2/mysql/p01 INDEX DIRECTORY = /disk2/mysql/p01,PARTITION p2 VALUES LESS THAN (21) DATA DIRECTORY= /disk2/mysql/p02 INDEX DIRECTORY = /disk2/mysql/p02,

8、PARTITION p3 VALUES LESS THAN (32) DATA DIRECTORY= /disk2/mysql/p03 INDEX DIRECTORY = /disk2/mysql/p03 );測(cè)試結(jié)論1. 對(duì)于playlog這樣大數(shù)據(jù)量的日志數(shù)據(jù)，“一次寫入，幾次查詢，零次更新，無事務(wù)支持”的特性，選擇MyISAM是比較好的選擇。測(cè)試結(jié)果說明，MyISAM在性能上明顯優(yōu)于InnoDB(2倍性能優(yōu)勢(shì))。2. 對(duì)于大數(shù)據(jù)量的數(shù)據(jù)，數(shù)據(jù)維護(hù)也會(huì)“與眾不同”，對(duì)于MyISAM表采用分區(qū)表（partition）可以3.4.5. 簡(jiǎn)化數(shù)據(jù)的導(dǎo)出和遷移（add|drop partition

9、），簡(jiǎn)化數(shù)據(jù)的備份和恢復(fù)。 是否采用分區(qū)表主要是基于管理的需要，在存儲(chǔ)設(shè)備沒有物理分離的情況下，對(duì)性能并無明顯影響。 對(duì)于1G內(nèi)存的系統(tǒng)，系統(tǒng)的iowait較高，采用2G內(nèi)存和分區(qū)功能，系統(tǒng)的iowait有顯著的降低（iowait：70% 6%），該數(shù)據(jù)可能有其他系統(tǒng)的擾動(dòng)。 對(duì)于數(shù)據(jù)的查詢，索引設(shè)計(jì)是關(guān)鍵。不能使用索引的查詢性能與數(shù)據(jù)量關(guān)系很大，2GB的數(shù)據(jù)量掃描約60秒。根據(jù)項(xiàng)目設(shè)計(jì)，600GB（一個(gè)月的明細(xì)數(shù)據(jù)）的數(shù)據(jù)掃描可能需要5小時(shí)。對(duì)于索引，同樣看索引的數(shù)據(jù)量大小。對(duì)于高性能的IO設(shè)備，掃描性能會(huì)高一些（這個(gè)根據(jù)不同的硬件設(shè)備可能有不同）。在硬盤容量超過一半時(shí)，寫性能就會(huì)明顯下降，

10、最大達(dá)到40% 6.7. 基于測(cè)試環(huán)境的結(jié)果進(jìn)行推算，生產(chǎn)系統(tǒng)在8*CPU 16GB Memory 的情況下；10000 display的數(shù)據(jù)處理可以控制在1小時(shí)之內(nèi)。建議1. 根據(jù)業(yè)務(wù)需求，需要事務(wù)支持，所以選擇innodb引擎。2. playsum可以不設(shè)計(jì)主鍵（Primary Key），可以設(shè)計(jì)1-2個(gè)索引來加速查詢性能。3. playlog可以不設(shè)計(jì)主鍵，基于管理和維護(hù)的要求，單獨(dú)設(shè)計(jì)一個(gè)database來存儲(chǔ)playlog（靈活備份）。4. 對(duì)于日期字段，若不參加運(yùn)算可以用varchar，例如reportdate5. 根據(jù)業(yè)務(wù)需求，每天的數(shù)據(jù)量約為90,000,000條數(shù)據(jù)，容量約20G；以此推算一個(gè)月的數(shù)據(jù)量約2,700,000,000條數(shù)據(jù)，600G（缺