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

高容量数据库性能测试 耿红杰 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 My.cnf 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 测试目的 myisam和innodb引擎对于性能的影响，采用2000w的数据进行写入和查询测试 座机电话号码0数据的查询性能测试 myisam 引擎的分区功能 测试步骤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; load data into table while i 座机电话号码 do INSERT INTO `innodb` `name`, `adress`, `markert`, `tel`, `base`, `log` VALUES play1XXXXXXXXXX1XXXXXXXXXX0101, abcdefghijklmnopqrstuvwxyz01座机电话号码0, abcdefghijklmnopqrstuvwxyz01座机电话号码0, 0086-27-座机电话号码8, abcdefghijklmnopqrstuvwxyz01座机电话号码0, abcdefghijklmnopqrstuvwxyz01座机电话号码0 ; set i i+1; end while; 记录数 运行时间 Record/sec Data Volume Binlog size MyISAM 座机电话号码 19 min 23.64 sec 16700 4.1+0.2 GB 8GB InnoDB 座机电话号码 35 min 36.90 sec 9300 4.8G 10GB 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 betweenand Query Time MyISAM InnoDB Q1 0.00 sec 2 min 35.50 sec Q2 1 min 55.75 sec 2 min 36.11 sec Q3 1 min 56.22 sec 2 min 34.34 sec Q4 1 min 56.27 sec 2 min 36.62 sec Q5 0.01 sec 0.01 sec 测试步骤2 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,