Cài đặt Percona toolkit – công cụ hỗ trợ đắc lực cho MySQL

Percona toolkit là tập hợp những công cụ, ứng dụng chạy trên dòng lệnh được phát triển bởi hãng Percona, giúp chúng ta có thể quản lý, phân tích, tối ưu những cơ sở dữ liệu như MySQL hoặc MongoDB một cách hiệu quả và nhanh chóng hơn.

Trước tiên chúng ta cần add GPG key và add repo vào Linux

Tiếp theo chúng ta tiến hành update để Linux có thể nhận được repo mới của Percona

Sau cùng chúng ta tiến hành cài đặt Percona toolkit

 

Phân tích những query chạy chậm trên MySQL bằng mysqldumpslow và pt-query-digest

*** Lưu ý: bài viết này dành cho MySQL version 5.5 trở lên. Đối với những version cũ hơn có thể những cú pháp, câu lệnh trong bài viết này sẽ không chính xác.

MySQL có thể lưu log những câu lệnh query chạy chậm và tốn nhiều thời gian để thực thi. Việc lưu log như vậy có thể giúp chúng ta debug được dễ dàng cũng như dễ dàng fix những lỗi trong code dẫn đến việc xảy ra những câu query của MySQL chạy chậm.

Bật tính năng slow-query-log

Mở file cấu hình của MySQL /etc/mysql/my.cnf và chỉnh với những thông số như bên dưới

Dòng cuối cùng sẽ khai báo với slow-query-log rằng chúng ta sẽ lưu log những câu query không sử dụng index

Sử dụng mysqldumpslow

Câu lệnh dưới đây sẽ hiển thị top 5 câu query trả về nhiều rows nhất, bạn có thể kiểm tra nếu quá nhiều rows trả về trong câu query, có thể bạn quên không sử dụng LIMIT của MySQL

Còn dưới đây là câu lệnh trả về top 5 câu query được chạy nhiều nhất trong slow logs. Bạn có thể kiểm tra và tối ưu chúng.

Sử dụng pt-query-digest

Công cụ này là 1 phần trong gói Percona toolkit

Chạy dòng lệnh dưới đây để phân tích slow queries

Nếu bạn đang có nhiều database, thì bạn có thể lọc theo database name bằng cách sau (db_wordpress là tên của database, bạn có thể thay tương ứng bằng tên database của bạn)

Sử dụng mysqlsla

Đây là 1 ứng dụng của bên thứ 3, bạn có thể tham khảo trên Github tại link này => https://github.com/daniel-nichter/hackmysql.com

Chạy dòng lệnh dưới đây để phân tích slow queries

Lọc theo database như sau

 

Căn bản MySQL

MySQL là 1 phần mềm quản lý cơ sở dữ liệu (database) mã nguồn mở giúp người dùng lưu trữ, sắp xếp và truy cập dữ liệu. MySQL là 1 phần mềm mạnh mẽ với nhiều tính năng linh hoạt, trong bài viết này tôi sẽ giới thiệu căn bản cách sử dụng MySQL thông qua dòng lệnh Linux.

Cài đặt MySQL lên CentOS hoặc Ubuntu

Cài đặt trên Ubuntu

Cài đặt trên CentOS

Truy cập vào MySQL shell (môi trường dòng lệnh của MySQL)

Sau khi gõ lệnh như trên và Enter, MySQL sẽ hỏi mật khẩu của user root, bạn gõ vào và Enter sẽ đăng nhập được và MySQL shell

Trước khi tiếp tục có 2 điều bạn cần lưu ý như sau:

– Tất cả các lệnh của MySQL đều kết thúc bằng dấu ; (chấm phẩy), nếu không kết thúc bằng ký tự ; lệnh sẽ không được thực thi.
– Một điều nữa không bắt buộc là lệnh của MySQL được viết bằng chữ in hoa và tên cơ sở dữ liệu (database), tên bảng (table), tên user được viết bằng chữ thường để phân biệt cho dễ dàng. Tuy nhiên, một điều bạn cần nhớ là các lệnh của MySQL không phân biệt viết hoa / viết thường.

Tạo / xóa cơ sở dữ liệu

– Trước tiên bạn có thể xem những database hiện có trong MySQL như sau

– Kết quả hiển thị sẽ tương tự như sau

– Tạo database mới

– Ví dụ tạo database mới tên là “events”

– Kết quả sau khi tạo database “events”

– Xóa database (trong ví dụ này là xóa database “events”)

Truy cập database

– Sau khi tạo database, bạn có thể truy cập vào database bằng lệnh sau (ví dụ database “events”)

– Bạn có thể xem toàn bộ tables trong database được chọn (“events”) như sau

Tạo table (trong ví dụ này là tạo table “potluck” trong database “events”)

– Dòng lệnh trên sẽ thực hiện các điều sau

a/ Tạo table “potluck” trong database “events”
b/ Tạo 5 column trong table “potluck” là “id”, “name”, “food”, “confirmed” và “signup_date”
c/ Column “id” với option “INT NOT NULL PRIMARY KEY AUTO_INCREMENT” nghĩa là kiểu lưu trữ của “id” là integer, NOT NULL = không được rỗng, PRIMARY KEY = khóa chính, AUTO_INCREMENT = tự động tăng +1 mỗi khi insert row mới vào trong table “potluck”
d/ Column “name” sử dụng kiểu giá trị VARCHAR và giới hạn bởi 20 ký tự
e/ Column “food” sử dụng kiểu giá trị VARCHAR và giới hạn bởi 30 ký tự
f/ Column “confirmed” sử dụng kiểu giá trị CHAR và chỉ cho phép lưu 1 ký tự (ví dụ “Y” hoặc “N”)
g/ Column “signup_date” được lưu theo kiểu ngày tháng năm (DATE) với kiểu format là yyyy-mm-dd (ví dụ 2016-10-26 => ngày 26 tháng 10 năm 2016)

– Sau khi tạo table “potluck” bạn có thể kiểm tra lại

– Để hiển thị toàn bộ thông tin về table “potluck” bao gồm tên các column, kiểu lưu trữ dữ liệu v.v… bạn có thể sử dụng lệnh sau

– Kết quả trên dòng lệnh sẽ như sau

Thêm thông tin vào table

– Bây giờ chúng ta sẽ insert 1 số thông tin vào table “potluck”

– Dòng lệnh sẽ trả về như sau nếu insert thành công

– Chúng ta sẽ thêm vài thông tin nữa

– Sau khi thêm xong, chúng ta sẽ hiển thị toàn bộ những gì đã thêm vào table “potluck” bằng cách chạy lệnh sau

– Kết quả trả về trên dòng lệnh sẽ như sau

Cập nhật thông tin đã thêm vào table

– Ví dụ

(Tìm tất cả những dòng trong table “potluck” có “name” là “Sandy”, sau đó cập nhật column “confirmed” thành “Y”)

Thêm / xóa column trong table

– Thêm column “email” vào table “potluck”

– Mặc định khi thêm mới column, MySQL sẽ xếp column đó ở phía dưới cùng của table. Bạn có thể chỉ định thứ tự của column được thêm vào như sau:

(Nghĩa là thêm mới column “email” đằng sau column “name”)

– Bạn có thể xóa column “email” bằng cách thực thi lệnh sau

Xóa dòng trong table

– Để xóa 1 dòng hoặc vài dòng trong table với điều kiện cho trước bạn chạy lệnh sau ([table name] là tên table, [column name] là tên column [field text] là giá trị so sánh với tên column)

– Ví dụ: xóa tất cả những dòng trong table “potluck” có column “name” là “Sandy”