why enable enable log_bin can speed up cluster innodb insert?

[Yin Xuesong]

I have a 3 node mariadb cluster,when I disable log_bin param，it response time become slow. But I haven't found other on Internet enable log_bin.Can anyone tell me whether I should enable it?

I use sysbench to test the cluster,the commod is 

sysbench --test=../share/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-db=sbtest --mysql-user=root --mysql-password=root prepare

sysbench --test=../share/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-db=sbtest --mysql-user=root --mysql-password=root --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=64 run

enable log_bin

OLTP test statistics:
    queries performed:
        read:                            732312
        write:                           209232
        other:                           104616
        total:                           1046160
    transactions:                        52308  (871.50 per sec.)
    read/write requests:                 941544 (15687.01 per sec.)
    other operations:                    104616 (1743.00 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)


General statistics:
    total time:                          60.0206s
    total number of events:              52308
    total time taken by event execution: 3840.2394s
    response time:
         min:                                 16.04ms
         avg:                                 73.42ms
         max:                                353.60ms
         approx.  95 percentile:                   121.78ms


Threads fairness:
    events (avg/stddev):           817.3125/13.63
    execution time (avg/stddev):   60.0037/0.01

disable log_bin

OLTP test statistics:
    queries performed:
        read:                            164388
        write:                           46968
        other:                           23484
        total:                           234840
    transactions:                        11742  (194.70 per sec.)
    read/write requests:                 211356 (3504.66 per sec.)
    other operations:                    23484  (389.41 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)


General statistics:
    total time:                          60.3072s
    total number of events:              11742
    total time taken by event execution: 3849.7797s
    response time:
         min:                                135.52ms
         avg:                                327.86ms
         max:                               1357.01ms
         approx.  95 percentile:             428.51ms


Threads fairness:
    events (avg/stddev):           183.4688/1.78
    execution time (avg/stddev):   60.1528/0.09

server.cnf

#
# These groups are read by MariaDB server.
#
[server]


[mysqld]
log_bin=Mysql50
server-id=50


innodb_autoextend_increment=128
innodb_buffer_pool_instances=10
innodb_buffer_pool_size=5120M
innodb_log_buffer_size=32M


character-set-server = gbk
skip-name-resolve


back_log = 250
max_connections = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 18


thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 2M


slow_query_log
long_query_time = 2
slow-query-log-file = /var/log/mysql/mysql-slow.log


innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 128


innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120


lower_case_table_names = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES




#max_connect_errors = 10
#ft_min_word_len = 4
#max_heap_table_size = 64M # memory engine
#read_buffer_size = 2M      #myisam
#read_rnd_buffer_size = 16M # myisam


#
# * Galera-related settings
#
[galera]
wsrep_cluster_name=MyCluster
wsrep_node_name = mariadb50
wsrep_node_address=172.16.4.50


wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_cluster_address="gcomm://172.16.4.50,172.16.4.63,172.16.4.69"
wsrep_cluster_address="gcomm://172.16.4.50,172.16.4.63,172.16.4.69,172.16.4.6,172.16.4.47"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2


#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0


#
# Optional setting
#
wsrep_slave_threads=8
innodb_flush_log_at_trx_commit=0


log-error=/var/log/mysqld.log


# Enable Debugging Output to Server Error Log
#wsrep_debug=ON
#wsrep_provider_options="cert.log_conflicts=YES"


wsrep_retry_autocommit=4
wsrep_provider_options="gcache.size=512M;gcache.keep_pages_size=32M;gcs.fc_limit=32;"


[embedded]


[mariadb]


[mariadb-10.1]

Can anyone help me? Thank a lot.

[hunter86bg]

 Is there anything in the logs ? You can always enable the debug log,but I think this is not necessary.
What is the output of the commands described in: Replication_health

Thanks to my colleague Martin, I used to debug my Cluster with the tools in this git: MYQ_tools (Direct link I have used is: MYQ_Tolls_1.0.4 )

Just Download it and then extract the myq_status.linux-amd64 (depends on your OS) and copy it to /usr/bin/

Then you can monitor the load with these commands example:

#myq_status.linux-amd64 -a "-uroot -ppassrord -S/tmp/mysql.6000.sock" wsrep

you need to replace the above values with the correct ones for (user password and socket)

You will get find out if there are pauses and from which node. Experiment a little bit and post your feedback :)

[Bojan F]

I noticed same problem (version 10.2.6 MariaDB).
If log_bin is not enabled performances are much worse. Looks like there is much more io work if log_bin are not enabled???

I don't see logic in that cause I would expect opposite - to get worse performances with log_bin cause have to save log files.

Anyone know why is that?

[David Song]

I am having the same problem. 

but get to know that enabling log_bin helps is good. 

still need to figure out logic here. 

2018년 3월 29일 목요일 오후 9시 29분 50초 UTC+9, Bojan F 님의 말: