发现在查询时,去更新某一条记录时,那几分钟单次请求时间为900ms,估计mysql在重新建缓存和索引导致
2.1.2 1000张表,1万条数据测试
CREATE TABLE www_a_com_ (
beginip int(10) unsigned DEFAULT NULL,
endip int(10) unsigned DEFAULT NULL,
rrtype tinyint(4) DEFAULT NULL,
data varchar(256) DEFAULT NULL,
ttl int(10) unsigned DEFAULT NULL,
INDEX ipset_index (beginip,endip) USING BTREE,
INDEX rrtype_index (rrtype) USING BTREE
);
sql语句:select data,ttl from www_a_com_ where and beginip<=3232238968 and endip>=3232238968 and rrtype=1;
//使用缓存
SET GLOBAL key_buffer_size=200*1024*1024;
SET GLOBAL table_cache=1200;
SET GLOBAL thread_cache_size=60;
SET GLOBAL query_cache_size=200*1024*1024;
SET GLOBAL query_cache_limit=200*1024*1024;
SET GLOBAL table_definition_cache=1200;
SET GLOBAL thread_cache_size=60;
SET GLOBAL myisam_data_pointer_size=60;
SET GLOBAL delayed_queue_size=2000;
SET GLOBAL preload_buffer_size=65536;
加索引: INDEX `ipset_index` (`beginip`,`endip`) USING BTREE,
INDEX `rrtype_index` (`rrtype`) USING BTREE
打开缓存:1.5万qps,单次请求时间为1~3ms
不缓存: 3000qps,单次请求时间为6~7ms
服务器一分钟负载:2~4
测试时,发现在进程里启动10个连接和1一个连接的测试数据相差不大。