从创建索引过程中内存变化来看SQL Server与MySQL的内存淘汰算法

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相比，这一点做的确实不到位，与MySQL相比，其buffer pool管理本身的算法就存在问题，又是一个黑盒，也没有人为可以调整的可能性。

 

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

从创建索引过程中内存变化来看SQL Server与MySQL的内存淘汰算法

标签：读数   image   and   代码   cut   页面   lru算法   测试   推断