性能最佳做法和配置指南Performance best practices and configuration guidelines

Azure SQL Edge 提供了几种可用于提高 SQL Edge 部署性能的特性和功能。Azure SQL Edge offers several features and capabilities that can be used to improve the performance of your SQL Edge deployment. 本文提供了一些最佳做法和建议,以最大程度地提高性能。This article provides some best practices and recommendations to maximize performance.

最佳做法Best practices

配置多个 tempdb 数据文件Configure multiple tempdb data files

默认情况下,Azure SQL Edge 在容器初始化过程中仅创建一个 tempdb 数据文件。Azure SQL Edge by default creates only one tempdb data file as part of the container initialization. 建议在部署后考虑创建多个 tempdb 数据文件。We recommend that you consider creating multiple tempdb data files post deployment. 有关详细信息,请参阅建议以减少 SQL Server tempdb 数据库中的分配争用一文。For more information, see the guidance in the article, Recommendations to reduce allocation contention in SQL Server tempdb database.

尽可能使用聚集列存储索引Use Clustered columnstore indexes where possible

IoT 和 Edge 设备往往会生成大量数据,这些数据通常在某个时间范围内汇总,以便进行分析。IoT and Edge devices tend to generate high volume of data that is typically aggregated over some time window for analysis. 无论进行哪种分析,都很少使用单个数据行。Individual data rows are rarely used for any analysis. 列存储索引非常适合存储和查询此类大型数据集。Columnstore indexes are ideal for storing and querying such large datasets. 此索引使用基于列的数据存储和查询处理,与面向行的传统存储相比,最多可实现 10 倍的查询性能提升。This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance over traditional row-oriented storage. 此外,与处理非压缩数据相比,处理压缩数据可将性能提升 10 倍。You can also achieve gains up to 10 times the data compression over the uncompressed data size. 有关详细信息,请参阅列存储索引For more information, see Columnstore Indexes

此外,其他 Azure SQL Edge 功能(如数据流式处理和数据保留)将受益于针对数据插入和数据删除进行的列存储优化。Additionally, other Azure SQL Edge features like data streaming and Data retention benefit from the columnstore optimizations around data insertion and data removal.

简单恢复模式Simple recovery model

由于边缘设备上的存储可能受限,因此默认情况下,Azure SQL Edge 中的所有用户数据库都使用简单恢复模式。Since storage can be constrained on edge devices, all user databases in Azure SQL Edge use the Simple Recovery model by default. 简单恢复模式会自动回收日志空间以减少空间需求,实际上不再需要管理事务日志空间。Simple recovery model automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. 在可用存储有限的边缘设备上,这可能很有用。On edge devices with limited storage available, this can be helpful. 有关简单恢复模式和其他可用恢复模式的详细信息,请参阅恢复模式For more information on the simple recovery model and other recovery models available, see Recovery Models

简单恢复模式不支持需要事务日志备份的日志传送和时间点还原等操作。Operations like Log Shipping and Point-In-time-restores, that require transaction log backups are not supported by the simple recovery model.

高级配置Advanced configuration

设置内存限制Setting memory limits

对于缓冲池,Azure SQL Edge 最多支持 64 GB 内存,而 SQL Edge 容器中运行的附属进程可能需要更多内存。Azure SQL Edge supports up to 64 GB of memory for the buffer pool, while additional memory may be required by satellite processes running within the SQL Edge container. 在内存有限的小型边缘设备上,建议限制 SQL Edge 容器可用的内存,以便 Docker 主机和其他边缘进程或模块可以正常运行。On smaller edge devices with limited memory, it is advisable to limit the memory available to SQL Edge containers, such that the docker host and other edge processes or modules can function properly. 可以使用以下机制控制 SQL Edge 可用的总内存。The total memory available for SQL Edge can be controlled using the following mechanisms.

  • 限制 SQL Edge 容器可用的内存:可以通过使用容器运行时配置选项 --memory 来限制 SQ​​L Edge 容器可用的总内存。Limiting memory available to the SQL Edge Containers: The total memory available to the SQL Edge container can be limited by using the container runtime configuration option --memory. 有关限制 SQL Edge 容器可用内存的详细信息,请参阅内存、CPU 和 GPU 的运行时选项For more information on limiting memory available to the SQL Edge container, see Runtime options with Memory, CPUs, and GPUs.

  • 限制容器内 SQL 进程可用的内存:默认情况下,容器内的 SQL 进程仅使用 80% 的可用物理 RAM。Limiting memory available to SQL process within the container: By default, the SQL process within the container uses only 80% of the physical RAM available. 对于大多数部署,可以使用默认配置。For majority of the deployments, the default configuration will be fine. 但在某些情况下,数据流式处理和 SQL Edge 容器中运行的 ONNX 进程可能需要额外的内存。However, there can be scenarios where additional memory might be required for the data streaming and the ONNX processes running inside the SQL Edge containers. 在这种情况下,可以使用 mssql-conf 文件中的 memory.memorylimitmb 设置来控制 SQL 进程可用的内存。In such scenarios, the memory available to the SQL process can be controlled using the memory.memorylimitmb setting in the mssql-conf file. 有关详细信息,请参阅使用 mssql.conf 文件进行配置For more information, see Configure using mssql.conf file.

设置内存限制时,注意不要将此值设置得太低。When setting the memory limits, be careful to not set this value too low. 如果没有为 SQL 进程设置足够的内存,可能会严重影响 SQL 性能。If you do not set enough memory for the SQL process, it can severely impact SQL performance.

延迟持续性Delayed durability

Azure SQL Edge 中的事务可以是完全持久事务、SQL Server 默认事务或延迟持久事务(也称作迟缓提交)。Transactions in Azure SQL Edge can be either fully durable, the SQL Server default, or delayed durable (also known as lazy commit).

完全持久事务提交是同步的,仅在事务的日志记录写入磁盘后报告提交成功,并将控制权归还客户端。Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. 延迟持久事务提交是异步的,并在事务的日志记录写入磁盘之前报告提交成功。Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. 事务要成为持久事务,必须将事务日志条目写入磁盘。Writing the transaction log entries to disk is required for a transaction to be durable. 当事务日志条目刷新到磁盘时,延迟持久事务成为持久事务。Delayed durable transactions become durable when the transaction log entries are flushed to disk.

在可以容忍部分数据丢失的部署中或在存储速度较慢的边缘设备上,可以使用延迟持久性来优化数据引入和基于数据保留的清理。In deployments where some data loss can be tolerated or on edge devices with slow storage, delayed durability can be used to optimize data ingestion and data retention-based cleanup. 有关详细信息,请参阅控制事务持续性For more information, see Control Transaction Durability.

Linux OS 配置Linux OS configurations

请考虑使用以下 Linux 操作系统配置设置,以体验 SQL 安装的最佳性能。Consider using the following Linux Operating System configuration settings to experience the best performance for a SQL Installation.