AWS RDS First Touch Penalty
According to AWS Documentation
The first time a DB instance is started and accesses an area of disk for the first time, the process can take longer than all subsequent accesses to the same disk area. This is known as the “first touch penalty.” Once an area of disk has incurred the first touch penalty, that area of disk does not incur the penalty again for the life of the instance, even if the DB instance is rebooted, restarted, or the DB instance class changes. Note that a DB instance created from a snapshot, a point-in-time restore, or a read replica is a new instance and does incur this first touch penalty.
Reference : http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html
I captured number of cached pages per database on our SQL Server RDS Instance and rebooted the instance and captured cached pages again.
Based on the documentation, the cached pages should be available and shouldn’t be affected by Reboot process.
Its such a neat feature which makes life lot easier for DBA’s to respond to unexpected situations.
But I noticed significant differences between the number of cached pages before and after reboot.
Cached Pages Before and After SQL Server RDS Reboot
| DBName | Bef_Buf_Pages | Size_MB | Aft_Buf_Pages | Size_MB |
|---|---|---|---|---|
| DatabaseOne | 558482 | 4363 | 596 | 4 |
| DatabaseTwo | 1017 | 7 | 487 | 3 |
| DatabaseThree | 609 | 4 | 201 | 1 |
| master | 190 | 1 | 107 | 0 |
| model | 18 | 0 | 37 | 0 |
| msdb | 882 | 6 | 284 | 2 |
| rdsadmin | 1253 | 9 | 87 | 0 |
| DatabaseFour | 133 | 1 | 59 | 0 |
| Resource Database | 1877 | 14 | 319 | 2 |
| tempdb | 779280 | 6088 | 123 | 0 |
For DatabaseOne , Cached Pages dropped from 558482 to 596.
I am not sure whether others have encountered the same issue. Not sure what to think of the First Touch Penalty Promise to keep the cache intact.
Maybe its not true for SQL Server RDS. 🙂
First Touch Penalty Experience with Docker Containers:
Recently I started running SQL Server docker container on Linux and whenever I want to spin up an image from docker instance, it takes about 10-15 Minutes to get the SQL Server up and running with medium Size Database. The initial commands takes unusually long time and results in numerous 502’s 504’s . Be cognizant of this fact whenever you want to bring an instance to the production workload and these types of response times might be acceptable for certain business cases.
References:
AWS Documentation on First Touch Penalty
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html
Query used to calculate number of pages cached.
http://ramblingsofraju.com/sql-server/sql-server-cached-pages/
AWS Discussion Forum
https://forums.aws.amazon.com/thread.jspa?threadID=227936
Another soul with similar Issue
https://digitalovercast.wordpress.com/2016/03/22/first-touch-penalty/