Kết nối tối đa mysql 5.7
|
Trong quá trình làm việc với hệ thống quản lý cơ sở dữ liệu (Mysql, Postgres, Oracle. ) rất nhiều khi gặp lỗi "Quá nhiều kết nối". Tại thời điểm đó, cơ sở dữ liệu không thể tiếp tục nhận các kết nối đến Show
-> Giải pháp đơn giản nhất là đặt lại cơ sở dữ liệu, để đóng các kết nối hiện tại
Nếu là 1 kỹ sư có tâm với sản phẩm của mình, chúng tôi sẽ cố gắng đi sâu vào gốc rễ vấn đề để xử lý Trong bài hướng dẫn này, mình hướng dẫn 1 số cách kiểm tra trên cơ sở dữ liệu Mariadb khác làm tương tự 2. Phân vùng nhân
LDT @ledangtuanbk Theo dõi 946 33 29 Đã đăng vào ngày 5 tháng 1 năm 2021 2. 21 SA 4 phút đọc 2. 2k 1 10 sự cố mysql. Quá nhiều kết nối
1. Lỗi Quá nhiều kết nốiTrong quá trình làm việc với hệ thống quản lý cơ sở dữ liệu (Mysql, Postgres, Oracle. ) rất nhiều khi gặp lỗi "Quá nhiều kết nối". Tại thời điểm đó, cơ sở dữ liệu không thể tiếp tục nhận các kết nối đến
-> Giải pháp đơn giản nhất là đặt lại cơ sở dữ liệu, để đóng các kết nối hiện tại
Nếu là 1 kỹ sư có tâm với sản phẩm của mình, chúng tôi sẽ cố gắng đi sâu vào gốc rễ vấn đề để xử lý Trong bài hướng dẫn này, mình hướng dẫn 1 số cách kiểm tra trên cơ sở dữ liệu Mariadb khác làm tương tự 2. Phân vùng nhân
khách hàng. Máy khách MySQL là một dòng lệnh công cụ hoặc một ứng dụng nói chuyện với Máy chủ MySQL & NBSP; . Một máy khách đa luồng duy nhất có thể mở nhiều kết nối đến máy chủ, nhưng để đơn giản, chúng tôi nói rằng một ứng dụng khách mở một kết nối với máy chủ Connected request. Máy khách MySQL gửi các yêu cầu kết nối đến máy chủ MySQL. Yêu cầu kết nối chỉ đơn giản là thông báo kết nối TCP-IP được gửi đến cổng 3306 trên máy chủ của máy chủ Chủ đề máy thu. Các yêu cầu kết nối được xếp hàng và sau đó được xử lý bởi chủ đề máy thu từng cái một. Công việc duy nhất của luồng máy thu là tạo luồng người dùng, công việc xử lý bổ sung được thực hiện bởi luồng người dùng buffers. Chủ đề máy thu sẽ tạo ra một luồng hệ thống điều hành mới hoặc sử dụng lại một luồng hệ thống điều hành miễn phí hiện có nếu được tìm thấy trong bộ đệm luồng. Bộ đệm chủ đề được sử dụng rất quan trọng đối với tốc độ kết nối khi các luồng hệ thống điều hành tốn kém để tạo. Ngày nay, việc tạo ra một luồng hệ thống điều hành tương đối rẻ và bộ đệm chủ đề có thể được coi là di sản. Giá trị mặc định của Thread_Cache_Size được tính là 8 + (Max_Connections / 100) và hiếm khi được thay đổi. Có thể có ý nghĩa khi thử tăng bộ đệm chỉ trong trường số lượng kết nối dao động giữa có rất ít kết nối và có nhiều kết nối User title. Đó là luồng người dùng xử lý giao thức máy khách-máy chủ, ví dụ. Gửi lại gói bắt đầu ban đầu. Chủ đề người dùng này sẽ phân bổ và khởi tạo THD tương ứng, sau đó tiếp tục với công việc phán đoán và xác thực khả năng. Trong quá trình này, thông tin đăng nhập của người dùng được lưu trữ trong cảnh bảo mật THD. Nếu mọi thứ diễn ra tốt đẹp trong giai đoạn kết nối, luồng người dùng sẽ nhập giai đoạn lệnh THD. Kết nối được biểu thị bằng cấu trúc dữ liệu được gọi là THD được tạo khi kết nối được thiết lập và xóa khi kết nối bị hủy. Luôn luôn có sự tương ứng giữa kết nối người dùng và THD, tức là THD không được sử dụng lại trên các kết nối. Kích thước của THD là ~ 10k và định nghĩa của nó được tìm thấy trong sql_class. h. THD là một cấu trúc dữ liệu được sử dụng để theo dõi các khía cạnh khác nhau của trạng thái thực thi. Bộ nhớ bắt nguồn từ THD sẽ phát triển đáng kể trong quá trình thực hiện truy vấn, nhưng chính xác thì nó sẽ phát triển bao nhiêu sẽ phụ thuộc vào truy vấn.   Hình 2. Active connectionHình 2 minh họa giai đoạn lệnh. Tại đây, máy khách gửi truy vấn đến máy chủ và nhận lại kết quả trong một vài vòng. Nói chung, một chuỗi câu lệnh có thể được gửi đi kèm theo giao dịch bắt đầu và cam kết/khôi phục. Trong trường hợp này, cần phải theo dõi cảnh giao dịch. Trong chế độ tự động-cam kết, mỗi câu lệnh sẽ được thực thi dưới dạng giao dịch (mỗi câu lệnh tạo thành bối cảnh giao dịch đầy đủ). Ngoài ra, có bối cảnh phiên bản, tức là phiên bản có thể giữ các biến phiên bản, biến người dùng và NBSP; . Do đó, miễn phí là cảnh báo có liên quan để thực hiện các truy vấn, tất cả các truy vấn trên kết nối phải sử dụng cùng một THD Hình 3. Ngắt kết nốiHình 3 minh họa những điều xảy ra khi máy khách MySQL ngắt kết nối với máy chủ MySQL. Khách hàng gửi A  . Một sự cố ngắt kết nối cũng có thể xảy ra khi một trong hai bên đóng đầu của ổ cắm. Khi ngắt kết nối, người dùng luồng sẽ thu dọn dẹp, hãy phân bổ THD và cuối cùng tự động đặt vào bộ đệm của luồng như lơ lửng nếu có các khe miễn phí. Nếu không có khe cắm miễn phí, luồng người dùng sẽ bị chấm dứt Shortcut connectionMột kết nối rút ngắn là một kết nối chỉ mở trong một khoảng thời gian ngắn. Đây thường là trường hợp cho các ứng dụng PHP, máy khách mở kết nối, thực hiện một truy vấn đơn giản và sau đó đóng kết nối. Do kiến trúc của nó, MySQL thực sự rất giỏi trong việc chấp nhận các kết nối mới ở tốc độ cao, lên tới 80. 000 connection/ngắt kết nối mỗi giây như trong Hình 4 bên dưới Hình 4. Connection+Truy vấn+Ngắt kết nối mỗi giâyHình 4 minh họa hiệu quả kết nối/ngắt kết nối MySQL. Máy kiểm tra có Intel (R) Xeon (R) E5-2699 V4 CPU, 2CPU-SOCKETS, 22 core-HT each socket, 2,20GHz (Broadwell). Chúng tôi đang chạy máy khách Sysbench và máy chủ MySQL trên cùng một bộ lõi. Mỗi luồng Sysbench kết nối mặc dù ổ cắm, đưa ra một điểm chọn, ngắt kết nối và sau đó lặp lại. Data is in memory. Biểu đồ hiển thị TPS cho số lượng khách hàng khác nhau, 20. 000 cho 8 máy khách, 43. 000 cho 16 máy khách, 73. 000 cho 32 máy khách và 80. 000 cho 64 máy khách MySQL luôn đối đầu giỏi trong việc kết nối/ngắt kết nối, nhưng thực sự rất tốt với một số trợ giúp từ Facebook trong MySQL 5. 6. Xem các bài đăng trên blog của Domas Mituzas và & NBSP; . Một số tiến trình bổ sung đã được thực hiện trong MySQL 5. 7 long connectionMột kết nối sống lâu là một kết nối mở ra không có giới hạn. Ví dụ, người ta có thể có máy chủ web hoặc máy chủ ứng dụng & NBSP; Số lượng máy khách tối đa mà máy chủ cho phép kết nối đồng thời được xác định bởi biến hệ thống max_connections, có thể được cấu hình bởi người dùng. Khi đạt đến mức tối đa này, máy chủ sẽ không chấp nhận các kết nối mới cho đến khi một trong các máy khách đang hoạt động ngắt kết nối. Máy chủ MySQL sẽ có một luồng người dùng (với THD) cho thời gian sống của kết nối, như được minh họa trong Hình 5 Hình 5. Nhiều máy khách kết nối với một máy chủ MySQL duy nhấtVì vậy, giá trị tốt nhất cho max_connections là gì? . Nó sẽ chủ yếu phụ thuộc vào hai điều khiển, tải máy khách và phần cứng MySQL đang chạy Một kết nối có thể ít bận rộn. Một kết nối rất bận rộn khi máy khách gửi truy vấn quay lại máy chủ, nghĩa là mỗi lần khách hàng nhận lại kết quả, nó ngay lập tức gửi truy vấn mới đến máy chủ. Một kết nối không bận nếu khách hàng bây giờ và sau đó gửi một truy vấn đến máy chủ và có những khoảng dừng dài giữa việc nhận kết quả từ một truy vấn cho đến khi truy vấn tiếp theo được gửi (khoảng thời gian rảnh rỗi . Trong một vấn đề khi có nhiều kết nối và tất cả các kết nối đều rất bận rộn, chúng tôi nói rằng máy chủ MySQL đang bị tải nghiêm trọng Khi các kết nối ít bận rộn hơn, người ta có thể chấp nhận nhiều kết nối hơn. Hãy để chúng tôi nói rằng máy chủ có thể tối đa hóa tối đa 5000 TPS để tải người dùng trên 200 kết nối. Cùng với một máy chủ sau đó sẽ có thể xử lý cùng một đồng thời (5000 TPS) trên 10000 kết nối, nhưng việc thêm nhiều kết nối sẽ không xử lý đồng thời. Tuy nhiên, 10000 kết nối sẽ yêu cầu sử dụng bộ nhớ cao hơn cho (nhiều) THD và sẽ dẫn đến việc sử dụng phần cứng kém hiệu quả hơn. Người ta cũng cần lưu ý rằng khi cài đặt MAX_CONNENTS lên 10000, có nguy cơ quá tải máy chủ nếu tất cả các kết nối gửi ngày càng nhiều truy vấn vượt quá dung lượng tổng thể 5000 TPS. Trong trường hợp này, máy chủ có thể bắt đầu đập max max là bao nhiêu? . Bạn có thể bắt đầu với 2 máy khách bận rộn và đo TPS cũng như tốc độ của máy chủ, sau đó tiếp tục các bước tiến bằng cách nhân đôi số lượng máy khách cho mỗi bước. Ban đầu, TPS sẽ tăng và tốc độ sẽ không thay đổi cho mỗi bước bạn thực hiện. Tại một số điểm TPS sẽ giống như trước đây và tốc độ sẽ bắt đầu tăng lên và đây là tải tối đa và lượng máy khách (hữu ích) tối đa MYSQL đồng thời chủ đề người dùng tối đa có thể xử lý là gì? . Một tải xuống phù hợp với yêu cầu này là tra từ khóa chính (chọn điểm Sysbench) của dữ liệu có trong bộ nhớ chính (bộ đệm nhóm). Từ khóa chính trong MySQL/Innodb được lưu trữ cùng với dữ liệu (chỉ mục phân cụm), điều duy nhất MySQL phải làm cho một điểm được chọn là điều hướng cây B trong bộ nhớ, hãy tìm bản ghi và trả về kết quả Vì tôi muốn minh họa đồng thời chủ đề, nên tôi chọn một máy có CPU 48 lõi. Máy kiểm tra có CPU Intel (R) Xeon (R) Platinum 8168, 2 ổ cắm CPU, 24 lõi-HT mỗi ổ cắm, 2,70GHz (Skylake). Chúng tôi đang chạy máy khách Sysbench và máy chủ MySQL 8. 0. 15 on the same core Hình 6. Sysbench point_select (data phù hợp với bộ nhớ)Hình 6 cho thấy mối quan hệ giữa lượng kết nối (luồng người dùng) và tải tổng thể về giao dịch từng giây (TPS). Mỗi máy khách tạo điểm chọn Tải Back-to-back. Ban đầu, tổng tải phát triển gần với tuyến tính so với lượng máy khách đồng thời, ví dụ 16 máy khách cung cấp cho bạn 300 điểm TPS, 32 máy khách cung cấp cho bạn 600 & NBSP; . Tối đa đạt được với 128 máy khách với 1,8 triệu TP. Với hơn 128 khách hàng, điểm dừng tăng và sau đó bắt đầu giảm. Vậy thì chuyện gì đã xảy ra ở đây? Hãy cùng xem đánh giá về độ truy vấn được mô tả trong Hình 7 ở trên. Từ 1 đến 64 máy khách thời gian phản hồi truy vấn (độ sáng) không thay đổi khoảng 50 micro giây. Sau đó, nó tăng lên 70 micro giây cho 128 máy khách, 140 micro giây cho 256 máy khách và 300 micro giây cho 512 máy khách MySQL đạt hiệu suất tối đa tối đa cho 128 luồng người dùng, với TPS tối đa (1,8 triệu) và tốc độ thấp (70 micro giây).   . Thêm nhiều luồng người dùng sẽ tạo ra mức độ phát triển và nó sẽ phụ thuộc vào các ứng dụng yêu cầu ứng dụng Những gì mức độ chấp nhận được chấp nhận, tức là Thỏa mãn mức độ dịch vụ đã được xác định. Trong ví dụ của chúng tôi ở đây, 95% tất cả các giao dịch có độ lệch dưới 210 micro giây, có thể đủ tốt cho hầu hết các ứng dụng. Điều này cung cấp cho tôi một luồng người dùng trên mỗi Tỷ lệ lõi là 256/48 = 5. 3. Dựa trên kinh nghiệm tích lũy, chúng tôi đề xuất tối đa 4 lần số lượng người dùng là số lượng CPU thực tế, do đó 4 × 48 = 196 Chủ đề người dùng trong ví dụ của chúng tôi MySQL không phải lúc nào cũng có tỷ lệ như cũng hiển thị ở trên. mysql 5. 6 đi kèm với các cải tiến khả năng kiểm tra RO lớn, nhưng bạn phải sử dụng tính năng giao dịch chỉ đọc và cam kết tự động để có hiệu ứng.   . 7 đi kèm với tự động khám phá các giao dịch RO và loại bỏ sự xung đột xung quanh khóa dữ liệu meta, thr_lock và innodb trx_sys mutex và mutex lock_sys. Đối với những người quan tâm đến lịch sử chuyên sâu hoàn chỉnh, hãy xem các bài đăng của Dimitri tại đây, tại đây, tại đây, tại đây và tại đây Điều gì sẽ xảy ra nếu bạn nhấn một số nút cổ chai khác trước khi bạn nhấn nút cổ chai đồng thời chủ đề? . Hình 8 cho thấy truy vấn chọn điểm sysbench tương tự như trong Hình 6 ở trên, nhưng bây giờ khối lượng dữ liệu cao hơn nhiều và dữ liệu phải được đọc từ đĩa hầu hết thời gian. Trong ví dụ này, chúng tôi đang sử dụng SSD Intel Optane Fast Intel nhưng vẫn có thể đảo băng thông từ đĩa đến bộ nhớ. I E. Các chủ đề của người dùng trong ví dụ này đang chờ các trang đĩa được mang từ SSD đến nhóm bộ đệm Innodb. Kết quả tổng thể là TPS thấp hơn (1 triệu so với 1,8 triệu) vì mỗi truy vấn sẽ dành nhiều thời gian hơn để chờ dữ liệu. Thêm nhiều máy khách tăng TPS cho đến khi dung lượng đĩa bị bão hòa, ở khoảng 128-256 kết nối. Tại thời điểm này, tất cả các luồng người dùng chỉ đang chờ IO và thêm nhiều luồng người dùng sẽ có hiệu quả Hình 8. Sysbench point_select (IO ràng buộc)Bài đăng của Dimitri ở đây thảo luận về các cải tiến 8. 0 về khả năng mở rộng IO, loại bỏ sự tranh chấp xung quanh innodb fil_system mutex. Trong bối cảnh hiện tại, nó chỉ cho thấy rằng mặc dù sự kiện tranh chấp ở đây là IO trên đĩa và TPS thấp hơn (1,1 triệu) Chủ đề, tối đa trên 256 luồng người dùng đạt 1,1 triệu qps Những điều gì giới hạn sự đồng chủ đề?Một chủ đề sẽ vui vẻ thực hiện các hướng dẫn cho đến khi nó cần chờ đợi một cái gì đó hoặc cho đến khi nó đã sử dụng thời gian của nó theo quyết định của bộ lập lịch hệ thống điều hành. Có ba điều kiện mà một chủ đề có thể cần phải chờ đợi. Mutex. Một mutex bảo vệ cấu trúc dữ liệu nội bộ được chia sẻ, tức là khi người ta cần đảm bảo rằng chỉ có một luồng có thể thực thi bất cứ lúc nào. Nếu một luồng giữ một mutex, các luồng khác phải chờ xếp hạng hàng cho đến khi chúng đến đích. Việc sử dụng của MuTexes là một kỹ thuật thực thi và làm điều đó, một cái gì đó có thể được các lập trình viên tung hứng miễn phí là duy trì tính chính xác của chương trình tổng thể. Các kỹ thuật bao gồm việc sử dụng các thuật toán toán học không có từ khóa và để phân tách các tài nguyên được bảo vệ thành các tài nguyên hạt mịn hơn để đảm bảo rằng các luồng khác nhau yêu cầu các loại mutex khác nhau (giảm sự tranh chấp . Phần lớn công việc mở rộng được thực hiện bởi các nhà phát triển MYSQL trong 5-10 năm qua tập trung vào việc sử dụng tốt hơn các loại mutexes khóa. Khóa cơ sở dữ liệu theo một nghĩa nào đó giống như điều tương tự nhưng chúng được gắn với nghĩa cơ sở dữ liệu và điều đó khó tránh hơn. (MySQL/Innodb khá giỏi trong việc tránh khóa điều khiển đồng thời đa phiên bản của nó, MVCC). Cơ sở dữ liệu key có thể được chia thành các khóa dữ liệu (gây ra bởi SQL DML) và khóa meta dữ liệu (gây ra bởi SQL DDL). Một từ khóa dữ liệu như từ khóa hàng hóa thông thường sẽ bảo vệ dữ liệu được cập nhật bởi một luồng được đọc hoặc viết bởi một luồng khác. Khóa dữ liệu meta thông thường sẽ bảo vệ lược đồ cơ sở dữ liệu khỏi các bản cập nhật đồng thời, không tương thích. Duy trì ngữ nghĩa cơ sở dữ liệu quan trọng hơn hiệu suất và quy mô, vì vậy việc loại bỏ từ khóa khó hơn là loại bỏ các loại mutexes. Kết quả là, các khả năng mở rộng khả năng khóa thông thường phải được giải quyết ở cấp độ thiết kế ứng dụng OLTP, ví dụ:. Một thiết kế lược đồ cơ sở dữ liệu tốt hơn kết hợp với các thiết kế truy vấn tốt hơn. mysql 8. 0 cũng cung cấp một số tính năng thú vị mới cho các nhà phát triển ứng dụng để tránh khóa, ví dụ. Không chờ đợi và bỏ qua khóa Disk and IO network. IO là thứ mà người ta cố gắng giảm thiểu bất cứ điều gì khi có thể, và khi không thể cố gắng làm điều đó hiệu quả nhất có thể, ví dụ. Lấy trước, song hóa, hàng loạt, vv Nhưng đến một lúc nào đó, hầu hết các chủ đề người dùng sẽ cần IO. Khi luồng người dùng cần đợi IO, HĐH thường sẽ đặt luồng ở trạng thái chờ và đưa CPU vào một luồng chờ khác. Điều này sẽ hoạt động tốt nếu chủ đề mới có khả năng đạt được tiến bộ và sẽ dẫn đến hiệu quả sử dụng CPU. Nhưng, ví dụ, nếu băng thông IO đã bị bão hòa, luồng mới cũng không có khả năng chỉ chờ IO và không có tiến trình phát triển nào có thể được thực hiện. Vì vậy, sự đồng thời của các chủ đề sẽ được giới hạn bởi IO công suất, như chúng ta đã thấy trong Hình 8 Điều gì đã xảy ra khi một chủ đề bị đình chỉ bởi hệ điều hành? . Thứ hai, nó có thể giữ Mutexes hoặc khóa các luồng phát triển khác. Thứ ba, khi nó được đánh giá công thức một lần nữa, các vật phẩm được lưu trong bộ nhớ cache có thể đã được xuất dữ liệu yêu cầu dữ liệu được đọc lại. Tại một số thời điểm, nhiều chủ đề sẽ chỉ tạo ra hàng đợi các luồng chờ phát triển và hệ thống sẽ sớm bị chặn. Giải pháp là giới hạn số lượng luồng người dùng bằng cách giới hạn MAX_Connections, chỉ cho phép nhiều luồng người dùng đồng thời như hệ thống có thể xử lý hiệu quả Vai trò của các nhà phát triển ứng dụngTrong một số trường hợp, các nhà phát triển ứng dụng đang kiểm tra giám sát kiến trúc hệ thống tổng thể, lược đồ cơ sở dữ liệu và truy vấn cơ sở dữ liệu. Nhưng có lẽ thường xuyên hơn không, các nhà phát triển ứng dụng sẽ được yêu cầu phát triển một ứng dụng trên cơ sở dữ liệu hiện có. Trong cả hai trường hợp, nhà phát triển ứng dụng sẽ cần chú ý đến các truy vấn được gửi đến lớp cơ sở dữ liệu Trường hợp sử dụng cổ điển cho MySQL là lý do xử lý giao dịch trực tuyến (OLTP) thường có yêu cầu thời gian trả lời. Thời gian phản hồi cơ sở dữ liệu có thể chấp nhận thông thường chỉ được tính bằng mili giây và điều này tất nhiên sẽ giới hạn loại truy vấn có thể được dự kiến sẽ chạy (có thể kết hợp với các giới hạn về . Điều này thường trái ngược với Đặc biệt đối với OLTP, nhà phát triển ứng dụng phải thận trọng trong việc thiết kế các truy vấn có thể thực thi trong thời gian SLA được phản hồi tốt nhất và có thể thực hiện song song.   |
