從創建索引程序中記憶體變化來看SQL Server與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庫的記憶體并沒有被大量的擠出，

總結
以個人淺薄的經歷以及測驗程序，發現sqlserver的記憶體管理，與MySQL相比，一直停留在小學二年級的水平？其buffer pool管理本身的演算法就存在問題，又是一個黑盒，也沒有人為可以調整的可能性，

sqlserver再不加油，真的就沒人用了……

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/52415.html

標籤：MySQL

上一篇：Win10 下載、安裝 Navicat 15并激活

下一篇：為什么pymysql執行SQL陳述句后提示成功，但沒有實際操作資料庫？