测试场景： 10个链接，每个链接一个线程，每个线程，根据给定的500个，user_id逐条 更新lvban_user_sysinfo的edittime, 统计各个线程，执行的时间。
一 、 mysql缺省配置，
1.1 线下20.106机器 （平均update时间 ： 1800/500=3.6毫秒）
Thread-0range :[1394076164934--1394076166770],total time============================= 1836
Thread-3range :[1394076164938--1394076166775],total time============================= 1837
Thread-2range :[1394076164938--1394076166714],total time============================= 1776
Thread-5range :[1394076164940--1394076166680],total time============================= 1740
Thread-1range :[1394076164934--1394076166746],total time============================= 1812
Thread-6range :[1394076164941--1394076166746],total time============================= 1805
Thread-8range :[1394076164942--1394076166737],total time============================= 1795
Thread-4range :[1394076164940--1394076166763],total time============================= 1823
Thread-9range :[1394076164943--1394076166808],total time============================= 1865
Thread-7range :[1394076164941--1394076166680],total time============================= 1739
1.2， 线上90.125机器（平均update时间 ： 380000/500=760毫秒）
Thread-2range :[1394074558705--1394074958154],total time============================= 399449
Thread-7range :[1394074558713--1394074952927],total time============================= 394214
Thread-0range :[1394074558703--1394074956598],total time============================= 397895
Thread-4range :[1394074558709--1394074933422],total time============================= 374713
Thread-9range :[1394074558716--1394074947718],total time============================= 389002
Thread-3range :[1394074558709--1394074955449],total time============================= 396740
Thread-8range :[1394074558715--1394074954444],total time============================= 395729
Thread-5range :[1394074558709--1394074952219],total time============================= 393510
Thread-1range :[1394074558703--1394074927728],total time============================= 369025
Thread-6range :[1394074558713--1394074956113],total time============================= 397400
二 、 修改配置项后，修改如下，
##by jeffrey 修改项
####key_buffer_size = 16M
####sort_buffer_size = 512K
####read_buffer_size = 256K
####read_rnd_buffer_size = 512K
####myisam_sort_buffer_size = 8M
####innodb_flush_log_at_trx_commit =1
#sync_binlog =1
key_buffer_size = 2048M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 1G
innodb_flush_log_at_trx_commit = 2
sync_binlog =1000
####新加项
innodb_log_buffer_size = 16M
open_files_limit = 10240
max_connections = 300
thread_concurrency = 4
query_cache_size = 64M
query_cache_limit = 4M
2.1 线下20.106机器 （平均update时间 ： 6400/500=12.8毫秒）
修改后时间反而变长，是因为，新配置值是按照16G内存，8核cpu的标准配置的，而106机器只是 2核2G内存
Thread-4range :[1394075925742--1394075932181],total time============================= 6439
Thread-2range :[1394075925742--1394075932233],total time============================= 6491
Thread-0range :[1394075925742--1394075932213],total time============================= 6471
Thread-8range :[1394075925747--1394075932152],total time============================= 6405
Thread-5range :[1394075925742--1394075932195],total time============================= 6453
Thread-1range :[1394075925742--1394075932165],total time============================= 6423
Thread-7range :[1394075925744--1394075932193],total time============================= 6449
Thread-6range :[1394075925743--1394075932212],total time============================= 6469
Thread-9range :[1394075925747--1394075932149],total time============================= 6402
Thread-3range :[1394075925742--1394075932153],total time============================= 6411
2.2， 线上90.125机器（平均update时间 ： 270000/500=540毫秒）
时间相对调整前缩短了30%
Thread-0range :[1394090276471--1394090543841],total time============================= 267370
Thread-2range :[1394090276481--1394090553404],total time============================= 276923
Thread-9range :[1394090276486--1394090551320],total time============================= 274834
Thread-4range :[1394090276484--1394090553356],total time============================= 276872
Thread-8range :[1394090276485--1394090541314],total time============================= 264829
Thread-1range :[1394090276471--1394090544144],total time============================= 267673
Thread-7range :[1394090276485--1394090548616],total time============================= 272131
Thread-3range :[1394090276484--1394090552990],total time============================= 276506
Thread-6range :[1394090276485--1394090546991],total time============================= 270506
Thread-5range :[1394090276485--1394090553179],total time============================= 276694
2.2， 线上90.125机器（将innodb_flush_log_at_trx_commit设置为0 。平均update时间 ： 210000/500=420毫秒）
时间相对调整前缩短了47%
Thread-5range :[1394092295223--1394092501094],total time============================= 205871
Thread-0range :[1394092295221--1394092502101],total time============================= 206880
Thread-6range :[1394092295223--1394092503845],total time============================= 208622
Thread-7range :[1394092295228--1394092502340],total time============================= 207112
Thread-8range :[1394092295229--1394092503585],total time============================= 208356
Thread-4range :[1394092295222--1394092504283],total time============================= 209062
Thread-9range :[1394092295230--1394092503371],total time============================= 208141
Thread-1range :[1394092295221--1394092503702],total time============================= 208481
Thread-2range :[1394092295221--1394092503954],total time============================= 208733
Thread-3range :[1394092295222--1394092499804],total time============================= 204582
测试代码：
@Test
public void testIA() {
int i = 0;
for (i = 0; i < 10; i++) {
new ThreadTest(0 * 100L).start();
}
while (results.size() < i ) {
// System.out.println("###########" + results.size());
}
for (String s : results) {
System.out.println(s);
}
}
class ThreadTest extends Thread {
private Long startId;
private int count = 0;
private long start = System.currentTimeMillis();
public ThreadTest(Long startId){
super();
this.startId = startId;
}
public void run() {
for (int i = 0; i < 500; i++) {
int result = userSysInfoDAO.updateForTest(startId + i);
// result = userDao.loadUserById(startId + i) == null ? 0 : 1;
System.out.println(Thread.currentThread().getName() + "count is " + (++count) + ",result is: " + result);
}
results.add(Thread.currentThread().getName() + "range :[" + start + "--" + System.currentTimeMillis()
+ "],total time============================= " + (System.currentTimeMillis() - start));
}
}
测试场景： 10个链接，每个链接一个线程，每个线程，根据给定的500个，user_id逐条 更新lvban_user_sysinfo的edittime, 统计各个线程，执行的时间。
一 、 mysql缺省配置，
1.1 线下20.106机器 （平均update时间 ： 1800/500=3.6毫秒）
Thread-0range :[1394076164934--1394076166770],total time============================= 1836
Thread-3range :[1394076164938--1394076166775],total time============================= 1837
Thread-2range :[1394076164938--1394076166714],total time============================= 1776
Thread-5range :[1394076164940--1394076166680],total time============================= 1740
Thread-1range :[1394076164934--1394076166746],total time============================= 1812
Thread-6range :[1394076164941--1394076166746],total time============================= 1805
Thread-8range :[1394076164942--1394076166737],total time============================= 1795
Thread-4range :[1394076164940--1394076166763],total time============================= 1823
Thread-9range :[1394076164943--1394076166808],total time============================= 1865
Thread-7range :[1394076164941--1394076166680],total time============================= 1739
1.2， 线上90.125机器（平均update时间 ： 380000/500=760毫秒）
Thread-2range :[1394074558705--1394074958154],total time============================= 399449
Thread-7range :[1394074558713--1394074952927],total time============================= 394214
Thread-0range :[1394074558703--1394074956598],total time============================= 397895
Thread-4range :[1394074558709--1394074933422],total time============================= 374713
Thread-9range :[1394074558716--1394074947718],total time============================= 389002
Thread-3range :[1394074558709--1394074955449],total time============================= 396740
Thread-8range :[1394074558715--1394074954444],total time============================= 395729
Thread-5range :[1394074558709--1394074952219],total time============================= 393510
Thread-1range :[1394074558703--1394074927728],total time============================= 369025
Thread-6range :[1394074558713--1394074956113],total time============================= 397400
二 、 修改配置项后，修改如下，
##by jeffrey 修改项
####key_buffer_size = 16M
####sort_buffer_size = 512K
####read_buffer_size = 256K
####read_rnd_buffer_size = 512K
####myisam_sort_buffer_size = 8M
####innodb_flush_log_at_trx_commit =1
#sync_binlog =1
key_buffer_size = 2048M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 1G
innodb_flush_log_at_trx_commit = 2
sync_binlog =1000
####新加项
innodb_log_buffer_size = 16M
open_files_limit = 10240
max_connections = 300
thread_concurrency = 4
query_cache_size = 64M
query_cache_limit = 4M
2.1 线下20.106机器 （平均update时间 ： 6400/500=12.8毫秒）
修改后时间反而变长，是因为，新配置值是按照16G内存，8核cpu的标准配置的，而106机器只是 2核2G内存
Thread-4range :[1394075925742--1394075932181],total time============================= 6439
Thread-2range :[1394075925742--1394075932233],total time============================= 6491
Thread-0range :[1394075925742--1394075932213],total time============================= 6471
Thread-8range :[1394075925747--1394075932152],total time============================= 6405
Thread-5range :[1394075925742--1394075932195],total time============================= 6453
Thread-1range :[1394075925742--1394075932165],total time============================= 6423
Thread-7range :[1394075925744--1394075932193],total time============================= 6449
Thread-6range :[1394075925743--1394075932212],total time============================= 6469
Thread-9range :[1394075925747--1394075932149],total time============================= 6402
Thread-3range :[1394075925742--1394075932153],total time============================= 6411
2.2， 线上90.125机器（平均update时间 ： 270000/500=540毫秒）
时间相对调整前缩短了30%
Thread-0range :[1394090276471--1394090543841],total time============================= 267370
Thread-2range :[1394090276481--1394090553404],total time============================= 276923
Thread-9range :[1394090276486--1394090551320],total time============================= 274834
Thread-4range :[1394090276484--1394090553356],total time============================= 276872
Thread-8range :[1394090276485--1394090541314],total time============================= 264829
Thread-1range :[1394090276471--1394090544144],total time============================= 267673
Thread-7range :[1394090276485--1394090548616],total time============================= 272131
Thread-3range :[1394090276484--1394090552990],total time============================= 276506
Thread-6range :[1394090276485--1394090546991],total time============================= 270506
Thread-5range :[1394090276485--1394090553179],total time============================= 276694
2.2， 线上90.125机器（将innodb_flush_log_at_trx_commit设置为0 。平均update时间 ： 210000/500=420毫秒）
时间相对调整前缩短了47%
Thread-5range :[1394092295223--1394092501094],total time============================= 205871
Thread-0range :[1394092295221--1394092502101],total time============================= 206880
Thread-6range :[1394092295223--1394092503845],total time============================= 208622
Thread-7range :[1394092295228--1394092502340],total time============================= 207112
Thread-8range :[1394092295229--1394092503585],total time============================= 208356
Thread-4range :[1394092295222--1394092504283],total time============================= 209062
Thread-9range :[1394092295230--1394092503371],total time============================= 208141
Thread-1range :[1394092295221--1394092503702],total time============================= 208481
Thread-2range :[1394092295221--1394092503954],total time============================= 208733
Thread-3range :[1394092295222--1394092499804],total time============================= 204582
测试代码：
@Test
public void testIA() {
int i = 0;
for (i = 0; i < 10; i++) {
new ThreadTest(0 * 100L).start();
}
while (results.size() < i ) {
// System.out.println("###########" + results.size());
}
for (String s : results) {
System.out.println(s);
}
}
class ThreadTest extends Thread {
private Long startId;
private int count = 0;
private long start = System.currentTimeMillis();
public ThreadTest(Long startId){
super();
this.startId = startId;
}
public void run() {
for (int i = 0; i < 500; i++) {
int result = userSysInfoDAO.updateForTest(startId + i);
// result = userDao.loadUserById(startId + i) == null ? 0 : 1;
System.out.println(Thread.currentThread().getName() + "count is " + (++count) + ",result is: " + result);
}
results.add(Thread.currentThread().getName() + "range :[" + start + "--" + System.currentTimeMillis()
+ "],total time============================= " + (System.currentTimeMillis() - start));
}
}