Giới hạn bộ nhớ cơ sở dữ liệu MySQL

Vì bạn chỉ có một lượng nhỏ dữ liệu nên không cần tăng key_buffer_size hoặc innodb_buffer_pool_size. Điều này sẽ được giải quyết lại nếu bạn tăng đáng kể kích thước tập dữ liệu

Tăng ulimit -n trong HĐH; . Điều này sẽ cho phép một số bộ đệm lớn hơn. table_open_cache [tôi cho là] ​​được tự động hóa thành 407, nhưng điều này có vẻ quá thấp. Bằng cách tăng giới hạn hệ điều hành, bộ đệm có thể sẽ được tự động kích thước lớn hơn

Một số truy vấn dường như kém hiệu quả hơn mức có thể. Nếu bạn có thể xác định như vậy, hãy thảo luận về chúng. Điều này có thể liên quan đến các chỉ mục tốt hơn, chỉ mục tổng hợp, tránh TEXT, tránh SELECT *, v.v.

Chi tiết và các quan sát khác

[ Opened_tables ] = 8,715,435 / 245130 = 36 /sec -- Tần suất mở Tables -- tăng table_open_cache

[ open_files_limit ] = 1,024 -- ulimit -n -- Để cho phép nhiều tệp hơn, hãy thay đổi ulimit hoặc /etc/security/limits. conf hoặc trong sysctl. conf [kern. tệp tối đa & kern. maxfilesperproc] hoặc cái gì khác [phụ thuộc vào hệ điều hành]

[ Table_open_cache_overflows ] = 8,713,321 / 245130 = 36 /sec -- Có thể cần tăng table_open_cache

key_buffer_size0 -- Có thể cần tăng table_open_cache

key_buffer_size1 -- THAY ĐỔI và TỐI ƯU HÓA có thể được tăng tốc đáng kể bằng cách sử dụng BẬT. -- Có lẽ tốt hơn là BẬT

key_buffer_size2 -- Để phân tích cú pháp. Phần trăm RAM -- 16K

key_buffer_size3 -- Để phân tích cú pháp. Phần trăm RAM -- 16K

key_buffer_size4 -- local_infile = ON là một vấn đề bảo mật tiềm ẩn

key_buffer_size5 -- Phần trăm key_buffer được sử dụng. cao-water-mark. -- Giảm key_buffer_size để tránh sử dụng bộ nhớ không cần thiết

key_buffer_size6 -- Hiệu quả ghi của key_buffer -- Nếu bạn có đủ RAM, bạn nên tăng key_buffer_size

key_buffer_size7 -- Tốc độ đọc chỉ mục MyISAM [từ đĩa] -- Nếu bạn có đủ RAM, bạn nên tăng key_buffer_size

key_buffer_size8 -- Tỷ lệ I/O của chỉ mục MyISAM -- Nếu bạn có đủ RAM, bạn nên tăng key_buffer_size

key_buffer_size9 -- Tần suất tạo các bảng "tạm thời" của đĩa như một phần của CHỌN phức tạp -- tăng tmp_table_size và max_heap_table_size. Kiểm tra các quy tắc cho các bảng tạm thời khi MEMORY được sử dụng thay vì MyISAM. Có lẽ những thay đổi nhỏ về lược đồ hoặc truy vấn có thể tránh được MyISAM. Các chỉ mục tốt hơn và định dạng lại các truy vấn có nhiều khả năng giúp ích hơn

innodb_buffer_pool_size0 -- Phần trăm truy vấn cần có trên bảng tmp trên đĩa. -- Chỉ mục tốt hơn / Không có đốm màu / v.v.

innodb_buffer_pool_size1 -- Xóa/Chèn [dưới dạng pct]. [Bỏ qua TẢI, THAY THẾ, v.v. ]

innodb_buffer_pool_size2 -- quét toàn bộ bảng -- Thêm chỉ mục/tối ưu hóa truy vấn [trừ khi chúng là các bảng nhỏ]

innodb_buffer_pool_size3 -- % lựa chọn thực hiện quét toàn bộ bảng. [Có thể bị lừa bởi Stored Routines. ] -- Thêm chỉ mục/tối ưu hóa truy vấn

Vậy bạn có thể làm gì thay thế? . Nếu bạn đang sử dụng MyISAM một cách nghiêm túc, bạn cũng có thể thêm kích thước của bộ đệm Hệ thống Hoạt động mà bạn muốn MySQL sử dụng cho bảng của mình. Lấy số này thêm vào số bộ nhớ Hệ điều hành và các ứng dụng khác cần, thêm có thể là 32MB nữa cho mã Máy chủ MySQL và các bộ đệm tĩnh nhỏ khác nhau. Đây là bộ nhớ mà bạn có thể cân nhắc sử dụng khi mới khởi động MySQL Server. Phần còn lại của bộ nhớ có sẵn cho các kết nối. Ví dụ: với máy chủ 8GB, bạn có thể có mọi thứ được liệt kê thêm tối đa 6GB, do đó, bạn còn 2 GB cho chuỗi của mình

Mỗi luồng kết nối với máy chủ MySQL sẽ cần bộ đệm riêng. Khoảng 256K được phân bổ cùng một lúc ngay cả khi luồng không hoạt động - chúng được sử dụng theo ngăn xếp luồng mặc định, bộ đệm mạng, v.v. Nếu giao dịch được bắt đầu, một số dung lượng khác có thể tăng lên. Việc chạy các truy vấn nhỏ có thể chỉ làm tăng mức tiêu thụ bộ nhớ cho một luồng nhất định, tuy nhiên, nếu bảng sẽ thực hiện các thao tác phức tạp như quét toàn bộ bảng, sắp xếp hoặc cần các bảng tạm thời thì bộ nhớ có thể được phân bổ nhiều như read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size. Nhưng chúng chỉ được phân bổ theo nhu cầu và được giải phóng sau khi hoàn thành giai đoạn truy vấn nhất định. Một số trong số chúng được phân bổ thành một đoạn duy nhất cùng một lúc, ví dụ tmp_table_size là dung lượng bộ nhớ tối đa mà MySQL sẽ phân bổ cho thao tác này. Lưu ý rằng nó phức tạp hơn một lần bạn có thể nghĩ - nhiều bộ đệm cùng loại có thể được phân bổ cho Exampe để xử lý các truy vấn con. Đối với một số truy vấn đặc biệt, việc sử dụng bộ nhớ có thể còn lớn hơn – việc chèn số lượng lớn có thể phân bổ các byte bộ nhớ Bulk_insert_buffer_size nếu được thực hiện cho các bảng MyISAM. myisam_sort_buffer_size được sử dụng cho các lệnh ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE

Đối với các ứng dụng OLTP có mức tiêu thụ bộ nhớ truy vấn đơn giản thường ít hơn 1MB cho mỗi luồng với bộ đệm mặc định và bạn thực sự không cần phải tăng bộ đệm cho mỗi luồng trừ khi bạn có các truy vấn phức tạp. Sắp xếp 10 hàng sẽ nhanh như với bộ đệm sắp xếp 1 MB như với 16 MB [thực ra 16 MB có thể còn chậm hơn nhưng đó là một câu chuyện khác]

Một cách tiếp cận khác mà bạn có thể thực hiện là đưa ra dung lượng bộ nhớ mà bạn muốn Máy chủ MySQL sử dụng ở mức cao nhất. Điều này có thể được tính toán dễ dàng bằng bộ nhớ cần thiết cho HĐH, Bộ đệm tệp và các ứng dụng khác. Đối với môi trường 32 bit, bạn cũng nên tính đến giới hạn 32 bit và có thể giới hạn kích thước “mysql” ở mức khoảng 2. 5GB [con số chính xác phụ thuộc vào rất nhiều yếu tố]. Bây giờ bạn có thể sử dụng “ps aux” để xem VSZ – Bộ nhớ ảo được cấp phát bởi quy trình MySQL. Bạn cũng có thể xem “Resident Memory” nhưng tôi thấy nó ít hữu ích hơn vì nó có thể bị lỗi do hoán đổi – không phải thứ bạn muốn xem. Theo dõi cách giá trị thay đổi để bạn biết yêu cầu bộ nhớ với cài đặt hiện tại và tăng/giảm giá trị một cách thích hợp

Một số người có thể nói, Này, chúng tôi muốn đảm bảo 100% rằng máy chủ của chúng tôi sẽ không bao giờ hết bộ nhớ, bất kể truy vấn hoặc người dùng nào sẽ quyết định chạy. Thật không may, điều này gần như không thể là không thực tế. Đây là lý do tại sao

Danh sách các Yêu cầu về Bộ nhớ Máy chủ MySQL hiếm khi được xem xét

• Bộ đệm luồng có thể được phân bổ nhiều lần cho mỗi luồng. Ví dụ, hãy xem xét các truy vấn con – mỗi lớp có thể cần read_buffer,sort_buffer, tmp_table_size, v.v.
• Nhiều biến có thể được đặt cho mỗi kết nối. Vì vậy, bạn không thể chuyển tiếp giá trị toàn cầu nếu nhà phát triển có thể sử dụng giá trị cục bộ của họ để chạy một số truy vấn
• Có thể có nhiều bộ đệm chính. Nhiều bộ đệm chính có thể được tạo để chứa các lần thực thi truy vấn
• Phân tích truy vấn và tối ưu hóa cần bộ nhớ. Điều này thường nhỏ để có thể bỏ qua nhưng một số truy vấn nhất định có thể yêu cầu bộ nhớ rất lớn cho bước này, đặc biệt là các truy vấn được chế tạo đặc biệt
• thủ tục lưu trữ. Các thủ tục lưu trữ phức hợp có thể yêu cầu nhiều bộ nhớ
• Báo cáo và con trỏ đã chuẩn bị. Một kết nối có thể có nhiều câu lệnh và con trỏ đã chuẩn bị. Số lượng của chúng cuối cùng có thể bị hạn chế nhưng mỗi người trong số họ vẫn có thể có mức tiêu thụ bộ nhớ rất lớn
• Bộ đệm ẩn bảng Innodb. Innodb có bộ đệm bảng riêng trong đó dữ liệu meta về mỗi bảng được truy cập từ đầu được lưu trữ. Nó không bao giờ bị xóa và có thể lớn nếu bạn có nhiều bảng. Điều đó cũng có nghĩa là người dùng có đặc quyền CREATE TABLE sẽ có thể chạy máy chủ MySQL hết bộ nhớ
• bộ đệm MyISAM. MyISAM có thể phân bổ bộ đệm đủ lớn để chứa bản ghi lớn nhất trong bảng đã cho, được giữ cho đến khi đóng bảng
• Công cụ lưu trữ liên kết. Điều này có thể yêu cầu bộ nhớ không giới hạn trả về tập hợp kết quả từ truy vấn xóa
• Các đốm màu có thể yêu cầu bộ nhớ gấp 3 lần. Điều này rất quan trọng nếu bạn đang xử lý các đốm màu lớn [max_allowed_packet của bạn lớn] Xử lý 256 MB đốm màu có thể yêu cầu 768 MB bộ nhớ
• Công cụ lưu trữ. Nói chung, các công cụ lưu trữ có thể có phân bổ bộ nhớ riêng cho mỗi luồng hoặc bộ nhớ chung không được điều chỉnh dưới dạng bộ đệm. Theo dõi những điều này đặc biệt là bây giờ với nhiều công cụ lưu trữ được phát hành cho MySQL bởi các bên khác nhau

Tôi không giả vờ đây là danh sách đầy đủ. Ngược lại, tôi khá chắc chắn rằng mình đã bỏ lỡ điều gì đó [hãy gửi cho tôi một ghi chú nếu bạn có điều gì đó cần bổ sung]. Nhưng điểm chính là – có rất nhiều người tiêu dùng bộ nhớ ở đâu đó và cố gắng tìm mức sử dụng cao nhất có thể cho từng người là không thực tế – vì vậy lời khuyên của tôi sẽ là đo lường những gì bạn nhận được trong thực tế và mức tiêu thụ bộ nhớ phản ứng như thế nào với việc thay đổi các biến số khác nhau. Ví dụ: bạn có thể thấy việc tăng sort_buffer_size từ 1MB lên 4MB và 1000 max_connections làm tăng mức tiêu thụ bộ nhớ cao nhất chỉ 30MB chứ không phải 3000MB như bạn có thể đã tính

MySQL có giới hạn bộ nhớ không?

Làm cách nào để tăng giới hạn bộ nhớ trong MySQL?