從創建索引過程中記憶體變化來看SQL Server與MySQL的記憶體淘汰演算法

在sqlserver中，幾年之前就註意到一個現象：sqlserver中對一個大表創建索引或者rebuild索引的過程中，會引起記憶體劇烈的動蕩，究其原因為何，這種現象到底正不正常，是不是sqlserver記憶體管理存在缺陷？另外，最近剛好想到跟MySQL對比一下類似操作引起的記憶體變化，測試MySQL會不 ...

在sqlserver中，幾年之前就註意到一個現象：sqlserver中對一個大表創建索引或者rebuild索引的過程中，會引起記憶體劇烈的動蕩，究其原因為何，這種現象到底正不正常，是不是sqlserver記憶體管理存在缺陷？
另外，最近剛好想到跟MySQL對比一下類似操作引起的記憶體變化，測試MySQL會不會有類似問題，這裡就簡單寫個代碼驗證一下這個問題。

資料庫是一個非常依賴記憶體資源的軟體系統，通過緩存數據（索引）到記憶體中，來改善數據物理訪問的性能問題，

但是記憶體往往又不是無限大，或者足以容納所有相關數據的容量，因此就存在記憶體頁面的淘汰問題。
記憶體頁的淘汰演算法，多數是遵循LRU演算法，LRU是Least Recently Used的縮寫，也即遵循“最近做少使用”的原則，選擇最近最久未使用的頁面予以淘汰。
這個演算法錶面上看起來沒什麼問題，如果有註意觀察過在一臺相對穩定的伺服器上，給大表創建索引的過程，就會發現，整個過程中，buffer pool會發生劇烈的動蕩，創建索引的表會迅速侵入記憶體，擠走記憶體中原本的緩存。

由於SQLServer作為商業資料庫，有關於它的頁面淘汰演算法的研究較少，僅僅是指導一個大概是遵循LRU的原則的，但是有沒有在LRU的基礎上進行改進或者優化，就不得而知，
但是SQLServer究竟有沒有對該問題做改進或者優化？這裡從一個索引的創建來管中窺豹，從側面驗證一下這個演算法。

這裡需要藉助SQLServer中的一個變數值：Page life expectancy，
相信稍微熟悉SQLServer一點的人應該都知道這個參數代表的意義：記憶體頁面的平均滯留時間，如果記憶體頁面不斷地被置換出去，這個值將會維持不變或者變得更小，因為新載入記憶體的頁面在記憶體中停留的時間是較短的。
不知道有沒有人註意過，在一臺記憶體相對穩定的伺服器上，對大表（1000W+）創建索引的時候，Page life expectancy這個變數值會急轉直下，這說明瞭什麼？
大表創建索引粗略講是讀數據，然後寫數據（索引樹）的過程，這個過程中必然將相關的表讀入記憶體，那麼讀入記憶體之後，他有沒有淘汰記憶體中已有的數據？|
如果有，這明顯是不合理的，創建索引只是創建索引，目的不是把記憶體中已有的熱數據擠走，但是它還真的給記憶體中已有的熱數據給擠走了。

反觀MySQL（Innodb引擎），Redis等資料庫，都是基於優化的LRU或者LFU的原則淘汰頁面。
MySQL甚至可以人為地去調整這個LFU演算法的一些參數值（innodb_old_blocks_pct，innodb_old_blocks_time），來達到優化記憶體淘汰的目的。
MySQL中雖然沒有類似於PLE的參數，但是可以從其他參數來間接推斷，如果發生同樣的操作，相關的表會不會擠走記憶體中的熱數據.
這裡基於MySQL information_schema.innodb_buffer_pool_stats這張表來作分析，其中這個表有兩個欄位，pages_made_young, pages_not_made_young ，這兩個的變化代表這個新進入記憶體中的頁面冷熱變化情況。
同樣的道理，如果記憶體中充斥著大量的熱點數據，在對一個大表創建索引的過程中，並不希望因為創建索引而把熱點數據擠出記憶體，究竟是不是這樣的，同樣在創建索引的過程中，觀察一下這兩個值的變化情況就可以了。

測試方法

這裡通過迴圈，以5秒為間隔，連續輸出sqlserver中的Page life expectancy這個變數的值，以及MySQL中的pages_made_young和pages_not_made_young。

#coding=utf-8
import threading
import pymssql
import pymysql
from time import ctime,sleep
import datetime
import time

mssql_conn_conf = {'host': '***.***.***.***', 'port': 1433,  'db': 'master'}
mysql_conn_conf = {'host': '***.***.***.***', 'port': 3306, 'user': 'root', 'password': '***', 'db': 'information_schema'}

def mssql_ple():
    conn = pymssql.connect(host=mssql_conn_conf['host'], port=mssql_conn_conf['port'], database=mssql_conn_conf['db'])
    cursor = conn.cursor()
    try:
        cursor.execute(" select cntr_value from sys.dm_os_performance_counters where object_name = 'MSSQL$SQL2014:Buffer Manager' and counter_name = 'Page life expectancy' ")
        row = cursor.fetchone()
        print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+ '------>'+str(row[0]))
    except pymssql.Error as e:
        print("mysql execute error:", e)
    cursor.close()
    conn.close()

def mysql_memory():
    conn = pymysql.connect(host=mysql_conn_conf['host'], port=mysql_conn_conf['port'], database=mysql_conn_conf['db'],user=mysql_conn_conf['user'],password = mysql_conn_conf['password'])
    cursor = conn.cursor()
    try:
        cursor.execute('''
                            SELECT 
                                SUM(pages_made_young) AS total_pages_made_young,
                                SUM(pages_not_made_young) AS total_pages_not_made_young
                            FROM
                            (
                                SELECT  pages_made_young, pages_not_made_young 
                                FROM information_schema.innodb_buffer_pool_stats
                            )t;
                        ''')
        row = cursor.fetchone()
        print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+ '------>'+'made_young:'+str(row[0])+' not_made_young:'+str(row[1]))
    except pymssql.Error as e:
        print("mysql execute error:", e)
    cursor.close()
    conn.close()


if __name__ == '__main__':
    while 1>0:
        mysql_memory()
        time.sleep(5)

SQLServer中的PLE變化測試

其實很容易觀察，對於一臺沒有負載的伺服器，因為沒有新的記憶體頁面載入記憶體，它的Page life expectancy值是遞增的的，這個變數的單位是秒，間隔一秒，這個值會自動加1。
一旦有新的頁面載入記憶體，如果記憶體已經被用完，隨著記憶體中已有的頁面淘汰出去，這個值是會自動遞減的，或者出現斷崖式的下降。
這裡運行上述腳本，列印出來當前伺服器的Page life expectancy值，稍等一段時間後，在某個大表上創建出一個索引，再觀察這個值的變化情況，

step1，對DB01庫上的表進行反覆的查詢，使其載入記憶體（最近較多使用），左圖是DB01庫占用的記憶體情況，
step2，在DB02庫上對一張大表創建索引，此過程中中會發現創建索引的表會迅速將已換成的數據擠出記憶體

MySQL中的pages_made_young和page_not_made_young測試

因筆者事前重啟過實例，因此made_young的值很小，關鍵要看，在某個大表上創建索引的過程中是不是會大量的made_young就行了。
這裡可以看到，在創建索引開始之後，會出現大量的not_made_young,實際上這種效果是預期的，僅僅是創建索引，而不是順帶讓當前這個大表的數據擠走熱點數據（並沒有大批量的made_young）

這裡也給出在db02上創建索引前後兩個庫占用的記憶體情況，雖然db02在其某個大表上創建索引之後占用了一定量的記憶體，但是這部分記憶體並非熱數據，是隨時可以被擠出buffer pool的，因為他們沒有page_made_young
step1，對db01庫上的表進行的多次查詢，使其載入記憶體，左圖是db01庫占用的記憶體情況，
step2，在db02庫上對一張大表創建索引，此過程中中會發現不斷地有大量的page_not_made_young,另外原本的db01庫的記憶體並沒有被大量的擠出。