Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.34
Description
Galera clusters do not necessary need binary logging enabled on nodes, so configuration with binary logging disabled is quite common. It turned out that after upgrade to 10.1.x, these configurations started to perform worse, as notable time is spent on keeping tc.log updates (in pre-10.1 versions tc.log was not maintained in such cases, see MDEV-16442).
Simple sysbench tests may show this effect. For example, I've prepared the following table on my 2 cores old netbook running under Ubuntu 14.04:
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/common.lua --mysql-host=127.0.0.1 --db-driver=mysql --mysql-user=root --mysql-db=test --oltp-table-size=2500000 prepare
|
Then I started single node cluster on the same datadir with the following command (all defaults unless specified):
openxs@ao756:~/dbs/maria10.1$ bin/mysqld_safe --no-defaults --binlog_format=row --wsrep_provider=/usr/lib/libgalera_smm.so --wsrep_on=1 --wsrep_cluster_address=gcomm://127.0.0.1:4567 --wsrep-new-cluster &
|
[1] 6991
|
|
|
openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot -e"select @@log_bin, @@wsrep_on, @@wsrep_cluster_address"
|
+-----------+------------+-------------------------+
|
| @@log_bin | @@wsrep_on | @@wsrep_cluster_address |
|
+-----------+------------+-------------------------+
|
| 0 | 1 | gcomm://127.0.0.1:4567 |
|
+-----------+------------+-------------------------+
|
and the run sysbench test with N concurrent threads, where N in (4,8,16):
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --db-driver=mysql --mysql-user=root --mysql-db=test --oltp-table-size=2500000 --report-interval=1 --max_requests=0 --max-time=300 --threads=*N* run
|
I've got the following (low) TPS values:
threads | TPS
|
4 | 6.08
|
8 | 8.46
|
16 | 8.96
|
Then I stopped the node and restarted it again with log_bin enabled:
openxs@ao756:~/dbs/maria10.1$ bin/mysqld_safe --no-defaults --binlog_format=row --wsrep_provider=/usr/lib/libgalera_smm.so --wsrep_on=1 --wsrep_cluster_address=gcomm://127.0.0.1:4567 --wsrep-new-cluster --log_bin=1 &
|
|
|
openxs@ao756:~/dbs/maria10.1$ 180618 13:46:14 mysqld_safe Logging to '/home/openxs/dbs/maria10.1/data/ao756.err'.
|
180618 13:46:14 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/maria10.1/data
|
|
|
openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot -e"select @@log_bin, @@wsrep_on, @@wsrep_cluster_address"
|
+-----------+------------+-------------------------+
|
| @@log_bin | @@wsrep_on | @@wsrep_cluster_address |
|
+-----------+------------+-------------------------+
|
| 1 | 1 | gcomm://127.0.0.1:4567 |
|
+-----------+------------+-------------------------+
|
I've got the following results, with TPS values still small, but notably higher:
threads | TPS
|
4 | 14.78
|
8 | 15.00
|
16 | 15.56
|
It seems we should do something to reduce the impact of log_bin NOT specified or clearly recommend to set it explicitly in the KB articles for Galera nodes on 10.1.x+
Side node: I do NOT see the same effect without wsrep_on = ON - there without log_bin enabled we run a bit faster.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-