所有线程一共执行1000次，但是这些线程不应该是并发执行的吗？那不是应该会快一些的吗？怎么和单线程一样的执行时间呢？测试样例如下：Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 90 Server version: 5.0.51a-community-log MySQL Community Edition (GPL)mysql> show create table test7; CREATE TABLE `test7` ( `c1` char(10) NOT NULL default '', `c2` char(10) NOT NULL default '', `c3` char(10) NOT NULL default '', `c4` char(10) NOT NULL default '', `c5` char(10) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 打开log_bin的情况 ：[root@liytest bin]# ./mysqlslap --concurrency=1,10,20,50,100 --number-of-queries 1000 --create-schema='test' --engine=innodb --query="insert into test7 values ('1111111111','1111111111','1111111111','1111111111','1111111111')" -h 192.168.2.56 -uroot -p Enter password: Bench Running for engine innodb Average number of seconds to run all queries: 12.368 seconds Minimum number of seconds to run all queries: 12.368 seconds Maximum number of seconds to run all queries: 12.368 seconds Number of clients running queries: 1 Average number of queries per client: 1000Bench Running for engine innodb Average number of seconds to run all queries: 12.711 seconds Minimum number of seconds to run all queries: 12.711 seconds Maximum number of seconds to run all queries: 12.711 seconds Number of clients running queries: 10 Average number of queries per client: 100 关闭log_bin[root@liytest bin]# ./mysqlslap --concurrency=1,10,20 --number-of-queries 1000 --create-schema='test' --engine=innodb --query="insert into test7 values ('1111111111','1111111111','1111111111','1111111111','1111111111')" -h 192.168.2.56 -uroot -p Enter password: Bench Running for engine innodb Average number of seconds to run all queries: 6.138 seconds Minimum number of seconds to run all queries: 6.138 seconds Maximum number of seconds to run all queries: 6.138 seconds Number of clients running queries: 1 Average number of queries per client: 1000Bench Running for engine innodb Average number of seconds to run all queries: 1.696 seconds Minimum number of seconds to run all queries: 1.696 seconds Maximum number of seconds to run all queries: 1.696 seconds Number of clients running queries: 10 Average number of queries per client: 100Bench Running for engine innodb Average number of seconds to run all queries: 1.433 seconds Minimum number of seconds to run all queries: 1.433 seconds Maximum number of seconds to run all queries: 1.433 seconds Number of clients running queries: 20 Average number of queries per client: 50 难道打开log_bin后只能顺序写入，那还谈什么并发，那网站怎么使用，是不是有主配置啊
配置文件如下： # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option.# The following options will be passed to all MySQL clients [client] user = mysql password = mysql port = 3306 socket = /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 1000M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M default-storage-engine = InnoDB innodb_file_per_table max_connections = 1000# Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking# Replication Master Server (default) # binary logging is required for replication # log-bin=mysql-bin # sync_binlog = 1# required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted # server-id = 1# Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = lrfsim # # The username the slave will use for authentication when connecting # to the master - required #master-user = mysql # # The password the slave will authenticate with when connecting to # the master - required #master-password = mysql # # The port the master is listening on. # optional - defaults to 3306 #master-port = 3306 # #master-connect-retry=60 # #replicate-do-db=jfytest #replicate-do-db=door # # binary logging - not required for slaves, but recommended# Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname# Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 10000# Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 2000M #innodb_buffer_pool_size = 1000M #innodb_additional_mem_pool_size = 80M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 500M innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 2 #innodb_lock_wait_timeout = 50 #innodb_flush_method=O_DSYNC[mysqldump] quick max_allowed_packet = 16M[mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates[isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M wrGite_buffer = 2M[myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M[mysqlhotcopy] interactive-timeout
[root@liytest bin]# ./mysqlslap --concurrency=1,10,20 --number-of-queries 1000 --create-schema='test' --engine=innodb --query="insert into test7 values ('1111111111','1111111111','1111111111','1111111111','1111111111')" -h 192.168.2.56 -uroot Bench Running for engine innodb Average number of seconds to run all queries: 0.120 seconds Minimum number of seconds to run all queries: 0.120 seconds Maximum number of seconds to run all queries: 0.120 seconds Number of clients running queries: 1 Average number of queries per client: 1000Bench Running for engine innodb Average number of seconds to run all queries: 0.098 seconds Minimum number of seconds to run all queries: 0.098 seconds Maximum number of seconds to run all queries: 0.098 seconds Number of clients running queries: 10 Average number of queries per client: 100Bench Running for engine innodb Average number of seconds to run all queries: 0.052 seconds Minimum number of seconds to run all queries: 0.052 seconds Maximum number of seconds to run all queries: 0.052 seconds Number of clients running queries: 20 Average number of queries per client: 50[root@liytest bin]# 这个值改了，速度猛增，但是
因为执行的sql总数是一样的
不是每个线程执行10000次 而是所有线程一共执行1000次
Your MySQL connection id is 90
Server version: 5.0.51a-community-log MySQL Community Edition (GPL)mysql> show create table test7;
CREATE TABLE `test7` (
`c1` char(10) NOT NULL default '',
`c2` char(10) NOT NULL default '',
`c3` char(10) NOT NULL default '',
`c4` char(10) NOT NULL default '',
`c5` char(10) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
打开log_bin的情况 ：[root@liytest bin]# ./mysqlslap --concurrency=1,10,20,50,100 --number-of-queries 1000 --create-schema='test' --engine=innodb --query="insert into test7 values ('1111111111','1111111111','1111111111','1111111111','1111111111')" -h 192.168.2.56 -uroot -p
Enter password:
Bench
Running for engine innodb
Average number of seconds to run all queries: 12.368 seconds
Minimum number of seconds to run all queries: 12.368 seconds
Maximum number of seconds to run all queries: 12.368 seconds
Number of clients running queries: 1
Average number of queries per client: 1000Bench
Running for engine innodb
Average number of seconds to run all queries: 12.711 seconds
Minimum number of seconds to run all queries: 12.711 seconds
Maximum number of seconds to run all queries: 12.711 seconds
Number of clients running queries: 10
Average number of queries per client: 100
关闭log_bin[root@liytest bin]# ./mysqlslap --concurrency=1,10,20 --number-of-queries 1000 --create-schema='test' --engine=innodb --query="insert into test7 values ('1111111111','1111111111','1111111111','1111111111','1111111111')" -h 192.168.2.56 -uroot -p
Enter password:
Bench
Running for engine innodb
Average number of seconds to run all queries: 6.138 seconds
Minimum number of seconds to run all queries: 6.138 seconds
Maximum number of seconds to run all queries: 6.138 seconds
Number of clients running queries: 1
Average number of queries per client: 1000Bench
Running for engine innodb
Average number of seconds to run all queries: 1.696 seconds
Minimum number of seconds to run all queries: 1.696 seconds
Maximum number of seconds to run all queries: 1.696 seconds
Number of clients running queries: 10
Average number of queries per client: 100Bench
Running for engine innodb
Average number of seconds to run all queries: 1.433 seconds
Minimum number of seconds to run all queries: 1.433 seconds
Maximum number of seconds to run all queries: 1.433 seconds
Number of clients running queries: 20
Average number of queries per client: 50
难道打开log_bin后只能顺序写入，那还谈什么并发，那网站怎么使用，是不是有主配置啊
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.# The following options will be passed to all MySQL clients
[client]
user = mysql
password = mysql
port = 3306
socket = /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 1000M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine = InnoDB
innodb_file_per_table
max_connections = 1000# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin
# sync_binlog = 1# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
# server-id = 1# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = lrfsim
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = mysql
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = mysql
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = 3306
#
#master-connect-retry=60
#
#replicate-do-db=jfytest
#replicate-do-db=door
#
# binary logging - not required for slaves, but recommended# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 2000M
#innodb_buffer_pool_size = 1000M
#innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 2
#innodb_lock_wait_timeout = 50
#innodb_flush_method=O_DSYNC[mysqldump]
quick
max_allowed_packet = 16M[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
wrGite_buffer = 2M[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[mysqlhotcopy]
interactive-timeout
把这个改成0 保证你速度提升几十倍
[root@liytest bin]# ./mysqlslap --concurrency=1,10,20 --number-of-queries 1000 --create-schema='test' --engine=innodb --query="insert into test7 values ('1111111111','1111111111','1111111111','1111111111','1111111111')" -h 192.168.2.56 -uroot
Bench
Running for engine innodb
Average number of seconds to run all queries: 0.120 seconds
Minimum number of seconds to run all queries: 0.120 seconds
Maximum number of seconds to run all queries: 0.120 seconds
Number of clients running queries: 1
Average number of queries per client: 1000Bench
Running for engine innodb
Average number of seconds to run all queries: 0.098 seconds
Minimum number of seconds to run all queries: 0.098 seconds
Maximum number of seconds to run all queries: 0.098 seconds
Number of clients running queries: 10
Average number of queries per client: 100Bench
Running for engine innodb
Average number of seconds to run all queries: 0.052 seconds
Minimum number of seconds to run all queries: 0.052 seconds
Maximum number of seconds to run all queries: 0.052 seconds
Number of clients running queries: 20
Average number of queries per client: 50[root@liytest bin]# 这个值改了，速度猛增，但是
sync_binlog
innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit=0，是很快，并发也会起作用但我们做的是OLTP运营系统的，要充分保证数据的完整性与一致性
innodb_flush_log_at_trx_commit=0，2，服务器掉电或mysql死掉，1S的事务数据就没了sync_binlog=0是最快的，但是我们也要保证主从的高一致性，因此这项也不能是0或2
因此，就只能是慢点，安全点了
谢谢楼上几位