Performance Testing: Self-built Databases vs. ApsaraDB for RDS

Image for post
Image for post

Abstract: Poorly configured ApsaraDB instances may appear to underperform compared with self-built databases. This article discusses how to troubleshoot this problem in your databases.

Recent comparisons of self-built databases with RDS show that self-built databases tend to have higher query speeds than those seen on RDS instances. Take a logistics company for example. At the very beginning, the ApsaraDB for RDS SQL server database accesses the RDS instance via ECS and executes a statement which takes about 60s, however the RDS SQL server database accesses the local self-built databases on the ECS in only 2–3s. So, is ApsaraDB RDS really worse than self-built databases? Next, we will discuss the key points for comparing self-built databases with RDS and how to compare their performances fairly.

The following factors should be taken into account when comparing the statement execution performance between self-built databases and RDS:

1. Zones and Network Connections

For an RDS SQL Server 2012 instance, the SQL Server Profiler can be enabled to capture events such as RPC:Completed, SQL:StmtStarting, SQL:StmtCompleted, SQL:BatchStarting, and SQL:BatchCompleted.

The SQL Server Profiler cannot currently be enabled for RDS SQL Server 2008 R2 instances, however the following statement can query the start_time and total_elapsed_time for recently executed statements. total_elapased_time represents the total time required to execute the statement after it reaches the SQL Server (the unit is ms).

2. Instance Parameter Configuration

3. Resource Waiting or Blocking

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

Refer to Solutions to blocking of RDS for SQL Server (article in Chinese) for blocking.

4. Whether the index fragmentation rate and statistical information are consistent across the two environments

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

A high index fragmentation rate can negatively affect query speed. If the index fragmentation rate is between 5% and 30%, it is recommended to restructure the index. If the index fragmentation rate is greater than 30%, the index should be rebuilt.

Refer to the following statement for checking statistical information:

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'

If the statistical information in the RDS is older than that in the self-built database, it can then be updated manually, thereby preventing the SQL Server from generating incorrect and inefficient implementation plans based on obsolete statistical information.

5. High Availability Architecture

Troubleshooting:

  1. Network latency is low.
  2. The MAXDOP value of the RDS is 2, and the MAXDOP value for the self-built ECS instance is default, i.e., the parallel statement can be applied to manage as many parallel threads as possible.
    It can be seen from the implementation plan that the DOP for a query statement in RDS is 2, while the DOP for a query statement in the self-built database is 8, meaning that execution on RDS is slower than on the self-built database.
Image for post
Image for post
Image for post
Image for post

Solutions:

Reference:

https://www.alibabacloud.com/blog/Performance-Testing-Self-built-Databases-vs-ApsaraDB-for-RDS_p279162?spm=a2c41.11184614.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store