[IGNITE-20617] Sql. Performance degradation in SELECTS (2 nodes VS 1 node)

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0
• Component/s: sql
• Labels: ignite-3 performance
• Assignee: Konstantin Orlov
• Reporter: Ivan Artiukhov
• Created: 11/Oct/23 07:05
• Updated: 04/Sep/24 16:10
• Resolved: 07/Nov/23 12:43

Description

Ignite 3, rev. 7d188ac7ae068bd69ff0e6e6cfe5a32ac5749d17

Benchmark: https://github.com/gridgain/YCSB/blob/ycsb-2023.3/ignite3/src/main/java/site/ycsb/db/ignite3/IgniteSqlClient.java 

The benchmark establishes an SQL Session and perform SELECTs via preparedStatement.

 

Steps:

• Run an Ignite cluster of 2 nodes with the attached config ignite-config.json .
  • fsync = false
• Run the SQL YCSB benchmark in preload mode:
  • -db site.ycsb.db.ignite3.IgniteSqlClient -load -P /opt/pubagent/poc/config/ycsb/workloads/workloadc -threads 1 -p recordcount=10000 -p dataintegrity=true -p measurementtype=timeseries -p hosts=192.168.1.60
• Run the SQL YCSB benchmark in 100% read mode: 
  • -db site.ycsb.db.ignite3.IgniteSqlClient -t -P /opt/pubagent/poc/config/ycsb/workloads/workloadc -threads 1 -p operationcount=10000 -p recordcount=10000 -p dataintegrity=true -p measurementtype=timeseries -p hosts=192.168.1.60 -s
• Observe the following average throughput on reads:

Server node's logs: sql-logs-2-server-nodes.zip

Repeat the test with only 1 server node and observe ~20x better throughput on reads:

 

 

Issue Links

• is caused by: IGNITE-19502 Separate component to keep database schema and manage changes in it
• is fixed by: IGNITE-20668 Increase wait after a DDL to account for idle safe-time propagation period
• links to: GitHub Pull Request #2804