【详述】使用阿里云ECS部署的集群，1台FE8台BE，磁盘的读写性能比较差，dd命令测试，读写只有140M/S左右，使用datax同步数据速度很慢，通过insert into … select 插入数据速度也很慢，6千万行数据的表需要25min，需要优化哪些配置可以稍微快一点么？
【StarRocks版本】3.2.1
【集群规模】1fe+8be
【 Query Profile】
| Summary |
| Version: 3.2.1-79ee91d |
| State: Finished |
| TotalTime: 25m55s |
| ExecutionTime: 25m41s [Scan: 25m38s (99.81%), Network: 0ns (0.00%), ResultDeliverTime: 1m36s (6.23%), ScheduleTime: 802.092ms (0.05%)] |
| CollectProfileTime: 0 |
| FrontendProfileMergeTime: 3.807ms |
| QueryPeakMemoryUsage: 251.996 MB, QueryAllocatedMemoryUsage: 5.645 TB |
| Top Most Time-consuming Nodes: |
| 1. OLAP_SCAN (id=0) : 25m38s (92.44%) |
| 2. OLAP_TABLE_SINK: 2m5s (7.55%) |
| Top Most Memory-consuming Nodes: |
| 1. OLAP_SCAN (id=0) : 14.139 GB |
| NonDefaultVariables: |
| big_query_profile_second_threshold: 0 -> 10 |
| character_set_results: utf8 -> NULL |
| enable_adaptive_sink_dop: false -> true |
| parallel_fragment_exec_instance_num: 1 -> 16 |
| query_timeout: 300 -> 30000 |
| sql_mode_v2: 32 -> 2097184 |
| use_compute_nodes: -1 -> 0 |
| Fragment 0 |
| │ BackendNum: 7 |
| │ InstancePeakMemoryUsage: 182.910 MB, InstanceAllocatedMemoryUsage: 5.645 TB |
| │ PrepareTime: 2.737ms |
| └──OLAP_TABLE_SINK |
| │ TotalTime: 2m5s (7.55%) [CPUTime: 2m5s] |
| │ OutputRows: 51.321M (51320702) |
| │ PartitionType: RANDOM |
| │ Table: xxx_info |
| └──OLAP_SCAN (id=0) |
| Estimates: [row: 44470555, cpu: 13132599556.43, memory: 0.00, network: 0.00, cost: 6566299778.21] |
| TotalTime: 25m38s (92.44%) [CPUTime: 238.113ms, ScanTime: 25m38s] |
| OutputRows: 68.419M (68418665) |
| PeakMemory: 14.139 GB, AllocatedMemory: 7.198 TB |
| Table: : xxx_info_tmp |
| SubordinateOperators: |
| CHUNK_ACCUMULATE |
| NOOP |
| OLAP_SCAN_PREPARE |
| Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime] |
| IOTaskExecTime: 22m18s [min=18m19s, max=25m38s] |
| IOTime: 14m11s [min=10m38s, max=17m56s] |
| LateMaterialize: 6m50s [min=5m7s, max=8m39s] |
| SegmentRead: 14m45s [min=12m34s, max=15m59s] |
| BlockFetch: 14m31s [min=12m21s, max=15m45s] |
| IOTaskWaitTime: 162.659ms [min=120.664ms, max=224.458ms]
- 大多数的查询消耗在磁盘io上，磁盘是ssd还是hdd的。
- 发一下 查询的sql 以及完整的 profile（set is_report_success=true; 执行sql）
- show data from table_name; 看下表的数据量，6千万行数据多少G