Mysql gây tải máy chủ cao

Chúng tôi có một máy chủ MySQL chuyên dụng và bên dưới là ảnh chụp nhanh của máy chủ hàng đầu. Mức trung bình tải đã duy trì ở mức gần 100 trong một giờ cộng với trạng thái sẵn sàng

top - 20:54:28 up  7:31,  2 users,  load average: 83.08, 96.88, 106.23
Tasks: 278 total,   2 running, 274 sleeping,   2 stopped,   0 zombie
Cpu0  : 18.8%us, 10.2%sy,  0.0%ni, 70.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  : 51.2%us,  4.3%sy,  0.0%ni, 44.2%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu2  :  9.0%us, 10.3%sy,  0.0%ni, 80.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  : 18.8%us,  7.4%sy,  0.0%ni, 73.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  7.8%us,  8.8%sy,  0.0%ni, 83.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  : 10.3%us,  8.4%sy,  0.0%ni, 81.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  6.2%us,  7.5%sy,  0.0%ni, 86.2%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  6.2%us,  6.2%sy,  0.0%ni, 87.3%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu8  :  8.8%us, 10.4%sy,  0.0%ni, 80.5%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu9  : 63.7%us,  4.6%sy,  0.0%ni, 12.2%id,  0.0%wa,  4.3%hi, 15.2%si,  0.0%st
Cpu10 :  9.2%us, 10.2%sy,  0.0%ni, 80.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu11 : 17.3%us,  5.9%sy,  0.0%ni, 76.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu12 :  8.0%us,  8.7%sy,  0.0%ni, 83.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu13 : 10.9%us,  7.4%sy,  0.0%ni, 81.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu14 :  6.2%us,  6.9%sy,  0.0%ni, 86.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu15 :  4.8%us,  6.1%sy,  0.0%ni, 89.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:  33009800k total, 23174396k used,  9835404k free,   120604k buffers
Swap: 35061752k total,        0k used, 35061752k free, 16459540k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3341 mysql     20   0 14.3g 4.6g 4240 S 417.8 14.5   1673:51 mysqld
24406 root      20   0 15008 1292  876 R  0.3  0.0   0:00.19 top
    1 root      20   0  4080  852  608 S  0.0  0.0   0:01.92 init
    2 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 kthreadd
    3 root      RT  -5     0    0    0 S  0.0  0.0   0:00.32 migration/0
    4 root      15  -5     0    0    0 S  0.0  0.0   0:00.29 ksoftirqd/0
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    6 root      RT  -5     0    0    0 S  0.0  0.0   0:03.21 migration/1
    7 root      15  -5     0    0    0 S  0.0  0.0   0:00.07 ksoftirqd/1
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
    9 root      RT  -5     0    0    0 S  0.0  0.0   0:00.17 migration/2
   10 root      15  -5     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/2
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
   12 root      RT  -5     0    0    0 S  0.0  0.0   0:00.32 migration/3
   13 root      15  -5     0    0    0 S  0.0  0.0   0:00.02 ksoftirqd/3
   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
   15 root      RT  -5     0    0    0 S  0.0  0.0   0:00.10 migration/4
   16 root      15  -5     0    0    0 S  0.0  0.0   0:00.04 ksoftirqd/4
   17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4
   18 root      RT  -5     0    0    0 S  0.0  0.0   0:00.35 migration/5

Chúng tôi cũng đã thử chạy lệnh này. Lệnh nào khác có thể giúp chúng tôi chẩn đoán vấn đề chính xác của tải cao này?

netstat -nat |grep 3306 | awk '{print $6}' | sort | uniq -c | sort -n
      1 LISTEN
      1 SYN_RECV
    410 ESTABLISHED
    964 TIME_WAIT

Đầu ra của vmstat 1

---------------memory--------------- --swap-- --io-- --system-- -----cpu------
 r b swpd  free     buff    cache    si so    bi  bo in  cs     us sy id wa st
 2 0 0     12978936 30944   15172360 0  0     259 3  184 265    6  6  77 12 0

MySQL khá phổ biến trong số các ứng dụng web nguồn mở, nhưng nó dễ gặp các vấn đề về hiệu suất nếu không được bảo trì đúng cách

Các vấn đề về hiệu suất xảy ra chủ yếu do phân mảnh bảng, cài đặt bộ nhớ không được tối ưu hóa, v.v. Tất cả điều này có thể dẫn đến việc sử dụng CPU cao của MySQL và lỗi ứng dụng

Tại Bobcares, các Kỹ sư hỗ trợ chuyên dụng của chúng tôi duy trì hàng nghìn máy chủ web và MySQL của máy chủ lưu trữ web, công ty thương mại điện tử và nhà tiếp thị kỹ thuật số trên toàn thế giới

Chúng tôi hoạt động như một nhóm công nghệ của các công ty này và giữ cho máy chủ của họ ổn định thông qua bảo trì chủ động và giám sát 24/7

Mô hình quy trình của chúng tôi cho các máy chủ MySQL ổn định

Dưới đây là 3 thao tác chính mà chúng tôi thực hiện để giữ cho máy chủ MySQL ổn định

1. Tối ưu hóa máy chủ ban đầu

Khi khách hàng đăng ký, các kỹ sư của chúng tôi sẽ kiểm tra hiệu suất máy chủ và tối ưu hóa cài đặt máy chủ MySQL để khắc phục mọi tắc nghẽn tài nguyên hiện có

Sau khi phân tích loại trang web và lưu lượng truy cập, chúng tôi điều chỉnh các cài đặt chính trong máy chủ MySQL giúp đảm bảo sử dụng tài nguyên tối ưu. Điều này bao gồm các giới hạn kết nối, kích thước bộ đệm và kích thước bộ đệm truy vấn, v.v.

2. Giám sát & ứng cứu khẩn cấp 24/7

Nhận xét spam, vũ phu, tấn công bot hoặc thậm chí tăng lưu lượng truy cập hợp pháp có thể khiến tải MySQL cao. Đó là lý do tại sao điều quan trọng là phải theo dõi số liệu máy chủ 24/7

Quản trị viên máy chủ từ Bobcares theo dõi sức khỏe của MySQL 24/7. Nó giúp chúng tôi phát hiện ngay lập tức mức tăng đột biến trong việc sử dụng CPU hoặc chương trình lạm dụng và khắc phục trước khi nó làm hỏng toàn bộ máy chủ

3. Kiểm tra theo dõi & tối ưu hóa

Cơ sở dữ liệu phát triển theo thời gian và tải trên nó thay đổi khi lưu lượng truy cập trang web thay đổi. Đó là lý do tại sao việc kiểm tra tiếp theo và tối ưu hóa lại là rất quan trọng trong việc duy trì sự ổn định của máy chủ MySQL

Công việc cốt lõi của Quản trị viên Máy chủ Chuyên dụng của chúng tôi là thường xuyên kiểm tra các máy chủ của khách hàng và tìm ra các tắc nghẽn về hiệu suất trước khi chúng xảy ra và khắc phục trước khi bất kỳ khách hàng nào bị ảnh hưởng

Hôm nay, chúng ta sẽ xem xét cách Quản trị viên máy chủ của chúng ta phản ứng với tình huống CPU cao của MySQL và những gì chúng ta làm để ngăn chặn sự tái diễn của nó

[ Bạn không cần phải là chuyên gia về MySQL để giữ cho trang web của mình nhanh và ổn định. Hãy để chúng tôi giúp bạn. Các chuyên gia của chúng tôi sẽ giữ cho máy chủ của bạn hoạt động nhanh, an toàn và vững chắc. ]

Cách phát hiện mức sử dụng CPU cao của MySQL

Mọi người thường liên kết tải máy chủ cao với mức sử dụng CPU cao. Điều đó không phải lúc nào cũng đúng. Tải máy chủ có thể tăng cao do tắc nghẽn trong bất kỳ tài nguyên nào. Điều này có thể bao gồm bộ nhớ, đĩa I/O, mạng hoặc CPU

Lý do hàng đầu cho tải máy chủ do MySQL gây ra là do cạn kiệt bộ nhớ hoặc I/O. Nếu đó là nút cổ chai của CPU, đầu ra của lệnh trên cùng sẽ như thế này

top - 8:31:15 up 23 days, 3:33, 1 user, load average: 22.31, 19.72, 25.02
 Tasks: 81 total, 5 running, 76 sleeping, 0 stopped, 0 zombie
 Cpu(s): 87.8%us, 0.3%sy, 0.0%ni, 11.2%id, 0.0%wa, 0.2%hi, 0.5%si, 0.0%st
 Mem: 2975920k total, 1018219k used, 1957701k free, 287408k buffers

Nếu đó là nút cổ chai do I/O gây ra, %wa (được gọi là trung bình chờ) sẽ có % CPU cao nhất. Ngược lại, nếu đó là tải do bộ nhớ gây ra, thì giới hạn bộ nhớ “miễn phí” chỉ ở mức vài MB

Nhấp vào đây để biết cách khắc phục tình trạng sử dụng bộ nhớ cao của MySQL

Sửa lỗi CPU cao của MySQL

Nếu tải của máy chủ thực sự liên quan đến mức sử dụng CPU cao, thì chúng tôi nhận thấy các cách khắc phục sau đây là hữu ích

1. Kích hoạt InnoDB để xử lý một số lượng lớn các kết nối đồng thời – Kiểm tra “PROCESSLIST” của MySQL và nếu bạn thấy nhiều truy vấn ở trạng thái “LOCK”, điều đó có nghĩa là rất nhiều truy vấn bị tạm dừng vì các bảng MyISAM đang xử lý các giao dịch khác. Để khắc phục điều này, hãy chuyển đổi các bảng đó thành công cụ InnoDB hỗ trợ khóa cấp hàng
2. Bật kết nối liên tục – Nếu bạn chỉ có một ứng dụng nhận hàng nghìn kết nối mỗi giờ, thì việc bật kết nối MySQL liên tục có thể cải thiện hiệu suất. Nếu máy chủ có nhiều ứng dụng (như máy chủ lưu trữ web dùng chung), điều này có thể không hoạt động
3. Chặn các quy trình lạm dụng – Khi một trang web đang bị tấn công (như DoS, spam nhận xét, v.v. ), kết quả là số lượng kết nối được thiết lập cao bất thường trong một thời gian ngắn. Sử dụng “PROCESSLIST” trong MySQL để xác định những người dùng hàng đầu và chặn quyền truy cập vào các kết nối lạm dụng
4. Tối ưu hóa các truy vấn cơ sở dữ liệu – Một số ứng dụng web sử dụng các truy vấn phức tạp để hiển thị thông tin trang web. Các truy vấn này có thể mất nhiều thời gian để thực hiện và gây ra tải CPU. Nhận danh sách các truy vấn như vậy từ “nhật ký truy vấn chậm” và giảm số lượng liên kết cũng như các thao tác bảng khác trong một truy vấn duy nhất
5. Kiểm tra “lỗi giây nhuận” – Vào ngày 1 tháng 7 năm 2015, đã có thêm giây nhuận vào giờ UTC tiêu chuẩn. Trong các máy chủ chạy các phiên bản nhân Linux cũ và sử dụng máy chủ thời gian, điều này được xem là nguyên nhân gây ra tình trạng tải cao cho MySQL. Nếu bạn có nhân Linux cũ, hãy thử đặt lại thời gian bằng cách sử dụng lệnh date -s “$(date)”

[ Đừng để mất khách hàng của bạn vào tay một máy chủ chậm chạp. Hãy để chúng tôi giúp bạn. Các chuyên gia của chúng tôi sẽ theo dõi và bảo trì máy chủ của bạn 24/7 để nó luôn hoạt động nhanh như chớp. ]

Cách ngăn chặn việc sử dụng CPU cao của MySQL

Thay đổi lưu lượng máy chủ và cơ sở dữ liệu phát triển theo thời gian. Các sự cố CPU cao của MySQL có thể được ngăn chặn ở mức độ lớn nếu máy chủ cơ sở dữ liệu được kiểm tra và điều chỉnh hiệu suất

1. Điều chỉnh hiệu suất MySQL – MySQL sử dụng nhiều bộ đệm và hệ thống bộ đệm khác nhau để thực hiện các truy vấn. Khi khối lượng và độ phức tạp của truy vấn cơ sở dữ liệu thay đổi, cài đặt máy chủ cần được điều chỉnh để có hiệu suất tối ưu. Có nhiều công cụ khác nhau như mysqltuner để xác định bất kỳ cài đặt nào cần điều chỉnh
2. Kiểm tra và tăng cường bảo mật – Các cuộc tấn công bằng thư rác và DoS có thể dễ dàng áp đảo máy chủ cơ sở dữ liệu. Triển khai tường lửa ứng dụng web như ModSecurity và tường lửa DoS như ModEvasive để ngăn kẻ tấn công ảnh hưởng đến thời gian hoạt động của máy chủ
3. Triển khai cân bằng tải – Khi lưu lượng máy chủ tăng lên, có thể phải chia tải thành nhiều máy chủ. MySQL có thể được cấu hình để sao chép master-master và master-slave, cho phép các truy vấn được phục vụ từ bất kỳ máy chủ nào trong một cụm
4. Tối ưu hóa các truy vấn cơ sở dữ liệu – Nếu các ứng dụng web được mã hóa kém, không có lượng tối ưu hóa cơ sở dữ liệu nào sẽ khắc phục được tải máy chủ. Theo dõi “nhật ký truy vấn chậm” của MySQL và giảm số lần THAM GIA để làm cho cơ sở dữ liệu nhanh hơn
5. Sử dụng các lựa chọn thay thế hiệu suất cao như Percona – MySQL có nhiều tắc nghẽn bộ nhớ/CPU do cách nó xử lý các truy vấn. Các spin-off của MySQL như MariaDB và Percona giải quyết những vấn đề này và giúp đạt được sự ổn định tốt hơn

Làm thế nào để bắt đầu

MySQL có thể hơi dày để hiểu. Cần có sự kiên nhẫn và nhiều kinh nghiệm để biết giải pháp nào hiệu quả ở đâu

Bạn có thể lao đầu vào các tài liệu MySQL và trở thành một chuyên gia (trong khoảng 2-3 năm) hoặc bạn có thể sửa máy chủ của mình (và giữ nó cố định) bằng cách thuê một quản trị viên