MySQL là 1 Database kết hợp chặt chẽ với PHP trong rất nhiều ứng dụng web động. Nhờ tính năng mã nguồn mở và nhỏ gọn, MySQL hiện đang là hệ Database nhanh nhất cho các ứng dụng thông thường.
Tuy nhiên theo thời gian, các ứng dụng web sử dụng MySQL ngày càng
"phình" to ra và Website của bạn ngày càng chạy chậm đi. Đó có phải là
điều không thể tránh khỏi? Hãy tham khảo bài viết sau đây để tăng tốc
MySQL và website của bạn lên gấp nhiều lần.
Một ngày kia bạn nhận ra rằng website của bạn chạy chậm đi, có thể là do đường truyền nhưng còn 1 nguyên nhân khác, đó là máy chủ server tính toán quá nhiều dẫn đến kết quả đưa ra chậm. Đây là điều thường thấy ở những website về Diễn Đàn (Forum) , Tin Tức (Portal) và thương mại điện tử (Ecommerce). Khi số lượng thành viên , số lượng bài viết tăng lên, đồng nghĩa với việc Database khi truy vấn (query) 1 yêu cầu phải duyệt qua tất cả các dữ liệu hiện có để tìm ra dữ liệu thích hợp. Cũng giống như 1 quyển sách. Nếu sách là mỏng, bạn dễ dàng tìm ra thông tin mình cần. Nhưng khi sách dầy lên , thời gian tìm kiếm của bạn sẽ tăng đáng kể.
Việc Database quá tải còn dẫn đến nhiều thiệt hại khác, các hàng đợi (Queue) dài ra , file logs lớn lên chiếm hết không gian đĩa và user khi kết nối sẽ bị từ chối. Rõ ràng là câu báo lỗi "Too many connections" không phải là hiếm gặp trong các website trên Internet.
Một ngày kia bạn nhận ra rằng website của bạn chạy chậm đi, có thể là do đường truyền nhưng còn 1 nguyên nhân khác, đó là máy chủ server tính toán quá nhiều dẫn đến kết quả đưa ra chậm. Đây là điều thường thấy ở những website về Diễn Đàn (Forum) , Tin Tức (Portal) và thương mại điện tử (Ecommerce). Khi số lượng thành viên , số lượng bài viết tăng lên, đồng nghĩa với việc Database khi truy vấn (query) 1 yêu cầu phải duyệt qua tất cả các dữ liệu hiện có để tìm ra dữ liệu thích hợp. Cũng giống như 1 quyển sách. Nếu sách là mỏng, bạn dễ dàng tìm ra thông tin mình cần. Nhưng khi sách dầy lên , thời gian tìm kiếm của bạn sẽ tăng đáng kể.
Việc Database quá tải còn dẫn đến nhiều thiệt hại khác, các hàng đợi (Queue) dài ra , file logs lớn lên chiếm hết không gian đĩa và user khi kết nối sẽ bị từ chối. Rõ ràng là câu báo lỗi "Too many connections" không phải là hiếm gặp trong các website trên Internet.
Những lỗi trên thông thường bắt nguồn từ khâu định nghĩa Database
(define) hay không sử dụng "Mục Lục" (Indexes). Khắc phục những thiếu
sót trên , Database của bạn sẽ "nhẹ nhàng" và nhanh chóng đáng kể. Hãy
xem xét ví dụ sau:
Mã:
CREATE TABLE employee ( employee_number char(10) NOT NULL, firstname varchar(40), surname varchar(40), address text, tel_no varchar(25), salary int(11), overtime_rate int(10) NOT NULL );
Và để tìm thông tin Lương của Nguyễn Nam (mã số 101802) , bạn sẽ query như sau:
Mã:
SELECT salary FROM employee WHERE employee_number = '101832';
MySQL biết rằng phải tìm ở table Employee nhưng nó sẽ không biết bắt
đầu từ đâu. Thậm chí nó cũng không biết trước rằng có bao nhiêu kết quả.
Do đó nó sẽ duyệt qua tất cả danh sách (vd Hơn 300000 người) để tìm
thông tin về Nguyễn Nam.
"Mục Lục" (Index) là 1 file riêng biệt được lưu trữ ở máy chủ và chỉ chứa những Fields mà bạn muốn nó chứa. Nếu bạn tạo 1 Index cho Field employee_number char (mã số công nhân) , MySQL sẽ dễ dàng tìm ra được mã số 1 cách nhanh chóng. Trở lại ví dụ quyển sách, khi cần tìm 1 thông tin, ta thường lật ngay tới phần "Mục Lục" và tìm từ đó để tăng tốc độ tìm. Và việc tạo ra Index này sẽ làm bạn thấy Database của bạn chạy nhanh 1 cách khác thường.
Nhưng trước khi sửa lại cấu trúc của table ở trên , tôi sẽ hướng dẫn bạn 1 chút về cách theo dõi kết quả "Tăng tốc MySQL" mà bạn đang làm. Hãy sử dụng lệnh EXPLAIN
"Mục Lục" (Index) là 1 file riêng biệt được lưu trữ ở máy chủ và chỉ chứa những Fields mà bạn muốn nó chứa. Nếu bạn tạo 1 Index cho Field employee_number char (mã số công nhân) , MySQL sẽ dễ dàng tìm ra được mã số 1 cách nhanh chóng. Trở lại ví dụ quyển sách, khi cần tìm 1 thông tin, ta thường lật ngay tới phần "Mục Lục" và tìm từ đó để tăng tốc độ tìm. Và việc tạo ra Index này sẽ làm bạn thấy Database của bạn chạy nhanh 1 cách khác thường.
Nhưng trước khi sửa lại cấu trúc của table ở trên , tôi sẽ hướng dẫn bạn 1 chút về cách theo dõi kết quả "Tăng tốc MySQL" mà bạn đang làm. Hãy sử dụng lệnh EXPLAIN
Cú pháp: EXPLAIN Query;
Bằng lệnh này bạn sẽ nhận ra được với câu Query của bạn , điều gì đang xảy ra và kiểu kết hợp (Join) nào đang diễn ra bên trong.
Xem ví dụ sau:
Mã:
EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';
Giải thích:
- table : Table nào đang liên quan đến output data
- type : Đây là thông tin quan trọng , nó cho chúng ta biết kiểu query nào nó đang sử dụng. Mức độ từ tốt nhất đến chậm nhất như sau: system, const, eq_ref, ref, range, index, all
- possible_keys : Đưa ra những Index có thể sử dụng để query
- key : và Index nào đang được sử dụng
- key_len : Chiều dài của từng mục trong Index
- ref : Cột nào đang sử dụng
- rows : Số hàng (rows) mà MySQL dự đoán phải tìm
- extra : Thông tin phụ , thật tệ nếu tại cột này là "using temporary" hay "using filesort"
Wow , nhìn lại câu query của chúng ta mới thật khủng khiếp. Không có Possible_keys nào được sử dụng, MySQL phải duyệt qua 9475 hàng mới tìm ra cái ta cần (Hãy tưởng tượng 1 Forum sẽ có đến hơn 200 000 hàng).
Bây giờ chúng ta sẽ thêm Index vào và query lại
Mã:
ALTER TABLE employee ADD INDEX(employee_number char); EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';
Tốt hơn nhiều rồi , kiểu TYPE = Const có nghĩa rằng MYSQL hiểu ra chỉ
có 1 hàng đúng với ý ta, và thể hiện qua cột Rows = 1 , kiểu key =
PRIMARY được sử dụng và chiều dài key_len là 10.Chỉ tìm 1 hàng tất nhiên
rằng tốt hơn nhiều so với tìm 9475 hàng.
Vậy câu hỏi đặt ra là , nếu tôi muốn thêm Index cho những cột mà có thể có nhiều hơn 1 kết quả khi query thì sao?
Vẫn add index như bình thường ,giả sử bạn cần tìm những người có họ là Nguyễn , tên là Nam
Vậy câu hỏi đặt ra là , nếu tôi muốn thêm Index cho những cột mà có thể có nhiều hơn 1 kết quả khi query thì sao?
Vẫn add index như bình thường ,giả sử bạn cần tìm những người có họ là Nguyễn , tên là Nam
Mã:
ALTER TABLE employee ADD INDEX(surname,firstname); EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Nguyễn' and firstname="Nam";
Tuy nhiên , nếu chỉ cần Firstname
Mã:
EXPLAIN SELECT overtime_rate FROM employee WHERE firstname='Name';
thì MySQL sẽ tìm hết vì không hề có Index cho Firstname mà chỉ có Index cho (Surname,Firstname)
Khi nào thì cần Add Index? Bất cứ khi nào bạn thay đổi Table bạn đều cần Add Index lại, giống như khi bạn thay đổi nội dung quyển sách, bạn cần phải làm lại mục lục. Vậy hãy cân nhắc , nếu Database của bạn sử dụng INSERT hay UPDATE nhiều hơn là SELECT thì Index chỉ làm chậm thêm mà thôi.
Có thể nhanh hơn nữa không? Có!
Khi nào thì cần Add Index? Bất cứ khi nào bạn thay đổi Table bạn đều cần Add Index lại, giống như khi bạn thay đổi nội dung quyển sách, bạn cần phải làm lại mục lục. Vậy hãy cân nhắc , nếu Database của bạn sử dụng INSERT hay UPDATE nhiều hơn là SELECT thì Index chỉ làm chậm thêm mà thôi.
Có thể nhanh hơn nữa không? Có!
Bạn không cần phải làm Index cho cả Field mà chỉ cần 1 phần. Giống như
chi tiết Mục Lục của sách mà quá dài cũng làm bạn khá vất vả, do đó họ
chỉ trích dẫn 1 tựa đề. Quay lại với table của chúng ta , Surname và
Firstname chỉ maximum là 40 chars , nếu chúng ta index nó , chúng ta tạo
ra mỗi record đến 80 chars
Có thể tiết kiệm bằng cách sau:
Mã:
ALTER TABLE employee ADD INDEX(surname(20),firstname(20));
Bây giờ thì bạn tiết kiệm được đến 50% mà vẫn đảm bảo được tốc độ rồi
đó (trừ phi bạn làm Index quá ngắn). Có thể bạn nói đĩa cứng server tôi
"vô tư" nhưng hãy nhớ rằng "Nhỏ hơn là nhanh hơn".
ĐIỀU KÌ DIỆU VỚI OPTIMIZE VÀ ANALYZE
"Ma thuật" của MySQL là biết cách chọn khoá (key) nào để query (nếu có). Quá trình này gọi là "query optimizer", nó sẽ "liếc" qua những Index đang có để quyết định sẽ dùng Index nào để tìm. Hãy tưởng tượng bạn đang tìm 1 dĩa CD của "Maria Carrey" có tên là "I Love You" , có nghĩa là có 2 Indexes ở đây , 1 cho tên tác giả và 1 cho tên CD. Bạn nhận thấy rằng danh mục có 20000 tên tác giả và 400000 tên Album. Một cách đơn giản ,bạn sẽ tìm theo tên tác giả. Khi có được , bạn lại thấy rằng "Maria Carrey" có 50 CDs và CD "I Love You" bắt đầu bằng chữ I. Đơn giản và dễ dàng tìm ra cái mình muốn phải không? MySQL cũng vậy nhưng... bạn phải chỉ cho nó bằng cách:
Mã:
ANALYZE TABLE tablename;
Những lệnh DELETE và UPDATE để lại rất nhiều những khoảng trống (gaps)
vô nghĩa cho table (Đặc biệt là khi bạn dùng kiểu varchar hay
text/blob). Điều đó có nghĩa rằng MySQL cũng phải đọc và phân tích những
thứ vô nghĩa đó khi query. Điều này được khắc phục khi bạn chạy
Mã:
OPTIMIZE tablename;
Do đó 2 câu lệnh trên bạn nên chạy 1 cách thường xuyên để bảo đảm tối ưu hoá Database của mình.
Khi database của bạn lớn/nhiều hoặc lượng truy cập và liên tục , chắc
chắn MySQL sẽ chiếm rất nhiều tài nguyên và khiến server load cao. Để
hạn giảm load cho server , chúng ta có thể cache lại kết quả của các lần
query vào bộ nhớ RAM. Tính năng này được MySQL hỗ trợ từ phiên bản
4.0.1 trở lên, để enable chức năng này chúng ta cần đặt các giá trị sau
vào file /etc/my.cnf :
Qcache_free_memory : lượng RAM hiện tại còn trống (chưa sử dụng) cho việc cache. Nếu thông số này quá cao bạn có thể giảm query_cache_size xuống , nếu quá thấp thì nâng query_cache_size lên.
Qcache_hits : số lượng query được lấy trong cache ra sử dụng thay vì phải thực hiện “real query” đến CSDL.
Qcache_inserts : số lượng query đã được add vào cache.
Qcache_lowmem_prunes : mỗi khi vùng nhớ dành cho việc cache bị hết (Qcache_free_memory thấp), khi add thêm query vào cache MySQL cần phải giải phóng 1 ít bộ nhớ để chứa query này. Mỗi lần thu hồi vùng nhớ , con số này tăng lên 1.
Nếu số này quá lớn chứng tỏ lượng RAM dành cho việc cache(query_cache_size) không đủ và cần phải tăng lên. Nếu số này bằng 0 thì lượng RAM dành cho cache quá lớn , cần giảm lại.
Qcache_queries_in_cache : số lượng query hiện tại đang được cache.
query_cache_type = 1- Kiểm tra xem query cache đã được kích hoạt hay chưa :
query_cache_size = 256M
query_cache_limit = 2M
query_cache_type : enable or disable tính năng query cache (enable = 1 ; disable = 0)
query_cache_size : lượng RAM dành cho việc cache query result.
query_cache_limit: dung lượng tối đa của một query result mà mình cho phép cache.
mysql> show variables like ‘%query_cache%’;- Kiểm tra xem query cache đã hoạt động hay chưa :
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| have_query_cache | YES |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———–+
mysql> show status like ‘%qcache%’;Vài thông số quan trọng cần chú ý :
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_free_blocks | 17034 |
| Qcache_free_memory | 36995488 |
| Qcache_hits | 4455579 |
| Qcache_inserts | 702176 |
| Qcache_lowmem_prunes | 87400 |
| Qcache_not_cached | 76260 |
| Qcache_queries_in_cache | 20198 |
| Qcache_total_blocks | 78340 |
+————————-+———-+
Qcache_free_memory : lượng RAM hiện tại còn trống (chưa sử dụng) cho việc cache. Nếu thông số này quá cao bạn có thể giảm query_cache_size xuống , nếu quá thấp thì nâng query_cache_size lên.
Qcache_hits : số lượng query được lấy trong cache ra sử dụng thay vì phải thực hiện “real query” đến CSDL.
Qcache_inserts : số lượng query đã được add vào cache.
Qcache_lowmem_prunes : mỗi khi vùng nhớ dành cho việc cache bị hết (Qcache_free_memory thấp), khi add thêm query vào cache MySQL cần phải giải phóng 1 ít bộ nhớ để chứa query này. Mỗi lần thu hồi vùng nhớ , con số này tăng lên 1.
Nếu số này quá lớn chứng tỏ lượng RAM dành cho việc cache(query_cache_size) không đủ và cần phải tăng lên. Nếu số này bằng 0 thì lượng RAM dành cho cache quá lớn , cần giảm lại.
Qcache_queries_in_cache : số lượng query hiện tại đang được cache.
Các thông số cần tuning sau khi cài đặt mysql-server :
key_buffer_size : là dung lượng RAM được sử dụng để lưu thông tin về indexes của database (file .MYI). Nếu sử dụng MYISAM table thì nên set giá trị này tầm khoảng 30-40% RAM. Giá trị này tốt nhất nên lớn hơn 50% so với file .MYI
innodb_buffer_pool_size : Giá trị này tương tự như key_buffer_size, dùng cho InnoDB.Nếu server chỉ chạy mySQL DB thì nên sử dụng hết tất cả lượng RAM không cần thiết cho giá trị này. Hoặc tính đơn giản hơn là nên để giá trị này cao hơn dung lượng của database 1 ít. (You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces))
See more :
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/ (Calculate innodb mem size)
See more :
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/ (Calculate innodb mem size)
innodb_additional_mem_pool_size : khi giá trị buffer_pool_size hết thì nó sẽ lấy giá trị này để tăng thêm, tuy nhiên không giúp ích được nhiều. Set giá trị này vào khoản 20MB
innodb_log_file_size : dung lượng của log file để lưu trữ các workload. Giá trị này càng lớn thì performance càng cao, tuy nhiên thời gian recovery sẽ cao hơn. Giá trị này nên để trong khoảng 128MB nếu lượng xử lý dữ liệu cao.
Phương pháp tính toán :
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
Phương pháp tính toán :
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
innodb_log_buffer_size : Dung lượng trên RAM dùng cho việc ghi log file. Nên để giá trị tầm 8MB nếu hệ thống có loads lớn.
innodb_flush_log_at_trx_commit : giá trị ghi transaction xuống disk, mặc định là 2, nên để giá trị này = 0 cho best performance, hoặc 1 nếu muốn an toàn dữ liệu cao hơn.
table_cache : Dung lượng để lưu các tables sẽ mở trên RAM, vì việc mở 1 table tốn rất nhiều thời gian. Nên set giá trị này trong khoảng 1024.
thread_cache : Tương tự như table_cache, nên set giá trị này = số core CPU
query_cache_size : cache dùng cho việc read trong khi truy vấn. Nên set giá trị trong khoảng 32MB – 128MB
From :
Another configure