近期经常遇到用户将自建数据库与RDS进行对比,简单的对比结果是自建库比RDS实例查询快。我们这里来看看一个实例,有一家物流公司,刚开始使用RDS SQL Server数据库,发现通过ECS访问RDS实例,执行语句需要60s左右,但是访问ECS本地自建库只需要2-3s。那么RDS是否是真的不如自建数据库呢? 接下来,我们来探讨对比自建库和RDS的正确姿势,如何公平地对比自建库和RDS的性能。
对比自建库和RDS的语句执行性能,下面这些因素必须都考虑到:
针对RDS SQL Server 2012实例,可以开启SQL Server Profiler, 抓取RPC:Completed, SQL:StmtStarting, SQL:StmtCompleted, SQL:BatchStarting和SQL:BatchCompleted这几个事件。
针对RDS SQL Server 2008 R2实例,暂不支持开启SQL Server Profiler, 可以通过下面语句查询近期执行的语句,及其start_time和total_elapased_time 。total_elapased_time指请求到达SQL Server后执行该语句总共消耗的时间(单位ms)。
SQL Server实例需要关注的参数主要有fill factor (%),max degree of parallelism和max server memory (MB)。
Fill Factor(%):
这是一个用于调优数据存储和性能的server_side参数,当创建或者重建索引时,该值可以确定每个叶级页上要填充数据的空间百分比,以保留一些剩余空间作为以后扩展索引的可用空间。
Max Degree of Parallism(MaxDOP):
限制并行计划执行时所用的处理器数量,即限制语句的并行度。
Max Server Memory(MB):
设置buffer pool获取的内存的上限。
查看等待情况:
RDS SQL Server为了保证主备数据的一致性,采用的是High Safety同步模式,相对于High Performance 模式,性能有所牺牲,但是增强了高可用性,保护了数据。
同时, RDS 还提供多可用区主备实例,双节点在不同机房,更进一步保证了高可用性和安全性。
问题排查:
1. RDS实例的内存配置比本地高;
2. 网络延迟不大;
3. RDS的MAXDOP值是2,而ECS自建实例是默认值,即并行语句可以申请尽可能多的并行线程。
通过执行计划看出,RDS中查询语句的并行度是2,而自建库中查询语句的并行度是8,RDS中执行速度自然没有自建库快。
解决方案:
1. 上述我们分析到,RDS的配置实际比自建库高,那么可以在RDS 实例控制台的参数设置中,增加max degree of parallelism值来提升并行度。
2. 通过执行计划,还发现用户表中有Missing Index。 在RDS添加了Missing Index后,查询性能有大幅度提升,即使并行度为2,也可以在5s执行完毕。
对比自建库和RDS的语句执行性能,下面这些因素必须都考虑到:
1. 可用区和网络链路。
可用区、网络链路的分析参考 性能测试:自建数据库对比RDS中应当注意的地方即可,这篇文章做了深刻的分析。如果需要验证网络因素,可以在RDS中开启profiler, 并且同时在客户端抓取网络包,对比RDS中执行结束的时间以及网络包中返回结果的时间, 时间差异较大,说明网络传输有延迟。针对RDS SQL Server 2012实例,可以开启SQL Server Profiler, 抓取RPC:Completed, SQL:StmtStarting, SQL:StmtCompleted, SQL:BatchStarting和SQL:BatchCompleted这几个事件。
针对RDS SQL Server 2008 R2实例,暂不支持开启SQL Server Profiler, 可以通过下面语句查询近期执行的语句,及其start_time和total_elapased_time 。total_elapased_time指请求到达SQL Server后执行该语句总共消耗的时间(单位ms)。
2. 实例参数配置。
MySQL实例需要关注的参数比较多,详细的分析和描述参考 性能测试:自建数据库对比RDS中应当注意的地方;SQL Server实例需要关注的参数主要有fill factor (%),max degree of parallelism和max server memory (MB)。
Fill Factor(%):
这是一个用于调优数据存储和性能的server_side参数,当创建或者重建索引时,该值可以确定每个叶级页上要填充数据的空间百分比,以保留一些剩余空间作为以后扩展索引的可用空间。
Max Degree of Parallism(MaxDOP):
限制并行计划执行时所用的处理器数量,即限制语句的并行度。
Max Server Memory(MB):
设置buffer pool获取的内存的上限。
3. 资源等待或者阻塞情况
两个环境中,语句执行过程中,需要对比,是否有等待和阻塞情况发生。查看等待情况:
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
-- Maybe uncomment these four if you have mirroring issues
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
-- Maybe uncomment these six if you have AG issues
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
N'ONDEMAND_TASK_QUEUE',
N'PREEMPTIVE_XE_GETTARGETSTATE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_RECOVERY',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO
查看阻塞情况,参考RDS for SQL Server 阻塞问题处理方法。
4. 两个环境中索引碎片率和统计信息是否一致。
检查索引碎片率语句如下:SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
索引碎片率大,影响查询的速度。如果索引碎片率在5%-30#之间,推荐重组索引;如果碎片率大于30%,推荐重建索引。
检查统计信息语句如下:
SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated
FROM sys.[stats] AS s JOIN sys.[tables] AS t ON [s].[object_id] = [t].[object_id] WHERE t.type = 'u'
如果发现RDS中统计信息比自建库要老旧,可以手动更新下统计信息。 防止由于统计信息老旧,造成SQL Server生成不准确的执行计划,降低执行效率。
5. 高可用性架构
RDS作为一个公共的关系数据库服务,首要是保证稳定高可用,高安全,保证用户使用的是既安全又稳定的服务。然后才是高性能。RDS SQL Server为了保证主备数据的一致性,采用的是High Safety同步模式,相对于High Performance 模式,性能有所牺牲,但是增强了高可用性,保护了数据。
同时, RDS 还提供多可用区主备实例,双节点在不同机房,更进一步保证了高可用性和安全性。
问题排查:
1. RDS实例的内存配置比本地高;
2. 网络延迟不大;
3. RDS的MAXDOP值是2,而ECS自建实例是默认值,即并行语句可以申请尽可能多的并行线程。
通过执行计划看出,RDS中查询语句的并行度是2,而自建库中查询语句的并行度是8,RDS中执行速度自然没有自建库快。
![1](http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/a5ecde4714795fa73f523d0fc6925fc6.png?x-oss-process=image/resize,w_1400/format,webp)
![2](http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/f183dd96a3729e3f18b8379b0f66212f.png?x-oss-process=image/resize,w_1400/format,webp)
解决方案:
1. 上述我们分析到,RDS的配置实际比自建库高,那么可以在RDS 实例控制台的参数设置中,增加max degree of parallelism值来提升并行度。
2. 通过执行计划,还发现用户表中有Missing Index。 在RDS添加了Missing Index后,查询性能有大幅度提升,即使并行度为2,也可以在5s执行完毕。