1. Tổng quan

2. Bài này giải thích lần lượt tất cả các option mà bạn đã nêu xuất hiện trong hai file cấu hình (bản cũ old_my.cnf và bản mới new_my.cnf), gồm:

   • Nhóm kết nối: port, socket
   • Nhóm bảo mật/I/O: local-infile=0, skip-external-locking
   • Nhóm bộ nhớ toàn cục: key_buffer_size, query_cache_size
   • Nhóm giới hạn gói: max_allowed_packet
   • Nhóm cache bảng: table_open_cache, table_definition_cache
   • Nhóm bộ nhớ per-thread: sort_buffer_size, read_buffer_size, read_rnd_buffer_size, myisam_sort_buffer_size
   • Nhóm quản lý thread: thread_cache_size
   • Nhóm công cụ: [mysqldump] với quick, max_allowed_packet; [mysql] với no-auto-rehash; [myisamchk] với các buffer; [mysqlhotcopy] với interactive-timeout

   • Mục tiêu:

   • Giải nghĩa ngắn gọn từng option

   • Chỉ ra giá trị “cũ” (rất lớn, thiên về MyISAM) và giá trị “mới” (hạ xuống, an toàn hơn)
   • Ví dụ để thấy lúc nào lợi, lúc nào hại
   • Đưa lời khuyên cuối để bạn chỉnh theo thực tế
   • Ý chính: file mới an toàn hơn vì nó hạ các ngưỡng có thể gây bùng nổ bộ nhớ; file cũ nới quá tay (1GB, 8GB) nên dễ làm MySQL chiếm RAM lớn chỉ vì vài truy vấn xấu.

3. File cấu hình cũ ```conf [client] port = 3306 socket = /var/lib/mysql/mysql.sock

   [mysqld]
   local-infile=0
   port        = 3306
   socket      = /var/lib/mysql/mysql.sock
   skip-external-locking
   key_buffer_size = 1024M
   max_allowed_packet = 1024M
   table_open_cache = 1024
   sort_buffer_size = 800M
   read_buffer_size = 800M
   read_rnd_buffer_size = 1024M
   myisam_sort_buffer_size = 1024M
   thread_cache_size = 64
   query_cache_size = 8192M
   log_bin_trust_function_creators = 1
   max_connections = 5000
   sql_mode=""
   innodb_io_capacity = 2000
   innodb_strict_mode = 0
   event_scheduler=ON
   thread_concurrency = 16
   server-id   = 1
   innodb_buffer_pool_size = 14G
   innodb_lock_wait_timeout = 1800
   innodb_rollback_on_timeout=1
   
   [mysqldump]
   quick
   max_allowed_packet = 1024M
   
   [mysql]
   no-auto-rehash
   
   [myisamchk]
   key_buffer_size = 1024M
   sort_buffer_size = 1024M
   read_buffer = 200M
   write_buffer = 200M
   
   [mysqlhotcopy]
   interactive-timeout
   ```
   

   1. File cấu hình mới ```conf [client] port = 3306 socket = /var/lib/mysql/mysql.sock

      [mysqld] local-infile=0 port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking

      key_buffer_size = 128M max_allowed_packet = 256M

      --- Table cache / files ---

      table_open_cache = 8192 table_definition_cache = 8192 open_files_limit = 65535

      --- Per-thread buffers ---

      sort_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 8M join_buffer_size = 2M

      query_cache_size = 0 query_cache_type = 0

      myisam_sort_buffer_size = 16M thread_cache_size = 64

      log_bin_trust_function_creators = 1 max_connections = 300 sql_mode="" innodb_io_capacity = 2000 innodb_strict_mode = 0 event_scheduler=ON

      InnoDB memory

      innodb_buffer_pool_size = 10G innodb_buffer_pool_instances = 8

      innodb_log_file_size = 4G innodb_log_buffer_size = 512M innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 120 innodb_rollback_on_timeout = 1

      [mysqldump] quick max_allowed_packet = 256M

      [mysql] no-auto-rehash

      [myisamchk] key_buffer_size = 16M sort_buffer_size = 128M read_buffer = 200M write_buffer = 200M

      [mysqlhotcopy] interactive-timeout ```

1. Nhóm kết nối chung

2.1. port = 3306

• Ý nghĩa: MySQL sẽ lắng nghe TCP trên cổng 3306.
• Lợi: chuẩn, client dễ kết nối.
• Hại: nếu để public mà không firewall thì dễ bị scan → thường đi kèm firewall hoặc bind-address nội bộ.

2.2. socket = /var/lib/mysql/mysql.sock

• Ý nghĩa: kết nối nội bộ trên cùng máy qua file socket (nhanh hơn TCP).
• Lợi: tốc độ, bảo mật nội bộ.
• Hại: nếu đường dẫn sai, client nội bộ không kết nối được.

1. Nhóm trên [mysqld] (máy chủ chính)

3.1. Bảo mật/I/O

• local-infile=0

  • Tắt LOAD DATA LOCAL INFILE.
  • Lợi: chặn kiểu lạm dụng tải file từ client vào server.
  • Hại: nếu app thật sự cần import kiểu này thì sẽ bị lỗi, phải bật lại.

  • skip-external-locking

  • Bỏ khóa file ở OS (kiểu lock file của hệ điều hành).

  • Lợi: giảm xung đột lock giữa OS và MySQL, tăng ổn định.
  • Hại: rất ít, gần như để vậy là được.

3.2. Bộ nhớ toàn cục

• key_buffer_size

  • Dùng để cache chỉ mục của bảng MyISAM.
  • Bản cũ: đặt rất lớn (cỡ 1024M = 1GB).
  • Bản mới: hạ xuống nhỏ hơn (kiểu 128M).
  • Lợi khi lớn: nếu bạn thực sự chạy MyISAM nhiều → đọc index nhanh.
  • Hại khi lớn: đây là buffer global, MySQL giữ sẵn; nếu bạn chủ yếu dùng InnoDB thì 1GB này là phí và làm giảm RAM còn lại cho InnoDB và OS.

  • Ví dụ:

  • Hệ thống vẫn còn vài bảng MyISAM nhỏ: 64M–128M là hợp lý hơn 1024M.

  • Hệ thống toàn InnoDB: để 16M cũng sống tốt.

• query_cache_size = 8192M

  • Đây là bộ nhớ để MySQL lưu kết quả SELECT.
  • 8192M = 8GB là rất lớn.
  • Lợi: nếu hệ thống chủ yếu đọc, query trùng nhau nhiều, dữ liệu ít thay đổi → trả kết quả rất nhanh.

  • Hại:

  • Mỗi lần bảng bị ghi, MySQL phải vô hiệu entry cache liên quan → cache càng to, việc này càng tốn → dễ chậm.

  • 8GB là cục RAM chiếm luôn khi start.
  • Các phiên bản mới thường tắt luôn query cache vì không hiệu quả với workload nhiều ghi.
  • Lời khuyên: trừ khi bạn biết chắc app là đọc nhiều, ít ghi, và bạn đang ở MySQL đời cũ, còn không thì tắt hoặc để rất nhỏ (32M–128M).

3.3. Giới hạn gói

• max_allowed_packet

  • Ý nghĩa: gói dữ liệu MySQL protocol tối đa mà server chấp nhận cho 1 lần gửi/nhận.
  • Bản cũ: 1024M (1GB) → rất thoáng.
  • Bản mới: 256M → vẫn đủ cho dữ liệu to, nhưng đỡ rủi ro hơn.
  • Lợi khi lớn: chạy dump có dòng SQL to, hoặc insert nhiều, hoặc lỡ có BLOB → không lỗi “packet too large”.
  • Hại khi quá lớn: MySQL phải cấp bộ nhớ để nhận gói đó; nếu nhiều kết nối cùng gửi gói to → RAM tăng đột biến.
  • Ví dụ: app chỉ insert JSON vài chục KB → 32M–64M đủ; chỉ cần khi bạn thật sự lưu file/BLOB mới cần tới hàng trăm MB.

3.4. Cache bảng

• table_open_cache

  • Ý nghĩa: số bảng MySQL giữ mở sẵn (file handle + cấu trúc) để khỏi mở/đóng liên tục.
  • Bản cũ: 1024.
  • Bản mới: 8192.
  • Lợi khi lớn: app nhiều bảng, nhiều kết nối đụng nhiều bảng khác nhau → cache lớn giúp đỡ chi phí mở/đóng.
  • Hại khi quá lớn: tốn thêm file handle và bộ nhớ quản lý, nhưng mức 8192 là hợp lý với hệ thống nhiều bảng.
  • Ví dụ: app ERP nhiều schema → 8192 tốt hơn 1024.

• table_definition_cache (chỉ thấy ở bản mới)

  • Ý nghĩa: cache khai báo/metadata của bảng để khỏi đọc lại từ đĩa.
  • Lợi: nhiều bảng, nhiều kết nối → giảm I/O metadata.
  • Hại: chiếm thêm bộ nhớ quản lý, nhưng thường không phải thứ gây nổ.

3.5. Bộ nhớ per-thread (chính là nhóm bạn hỏi rất nhiều)

Các option:

• sort_buffer_size
• read_buffer_size
• read_rnd_buffer_size
• myisam_sort_buffer_size

Đặc điểm chung:

• Đây là buffer được cấp cho tác vụ cụ thể (sort, full scan, đọc ngẫu nhiên sau sort, build index MyISAM…).
• Nó là per-thread/per-operation → mỗi connection làm việc đó thì có thể cấp 1 cục riêng.
• Bản cũ: đặt cực lớn (800M, 1024M) → chỉ cần 1 query vừa join vừa sort là có thể ăn 2–3 cục lớn cùng lúc.
• Bản mới: xu hướng hạ xuống để tránh bùng nổ bộ nhớ.

Mô tả từng cái:

3.5.1. sort_buffer_size

• Dùng khi MySQL phải sắp xếp mà không đi được bằng index.
• Nếu đặt 800M: mỗi lần sort, thread đó có thể xin hẳn 800MB.
• Lợi khi lớn: sort dataset lớn trong RAM nhanh hơn, ít ghi ra đĩa.
• Hại khi lớn: một thread đã có thể ăn 800MB; nhiều thread cùng sort → đầy RAM.

3.5.2. read_buffer_size

• Dùng khi MySQL quét tuần tự (full table scan).
• Lợi khi vừa phải: đọc đĩa theo block lớn hơn, nhanh hơn.
• Hại khi đặt quá lớn: mỗi thread full scan sẽ ăn nguyên cục đó.

3.5.3. read_rnd_buffer_size

• Dùng khi MySQL phải đọc lại các row theo thứ tự không tuần tự (ví dụ sau khi sort biết cần dòng #5, #200, #17…).
• Lợi khi vừa phải: giảm I/O lặt vặt.
• Hại khi lớn: giống trên, mỗi thread ăn một cục.

3.5.4. myisam_sort_buffer_size

• Dùng khi MyISAM cần xây lại index (REPAIR, ALTER…).
• Bản cũ để 1024M → thao tác repair chạy nhanh, nhưng rất tốn RAM.
• Bản mới để nhỏ (128M) → an toàn hơn.
• Nếu server chủ yếu chạy, không repair thường xuyên thì không cần để to.

Lưu ý quan trọng:

• Những buffer này không “xài bao nhiêu cấp bấy nhiêu”, mà nhiều phiên bản sẽ “cấp theo cấu hình” để có sẵn chỗ làm việc.
• Nếu bạn đặt 800M và query phải sort, nó có thể cấp luôn 800M cho query đó.
• Nếu query đó còn join kiểu dùng join-buffer nữa thì nó xin thêm một cục khác.
• Và bạn còn max_connections nữa → nhiều connection cùng làm → rủi ro.

3.6. Quản lý thread

• thread_cache_size = 64

  • Ý nghĩa: giữ tối đa 64 thread rảnh để tái dùng.
  • Lợi: nhiều kết nối ngắn → đỡ chi phí tạo thread.
  • Hại: gần như không đáng kể; 64 là con số ổn.

1. Nhóm công cụ phụ

4.1. [mysqldump]

• quick

  • Đọc từng row một, không giữ hết vào RAM → tốt cho dump bảng lớn.

  • max_allowed_packet

  • Bản cũ: 1024M

  • Bản mới: 256M
  • Lợi khi lớn: dump bảng có row to, text to sẽ không bị lỗi.
  • Hại: cùng kiểu với bên server, quá to là phí.

4.2. [mysql]

• no-auto-rehash

  • Tắt tự động gợi ý/tự động hoàn thành tên bảng khi dùng client mysql.
  • Lợi: mở client nhanh hơn.
  • Hại: không bấm tab ra tên bảng được.

4.3. [myisamchk]

• key_buffer_size, sort_buffer_size, read_buffer, write_buffer
• Đây là cấu hình cho công cụ sửa/chẩn đoán MyISAM chạy bên ngoài mysqld.
• Bản cũ để 1024M → mỗi lần chạy myisamchk có thể ăn 1GB RAM.
• Bản mới hạ xuống (16M, 128M…) → vẫn sửa được nhưng không ăn RAM quá đáng.
• Lợi khi lớn: sửa bảng MyISAM to sẽ nhanh.
• Hại khi lớn: bạn chạy kiểm tra lúc server đang ít RAM → dễ nghẹt.

4.4. [mysqlhotcopy]

• interactive-timeout

  • Dùng timeout kiểu interactive khi sao lưu nhanh MyISAM.
  • Lợi: tránh kết nối treo.
  • Hại: hầu như không.

1. So sánh lợi – hại giữa 2 file

5.1. File cũ (old_my.cnf)

• Đặc điểm:

  • Nhiều giá trị “khổng lồ”: key_buffer_size=1024M, max_allowed_packet=1024M, các buffer MyISAM 800M–1024M, query_cache_size=8192M.
  • Rõ ràng thiên về MyISAM, hoặc admin muốn “cho thoải mái”.

  • Lợi:

  • Nếu workload đúng kiểu: MyISAM nhiều, ít connection, đôi lúc cần repair nhanh, và có thao tác lớn → chạy rất mượt.

  • Hại:

  • Rất dễ ăn RAM chết người khi số connection tăng.

  • Toàn cục như query_cache_size=8192M chiếm RAM ngay cả khi không cần.
  • Không phù hợp với môi trường nhiều app nhỏ, traffic web tăng đột biến.

5.2. File mới (new_my.cnf)

• Đặc điểm:

  • Hạ các ngưỡng có thể gây nổ: max_allowed_packet=256M, MyISAM tool nhỏ lại.
  • Tăng cache bảng: table_open_cache=8192, có thêm table_definition_cache.

  • Lợi:

  • An toàn, ổn định hơn khi nhiều connection.

  • Phù hợp hệ thống thiên về InnoDB, ít MyISAM.

  • Hại:

  • Nếu bạn thật sự cần import một cục dữ liệu rất to (trên 256M) thì phải nâng lại.

  • Nếu bạn đang chạy duy nhất vài bảng MyISAM cực to và hay repair thì buffer nhỏ hơn sẽ sửa chậm hơn.

1. Lời khuyên

6.1. Nguyên tắc đặt per-thread

• Đặt nhỏ trước (1M–4M) → quan sát → tăng dần.
• Đừng đặt kiểu 800M trừ khi bạn biết rõ 1 query sẽ cần khối đó.

6.2. Giảm bộ nhớ global không dùng

• Nếu chủ yếu InnoDB: giảm key_buffer_size xuống 16M–128M.
• Nếu MySQL version mới, workload nhiều ghi: tắt query_cache_size.

6.3. Giữ giới hạn gói ở mức hợp lý

• Nếu không có BLOB lớn: 64M–128M.
• Nếu có đôi khi cần to: 256M như file mới là hợp lý.

6.4. Đặt table_open_cache và table_definition_cache

• Nếu app nhiều bảng, nhiều kết nối: giá trị file mới (8192) hợp lý.
• Nếu app nhỏ: có thể để thấp hơn để tiết kiệm.

6.5. Luôn nhìn cùng với max_connections

• Vì các buffer này là per-thread, nên nếu bạn cho phép 300 kết nối thì tổng RAM = global + 300 × (tổng per-thread).
• Nếu RAM không nhiều, hạ max_connections xuống 100–150.

6.6. Sơ đồ ý tưởng (logic RAM)

[Global buffers] | +-- key_buffer_size +-- query_cache_size +-- table_*_cache | mỗi connection | +-- sort_buffer_size +-- read_buffer_size +-- read_rnd_buffer_size +-- join_buffer_size (nếu có)

Nhìn vào sẽ thấy: global để quá to → tốn ngay; per-connection để quá to → tốn theo số lượng kết nối.

1. Kết luận

7.1. Hai file cấu hình của bạn khác nhau chủ yếu ở mức “độ chịu chơi” về bộ nhớ:

• File cũ mở rất rộng → phù hợp trường hợp cực kỳ cụ thể, ít connection, cần thao tác MyISAM to.
• File mới thu hẹp lại và nâng cache bảng → phù hợp môi trường thật, nhiều connection, tránh nổ RAM.

7.2. Các option quan trọng nhất cần soi kỹ là:

• max_allowed_packet (giới hạn gói)
• nhóm per-thread (sort_buffer_size, read_buffer_size, read_rnd_buffer_size, myisam_sort_buffer_size)
• bộ nhớ global dễ phí (key_buffer_size, query_cache_size)

7.3. Nếu mục tiêu của bạn là “ổn định, không chết vì RAM” thì đi theo hướng của file mới: global vừa phải, per-thread nhỏ, cache bảng đủ lớn, và tăng nếu thật sự đo được nhu cầu.