MySQL Forums
Forum List  »  Performance

MySQL Performance of Master-Master Setup
Posted by: Varun Datta
Date: September 05, 2013 08:01AM

Hi,

We are benchmarking our Product on MySQL 5.5.28 Master-Master Setup using Semi-Sync Replication.

Application load was not able to sustain on this setup as we saw 2-3 second wait time for Insert + Commit operation waiting for Semi-Sync Ack from Slave.

So we disabled Semi-Sync Replication and again ran the application load.
This time also, application load was not able to sustain but this time we enabled slow query log , we got only commit in the slow query log.


Commit normally takes 60-80 ms to complete when there is no load on database and takes around 250-300 ms when appliction load is running.

Application load only does 2 select operation which hardly takes any time and 1 insert operation in each interation.


We have given exclusive Disk to MySQL database. There is no IO Issue as such and there is no CPU contention also.

Not sure why Commit performance with and without Semi-Sync Replication is still bad.

Request assistance for this.

Below is the ini file:-

[client]
#password = [your_password]
port = 3306
socket = /ag03/mysql/logs/server.sock


[mysqld]

port = 3306
pid-file = /ag03/mysql/logs/server.pid
socket = /ag03/mysql/logs/server.sock
server-id = 1


autocommit=0
auto_increment_offset=1
auto_increment_increment=1

back_log = 50

basedir = /ag01/app/mysql/product/5.5
datadir = /ag02/mysql/data

character_set_server = latin1
collation_server = latin1_general_cs

event-scheduler=1

general_log = 1
general_log_file = /ag03/mysql/logs/trace/alert.log
log-error = /ag03/mysql/logs/trace/error.log
slow_query_log = OFF
slow_query_log_file = /ag03/mysql/logs/trace/slow.log

lower_case_table_names=1

max_allowed_packet = 16M
max_connections = 500
max_connect_errors = 10000

query_cache_size = 0

sql_mode = TRADITIONAL,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,PIPES_AS_CONCAT

table_open_cache = 1024
transaction-isolation = READ-COMMITTED

##REPLICATION
binlog_format=MIXED
sync_binlog=1
log-bin=/ag03/mysql/logs/bin/mysql-bin
log_bin_index = /ag03/mysql/logs/bin/mysql-bin.log.index
relay_log = /ag03/mysql/logs/bin/mysql-relay-bin
relay_log_index= /ag03/mysql/logs/bin/mysql-relay-bin.index
max_binlog_size= 268435456

## TO BE UNCOMMENTED AFTER SEMI-SYNC PLUGIN INSTALL

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000 # 10 second
rpl_semi_sync_slave_enabled=1

## TO BE UNCOMMENTED FOR MASTER-MASTER REPLICATION SETUP
log_slave_updates=1

user=mysql

#PERFORMANCE
innodb_change_buffering=all


#INNODB PARAMS

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 803658956
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /ag03/mysql/logs/redo
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

#TIMEOUT VALUES
wait_timeout= 31536000
interactive_timeout= 31536000


[mysql]
no-auto-rehash
port = 3306
socket = /ag03/mysql/logs/server.sock


[mysqldump]
max_allowed_packet = 16M


[mysqlhotcopy]
interactive-timeout

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Performance of Master-Master Setup
2136
September 05, 2013 08:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.