Thực đơn
trang chủMicrosoft

Thiết lập MYSQL cho Linux. Những gì cần được cấu hình trong mySQL ngay sau khi cài đặt

Bài viết này sẽ đề cập đến nhiều cài đặt MySQL khác nhau, chủ yếu là những cài đặt ảnh hưởng đến hiệu suất. Để thuận tiện, tất cả các biến được chia thành các phần (cài đặt cơ bản, hạn chế, cài đặt luồng, bộ nhớ đệm truy vấn, thời gian, bộ đệm, InnoDB). Trước tiên, hãy làm rõ tên của một số biến đã thay đổi trong phiên bản 4 của MySQL và cả hai biến thể tên cũ và mới vẫn tiếp tục được tìm thấy trên Internet, điều này đặt ra câu hỏi.

Vì vậy, trong phiên bản 4, một số biến hiện có đuôi _size. Điều này áp dụng cho biến thread_cache_size và các biến từ phần Bộ đệm. Và biến read_buffer_size được gọi là record_buffer trước phiên bản 4. Ngoài ra, biến Skip_external_locking từ phần Cài đặt cơ bản trước phiên bản 4 nó được gọi là Skip_locking.

Các biến thuộc hai loại chính: biến giá trị và biến cờ. Các biến có giá trị được ghi trong tệp cấu hình dưới dạng biến = value và các biến cờ được chỉ định đơn giản. Ngoài ra, bạn có thể nhận thấy rằng trong một số trường hợp, “-” được sử dụng trong tên biến và trong những trường hợp khác, “_”. Các biến có dấu gạch nối là các tùy chọn khởi động máy chủ và không thể thay đổi trong khi máy chủ đang chạy (sử dụng SET); các biến có dấu gạch dưới là các tùy chọn vận hành máy chủ và có thể được thay đổi nhanh chóng. Nếu chúng ta đang nói về một “biến trạng thái” hoặc nên theo dõi giá trị của một biến có tên được viết dưới dạng Variable_Name , thì bạn nên chạy yêu cầu SHOW STATUS THÍCH “Variable_Name” để nhận giá trị của biến này, hoặc xem tab trạng thái trong phpMyAdmin, nơi sẽ có thêm nhận xét theo giá trị của biến này.

Bây giờ hãy bắt đầu mô tả các biến và giá trị có thể có của chúng.

Cài đặt cơ bản

  • cập nhật có mức độ ưu tiên thấp- tùy chọn này làm giảm mức độ ưu tiên của các thao tác CHÈN/CẬP NHẬT so với CHỌN. Điều này có liên quan nếu điều quan trọng là đọc dữ liệu nhanh hơn ghi dữ liệu nhanh hơn.
  • bỏ qua khóa bên ngoài- tùy chọn được cài đặt theo mặc định, bắt đầu từ phiên bản 4. Hướng dẫn máy chủ MySQL không sử dụng khóa bên ngoài khi làm việc với cơ sở dữ liệu. Cần có khóa bên ngoài trong trường hợp nhiều máy chủ đang làm việc với cùng một tệp dữ liệu, tức là. có cùng datadir , không được sử dụng trong thực tế.
  • bỏ qua tên-giải quyết- Không xác định tên miền cho địa chỉ IP của các client kết nối. Trong trường hợp này, quyền của người dùng cần được cấu hình không phải cho máy chủ mà cho địa chỉ IP (ngoại trừ localhost). Nếu bạn chỉ kết nối với máy chủ từ máy cục bộ thì điều đó không thực sự quan trọng. Đối với các kết nối bên ngoài, nó sẽ tăng tốc độ thiết lập kết nối.
  • bỏ qua mạng- không sử dụng mạng, tức là. hoàn toàn không xử lý các kết nối TCP/IP. Giao tiếp với máy chủ sẽ diễn ra độc quyền thông qua một ổ cắm. Được khuyên dùng trừ khi bạn có phần mềm chỉ sử dụng TCP/IP để liên lạc với máy chủ.

Những hạn chế

  • địa chỉ liên kết- giao diện mà máy chủ sẽ lắng nghe. Vì lý do bảo mật, bạn nên đặt giá trị này thành 127.0.0.1 nếu bạn không sử dụng kết nối bên ngoài với máy chủ.
  • max_allowed_packet- kích thước tối đa của dữ liệu có thể được chuyển trong một yêu cầu. Bạn nên tăng nó lên nếu gặp lỗi "Gói quá lớn".
  • max_connections- số lượng kết nối song song tối đa đến máy chủ. Hãy tăng nó nếu bạn gặp phải vấn đề "Quá nhiều kết nối".
  • max_join_size- vô hiệu hóa các câu lệnh CHỌN dự kiến ​​​​sẽ phân tích nhiều hơn một số hàng được chỉ định hoặc nhiều hơn một số lượng tìm kiếm trên đĩa được chỉ định. Được sử dụng để bảo vệ chống lại các truy vấn quanh co cố gắng đếm hàng triệu hàng. Giá trị mặc định là hơn 4 tỷ, vì vậy bạn có thể muốn giảm đáng kể.
  • max_sort_length- cho biết số byte từ đầu trường BLOB hoặc TEXT sẽ sử dụng khi sắp xếp. Giá trị mặc định là 1024; nếu bạn lo ngại về các bảng hoặc truy vấn được thiết kế không chính xác, bạn nên giảm giá trị đó.

Cài đặt luồng

  • thread_cache_size- cho biết số lượng chủ đề được lưu trữ. Sau khi xử lý yêu cầu, máy chủ sẽ không chấm dứt luồng mà sẽ đặt nó vào bộ đệm nếu số lượng luồng trong bộ đệm nhỏ hơn giá trị được chỉ định. Giá trị mặc định là 0, tăng nó lên 8 hoặc ngay lập tức lên 16. Nếu bạn quan sát thấy giá trị của biến trạng thái Threads_Created tăng lên thì bạn nên tăng thêm thread_cache_size.
  • thread_concurrency- chỉ liên quan đến Solaris/SunOS, trái ngược với những gì họ viết trên Internet. "Cho" hệ thống biết có bao nhiêu luồng chạy đồng thời bằng cách thực hiện lệnh gọi hàm thr_setconcurrency. Giá trị được đề xuất là gấp đôi hoặc gấp ba số lõi bộ xử lý.

Bộ nhớ đệm truy vấn

  • query_cache_limit- kích thước tối đa của yêu cầu được lưu trong bộ nhớ đệm.
  • query_cache_min_res_unit- kích thước tối thiểu của một khối được lưu trữ trong bộ đệm.
  • query_cache_size- kích thước bộ nhớ cache. 0 vô hiệu hóa việc sử dụng bộ đệm. Để chọn giá trị tối ưu, bạn cần theo dõi biến trạng thái Qcache_lowmem_prunes và đảm bảo rằng giá trị của nó tăng nhẹ. Bạn cũng cần nhớ rằng bộ đệm quá lớn sẽ tạo ra tải không cần thiết.
  • query_cache_type- (TẮT, NHU CẦU, BẬT). TẮT vô hiệu hóa bộ nhớ đệm, YÊU CẦU– bộ nhớ đệm sẽ chỉ được thực hiện nếu có lệnh SQL_CACHE trong yêu cầu, TRÊN cho phép lưu vào bộ nhớ đệm.
  • query_cache_wlock_invalidate- xác định liệu dữ liệu có được lấy từ bộ đệm hay không nếu bảng chứa dữ liệu đó bị khóa để đọc.

Bạn có thể coi bộ đệm truy vấn như một mảng băm có khóa là truy vấn và giá trị của nó là kết quả truy vấn. Ngoài các kết quả, MySQL còn lưu trữ trong bộ đệm của nó một danh sách các bảng, một lựa chọn được lưu vào bộ đệm. Nếu các thay đổi xảy ra trong bất kỳ bảng nào có mẫu trong bộ đệm, MySQL sẽ xóa các mẫu đó khỏi bộ đệm. MySQL cũng không lưu trữ các truy vấn có kết quả có thể thay đổi.

Khi MySQL khởi động, nó phân bổ một khối bộ nhớ có kích thước query_cache_size. Khi thực hiện một truy vấn, ngay sau khi nhận được hàng đầu tiên của kết quả, máy chủ sẽ bắt đầu lưu chúng vào bộ đệm: nó phân bổ một khối bộ nhớ trong bộ đệm bằng query_cache_min_res_unit và ghi kết quả của lựa chọn vào đó. Nếu toàn bộ lựa chọn không khớp với một khối thì máy chủ sẽ phân bổ khối tiếp theo, v.v. Tại thời điểm bắt đầu ghi, MySQL không biết về kích thước của mẫu kết quả, vì vậy nếu kích thước mẫu được ghi vào bộ đệm lớn hơn query_cache_limit thì quá trình ghi sẽ dừng và không gian bị chiếm dụng sẽ được giải phóng, do đó, nếu bạn biết trong trước rằng kết quả của mẫu sẽ lớn, nên thực hiện nó bằng một lệnh SQL_NO_CACHE.

Thời gian

  • tương tác_timeout- thời gian tính bằng giây trong đó máy chủ chờ hoạt động từ kết nối tương tác (sử dụng cờ CLIENT_INTERACTIVE) trước khi đóng nó.
  • log_slow_queries- yêu cầu máy chủ ghi lại các truy vấn dài (“chậm”) (thực thi lâu hơn long_query_time). Giá trị là tên tệp đầy đủ (ví dụ /var/log/slow_queries).
  • long_query_time- nếu yêu cầu chạy lâu hơn thời gian quy định (tính bằng giây), thì nó sẽ bị coi là “chậm”.
  • net_read_timeout
  • net_write_timeout- thời gian tính bằng giây mà máy chủ sẽ đợi để nhận dữ liệu trước khi kết nối bị ngắt. Nếu máy chủ không phục vụ khách hàng có kênh rất chậm hoặc không ổn định thì ở đây 15 giây là đủ.
  • chờ_thời gian chờ- thời gian tính bằng giây trong đó máy chủ chờ kết nối hoạt động trước khi chấm dứt kết nối đó. Nói chung, 30 giây là đủ.

Bộ đệm

Tất cả các bộ đệm đều có một đặc điểm chung - nếu do đặt kích thước bộ đệm lớn, dữ liệu sẽ được chuyển đến tệp hoán trang, thì bộ đệm sẽ gây hại nhiều hơn là có lợi. Do đó, hãy luôn tập trung vào dung lượng RAM vật lý có sẵn cho bạn.

  • key_buffer_size- kích thước của bộ đệm được phân bổ cho các chỉ mục và có sẵn cho tất cả các luồng. Một cài đặt rất quan trọng ảnh hưởng đến hiệu suất. Giá trị mặc định là 8 MB, chắc chắn nên tăng nó lên. Nên sử dụng 15-30% tổng RAM, nhưng không có ích gì khi đặt nhiều hơn tổng kích thước của tất cả các tệp .MYI. Xem các biến trạng thái Key_reads và Key_read_requests, tỷ lệ Key_reads/Key_read_requests phải càng nhỏ càng tốt (< 0,01). Если это отношение велико, то размер буфера стоит увеличить.
  • max_heap_table_size- kích thước tối đa cho phép của bảng được lưu trong bộ nhớ (loại BỘ NHỚ). Giá trị mặc định là 16 MB, nếu bạn không sử dụng bảng MEMORY thì hãy đặt giá trị này thành tmp_table_size.
  • myisam_sort_buffer_size- kích thước của bộ đệm được MyISAM phân bổ để sắp xếp các chỉ mục khi BÀN SỬA CHỮA hoặc để tạo chỉ mục khi TẠO CHỈ SỐ, THAY ĐỔI BẢNG. Giá trị mặc định là 8 MB, nên tăng lên tới 30-40% RAM. Theo đó, hiệu suất đạt được sẽ chỉ đạt được khi thực hiện các truy vấn nêu trên.
  • net_buffer_length- lượng bộ nhớ được phân bổ cho bộ đệm kết nối và bộ đệm kết quả trên mỗi luồng. Bộ đệm kết nối sẽ có kích thước được chỉ định và bộ đệm kết quả sẽ có cùng kích thước, tức là. Mỗi luồng sẽ được phân bổ hai lần net_buffer_length . Giá trị được chỉ định là giá trị ban đầu và bộ đệm sẽ được tăng lên khi cần thiết lên tới max_allowed_packet. Kích thước mặc định là 16 KB. Nếu bộ nhớ bị giới hạn hoặc chỉ sử dụng các truy vấn nhỏ thì giá trị có thể bị giảm. Trong trường hợp sử dụng liên tục các truy vấn lớn và đủ bộ nhớ, giá trị sẽ được tăng lên theo kích thước truy vấn trung bình dự kiến.
  • read_buffer_size- mỗi luồng, khi quét các bảng một cách tuần tự, sẽ phân bổ lượng bộ nhớ được chỉ định cho mỗi bảng. Như các thử nghiệm cho thấy, giá trị này không nên tăng lên một cách đặc biệt. Kích thước mặc định là 128 KB, hãy thử tăng lên 256 KB rồi lên 512 KB và quan sát tốc độ của các truy vấn như SELECT COUNT(*) FROM table WHERE expr THÍCH "a%"; trên những chiếc bàn lớn.
  • read_rnd_buffer_size- phù hợp với các truy vấn có " ĐẶT BỞI", tức là đối với các truy vấn có kết quả phải được sắp xếp và truy cập vào bảng có chỉ mục. Giá trị mặc định là 256 KB, hãy tăng lên 1 MB hoặc cao hơn nếu bộ nhớ cho phép. Lưu ý rằng giá trị bộ nhớ đã chỉ định cũng được phân bổ cho mọi luồng.
  • sắp xếp_buffer_size- mỗi luồng thực hiện các thao tác sắp xếp ( ĐẶT BỞI) hoặc nhóm ( NHÓM THEO), phân bổ bộ đệm có kích thước được chỉ định. Giá trị mặc định là 2 MB, nếu bạn sử dụng các loại yêu cầu được chỉ định và nếu bộ nhớ cho phép thì giá trị sẽ được tăng lên. Giá trị lớn cho biến trạng thái Sort_merge_passes cho biết rằng cần phải tăng kích thước Sort_buffer_size. Cũng cần kiểm tra tốc độ thực hiện của các truy vấn như SELECT * FROM table ORDER BY name DESC trên các bảng lớn; có lẽ việc tăng bộ đệm sẽ chỉ làm chậm công việc (trong một số trường hợp thử nghiệm).
  • bảng_cache (bảng_open_cache từ phiên bản 5.1.3) - số lượng bảng mở được lưu trong bộ nhớ đệm cho tất cả các luồng. Mở tệp bảng có thể là một thao tác tốn nhiều tài nguyên, vì vậy tốt nhất bạn nên giữ các bảng đang mở trong bộ nhớ đệm. Lưu ý rằng mỗi mục trong bộ đệm này sử dụng một bộ điều khiển hệ thống, vì vậy bạn có thể cần tăng giới hạn bộ xử lý ( giới hạn). Giá trị mặc định là 64, tốt nhất nên tăng lên tổng số bảng nếu số lượng nằm trong giới hạn chấp nhận được. Biến trạng thái Opened_tables cho phép bạn theo dõi số lượng bảng được mở mà bỏ qua bộ đệm, tốt nhất là giữ giá trị của nó càng thấp càng tốt.
  • tmp_table_size- kích thước bộ nhớ tối đa được phân bổ cho các bảng tạm thời do MySQL tạo cho nhu cầu nội bộ của nó. Giá trị này cũng bị giới hạn bởi biến max_heap_table_size, do đó, cuối cùng giá trị tối thiểu giữa max_heap_table_size và tmp_table_size sẽ được chọn và các bảng tạm thời còn lại sẽ được tạo trên đĩa. Giá trị mặc định tùy thuộc vào hệ thống, hãy thử đặt nó thành 32 MB và xem biến trạng thái Created_tmp_disk_tables, giá trị của nó phải càng nhỏ càng tốt.

Các giá trị trong tệp cấu hình được chỉ định theo byte, tương ứng, kilobyte và megabyte phải được chuyển đổi thành byte.

InnoDB

  • innodb_bổ sung_mem_pool_size- lượng bộ nhớ được InnoDB phân bổ để lưu trữ các cấu trúc bên trong khác nhau. Nếu InnoDB không có đủ bộ nhớ, nó sẽ yêu cầu bộ nhớ từ HĐH và viết cảnh báo vào nhật ký lỗi MySQL.
  • innodb_buffer_pool_size- lượng bộ nhớ được InnoDB phân bổ để lưu trữ cả chỉ mục và dữ liệu. Ý nghĩa - càng nhiều thì càng tốt. Có thể tăng lên tới tổng kích thước của tất cả các bảng InnoDB hoặc lên tới 80% RAM, tùy theo mức nào nhỏ hơn.
  • innodb_flush_log_at_trx_commit- có ba giá trị hợp lệ: 0, 1, 2. Nếu giá trị bằng 0 , nhật ký sẽ được chuyển vào đĩa một lần mỗi giây, bất kể các giao dịch đang diễn ra. Với giá trị bằng 1 , nhật ký sẽ được xóa vào đĩa với mỗi giao dịch. Với giá trị bằng 2 , nhật ký được ghi với mỗi giao dịch, nhưng không bao giờ được ghi vào đĩa, để lại điều này cho lương tâm của hệ điều hành. Giá trị mặc định là 1, đây là cài đặt đáng tin cậy nhất nhưng không phải là cài đặt nhanh nhất. Nói chung, bạn có thể sử dụng một cách an toàn 2, dữ liệu chỉ có thể bị mất nếu hệ điều hành gặp sự cố và chỉ trong vài giây (tùy thuộc vào cài đặt hệ điều hành). 0 là chế độ nhanh nhất nhưng dữ liệu có thể bị mất cả khi hệ điều hành gặp sự cố và khi chính máy chủ MySQL gặp sự cố (tuy nhiên, dữ liệu chỉ tồn tại trong 1-2 giây).
  • innodb_log_buffer_size- kích thước bộ đệm đăng nhập. Giá trị mặc định là 1 MB, nên tăng giá trị này nếu bạn biết rằng sẽ có một số lượng lớn giao dịch InnoDB hoặc nếu giá trị của biến trạng thái Innodb_log_waits đang tăng lên. Bạn không cần phải tăng nó lên quá 8MB.
  • innodb_log_file_size- kích thước tối đa của một tệp nhật ký. Khi đạt đến kích thước này, InnoDB sẽ tạo một tệp mới. Giá trị mặc định là 5 MB, việc tăng kích thước sẽ cải thiện hiệu suất nhưng tăng thời gian khôi phục dữ liệu. Đặt giá trị này trong khoảng 32 MB - 512 MB tùy thuộc vào kích thước máy chủ (đánh giá chủ quan).

Việc sử dụng phpMyAdmin để giám sát hoạt động của máy chủ cũng rất thuận tiện; Tình trạng Và Biến. Ngoài ra, phpMyAdmin còn đưa ra lời khuyên về việc điều chỉnh một số biến nhất định tùy thuộc vào thông số vận hành của máy chủ.

Khi chuẩn bị bài viết, ngoài tài liệu chính thức và do chính tôi chủ trì, các tài liệu sau đã được sử dụng.

  • Dịch

Bản dịch miễn phí của một bài viết khá cũ từ Blog Hiệu suất MySQL về cách tốt nhất để cấu hình ngay sau khi cài đặt phiên bản cơ bản của mySQL.

Thật ngạc nhiên khi có nhiều người cài đặt mySQL trên máy chủ của họ và để nó ở chế độ cài đặt mặc định.

Mặc dù thực tế là có khá nhiều cài đặt trong mySQL mà bạn có thể thay đổi, nhưng có một tập hợp các đặc điểm thực sự rất quan trọng phải được tối ưu hóa cho máy chủ của riêng bạn. Thông thường, sau những chỉnh sửa nhỏ như vậy, hiệu suất máy chủ sẽ tăng lên rõ rệt.

  • key_buffer_size- một thiết lập cực kỳ quan trọng khi sử dụng bảng MyISAM. Đặt nó ở mức khoảng 30-40% RAM khả dụng nếu bạn chỉ sử dụng MyISAM. Kích thước chính xác phụ thuộc vào kích thước của chỉ mục, dữ liệu và tải trên máy chủ - hãy nhớ rằng MyISAM sử dụng bộ đệm của hệ điều hành (OS) để lưu trữ dữ liệu, vì vậy bạn cần chừa đủ dung lượng RAM cho dữ liệu và dữ liệu có thể chiếm nhiều không gian hơn đáng kể so với các chỉ mục. Tuy nhiên, hãy chắc chắn kiểm tra xem tất cả không gian được phân bổ bởi lệnh key_buffer_sizeđối với bộ đệm, được sử dụng liên tục - bạn thường có thể thấy các tình huống trong đó 4 GB được phân bổ cho bộ đệm chỉ mục, mặc dù tổng kích thước của tất cả các tệp .MYI không vượt quá 1 GB. Làm điều này hoàn toàn vô ích; bạn sẽ chỉ lãng phí tài nguyên. Nếu bạn thực tế không có bảng MyISAM thì key_buffer_size nên được đặt ở mức khoảng 16-32 MB - chúng sẽ được sử dụng để lưu trữ chỉ mục của các bảng tạm thời được tạo trên đĩa trong bộ nhớ.
  • innodb_buffer_pool_size- một cài đặt quan trọng không kém, nhưng đối với InnoDB, hãy nhớ chú ý đến nó nếu bạn chủ yếu sử dụng các bảng InnoDB, bởi vì chúng nhạy cảm hơn nhiều với kích thước bộ đệm so với bảng MyISAM. Về nguyên tắc, các bảng MyISAM có thể hoạt động tốt ngay cả với lượng dữ liệu lớn và với giá trị tiêu chuẩn key_buffer_size, tuy nhiên mySQL có thể rất chậm nếu giá trị không chính xác innodb_buffer_pool_size. InnoDB sử dụng bộ đệm riêng để lưu trữ cả chỉ mục và dữ liệu, do đó không cần để lại bộ nhớ cho bộ đệm của hệ điều hành - cài đặt innodb_buffer_pool_size trong 70-80% RAM có sẵn (tất nhiên nếu chỉ sử dụng bảng InnoDB). Về kích thước tối đa của tùy chọn này - tương tự key_buffer_size- đừng quá lo lắng, bạn cần tìm kích thước tối ưu, tìm cách sử dụng tốt nhất bộ nhớ hiện có.
  • innodb_bổ sung_mem_pool_size- tùy chọn này hầu như không ảnh hưởng đến hiệu suất của mySQL, nhưng tôi khuyên bạn nên để lại khoảng 20 MB (hoặc nhiều hơn một chút) cho InnoDB cho các nhu cầu nội bộ khác nhau.
  • innodb_log_file_size- một cài đặt cực kỳ quan trọng trong cơ sở dữ liệu có thao tác ghi thường xuyên vào bảng, đặc biệt với khối lượng lớn. B ồ Kích thước lớn hơn sẽ tăng hiệu suất nhưng hãy cẩn thận - thời gian khôi phục dữ liệu cũng sẽ tăng lên. Tôi thường đặt nó ở mức khoảng 64-512 MB tùy thuộc vào kích thước máy chủ.
  • innodb_log_buffer_size- giá trị tiêu chuẩn của tùy chọn này khá phù hợp với hầu hết các hệ thống có số lượng thao tác ghi trung bình và giao dịch nhỏ. Nếu hệ thống của bạn gặp phải các hoạt động bùng nổ hoặc bạn tích cực làm việc với dữ liệu BLOB thì tôi khuyên bạn nên tăng giá trị lên một chút innodb_log_buffer_size. Tuy nhiên, đừng lạm dụng nó - giá trị quá lớn sẽ gây lãng phí bộ nhớ: bộ đệm bị xóa mỗi giây, do đó bạn sẽ không cần nhiều dung lượng hơn mức cần thiết trong giây đó. Giá trị được đề xuất là khoảng 8-16 MB và thậm chí còn ít hơn đối với cơ sở dữ liệu nhỏ.
  • - phàn nàn rằng InnoDB chậm hơn MyISAM 100 lần? Có lẽ bạn đã quên cài đặt innodb_flush_log_at_trx_commit. Giá trị mặc định là 1 có nghĩa là mọi giao dịch CẬP NHẬT (hoặc lệnh phi giao dịch tương tự) phải chuyển bộ đệm vào đĩa, việc này khá tốn tài nguyên. Hầu hết các ứng dụng, đặc biệt là những ứng dụng đã sử dụng bảng MyISAM trước đây, sẽ hoạt động tốt với giá trị "2" (tức là "không xóa bộ đệm vào đĩa, chỉ vào bộ đệm của hệ điều hành"). Tuy nhiên, nhật ký vẫn sẽ được xóa vào đĩa sau mỗi 1-2 giây, vì vậy trong trường hợp xảy ra tai nạn, bạn sẽ mất tối đa 1-2 giây cập nhật. Giá trị "0" sẽ cải thiện hiệu suất nhưng bạn có nguy cơ mất dữ liệu ngay cả khi máy chủ mySQL gặp sự cố khi đặt giá trị thành innodb_flush_log_at_trx_commitở “2”, bạn sẽ chỉ mất dữ liệu nếu toàn bộ hệ điều hành gặp sự cố.
  • bảng_cache- việc mở bảng có thể khá tốn tài nguyên. Ví dụ: bảng MyISAM đánh dấu tiêu đề của tệp .MYI là “hiện đang được sử dụng”. Nói chung, mở bảng quá thường xuyên không phải là một ý tưởng hay, vì vậy tốt nhất bạn nên có bộ đệm đủ lớn để giữ cho tất cả các bảng của bạn luôn mở. Điều này sử dụng một số tài nguyên hệ điều hành và RAM, nhưng đây thường không phải là vấn đề nghiêm trọng trên các máy chủ hiện đại. Nếu bạn có vài trăm bảng thì giá trị bắt đầu cho tùy chọn bảng_cache có thể là "1024" (hãy nhớ rằng mỗi kết nối yêu cầu trình điều khiển riêng). Nếu bạn có nhiều bảng hơn hoặc nhiều kết nối hơn, hãy tăng giá trị của tham số. Tôi thấy một máy chủ mySQL có giá trị bảng_cache bằng 100.000.
  • thread_cache- tạo/hủy các luồng cũng là một hoạt động tiêu tốn nhiều tài nguyên, xảy ra mỗi khi kết nối được thiết lập và mọi kết nối đều bị hỏng. Tôi thường đặt tùy chọn này thành 16. Nếu ứng dụng của bạn có thể tăng vọt về số lượng kết nối đồng thời và theo biến Chủ đề_Đã tạo Nếu bạn thấy số lượng thread tăng lên nhanh chóng thì bạn nên tăng giá trị lên thread_cache. Mục đích là ngăn chặn việc tạo các luồng mới trong quá trình hoạt động bình thường của máy chủ.
  • query_cache_size- nếu ứng dụng của bạn đọc dữ liệu nhiều và thường xuyên và bạn không có bộ đệm cấp ứng dụng thì tùy chọn này có thể rất hữu ích. Đừng đặt giá trị này quá cao, vì việc duy trì bộ đệm truy vấn lớn sẽ tốn kém. Giá trị được đề xuất là từ 32 đến 512 MB. Đừng quên kiểm tra xem bộ đệm truy vấn đang được sử dụng tốt như thế nào - trong một số điều kiện (với một số lần truy cập nhỏ trong bộ đệm, tức là khi hầu như không có dữ liệu giống hệt nào được tìm nạp), việc sử dụng bộ đệm lớn có thể làm giảm hiệu suất.
Như bạn có thể thấy, đây là những cài đặt chung. Các biến này phụ thuộc vào phần cứng máy chủ và công cụ MySQL được sử dụng, trong khi các biến phiên thường được cấu hình cụ thể cho các tác vụ cụ thể. Nếu bạn chủ yếu sử dụng các truy vấn đơn giản thì không cần tăng giá trị sắp xếp_buffer_size, ngay cả khi bạn có thêm 64 GB RAM. Hơn nữa, giá trị bộ đệm lớn chỉ có thể làm giảm hiệu suất của máy chủ. Tốt hơn là để lại các biến phiên cho lần sau để tinh chỉnh máy chủ.

Tái bút: bản cài đặt mySQL đi kèm với một số tệp my.cnf được cài đặt sẵn, được thiết kế cho các tải khác nhau. Nếu bạn không có thời gian để định cấu hình máy chủ theo cách thủ công, thì tốt hơn bạn nên sử dụng chúng thay vì tệp cấu hình tiêu chuẩn, chọn tệp phù hợp hơn với tải của máy chủ của bạn.

Các tham số cấu hình mặc định trong Mysql được thiết kế cho các cơ sở dữ liệu nhỏ chạy ở mức tải thấp trên phần cứng rất khiêm tốn. Nếu kế hoạch Mysql của bạn vượt ra ngoài ranh giới bảng tới vài trăm bản ghi, bạn chắc chắn sẽ phải thay đổi cài đặt mặc định. Quá trình cấu hình Mysql tối ưu bao gồm hai phần - cấu hình ban đầu và điều chỉnh các thông số trong quá trình hoạt động. Việc điều chỉnh các thông số trong chế độ vận hành phần lớn phụ thuộc vào thông số cụ thể của hệ thống của bạn và việc giám sát nó - không có quy tắc đặc biệt nào ở đây. Có một số khuyến nghị cho thiết lập ban đầu:

MySQL là một hệ thống quản lý cơ sở dữ liệu miễn phí. MySQL được phát triển và hỗ trợ bởi Tập đoàn Oracle, công ty đã mua lại quyền đối với nhãn hiệu này cùng với Sun Microsystems được mua lại, công ty trước đây đã mua lại công ty MySQL AB của Thụy Điển. Sản phẩm được phân phối theo cả Giấy phép Công cộng GNU và giấy phép thương mại riêng. Ngoài ra, các nhà phát triển còn tạo ra chức năng theo yêu cầu của người dùng được cấp phép; chính nhờ thứ tự này mà cơ chế sao chép đã xuất hiện ở hầu hết các phiên bản đầu tiên.

Mở tệp cài đặt mysql, ví dụ:

/etc/mysql/my.cnf

Các thông số phổ biến nhất mà bạn nên chú ý và thay đổi cho phù hợp với yêu cầu của mình:

key_buffer_size

Nếu bạn chỉ sử dụng bảng MyIsam, hãy đặt giá trị này thành 30%...40% tổng số RAM có sẵn trên máy chủ. MyIsam sử dụng bộ đệm của hệ điều hành cho dữ liệu, vì vậy hãy lưu ý rằng bộ nhớ trống còn lại sẽ cần thiết cho việc này. Nếu bạn có ít bảng MyIsam và tổng kích thước của chúng nhỏ, hãy để giá trị này trong khoảng 32M.

innodb_buffer_pool_size

Nếu bạn chỉ sử dụng bảng InnoDB, hãy đặt giá trị này ở mức tối đa có thể cho hệ thống của bạn. Bộ đệm InnoDB lưu trữ cả dữ liệu và chỉ mục (và bộ đệm của hệ điều hành không được sử dụng), vì vậy giá trị của khóa này phải được đặt thành 70%...80% bộ nhớ khả dụng.

Nếu máy chủ của bạn chạy trên Linux hoặc Unix, đừng quên đặt tham số innodb_flush_method thành “O_DIRECT” để tránh lưu vào bộ nhớ đệm ở cấp hệ điều hành mà Mysql đã lưu vào bộ nhớ đệm.

innodb_log_file_size

Hãy chú ý đến tham số này nếu bạn có số lượng lớn bản ghi. Kích thước của khóa này càng lớn thì việc ghi dữ liệu sẽ càng hiệu quả. Nhưng hãy nhớ rằng điều này sẽ làm tăng thời gian phục hồi hệ thống! Thông số này thường được đặt thành 64M-512M.

innodb_flush_log_at_trx_commit

Thông số này ảnh hưởng đáng kể đến tốc độ thao tác (ghi) của bảng innoDB.
Giá trị “1” có nghĩa là mọi giao dịch đã hoàn thành sẽ đồng bộ hóa nhật ký vào đĩa.
Giá trị “2” cũng thực hiện điều tương tự, chỉ có điều nó lưu nhật ký không phải vào đĩa mà vào bộ nhớ đệm của hệ điều hành. Giá trị này phù hợp trong hầu hết các trường hợp, bởi vì... không thực hiện thao tác ghi tốn kém sau mỗi giao dịch. Trong trường hợp này, nhật ký được ghi vào đĩa với độ trễ vài giây, điều này rất an toàn về mặt an toàn dữ liệu.
Giá trị “0” sẽ cho hiệu suất tốt nhất. Trong trường hợp này, bộ đệm sẽ được xóa vào tệp nhật ký bất kể giao dịch. Bạn phải tự chịu rủi ro khi đặt tham số này thành “0” vì trong trường hợp này, nguy cơ mất dữ liệu sẽ tăng lên.

bảng_cache

Phím này xác định bộ nhớ được phân bổ để lưu trữ các bảng đang mở. Nếu bạn có vài trăm bảng, hãy đặt giá trị này thành 1024. Nếu bạn có số lượng kết nối khổng lồ, hãy tăng dần giá trị này, bởi vì Một bản ghi riêng biệt được lưu trữ cho mỗi kết nối.

thread_cache

Tham số này giúp tránh các thao tác tạo/hủy luồng khi kết nối với máy chủ. Đặt tham số này thành 16 và tăng nếu cần. Kiểm tra chỉ báo “Threads_created”, lý tưởng nhất là nó phải bằng 0:

Mysql> hiển thị trạng thái như 'threads_created'; +------–+---–+ | Tên biến | Giá trị | +------–+---–+ | Chủ đề_được tạo | 423312 | +------–+--–+

query_cache_size

Giá trị của tham số này xác định dung lượng bộ nhớ sẽ được sử dụng cho bộ đệm truy vấn. Đừng quá bận tâm với việc thiết lập các giá trị lớn. Bộ đệm truy vấn không được lớn, vì... mysql sẽ ngốn tài nguyên để quản lý dữ liệu trong bộ đệm. Bắt đầu với 32M...128M và tăng lên nếu cần.

Kể từ phiên bản 3.22, MySQL có thể đọc các tùy chọn khởi động mặc định cho máy chủ và máy khách từ các tệp tùy chọn.

Trên Unix, các tham số MySQL mặc định được đọc từ các tệp sau:

DATADIR là thư mục dữ liệu MySQL (thường là /usr/local/mysql/data cho cài đặt nhị phân hoặc /usr/local/var cho cài đặt nguồn). Lưu ý rằng đây là thư mục được chỉ định trong quá trình thiết lập, không phải thư mục được chỉ định bằng --datadir khi khởi động mysqld! (--datadir không ảnh hưởng đến việc xem các tệp tham số của máy chủ, vì chúng được xem trước khi các đối số dòng lệnh được xử lý).

Trên Windows, các tham số MySQL mặc định được đọc từ các tệp sau:

Xin lưu ý rằng trên Windows tất cả các đường dẫn phải được chỉ định bằng / thay vì \. Nếu bạn phải sử dụng \, bạn phải chỉ định nó hai lần vì \ là ký tự thoát trong MySQL.

MySQL cố gắng đọc các tệp tham số theo thứ tự được liệt kê ở trên. Nếu có một số tệp như vậy thì tham số được chỉ định trong tệp đến sau sẽ được ưu tiên hơn cùng tham số được chỉ định trong tệp nằm trước đó. Các tùy chọn được chỉ định trên dòng lệnh được ưu tiên hơn các tùy chọn được chỉ định trong bất kỳ tệp tùy chọn nào. Một số tham số có thể được đặt bằng cách sử dụng biến môi trường. Các tùy chọn được chỉ định trên dòng lệnh hoặc trong các tệp tùy chọn được ưu tiên hơn các biến môi trường (xem Phụ lục F, Biến môi trường).

Dưới đây là danh sách các chương trình hỗ trợ các tệp tham số: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk và myisampack.

Bất kỳ tham số nào có thể được chỉ định trên dòng lệnh khi chạy chương trình MySQL cũng có thể được chỉ định trong tệp tham số (không có dấu gạch chéo kép ở đầu). Để có danh sách các tùy chọn khả dụng, hãy chạy chương trình với tùy chọn --help.

Các tệp tham số có thể chứa các dòng như sau:

    Dòng chú thích bắt đầu bằng ký tự " # " hoặc " ; ". Các dòng trống sẽ bị bỏ qua.

    nhóm là tên của chương trình hoặc nhóm mà bạn muốn đặt tham số. Bất kỳ tham số hoặc chuỗi nào chỉ định giá trị biến được chỉ định sau một dòng nhóm sẽ được gán cho nhóm được đặt tên cho đến khi tệp tham số kết thúc hoặc dòng nhóm khác được chỉ định.

    Tương đương với --option trên dòng lệnh.

    Tương đương với --option=value trên dòng lệnh.

    tập-biến = biến=giá trị

    Tương đương với --set-variable Variable=value trên dòng lệnh. Cú pháp này phải được sử dụng để đặt các biến mysqld. Lưu ý rằng --set-variable không được sử dụng kể từ MySQL 4.0. Chỉ cần sử dụng --variable=value .

Nhóm khách hàng cung cấp khả năng thiết lập các tham số áp dụng cho tất cả các máy khách MySQL (ngoại trừ chính mysqld). Nhóm này rất tốt cho việc chỉ định mật khẩu để sử dụng khi kết nối với máy chủ (nhưng hãy đảm bảo rằng chỉ bạn mới có quyền đọc và ghi tệp này).

Lưu ý rằng đối với các tham số và giá trị, mọi khoảng trắng được nhập trước hoặc sau chúng sẽ tự động bị xóa. Các chuỗi thoát sau đây có thể được sử dụng trong các chuỗi giá trị: "\b", "\t", "\n", "\r", "\\" và "\s" ("\s" là khoảng trắng).

Ví dụ về tệp tùy chọn chung điển hình:

Port=3306 socket=/tmp/mysql.sock port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M quick

Ví dụ về tệp tùy chọn người dùng thông thường:

# Mật khẩu được chỉ định sẽ được gửi đến tất cả các máy khách MySQL tiêu chuẩn pass=my_password no-auto-rehash set-variable = connect_timeout=2 Interactive-timeout

Nếu bạn có bản phân phối nguồn thì có thể tìm thấy các tệp cấu hình ví dụ có tên my-xxxx.cnf trong thư mục tệp hỗ trợ. Để phân phối nhị phân, hãy xem thư mục DIR/support-files, trong đó DIR là tên của thư mục cài đặt MySQL (thường là /usr/local/mysql). Hiện tại có các tệp cấu hình mẫu cho các hệ thống nhỏ, vừa, lớn và rất lớn. Để thử nghiệm tệp, bạn có thể sao chép my-xxxx.cnf vào thư mục chính của mình (đổi tên bản sao thành .my.cnf).

Tất cả các máy khách MySQL hỗ trợ các tệp tùy chọn đều chấp nhận các tùy chọn sau:

Xin lưu ý rằng các tham số trên phải xuất hiện đầu tiên trên dòng lệnh! Tuy nhiên, tùy chọn --print-defaults có thể được sử dụng ngay sau lệnh --defaults-xxx-file.

Lưu ý dành cho nhà phát triển: Quá trình xử lý tệp tham số diễn ra như sau: tất cả các tham số khớp (nghĩa là các tham số trong nhóm khớp) đều được xử lý trước bất kỳ đối số dòng lệnh nào. Thuật toán này rất phù hợp cho các chương trình, nếu cùng một tham số được chỉ định nhiều lần, hãy sử dụng phiên bản cuối cùng của tham số đó. Nếu bạn đang làm việc với một chương trình cũ đọc nhiều tham số theo cách này nhưng không đọc các tệp tham số, bạn chỉ cần thêm hai dòng để cung cấp khả năng này. Để biết cách thực hiện việc này, chỉ cần nhìn vào mã nguồn của bất kỳ máy khách MySQL tiêu chuẩn nào.

Tôi không biết tại sao, nhưng theo mặc định, cài đặt MySQL được thiết kế cho máy tính để bàn của những năm 90. Ví dụ: 8Mb bộ nhớ cho các chỉ mục InnoDB. Hãy nhớ Bill Gates đã nói rằng “Bộ nhớ 640 KB là đủ cho tất cả mọi người”. Cài đặt MySQL mặc định từ loạt bài này.

Đầu tiên mình trích xuất từ ​​config (RAM 4G, AMD Athlon 64 X2 Dual 5600+)

# CHỈ UTF! CHỈ CỨNG! collation_server=utf8_general_ci character_set_server=utf8 default-character-set = utf8 # để mặc định là InnoDB default-storage-engine = InnoDB key_buffer_size = 512M innodb_buffer_pool_size = 512M innodb_additable_mem_pool_size = 16M innodb_flush_method = O _DIRECT innodb_flush_log_at_ trx_commit = 2 innodb_thread_concurrency = 8 tham gia_buffer_size = 8M Sort_buffer_size = 8M read_rnd_buffer_size = 8M tmp_table_size = 64M max_heap_table_size = 32M table_cache = 256 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 query_cache_type = 2 query_cache_limit = 1M query_cache_size = 32M

Làm thế nào để xem có vấn đề gì với cơ sở dữ liệu
Tùy chọn nhanh nhất là truy cập phpMyAdmin trong tab “Trạng thái MySQL hiện tại”
Ở đó bạn sẽ thấy các mẹo về những cài đặt bạn cần điều chỉnh.

Bản thân các cài đặt có thể được xem trong cùng một phpMyAdmin trong tab “Biến hệ thống”.

MySQL có một số cài đặt mà bạn có thể ép xung cơ sở dữ liệu lên không gian đầu tiên. Đầu tiên là cài đặt lưu trữ chỉ mục trong bộ nhớ. Các chỉ mục không chỉ tăng tốc độ truy xuất đáng kể mà nếu chúng được lưu trữ trong bộ nhớ chứ không phải trên đĩa (nơi chúng thường được đặt), lợi nhuận sẽ rất đáng kể.

key_buffer_size = 512M
Do đó, chúng tôi phân bổ 512 Mb cho các chỉ mục của bảng MyISAM. Thực tế là tôi có một nửa cơ sở dữ liệu của mình trong MyISAM (đây là cách nó đã xảy ra trong lịch sử). 99,9% cơ sở dữ liệu này được sử dụng để đọc, vì vậy không có ích gì khi chuyển sang InnoDB.

innodb_buffer_pool_size = 512M
Chúng tôi phân bổ cùng một lượng bộ nhớ cho các bảng InnoDB.
Ở đây bạn cần biết khi nào nên dừng lại. Nếu bạn có 1 cơ sở dữ liệu có kích thước 100 Mb thì việc phân bổ 1 GB bộ nhớ cũng chẳng ích gì - dù sao thì nó cũng sẽ không được sử dụng.
Thứ hai, bạn không cần nhìn vào kích thước của bảng mà nhìn vào kích thước của các chỉ mục. Ví dụ thực tế: một bảng gồm 300.000 nhận xét nặng 300 MB và các chỉ mục của nó chiếm ít hơn 15 lần, điều này khá logic vì các chỉ mục thường được đặt trên các cột số và cột tạm thời chứ không phải trên văn bản. Bạn có thể xem lại điều này trong phpMyAdmin

innodb_bổ sung_mem_pool_size = 16M
Dung lượng bộ nhớ được InnoDB phân bổ để lưu trữ các cấu trúc bên trong khác nhau.

innodb_flush_method = O_DIRECT
Ở đây chúng tôi vô hiệu hóa tính năng đệm bảng cho hệ thống tệp và yêu cầu MySQL truy cập trực tiếp vào tệp.

innodb_flush_log_at_trx_commit = 2
Với mỗi giao dịch, MySQL ghi nhật ký và xóa nó vào đĩa (giá trị 1). Giá trị 2 - đặt lại vào bộ nhớ. Việc tôi mất giao dịch trong 2 giây cuối cùng nếu máy chủ gặp sự cố không phải là điều nghiêm trọng.

tham gia_buffer_size = 8M
Bộ nhớ dành cho các truy vấn có liên kết, khi liên kết xảy ra mà không sử dụng chỉ mục.

sắp xếp_buffer_size = 8M
read_rnd_buffer_size = 8M
Hữu ích cho các truy vấn có tính năng sắp xếp ORDER BY và nhóm GROUP BY. Nếu giá trị nhỏ, việc sắp xếp sẽ diễn ra trong bảng tạm thời trên đĩa.

tmp_table_size = 64M
max_heap_table_size = 32M
Cài đặt lưu trữ các bảng tạm thời trong bộ nhớ. Các bảng tạm thời thường được hình thành trong quá trình nối lớn.

bảng_cache = 256
Số lượng bảng mở đồng thời tối đa.

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1