Paging và Memory management
Quản lý bộ nhớ là một phần quan trọng trong quản trị database. Nhiều máy chủ database của các công ty nhỏ và vừa có thể gặp trường hợp bị phân trang bộ nhớ (paged out) dẫn đến hệ thống bị chậm. Nguyên nhân là thiếu RAM vật lý hoặc cấu hình chưa tối ưu.Khi paging xảy ra, hệ điều hành và các ứng dụng sử dụng thêm bộ nhớ nhớ ảo lưu trên đĩa cứng với tốc độ chậm hơn nhiều so với RAM vật lý.
Kiểm tra virtual memory và paging
Để kiểm tra mức sử dụng virtual memory của hệ điều hành Windows, ta sử dụng lệnh:wmic pagefile list
AllocatedBaseSize CurrentUsage Description Name PeakUsage
10529 1366 C:\pagefile.sys C:\pagefile.sys 1539
Chỉ số CurrentUsage chính là dung lượng sử dụng thực tế. Pagefile.sys chính là tên file của bộ nhớ ảo trên đĩa cứng. Trong ví dụ này, hệ điều hành phân bổ dung lượng bộ nhớ ảo 10529 MB, và đang sử dụng 1366 MB, mức sử dụng cao nhất 1539 MB.
Để kiểm tra khả năng SQL Server bị phân trang hay không, có nhiều cách để kiểm tra và khá phức tạp, tuy nhiên có một eventlog quan trọng có ID là 17890 thông báo trực tiếp vấn đề này, kiểm tra nhanh bằng lệnh powershell:
Get-WinEvent -FilterHashtable @{LogName="Application"; ID=17890}
Cách để chống paging
Cấu hình max server memory trên SQL Server để ngăn không cho SQL Server chiếm dụng bộ nhớ quá lớn. Mặc định, SQL Server có thể tiêu tốn lượng RAM quá mức dẫn đến hệ điều hành không đủ RAM vật lý nên phải sử dụng đến virtual memory trên đĩa cứng.Cấu hình Lock pages in memory (LPIM) cho tài khoản dịch vụ SQL Server để ngăn hệ điều hành và các ứng dụng khác tranh chấp RAM vật lý với SQL Server, điều này đảm bảo SQL Server luôn được ưu tiên sử dụng RAM vật lý.
Cấu hình max server memory trên SQL Server
Khuyến nghị chung là nên thiết lập ở mức 75% bộ nhớ. Tuy nhiên trong thực tế, chúng ta cần theo dõi hệ thống để có số liệu chính xác và điều chỉnh thiết lập.
Công thức tính:
- 1 GB of RAM for the OS
- 1 GB of RAM per every 4 GB of RAM installed (up to 16-GB RAM)
- 1 GB of RAM per every 8-GB RAM installed (above 16-GB RAM)
Việc thiết lập có thể thực hiện bằng giao diện hoặc bằng dòng lệnh, ví dụ thiết lập max server memory là 13GB:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 13312;
GO
RECONFIGURE;
GO
Cấu hình Lock pages in memory (LPIM)
SELECT service_account FROM sys.dm_server_services
Sau đó cấu hình Group Policy theo đường dẫn:
Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Lock pages in memory
Hiệu quả sau khi thay đổi cấu hình
Trước khi thay đổi cấu hình, sau khoảng 5 ngày thì hệ điều hành sử dụng 50% page file, sau 20 ngày sử dụng đến 80%.Theo chu kỳ chưa đến 1 tháng, phần mềm kết nối vào database này để truy xuất thông tin bị chậm. Và thông thường, người quản trị hệ thống có thể khởi động lại máy chủ để tạm thời giải quyết vấn đề này.
Sau khi thay đổi cấu hình, hệ thống gần như không sử dụng page file dù cho RAM vật lý chỉ còn rất ít (1.17 GB / 17BG):
Truy vấn thông tin sử dụng bộ nhớ bằng SSMS:
SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024) AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;
--SQL Server memory
SELECT
physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
page_fault_count AS sql_page_fault_count,
memory_utilization_percentage AS sql_memory_utilization_percentage,
process_physical_memory_low AS sql_process_physical_memory_low,
process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
--Windows OS pagefile
DECLARE @Result TABLE (WindowsPageFile NVARCHAR(4000));
INSERT INTO @Result
EXEC xp_cmdshell 'powershell -command "Get-CimInstance Win32_PageFileUsage | Select-Object Name,AllocatedBaseSize,CurrentUsage"';
SELECT WindowsPageFile
FROM @Result
WHERE WindowsPageFile IS NOT NULL;
Kết luận
Đối với các máy chủ database SQL Server, cơ chế quản lý bộ nhớ tự động giữa hệ điều hành Windows và SQL Server thường không tối ưu cho hiệu năng truy xuất database, hệ thống nhanh chóng sử dụng bổ nhớ ảo trên đĩa cứng gây ra tình trạng phần mềm hoạt động chậm chạp.Bài viết này đã đề cập đến 2 cấu hình quan trọng là LPIM và max server memory để ngăn paging xảy ra, một phần quan trọng trong hành trình quản trị và tối ưu hiệu suất của máy chủ cơ sở dữ liệu.
Tài liệu tham khảo:
Enable the Lock pages in memory option (Windows)
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15
MSSQLSERVER_17890
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-17890-database-engine-error?view=sql-server-ver16
Server memory configuration options
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16
SQL Server Design Considerations
https://learn.microsoft.com/en-us/system-center/scom/plan-sqlserver-design?view=sc-om-2022
Không có nhận xét nào:
Đăng nhận xét