怎么开不开replication 数据库的性能会有一倍的差距？

ayalastrike

我在不打开replication的情况下，往master db中插入数据，用JMeter测试结果如下(参见附件)：
insert aw_user average  72ms
insert prsn        average  83ms
insert ctcn_info average 115ms

在打开replication情况下，还是往master db中插入数据，插入时间竟然差了一倍？是binlog写入的影响吗？如何提高性能呢？
insert aw_user average  142ms
insert prsn        average  236ms
insert ctcn_info average  138ms

枫影谁用了

是在同一内网？

ayalastrike

嗯，同一个内网，同一种情况下测试的结果，应该不存在netIO影响的问题
测试了多次的结果，每次都差一倍左右

枫影谁用了

以前我在公网同步时有这样的情况。

后来搬到同一内网就没有了。负载高不？

你这个是用那个软件测试出来的结果？

ayalastrike

同一个内网，db在9.35.11.137上，测试机器在9.35.11.34上，用的JMeter测的数据

yejr

binlog配置贴上来，开binlog后，性能会差一些很正常，但是差一倍倒还是有点多了

ayalastrike

mysqld1是master，mysqld2是slave。
配置如下：


[mysql]
no-auto-rehash

[mysqld_safe]
open-files-limit = 819

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
user=root
password=r00t

# MASTER

[mysqld1]
port                = 23307
user        = mysql
sync-binlog = 1
log-bin = /disk2/mysql/data/mysqld_rep1/mysqld-bin.log
log-bin-index = /disk2/mysql/data/mysqld_rep1/mysqld-bin.index
binlog-do-db = awapp
binlog-do-db = cmsstaging
binlog-do-db = cmslive

log-bin
server-id = 127007
datadir= /disk2/mysql/data/mysqld_rep1
tmpdir=/rt/db/tmp
default-character-set=utf8
character_set_server=utf8
skip_name_resolve
max_connect_errors=1000

socket              = /rt/db/tmp/mysqld_rep1.sock
back_log = 50
max_connections = 1000
max_connect_errors = 100
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
thread_cache_size = 64
thread_concurrency = 8
ft_min_word_len = 4
default_table_type = InnoDB
thread_stack = 192K
# transaction_isolation = READ-COMMITTED  *** using default is recommended
tmp_table_size = 64M
#log_slow_queries = /disk2/mysql/data/mysqld_multi1/mysqld-slow.log
long_query_time = 1
log_long_format
log_error = /disk2/mysql/data/mysqld_rep1/mysqld.err
log_queries_not_using_indexes
log_warnings = 2

#*** MyISAM Specific options
key_buffer_size = 32M
myisam_repair_threads = 1
myisam_recover

# *** BDB Specific options ***
skip-bdb

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 137M
innodb_log_file_size = 50M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
# innodb_thread_concurrency = 4 *** using default is recommended
# innodb_flush_log_at_trx_commit = 1 *** using default is recommended
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
# innodb_lock_wait_timeout = 120 *** using default is recommended
innodb_file_per_table

[mysqld2]
server-id=127008
port=23308
user=mysql
socket=/rt/db/tmp/mysqld_rep2.sock
datadir=/disk2/mysql/data/mysqld_rep2

master-host=127.0.0.1
master-user=rep
master-password=r00t
master-port=23307

replicate-do-db=awapp
replicate-do-db=cmsstaging
replicate-do-db=cmslive

#replicate-ignore-table=awapp.lucene_multi_master
replicate-ignore-table=awapp.lucene_lock
replicate-ignore-table=awapp.aw_login_session

log_error=/disk2/mysql/data/mysqld_rep2/mysqld.err

ayalastrike

我把sync_binlog设为0，很快。
但是这样会不会有潜在的问题？

ruochen

应该没有这么大的差别的

realzyy

用mysqlslap测试一下吧。
我开启了binlog，MyISAM引擎在某些情况下反而比没有开启要快