Why doesn't sysbench access the read replica node?

General
1

Thank you for always providing useful software.I will report the issue because there was a problem while using it.

We created an evaluation environment for YugabyteDB’s Read Replica with the following Ansible Playbook.
The version I’m using is yugabyte-2.25.2.0-b359 running on AlmaLinux 9.6.

---
- hosts: yuganode1.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node1
      ansible.builtin.shell: sudo yugabyted start --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.red.rack1
      register: result_d1
    - name: Debug output from node1
      ansible.builtin.debug:
        msg: "{{ result_d1.stdout }}"

- hosts: yuganode2.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node2
      ansible.builtin.shell: sudo yugabyted start --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.red.rack2
      register: result_d2
    - name: Debug output from node2
      ansible.builtin.debug:
        msg: "{{ result_d2.stdout }}"

- hosts: yuganode3.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node3
      ansible.builtin.shell: sudo yugabyted start --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.red.rack3
      register: result_d3
    - name: Debug output from node3
      ansible.builtin.debug:
        msg: "{{ result_d3.stdout }}"

- hosts: yuganode4.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node4
      ansible.builtin.shell: sudo yugabyted start --read_replica --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.green.rack1
      register: result_d4
    - name: Debug output from node4
      ansible.builtin.debug:
        msg: "{{ result_d4.stdout }}"

- hosts: yuganode5.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node5
      ansible.builtin.shell: sudo yugabyted start --read_replica --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.green.rack2
      register: result_d5
    - name: Debug output from node5
      ansible.builtin.debug:
        msg: "{{ result_d5.stdout }}"

- hosts: yuganode6.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node6
      ansible.builtin.shell: sudo yugabyted start --read_replica --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.green.rack3
      register: result_d6
    - name: Debug output from node6
      ansible.builtin.debug:
        msg: "{{ result_d6.stdout }}"

- hosts: yuganode7.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node7
      ansible.builtin.shell: sudo yugabyted start --read_replica --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.blue.rack1
      register: result_d7
    - name: Debug output from node7
      ansible.builtin.debug:
        msg: "{{ result_d7.stdout }}"
        
- hosts: yuganode8.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node8
      ansible.builtin.shell: sudo yugabyted start --read_replica --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.blue.rack2
      register: result_d8
    - name: Debug output from node8
      ansible.builtin.debug:
        msg: "{{ result_d8.stdout }}"
        
- hosts: yuganode9.maas
  gather_facts: no
  tasks:
    - name: Run yugabyted start on node9
      ansible.builtin.shell: sudo yugabyted start --read_replica --join=yuganode1.maas --background=true --base_dir=/home/yugabyte/data --cloud_location=cloud1.blue.rack3
      register: result_d9
    - name: Debug output from node9
      ansible.builtin.debug:
        msg: "{{ result_d9.stdout }}"

- hosts: yuganode1.maas
  gather_facts: no
  tasks:
    - name: Enable read replica flags
      ansible.builtin.shell: sudo yugabyted configure_read_replica new --base_dir=/home/yugabyte/data --rf=6
      register: result_d10
    - name: Debug output from node1
      ansible.builtin.debug:
        msg: "{{ result_d10.stdout }}"

As Yugabyte Docs AI taught me, I also executed the following command, but nothing changed.

ALTER DATABASE yugabyte SET yb_read_from_followers = true;

I ran a test for oltp_read_only in sysbench.
Despite specifying the Read Replica node, when viewed in the YugabyteDB GUI, the Read is concentrated on the primary node, and the Read Replica node does not seem to be accessing.
Are you making a configuration error? Or am I misunderstanding about Read Replica?

If you have any advice, please.

 IPS=yuganode1.maas

sysbench oltp_read_only \
  --pgsql-host=$IPS \
  --tables=10 \
  --table_size=5000 \
  --range_key_partitioning=false \
  --serial_cache_size=1000 \
  --create_secondary=true \
  --pgsql-db=yugabyte \
  --pgsql-user=yugabyte \
  --db-driver=pgsql \
  --pgsql-port=5433 \
  prepare
  
IPS=yuganode4.maas,yuganode5.maas,yuganode6.maas,yuganode7.maas,yuganode8.maas,yuganode9.maas

sysbench oltp_read_only \
  --pgsql-host=$IPS \
  --tables=10 \
  --table_size=5000  \
  --range_key_partitioning=false \
  --serial_cache_size=1000 \
  --create_secondary=true \
  --pgsql-db=yugabyte \
  --pgsql-user=yugabyte \
  --db-driver=pgsql \
  --pgsql-port=5433 \
  --time=300 \
  --warmup-time=120 \
  --num_rows_in_insert=10 \
  --point_selects=10 \
  --index_updates=10 \
  --non_index_updates=10 \
  --range_selects=false \
  --thread-init-timeout=90 \
  --threads=30 \
  run

image1
image12906×1704 347 KB

2

Good question @ytooyama !

To use “read from follower semantics” (i.e. to be able to read from a non-leader in the primary cluster or a node in the read-replica cluster).. two properties need to be true:

In the case of sysbench oltp_read_only – while the benchmark name has the words read_only in it, it is likely not setting the transaction in read only mode.

In this example below, I connect to a read-replica node. The primary node is about 20ms away.

[yugabyte@ip-172-151-27-122 ~]$ ~/tserver/bin/ysqlsh -h 172.151.27.122
ysqlsh (15.12-YB-2025.1.1.0-b0)
Type "help" for help.
yugabyte=# \timing on
Timing is on.

I have also already turned on yb_read_from_followers at the database level like you did.

ALTER DATABASE yugabyte SET yb_read_from_followers = true;

but you don’t need to do it at the DATABASE level. In practice, you’ll likely do it at the session level. But for running oltp_bench, that’s probably a convenient way.

yugabyte=# show yb_read_from_followers;
 yb_read_from_followers
------------------------
 on
(1 row)

But note that select still takes ~20ms. So it is going to primary node and not using the read-replica.

yugabyte=# select v from t where k = 1;
 v
---
 v
(1 row)

Time: 19.310 ms

Next I set the transaction default to read only. This is one of the ways you can do it.

yugabyte=# show default_transaction_read_only;
 default_transaction_read_only
-------------------------------
 off
(1 row)

yugabyte=# set default_transaction_read_only = true;
SET

Now the select is running in less than 1ms! So it must be coming from the read-replica.

yugabyte=# select v from t where k = 1;
 v
---
 v
(1 row)

Time: 0.741 ms

Before running sysbench oltp_read_only, may be you can try this..

ALTER DATABASE yugabyte SET default_transaction_read_only = true;

but this may not work if the benchmark’s create table phase/data load phase cannot be run upfront in a separate step. Because once you put the database in read-only mode, you can’t even create the test tables to perform the read against.

You might have to modify oltp_read_only source code such that before the read phase, you actually set the session/transaction in READ ONLY transaction mode.

1 Like
3

Thank you kannan-san. I would like to try it.
By the way, I forgot to tell you, but sysbench uses the version provided by this one.
GitHub - yugabyte/sysbench: Scriptable database and system performance benchmark

I’ll contact you again if anything happens.

4

After running this, you can now access the Read replica node as expected.

ALTER DATABASE yugabyte SET default_transaction_read_only = true

image3
image31173×648 52.5 KB

To turn this off, it seems that you need to run it from another database.

$  ysqlsh -h yuganode4.maas -U yugabyte -d yugabyte -c "ALTER DATABASE yugabyte SET default_transaction_read_only = false"
ERROR:  cannot execute ALTER DATABASE in a read-only transaction

$ ysqlsh -h yuganode4.maas -U yugabyte -d postgres -c "ALTER DATABASE yugabyte SET default_transaction_read_only = false"
ALTER DATABASE

Thanks!

1 Like
5

By the way, what kind of action will be done when writing occurs, such as oltp_read_write?

6

In your case, where you changed the default transaction mode of the database, if it’s not starting write transactions with BEGIN TRANSACTION READ WRITE, it will fail to do writes.

Not all cases benefit from reading from followers. When you have a sharded cluster with data larger than memory, reading only from leaders will result in maximum memory cache usage (because you’re sharding the memory cache too over the servers).

While reading from followers you’ll cache the same data on multiple servers and lower cache efficiency but get more throughput (if you were limited by that).

1 Like
7

Thank you, dorian-san.
I have learned that it would be better to set it at the session level, not at the database level, when actually using it.

8

I executed the following code and confirmed that it worked as expected.

import psycopg2
from concurrent.futures import ThreadPoolExecutor
import time

# host-list
READ_REPLICA_HOSTS = [
    "yuganode4.maas",
    "yuganode5.maas",
    "yuganode6.maas",
    "yuganode7.maas",
    "yuganode8.maas",
    "yuganode9.maas",
]

DB_CONFIG = {
    'port': 5433,
    'dbname': 'yugabyte',
    'user': 'yugabyte',
    # 'password': ''
}

SQL = """
    SELECT COUNT(*) FROM uriage1 
    WHERE name = 'Bob' AND okashi = 'Chocolate';
"""

def run_query(host):
    config = DB_CONFIG.copy()
    config['host'] = host
    try:
        conn = psycopg2.connect(**config)
        with conn.cursor() as cur:
            #start the transaction
            cur.execute("BEGIN;")
            cur.execute("START TRANSACTION READ ONLY;")
            cur.execute("SET LOCAL yb_read_from_followers = true;")
            cur.execute(SQL)
            result = cur.fetchone()
            #close the transaction
            cur.execute("COMMIT;")
            #The line below from here has not been changed
            print(f"[{host}] Results: {result[0]}")
    except Exception as e:
        print(f"[{host}] ERROR: {e}")
    finally:
        if conn:
            conn.close()

def main_loop():
    with ThreadPoolExecutor(max_workers=len(READ_REPLICA_HOSTS)) as executor:
        while True:
            executor.map(run_query, READ_REPLICA_HOSTS)
            time.sleep(5)  #  Running Time (s)

if __name__ == '__main__':
    print("YugabyteDB Read Replica Start of load generator: Stop with Ctrl+C")
    try:
        main_loop()
    except KeyboardInterrupt:
        print("Execution has been stopped")
1 Like