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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.1 [ 16100 ] | |
| Assignee | Seppo Jaakola [ seppo ] |
| 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 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: {noformat} 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 {noformat} Then I started single node cluster on the same datadir with the following command (all defaults unless specified): {noformat} 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 | +-----------+------------+-------------------------+ {noformat} and the run sysbench test with N concurrent threads, where N in (4,8,16): {noformat} 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 {noformat} I've got the following (low) TPS values: {noformat} threads | TPS 4 | 6.08 8 | 8.46 16 | 8.96 {noformat} Then I stopped the node and restarted it again with log_bin enabled: {noformat} 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 | +-----------+------------+-------------------------+ {noformat} I've got the following results, with TPS values still small, but notably higher: {noformat} threads | TPS 4 | 14.78 8 | 15.00 16 | 15.56 {noformat} 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. |
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 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: {noformat} 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 {noformat} Then I started single node cluster on the same datadir with the following command (all defaults unless specified): {noformat} 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 | +-----------+------------+-------------------------+ {noformat} and the run sysbench test with N concurrent threads, where N in (4,8,16): {noformat} 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 {noformat} I've got the following (low) TPS values: {noformat} threads | TPS 4 | 6.08 8 | 8.46 16 | 8.96 {noformat} Then I stopped the node and restarted it again with log_bin enabled: {noformat} 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 | +-----------+------------+-------------------------+ {noformat} I've got the following results, with TPS values still small, but notably higher: {noformat} threads | TPS 4 | 14.78 8 | 15.00 16 | 15.56 {noformat} 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. |
| 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 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: {noformat} 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 {noformat} Then I started single node cluster on the same datadir with the following command (all defaults unless specified): {noformat} 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 | +-----------+------------+-------------------------+ {noformat} and the run sysbench test with N concurrent threads, where N in (4,8,16): {noformat} 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 {noformat} I've got the following (low) TPS values: {noformat} threads | TPS 4 | 6.08 8 | 8.46 16 | 8.96 {noformat} Then I stopped the node and restarted it again with log_bin enabled: {noformat} 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 | +-----------+------------+-------------------------+ {noformat} I've got the following results, with TPS values still small, but notably higher: {noformat} threads | TPS 4 | 14.78 8 | 15.00 16 | 15.56 {noformat} 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. |
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 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: {noformat} 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 {noformat} Then I started single node cluster on the same datadir with the following command (all defaults unless specified): {noformat} 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 | +-----------+------------+-------------------------+ {noformat} and the run sysbench test with N concurrent threads, where N in (4,8,16): {noformat} 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 {noformat} I've got the following (low) TPS values: {noformat} threads | TPS 4 | 6.08 8 | 8.46 16 | 8.96 {noformat} Then I stopped the node and restarted it again with log_bin enabled: {noformat} 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 | +-----------+------------+-------------------------+ {noformat} I've got the following results, with TPS values still small, but notably higher: {noformat} threads | TPS 4 | 14.78 8 | 15.00 16 | 15.56 {noformat} 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. |
| 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 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: {noformat} 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 {noformat} Then I started single node cluster on the same datadir with the following command (all defaults unless specified): {noformat} 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 | +-----------+------------+-------------------------+ {noformat} and the run sysbench test with N concurrent threads, where N in (4,8,16): {noformat} 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 {noformat} I've got the following (low) TPS values: {noformat} threads | TPS 4 | 6.08 8 | 8.46 16 | 8.96 {noformat} Then I stopped the node and restarted it again with log_bin enabled: {noformat} 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 | +-----------+------------+-------------------------+ {noformat} I've got the following results, with TPS values still small, but notably higher: {noformat} threads | TPS 4 | 14.78 8 | 15.00 16 | 15.56 {noformat} 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. |
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 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: {noformat} 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 {noformat} Then I started single node cluster on the same datadir with the following command (all defaults unless specified): {noformat} 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 | +-----------+------------+-------------------------+ {noformat} and the run sysbench test with N concurrent threads, where N in (4,8,16): {noformat} 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 {noformat} I've got the following (low) TPS values: {noformat} threads | TPS 4 | 6.08 8 | 8.46 16 | 8.96 {noformat} Then I stopped the node and restarted it again with log_bin enabled: {noformat} 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 | +-----------+------------+-------------------------+ {noformat} I've got the following results, with TPS values still small, but notably higher: {noformat} threads | TPS 4 | 14.78 8 | 15.00 16 | 15.56 {noformat} 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. |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Labels | performance regression wsrep | need_feedback performance regression wsrep |
| Assignee | Seppo Jaakola [ seppo ] | Stepan Patryshev [ stepan.patryshev ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Labels | need_feedback performance regression wsrep | performance regression wsrep |
| Assignee | Stepan Patryshev [ stepan.patryshev ] | Jan Lindström [ jplindst ] |
| Link |
This issue relates to |
| Assignee | Jan Lindström [ jplindst ] | Seppo Jaakola [ seppo ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Seppo Jaakola [ seppo ] | Jan Lindström [ jplindst ] |
| issue.field.resolutiondate | 2019-03-15 05:10:20.0 | 2019-03-15 05:10:20.155 |
| Fix Version/s | 10.4.4 [ 23310 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue is duplicated by |
| Labels | performance regression wsrep | performance regression test_disabled_ES wsrep |
| Labels | performance regression test_disabled_ES wsrep | performance regression wsrep |
| Workflow | MariaDB v3 [ 87897 ] | MariaDB v4 [ 154525 ] |
| Zendesk Related Tickets | 165555 140179 |