# Lightning Impact on Online Cluster Test Summary
## Test content
------------
The purpose of this test is to study the impact of the import of large amounts of data using Lightning on a TiDB cluster and to explore possible optimizations.
We prepare for the test by
* Pre-generating 1000 warehouses data and importing it into the cluster using the Lightning backend that is used for TPC-C testing.
* Importing 5000 warehouses of CSV data via local backend using Lightning. The target database for import is independent of the TPC-C test database.
## Cluster Information
------------
Single-node TiDB and PD and 3-node TiKV.
TiDB is on the same machine as PD, with a 20-core 125G machine configuration.
The 3 nodes of TiKV are located on three machines configured with 20 cores and 125G, using 1.5T NVME disks per node.
Lightning runs on a standalone machine with a 16-core 88 G configuration.
The TPC-C tool runs on a TiKV node, and the two are carefully calibrated so as not to interfere with each other.
## TPC-C
------------
TPC-C testing is conducted using the [go-tpc](https://www.google.com/url?q=https://github.com/pingcap/go-tpc&sa=D&source=editors&ust=1648583498537856&usg=AOvVaw1QgrX3oUdkVInOjGme9bcc) tool. TPC\-C tests OLTP systems using a commodity model with five types of transactions.
* [NewOrder](https://www.google.com/url?q=https://github.com/pingcap/go-tpc/blob/master/tpcc/new_order.go&sa=D&source=editors&ust=1648583498538378&usg=AOvVaw2uiVIKxS_-nzqE47KBqX_L) - New order generation
* [Payment](https://www.google.com/url?q=https://github.com/pingcap/go-tpc/blob/master/tpcc/payment.go&sa=D&source=editors&ust=1648583498538667&usg=AOvVaw29WpWTWwxJSjXVViZQ45me) \- Payment
* [OrderStatus](https://www.google.com/url?q=https://github.com/pingcap/go-tpc/blob/master/tpcc/order_status.go&sa=D&source=editors&ust=1648583498538961&usg=AOvVaw3VvwRXowp8Bc62rMSPD9fO) \- Recent Orders Search
* [Delivery](https://www.google.com/url?q=https://github.com/pingcap/go-tpc/blob/master/tpcc/delivery.go&sa=D&source=editors&ust=1648583498539233&usg=AOvVaw2LhY-fTPb0kJ8wyCmIdtrG) \- Dispatch
* [StockLevel](https://www.google.com/url?q=https://github.com/pingcap/go-tpc/blob/master/tpcc/stock_level.go&sa=D&source=editors&ust=1648583498539502&usg=AOvVaw0t7TOohsAeYdlrmSu0BbfA) \- Inventory Out of Stock Status Analysis
## Test commands
```{.bash .copyable}
. /bin/go-tpc -H 172.16.5.40 -P 4008 --metrics-addr=:8982 tpcc run --warehouses=1000
```
## Lightning
------------
The main process of Lightning import starts by encoding the raw data into key-value pairs and writing them locally to Pebble DB for sorting. It then imports the key-value pairs directly into the underlying RocksDB through the write and ingest interfaces of TiKV’s ImportSSTService.
The following tests will focus on the write and ingest interfaces in order to investigate how to reduce the impact of write and ingest requests on the online cluster. The write and ingest interfaces mentioned in this article refer to the write and ingest interfaces of ImportSSTService unless otherwise specified. The lightning parameters are the default parameters, except for the distsql-scan-concurrency modification.
## Summary
------------
1. ingest mainly affects write transaction latency, pre-write p99 latency rises from 2ms to 13ms, apply\-wait P99 latency rises from 495~497 μs to 12 ms, and transaction p99 latency increases to 2~4 times.
2. write affects the write latency of RocksDB, causing the commit log latency and pre-write latency to increase, and has a 30-50 % impact on the p99 latency of write transactions. In addition, writing increases the CPU usage of the gRPC thread pool by 30~40%.
3. checksum and the effect of analyze on query performance of transactions, with read-only P99 latency rising by more than 10x.
## Optimization points
------------
1. Implement batch ingest to eliminate the effect of ingesting on\-apply wait latency and to reduce `prewrite` latency.
2. Reduce the number of threads in the import service, write limit the flow, and reduce the impact of write on RocksDB write latency.
3. Moderately reduce `distsql-scan-concurrency`. Try to eliminate the impact of checksum&analyze on read-only transaction latency.
4. Optimize the IO resource usage of the sst-import write interface. (further investigation of the impact on transaction latency is needed)
## Optimization effect preview
------------
Demo1: Batch ingest every 5s.
* apply\-wait p99 The delayed effect disappears.
* prewrite p99 Delay impact is reduced by half.
* Apply log p99 latency impact reduced from 8x to 2x.
* The payment p99 latency impact is stable within a factor of 2 in most cases.
Demo2: On top of demo1, write limit to 128M/s, import service threads are set to 2, and distsql-scan-concurrency is set to 3.
* The write transaction p99 latency impact is reduced to about 50%.
* Eliminate the effects of latency during checksum and analyze.
Various delays during data import
| No import | master import | demo1 | demo2 |
prewrite p99 | 2ms | 13ms | 5.7ms | 3.8ms |
apply log p99 | 950μs | 8ms | 1.8ms | 1.4ms |
commit log p99 | 980μs | 3.5ms | 3.6ms | 3.2ms |
apply-wait p99 | 496μs | 12ms | 497μs | 497μs |
new order p99 | 16~20ms | 25~40ms | 21~48ms | 17~26ms |
new order avg | 12~14ms | 14~15ms | 13~15ms | 13~14ms |
payment p99 | 9~11ms | 27~38ms | 16~28ms | 14~18ms |
payment avg | 7~8ms | 8~9ms | 8~9ms | 7~8ms |
## master import
------------
demo1 import
demo2 import
Note:
1. No direct correlation between OrderStatus delayed steady rise and fall during import and import has been found so far.
2. demo2 will take 30 minutes longer in the ingest&write and checksum&analyze phases respectively.
## Comparison of test results
------------
In this study, we mainly observe the P99 latency of transactions and evaluate the impact size by the P99 latency of transactions. Unless otherwise specified, the transaction latency mentioned below refers to P99 latency uniformly.
### No data import
prewrite | apply log | append log | commit log | propose wait | apply wait |
1.9~2.0 ms | 930~970 μs | 930~970 μs | 960~990 μs | 495~497 μs | 495~497 μs |
In the absence of data import, the transaction latency jittered 20 ~ 30 % above and below the average latency, and the exact cause of the jitter is out of the scope of this study.
### Normal data import
[detail](https://www.google.com/url?q=https://docs.google.com/document/d/1DdVjB4D4dQKN9JXu5GgWd_V91_wYHbNsyvhXs9Q_Gk4/edit%2523heading%3Dh.9kic3h47u0n9&sa=D&source=editors&ust=1648583498564187&usg=AOvVaw3xXdCj01ZuIajUghEevm0I)
Ingest and write Peak delay
prewrite | apply log | append log | commit log | propose wait | apply wait |
13 ms | 8 ms | 1.6 ms | 3.5 ms | 1.6 ms | 12 ms |
The entire import process took 70 minutes, from 2:37 to 3:47. The last 20 minutes were spent on checksum and analyze.
During write and ingest, the transaction latency is affected in the following order: Payment > NewOrder > Delivery > StockLevel ≈ OrderStatus. write transactions are severely affected, and the latency increases to 2~4 times the original, while read-only transactions are less affected.
During checksum and analyze, the individual transaction latency is intermittently jittery and the latency increases by more than 10 times. The main reason for this is the resource consumption of the coprocessor generated by checksum and analyze.
### Distinguish between write and ingest
RaftDB Write Latency
write Peak Delay
prewrite | apply log | append log | commit log | propose wait | apply wait |
5.3 ms | 1.4 ms | 1.8 ms | 3.5 ms | 1.8 ms | 496 μs |
ingest Peak Delay
prewrite | apply log | append log | commit log | propose wait | apply wait |
7.7 ms | 3 ms | 960 μs | 1.9 ms | 495 μs | 7 ms |
Hack ingest and write, intercept all ingest requests after all the writes are finished, and replay them after the writes are finished.
During the write period, the P99 latency of Delivery, NewOrder, and StockLevel transactions is not significantly affected, while the Payment transaction latency has 30-50% jitter, and the corresponding prewrite latency rises from 2ms to 5.3ms.
During ingestion, two transactions, Payment and NewOrder, were more affected, with latency increasing to 2 to 4 times. The other three transactions also had some increase in latency, but it was not significant.
------------
### Ingest bypass raftstore
[detail](https://www.google.com/url?q=https://docs.google.com/document/d/1DdVjB4D4dQKN9JXu5GgWd_V91_wYHbNsyvhXs9Q_Gk4/edit%2523heading%3Dh.m21v786eurlg&sa=D&source=editors&ust=1648583498575932&usg=AOvVaw0-7h9Z1IJY6zal5HGaG7WA)
ingest and write Peak delay
prewrite | apply log | append log | commit log | propose wait | apply wait |
13 ms | 4.9 ms | 2 ms | 3.8 ms | 2.1 ms | 5.7 ms |
By bypassing raftstore for ingest, the apply log duration and apply wait latency are reduced by about half, but there is no optimization for prewrite latency, so there is no significant optimization for transaction latency.
### batch ingest
[detail](https://www.google.com/url?q=https://docs.google.com/document/d/1DdVjB4D4dQKN9JXu5GgWd_V91_wYHbNsyvhXs9Q_Gk4/edit%2523heading%3Dh.hultskutn8et&sa=D&source=editors&ust=1648583498580441&usg=AOvVaw02nqgOj648YlM5tl2il0PI)
Ingest and write Peak delay
prewrite | apply log | append log | commit log | propose wait | apply wait |
5.7 ms | 1.8 ms | 1.9 ms | 3.6 ms | 1.8 ms | 497 μs |
Apply wait latency is hardly impacted compared to when no data is imported, and prewrite latency has been cut in half compared to before the optimization.
The most affected transactions are still Payment and NewOrder, but the latency impact of Payment transaction is reduced from 2-4 times to about 2 times, and the latency of NewOrder transaction is reduced to about 50% when ingest is most frequent.
### Lower distsql-scan-concurrency
[detail](https://www.google.com/url?q=https://docs.google.com/document/d/1DdVjB4D4dQKN9JXu5GgWd_V91_wYHbNsyvhXs9Q_Gk4/edit%2523heading%3Dh.26tyulnvr1k9&sa=D&source=editors&ust=1648583498585670&usg=AOvVaw0pEtkdqh7ORhjXJSGi8eEr)
Set the distsql-scan-concurrency parameter in lightning to 3. checksum and analyze takes 50 minutes, 30 minutes longer than before (distsql-scan-concurrency = 15). checksum and analyze has a significantly lower impact on transaction latency. Delivery and StockLevel transaction delays are accompanied by intermittent jitter, but the magnitude of the jitter is lower than the previous test by more than 10 times the value.
### write Data without disk write
[detail](https://www.google.com/url?q=https://docs.google.com/document/d/1DdVjB4D4dQKN9JXu5GgWd_V91_wYHbNsyvhXs9Q_Gk4/edit%2523heading%3Dh.5ev39tbp2n69&sa=D&source=editors&ust=1648583498586635&usg=AOvVaw1tXmdhV7z6FKQwDV4wijig)
Study the case in which the data is not written to disk, to find what is the impact of gRPC. gRPC thread pool CPU usage increases, but there is no impact on transaction latency.
## Analysis of test results
------------
1. The biggest impact on online transaction latency is ingest and the final checksum and analyze during the use of lightning with the local backend. The ingest operation primarily causes write transaction latency to increase, Payment transaction latency to increase by a factor of 2 to 4, and NewOrder latency to increase by a factor of about 2. The final checksum and analyze can cause the read-only transaction latency to increase by more than 10 times.
2. The ingestion operation affects latency in two main ways: first - the RocksDB ingestion operation may block other write operations. Second - the apply ingest command in the raftstore may block the apply in region raft command, because the ingest operation takes more time than a normal write operation. This is reflected in the increase in apply wait and prewrite latency. However bypassing the raftstore for ingest improves apply wait latency, but does not optimize prewrite latency.
3. The test results show that batch ingest will effectively optimize transaction latency. For every 5s batch ingest, the impact of peak transaction latency is reduced from more than 2 ~ 4 times to less than 2 times. The impact of apply wait latency is eliminated, and the latency impact of prewrite is reduced by half.
4. Write operations have a 5 to 10 times impact on RocksDB write latency (write duration and write WAL duration), causing commit log and prewrite latency to rise, and a 30 to 50 % impact on write transaction latency. Since write operations involve gPRC, a large number of writes will cause the CPU usage of the gPRC thread pool to increase. In this test environment, the import of 5000 warehouses will occupy an additional 30% to 40% of the CPU of the gPRC thread pool, but since the CPU is not full, the impact of gRPC CPU usage on transaction latency is not found.
5. During checksum and analyze, the coprocessor uses a lot of CPU for checksum and analyze, which affects other normal coprocessor requests. Some transactions, especially OrderStatus and StockLevel transactions, have dramatically increased latency.