Nội dung
- Bước một - Cài đặt server cộng đồng MySQL trên hệ thống thử nghiệm - Bước hai - Cài đặt database mẫu - Bước 3 - Sử dụng mysqlslap - Khắc phục sự cố - mysqlslap không hiển thị kết quả

Thứ năm, 02/10/2014 | 00:00 GMT+7

Cách đo hiệu suất truy vấn MySQL với mysqlslap

MySQL đi kèm với một công cụ chẩn đoán nhỏ tiện dụng được gọi là mysqlslap đã có từ version 5.1.4. Đó là một công cụ đo điểm chuẩn có thể giúp các DBA và nhà phát triển tải kiểm tra các server database của họ.

mysqlslap có thể mô phỏng một số lượng lớn các kết nối client truy cập vào server database cùng một lúc. Các tham số thử nghiệm tải có thể được cấu hình đầy đủ và kết quả từ các lần chạy thử nghiệm khác nhau được dùng để tinh chỉnh thiết kế database hoặc tài nguyên phần cứng.

Trong hướng dẫn này, ta sẽ tìm hiểu cách sử dụng mysqlslap để tải kiểm tra database MySQL với một số truy vấn cơ bản và xem cách điểm chuẩn có thể giúp ta tinh chỉnh các truy vấn đó. Sau một số trình diễn cơ bản, ta sẽ chạy qua một kịch bản thử nghiệm khá thực tế, nơi ta tạo một bản sao của database hiện có để thử nghiệm, thu thập các truy vấn từ log và chạy thử nghiệm từ một tập lệnh.

Các lệnh, gói và file được hiển thị trong hướng dẫn này đã được thử nghiệm trên CentOS 7 . Các khái niệm vẫn giữ nguyên cho các bản phân phối khác.

Tôi nên sử dụng server kích thước nào?

Nếu bạn quan tâm đến việc đo điểm chuẩn cho một server database cụ thể, bạn nên kiểm tra trên một server có cùng thông số kỹ thuật và có cài đặt bản sao chính xác của database của bạn.

Nếu bạn muốn chạy qua hướng dẫn này cho mục đích học tập và thực hiện mọi lệnh trong đó, ta khuyên bạn nên sử dụng ít nhất 2 GB Server. Vì các lệnh trong hướng dẫn này nhằm đánh thuế server , bạn có thể thấy rằng chúng hết thời gian chờ trên một server nhỏ hơn.

Đầu ra mẫu trong hướng dẫn này được tạo ra theo nhiều cách khác nhau để tối ưu hóa các ví dụ cho việc giảng dạy.

Bước một - Cài đặt server cộng đồng MySQL trên hệ thống thử nghiệm

Ta sẽ bắt đầu bằng cách cài đặt một bản sao mới của MySQL Community Server trên database thử nghiệm. Bạn không nên chạy bất kỳ lệnh hoặc truy vấn nào từ hướng dẫn này trên server database production .

Các thử nghiệm này nhằm mục đích gây căng thẳng cho server thử nghiệm và có thể gây ra độ trễ hoặc thời gian chết trên server production . Hướng dẫn này đã được thử nghiệm với môi trường sau:

CentOS 7
Các lệnh được thực thi bởi user sudo
Khuyến nghị 2 GB Server; hãy nhớ rằng kết quả điểm chuẩn hiển thị trong hướng dẫn này được tạo cho mục đích giảng dạy và không phản ánh điểm chuẩn DigitalOcean cụ thể

Đầu tiên, ta sẽ tạo một folder để chứa tất cả các file liên quan đến hướng dẫn này. Điều này sẽ giúp mọi thứ gọn gàng hơn. Điều hướng vào folder này:

sudo mkdir /mysqlslap_tutorial cd /mysqlslap_tutorial

Tiếp theo, ta sẽ download repository MySQL Community Release yum. Kho lưu trữ ta đang download dành cho Red Hat Enterprise Linux 7 hoạt động cho CentOS 7:

sudo wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

Tiếp theo, ta có thể chạy lệnh rpm -Uvh để cài đặt repository :

sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm

Kiểm tra xem các repository đã được cài đặt chưa bằng cách xem nội dung của folder /etc/yum.repos.d :

sudo ls -l /etc/yum.repos.d

Đầu ra sẽ như thế này:

-rw-r--r--. 1 root root 1612 Jul  4 21:00 CentOS-Base.repo -rw-r--r--. 1 root root  640 Jul  4 21:00 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 1331 Jul  4 21:00 CentOS-Sources.repo -rw-r--r--. 1 root root  156 Jul  4 21:00 CentOS-Vault.repo -rw-r--r--. 1 root root 1209 Jan 29  2014 mysql-community.repo -rw-r--r--. 1 root root 1060 Jan 29  2014 mysql-community-source.repo

Ta cũng có thể kiểm tra xem bản phát hành MySQL chính xác đã được kích hoạt để cài đặt hay chưa:

sudo yum repolist enabled | grep mysql

Trong trường hợp của ta , Server cộng đồng MySQL 5.6 là những gì ta muốn:

mysql-connectors-community/x86_64       MySQL Connectors Community           10 mysql-tools-community/x86_64            MySQL Tools Community                 6 mysql56-community/x86_64                MySQL 5.6 Community Server           64

Cài đặt Server cộng đồng MySQL:

sudo yum install mysql-community-server

Khi hoàn tất , hãy kiểm tra các thành phần được nạp vào:

sudo yum list installed | grep mysql

Danh sách sẽ như thế này:

mysql-community-client.x86_64      5.6.20-4.el7      @mysql56-community mysql-community-common.x86_64      5.6.20-4.el7      @mysql56-community mysql-community-libs.x86_64        5.6.20-4.el7      @mysql56-community mysql-community-release.noarch     el7-5             installed mysql-community-server.x86_64      5.6.20-4.el7      @mysql56-community

Tiếp theo, ta cần đảm bảo MySQL daemon đang chạy và tự động khởi động khi server khởi động. Kiểm tra trạng thái của daemon mysqld.

sudo systemctl status mysqld.service

Nếu dừng, nó sẽ hiển thị kết quả này:

mysqld.service - MySQL Community Server    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)    Active: inactive (dead)

Bắt đầu dịch vụ:

sudo systemctl start mysqld.service

Đảm bảo rằng nó được cấu hình để tự động khởi động lúc khởi động:

sudo systemctl enable mysqld.service

Cuối cùng, ta phải bảo mật MySQL:

sudo mysql_secure_installation

Điều này sẽ đưa ra một loạt dấu nhắc . Ta sẽ hiển thị dấu nhắc bên dưới, với câu trả lời bạn nên nhập bằng màu đỏ . Lúc đầu không có password cho user root MySQL, vì vậy chỉ cần nhấn Enter .

Theo dấu nhắc , bạn cần cung cấp password root an toàn mới mà bạn nên tự chọn. Bạn nên trả lời y để xóa account user database ẩn danh, vô hiệu hóa đăng nhập root từ xa, reload bảng quyền , v.v.:

... Enter current password for root (enter for none): OK, successfully used password, moving on... ... Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables..  ... Success! ... Remove anonymous users? [Y/n] y  ... Success! ... Disallow root login remotely? [Y/n] y  ... Success! Remove test database and access to it? [Y/n] y  - Dropping test database...  ... Success! ... Reload privilege tables now? [Y/n] y  ... Success! Cleaning up...

Bây giờ ta có thể kết nối với database và đảm bảo mọi thứ đang hoạt động:

sudo mysql -h localhost -u root -p

Nhập password MySQL root mà bạn vừa đặt tại dấu nhắc . Bạn sẽ thấy kết quả như sau:

Enter password: Welcome to the MySQL monitor....  mysql>

Tại dấu nhắc mysql> , nhập lệnh để xem tất cả database của bạn:

show databases;

Bạn sẽ thấy kết quả như sau:

+--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)

Cuối cùng, hãy tạo một account user có tên là sysadmin . Tài khoản này sẽ được sử dụng để đăng nhập vào MySQL thay vì user root. Hãy chắc chắn để thay thế mypassword với password riêng của bạn cho user này. Ta cũng sẽ cấp tất cả các quyền cho account này. Tại dấu nhắc MySQL, hãy nhập các lệnh sau:

create user sysadmin identified by 'mypassword';

Đầu ra:

Query OK, 0 rows affected (0.00 sec)

Cấp các quyền :

grant all on *.* to sysadmin;

Đầu ra:

Query OK, 0 rows affected (0.01 sec)

Bây giờ ta hãy quay lại dấu nhắc của hệ điều hành:

quit;

Đầu ra:

Bye

Bước hai - Cài đặt database mẫu

Tiếp theo, ta cần cài đặt một database mẫu để thử nghiệm. Database này được gọi là nhân viên và có thể truy cập miễn phí từ trang web MySQL . Database cũng có thể được download từ Launchpad . Database nhân viên được phát triển bởi Patrick Crews và Giuseppe Maxia. Dữ liệu ban đầu được tạo ra bởi Fusheng Wang và Carlo Zaniolo tại Siemens Corporate Research.

Ta đang chọn database nhân viên vì nó có một tập dữ liệu lớn. Cấu trúc database đủ đơn giản: nó chỉ có sáu bảng; nhưng dữ liệu nó chứa có hơn 3.000.000 profile nhân viên (bản thân bảng lương có gần ba triệu hàng). Điều này sẽ giúp ta mô phỏng dung lượng công việc production thực tế hơn.

Trước tiên, hãy đảm bảo ta đang ở trong folder / mysqlslap_tutorial :

cd /mysqlslap_tutorial

Download version mới nhất của database mẫu nhân viên:

sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

Cài đặt công cụ bzip2 để ta có thể extract repository :

sudo yum install bzip2

Extract repository database . Điều này sẽ mất một phút. Ta đang thực hiện nó theo hai bước ở đây:

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2 sudo tar -xf employees_db-full-1.0.6.tar

Nội dung sẽ được extract vào một folder mới, riêng biệt có tên là worker_db . Ta cần chuyển vào folder này để chạy truy vấn cài đặt database . Nội dung bao gồm tài liệu README, log thay đổi, kết xuất dữ liệu và các file truy vấn SQL khác nhau sẽ tạo cấu trúc database :

cd employees_db ls -l

Đây là những gì bạn sẽ thấy:

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog -rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql -rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql -rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql -rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql -rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump -rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump -rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump -rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump -rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump -rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump -rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql -rw-r--r--. 1 501 games      2211 Jul 30  2008 README -rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql -rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

Chạy lệnh này để kết nối với MySQL và chạy các script employees.sql, mà sẽ tạo ra database và tải các dữ liệu:

sudo mysql -h localhost -u sysadmin -p -t < employees.sql

Tại dấu nhắc , hãy nhập password bạn đã tạo cho user sysadmin MySQL trong phần trước.

Đầu ra của quy trình sẽ như thế này. Sẽ mất một phút hoặc lâu hơn để chạy:

+-----------------------------+ | INFO                        | +-----------------------------+ | CREATING DATABASE STRUCTURE | +-----------------------------+ +------------------------+ | INFO                   | +------------------------+ | storage engine: InnoDB | +------------------------+ +---------------------+ | INFO                | +---------------------+ | LOADING departments | +---------------------+ +-------------------+ | INFO              | +-------------------+ | LOADING employees | +-------------------+ +------------------+ | INFO             | +------------------+ | LOADING dept_emp | +------------------+ +----------------------+ | INFO                 | +----------------------+ | LOADING dept_manager | +----------------------+ +----------------+ | INFO           | +----------------+ | LOADING titles | +----------------+ +------------------+ | INFO             | +------------------+ | LOADING salaries | +------------------+

Đến đây bạn có thể đăng nhập vào MySQL và chạy một số truy vấn cơ bản để kiểm tra xem dữ liệu đã được nhập thành công hay chưa.

sudo mysql -h localhost -u sysadmin -p

Nhập password cho user sysadmin MySQL.

Kiểm tra danh sách database cho database nhân viên mới:

show databases;

Đầu ra:

+--------------------+ | Database           | +--------------------+ | information_schema | | employees          | | mysql              | | performance_schema | +--------------------+ 4 rows in set (0.01 sec)

Sử dụng database nhân viên:

use employees;

Kiểm tra các bảng trong đó:

show tables;

Đầu ra:

+---------------------+ | Tables_in_employees | +---------------------+ | departments         | | dept_emp            | | dept_manager        | | employees           | | salaries            | | titles              | +---------------------+ 6 rows in set (0.01 sec)

Nếu bạn muốn, bạn có thể kiểm tra chi tiết cho từng bảng này. Ta sẽ chỉ kiểm tra thông tin cho bảng tiêu đề :

describe titles;

Đầu ra:

+-----------+-------------+------+-----+---------+-------+ | Field     | Type        | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | emp_no    | int(11)     | NO   | PRI | NULL    |       | | title     | varchar(50) | NO   | PRI | NULL    |       | | from_date | date        | NO   | PRI | NULL    |       | | to_date   | date        | YES  |     | NULL    |       | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

Kiểm tra số lượng mục nhập:

mysql> select count(*) from titles; +----------+ | count(*) | +----------+ |   443308 | +----------+ 1 row in set (0.14 sec)

Kiểm tra bất kỳ dữ liệu nào khác mà bạn muốn. Bây giờ ta có thể quay lại dấu nhắc hệ điều hành của bạn :

quit;

Bước 3 - Sử dụng mysqlslap

Bây giờ ta có thể bắt đầu sử dụng mysqlslap. mysqlslap có thể được gọi từ một dấu nhắc shell thông thường nên không cần phải đăng nhập rõ ràng vào MySQL. Tuy nhiên, đối với hướng dẫn này, ta sẽ mở một kết nối terminal khác tới server Linux của ta và bắt đầu một phiên MySQL mới từ đó với user sysadmin mà ta đã tạo trước đó, vì vậy ta có thể kiểm tra và cập nhật một số thứ trong MySQL dễ dàng hơn. Vì vậy, ta sẽ có một dấu nhắc mở với user sudo của ta và một dấu nhắc đăng nhập vào MySQL.

Trước khi ta đi vào các lệnh cụ thể để thử nghiệm, bạn có thể cần xem danh sách các tùy chọn mysqlslap hữu ích nhất này. Điều này có thể giúp bạn thiết kế các lệnh mysqlslap của riêng mình sau này.

Lựa chọn	Nó nghĩa là gì
- user	Tên user MySQL để kết nối với server database
-mật khẩu	Mật khẩu cho account user . Tốt nhất là để trống trong dòng lệnh
-tổ chức	Tên server database MySQL
-Hải cổng	Số cổng để kết nối với MySQL nếu mặc định không được sử dụng
– Tiền tệ	Số lượng kết nối client đồng thời mysqlslap sẽ mô phỏng
–Câu hỏi	Số lần truy vấn thử nghiệm sẽ được chạy
–Create-schema	Lược đồ để chạy các bài kiểm tra
-truy vấn	Truy vấn để thực thi. Đây có thể là một chuỗi truy vấn SQL hoặc một đường dẫn đến file tập lệnh SQL
-tạo nên	Truy vấn để tạo bảng. , đây có thể là một chuỗi truy vấn hoặc một đường dẫn đến file SQL
–Delimiter	Dấu phân tách được sử dụng để phân tách nhiều câu lệnh SQL
-động cơ	Công cụ database MySQL để sử dụng (ví dụ: InnoDB)
–Auto-create-sql	Cho phép MySQL thực hiện kiểm tra tải bằng lệnh SQL được tạo tự động của riêng nó

Trường hợp sử dụng: Đo điểm chuẩn với dữ liệu và SQL được tạo tự động

Ta sẽ bắt đầu bằng cách sử dụng tính năng tự động tạo-sql của mysqlslap. Khi ta sử dụng SQL được tạo tự động, mysqlslap sẽ tạo một database tạm thời riêng biệt - được gọi là mysqlslap một cách khéo léo. Database này sẽ có một bảng đơn giản với một số nguyên và một cột kiểu varchar được điền dữ liệu mẫu. Đây có thể là một cách nhanh chóng và dễ dàng để kiểm tra hiệu suất tổng thể của server database .

Ta bắt đầu bằng cách thử nghiệm một kết nối client duy nhất thực hiện một lần lặp lại SQL được tạo tự động:

sudo mysqlslap --user=sysadmin --password --host=localhost  --auto-generate-sql --verbose

Đầu ra sẽ như thế này:

Benchmark         Average number of seconds to run all queries: 0.009 seconds         Minimum number of seconds to run all queries: 0.009 seconds         Maximum number of seconds to run all queries: 0.009 seconds         Number of clients running queries: 1         Average number of queries per client: 0

mysqlslap báo cáo một số thống kê điểm chuẩn như trong kết quả . Nó báo cáo số giây trung bình, tối thiểu và tối đa để chạy truy vấn. Ta cũng có thể thấy rằng số lượng kết nối client được sử dụng cho thử nghiệm tải này là một.

Bây giờ hãy thử 50 kết nối đồng thời và yêu cầu truy vấn được tạo tự động chạy 10 lần:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose

Lệnh này nghĩa là năm mươi kết nối client được mô phỏng sẽ đưa ra cùng một truy vấn kiểm tra cùng một lúc và kiểm tra này sẽ được lặp lại mười lần.

Kết quả kết quả cho ta thấy sự khác biệt rõ rệt khi tải tăng lên:

Benchmark         Average number of seconds to run all queries: 0.197 seconds         Minimum number of seconds to run all queries: 0.168 seconds         Maximum number of seconds to run all queries: 0.399 seconds         Number of clients running queries: 50         Average number of queries per client: 0

Lưu ý cách trường Số lượng khách hàng đang chạy truy vấn: hiện đang hiển thị giá trị là 50. Số lượng truy vấn trung bình trên mỗi khách hàng bằng 0.

SQL được tạo tự động tạo một bảng đơn giản với hai trường. Trong hầu hết các môi trường production , cấu trúc bảng sẽ lớn hơn nhiều. Ta có thể hướng dẫn mysqlslap mô phỏng điều này bằng cách thêm các trường bổ sung vào bảng thử nghiệm. Để làm như vậy, ta có thể sử dụng hai tham số mới: --number-char-cols và --number-int-cols . Các tham số này chỉ định số lượng kiểu cột varchar và int để thêm vào bảng kiểm tra.

Trong ví dụ sau, ta đang thử nghiệm với một truy vấn SQL được tạo tự động chạy trên một bảng có 5 cột số và 20 cột kiểu ký tự. Ta cũng đang mô phỏng 50 kết nối client và ta muốn thử nghiệm lặp lại 100 lần:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbose

Quá trình này sẽ lâu hơn một chút. Trong khi thử nghiệm đang chạy, ta có thể chuyển sang cửa sổ terminal khác nơi ta có phiên MySQL đang chạy và xem điều gì đang xảy ra. Lưu ý nếu bạn đợi quá lâu, quá trình kiểm tra sẽ hoàn thành và bạn sẽ không thể xem database kiểm tra.

Từ dấu nhắc MySQL:

show databases;

Lưu ý database mysqlslap :

+--------------------+ | Database           | +--------------------+ | information_schema | | employees          | | mysql              | | mysqlslap          | | performance_schema | +--------------------+ 5 rows in set (0.01 sec)

Bạn có thể kiểm tra bảng trong database thử nghiệm nếu bạn muốn; nó được gọi là t1 .

Kiểm tra lại cửa sổ terminal khác của bạn. Khi quá trình kiểm tra kết thúc, bạn sẽ thấy rằng hiệu suất còn chậm lại nhiều hơn khi tải tăng lên:

Benchmark         Average number of seconds to run all queries: 0.695 seconds         Minimum number of seconds to run all queries: 0.627 seconds         Maximum number of seconds to run all queries: 1.442 seconds         Number of clients running queries: 50         Average number of queries per client: 0

Quay lại phiên terminal MySQL của bạn. Ta có thể thấy rằng mysqlslap đã loại bỏ database của nó. Tại dấu nhắc MySQL:

show databases;

+--------------------+ | Database           | +--------------------+ | information_schema | | employees          | | mysql              | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)

Trường hợp sử dụng: Đo điểm chuẩn với Truy vấn tùy chỉnh

SQL được tạo tự động rất tốt nếu bạn đang đánh giá tài nguyên vật lý của server . Nó hữu ích khi bạn muốn tìm mức tải mà một hệ thống nhất định có thể thực hiện.

Tuy nhiên, khi bạn muốn khắc phục sự cố hiệu suất cho một ứng dụng phụ thuộc vào database cụ thể, bạn cần kiểm tra các truy vấn thực trên dữ liệu thực. Các truy vấn này có thể đến từ web server hoặc ứng dụng của bạn.

Hiện tại, ta giả sử bạn biết truy vấn cụ thể mà bạn muốn kiểm tra. Trong phần tiếp theo, ta sẽ chỉ cho bạn một cách để tìm các truy vấn đang chạy trên server của bạn.

Ta sẽ bắt đầu với các truy vấn trong dòng. Bạn có thể đưa ra một truy vấn nội dòng cho mysqlslap bằng tùy chọn –query . Các câu lệnh SQL không được có dấu ngắt dòng và chúng cần được phân cách bằng dấu chấm phẩy (;). Các truy vấn cũng cần được đặt trong dấu ngoặc kép.

Trong đoạn mã sau, ta đang chạy một truy vấn đơn giản đối với bảng dept emp . Bảng `deptemp` có hơn ba trăm nghìn bản ghi. Lưu ý cách ta đã chỉ định database nhân viên với tùy chọn –create-schema :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose

Điều này sẽ mất một lúc để chạy. Bạn sẽ nhận được điểm chuẩn hiệu suất như thế này sau một hoặc hai phút:

Benchmark         Average number of seconds to run all queries: 18.486 seconds         Minimum number of seconds to run all queries: 15.590 seconds         Maximum number of seconds to run all queries: 28.381 seconds         Number of clients running queries: 50         Average number of queries per client: 1

(Lưu ý: Nếu truy vấn này bị treo hơn mười phút hoặc không đưa ra bất kỳ kết quả nào, bạn nên thử lại với số thấp hơn cho –concurrency và / hoặc –iterations hoặc thử trên server lớn hơn.)

Tiếp theo, ta sẽ sử dụng nhiều câu lệnh SQL trong tham số –query . Trong ví dụ sau, ta kết thúc mỗi truy vấn bằng dấu chấm phẩy. mysqlslap biết rằng ta đang sử dụng một số lệnh SQL riêng biệt vì ta đã chỉ định tùy chọn –delimiter :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --iterations=10 --create-schema=employees --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" --delimiter=";" --verbose

Thử nghiệm này sử dụng cùng một số lượng kết nối và cùng một số lần lặp lại. Tuy nhiên, hiệu suất ngày càng chậm hơn đối với nhiều câu lệnh SELECT (trung bình 23,8 giây so với 18,486 giây):

Benchmark         Average number of seconds to run all queries: 23.800 seconds         Minimum number of seconds to run all queries: 22.751 seconds         Maximum number of seconds to run all queries: 26.788 seconds         Number of clients running queries: 20         Average number of queries per client: 5

Câu lệnh SQL production có thể phức tạp. Việc thêm một câu lệnh SQL phức tạp vào một tập lệnh sẽ dễ dàng hơn là gõ nó ra để kiểm tra. Vì vậy, ta có thể hướng dẫn mysqlslap đọc truy vấn từ một file script.

Để minh họa điều này, hãy tạo một file script từ các lệnh SQL. Ta có thể sử dụng đoạn mã dưới đây để tạo một file như vậy:

sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql  sudo cp ~/select_query.sql /mysqlslap_tutorial/

Tệp select_query.sql hiện chứa tất cả năm câu lệnh SELECT.

Vì tập lệnh này có nhiều truy vấn, ta có thể giới thiệu một khái niệm thử nghiệm mới. mysqlslap có thể song song hóa các truy vấn. Ta có thể làm điều này bằng cách chỉ định số lượng truy vấn mà mỗi client thử nghiệm sẽ thực hiện. mysqlslap thực hiện điều này với tùy chọn –number-of-queries . Vì vậy, nếu ta có 50 kết nối và 1000 truy vấn để chạy, mỗi client sẽ thực hiện khoảng 20 truy vấn.

Cuối cùng, ta cũng có thể sử dụng lựa chọn –debug-info , nó sẽ cung cấp cho ta dấu hiệu về các tài nguyên máy tính được sử dụng.

Trong đoạn mã sau, ta yêu cầu mysqlslap sử dụng file script mà ta vừa tạo. Ta cũng đang chỉ định tham số number-of-queries . Quá trình sẽ được lặp lại hai lần và ta muốn thông tin gỡ lỗi trong kết quả :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/mysqlslap_tutorial/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info

Sau khi lệnh này hoàn thành, ta có thể thấy một số kết quả thú vị:

Benchmark         Average number of seconds to run all queries: 217.151 seconds         Minimum number of seconds to run all queries: 213.368 seconds         Maximum number of seconds to run all queries: 220.934 seconds         Number of clients running queries: 20         Average number of queries per client: 50   User time 58.16, System time 18.31 Maximum resident set size 909008, Integral resident set size 0 Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 102785, Involuntary context switches 43

Ở đây số giây trung bình để chạy tất cả các truy vấn trong version MySQL của ta là 217 giây, gần 4 phút. Mặc dù điều đó chắc chắn bị ảnh hưởng bởi dung lượng RAM và CPU có sẵn cho máy ảo của ta , nó cũng do số lượng lớn các truy vấn từ một số lượng vừa phải các kết nối client lặp lại hai lần.

Ta có thể thấy có một số lượng lớn các lỗi trang phi thực tế. Lỗi trang xảy ra khi không thể tìm thấy dữ liệu trong bộ nhớ và hệ thống phải truy xuất dữ liệu đó từ file swap trên đĩa. Đầu ra cũng hiển thị thông tin liên quan đến CPU. Trong trường hợp này, ta thấy một số lượng lớn các lựa chọn ngữ cảnh.

Trường hợp sử dụng: Kịch bản đo điểm chuẩn thực tế và chụp các truy vấn trực tiếp

Cho đến nay trong các ví dụ của ta , ta đã chạy các truy vấn dựa trên database nhân viên ban đầu. Đó là điều mà các DBA chắc chắn sẽ không muốn bạn làm. Và có một lý do chính đáng cho nó. Bạn không muốn thêm tải database production của bạn và bạn không muốn chạy các truy vấn thử nghiệm có thể xóa, cập nhật hoặc chèn dữ liệu vào bảng production của bạn .

Ta sẽ hướng dẫn bạn cách tạo bản backup database production và sao chép nó vào môi trường thử nghiệm. Trong ví dụ này, nó trên cùng một server , nhưng bạn nên sao chép nó vào một server riêng biệt có cùng dung lượng phần cứng.

Quan trọng hơn, ta sẽ chỉ cho bạn cách ghi lại các truy vấn trực tiếp từ database production và thêm chúng vào tập lệnh thử nghiệm. Đó là, bạn sẽ nhận được các truy vấn từ database production , nhưng chạy các bài kiểm tra đối với database thử nghiệm.

Các bước chung như sau và bạn có thể sử dụng chúng cho bất kỳ thử nghiệm mysqlslap nào:

1. Sao chép database production sang môi trường thử nghiệm.
2 . Cấu hình MySQL để ghi và nắm bắt tất cả các yêu cầu và truy vấn kết nối trên database production .
3. Mô phỏng trường hợp sử dụng bạn đang cố gắng kiểm tra. Ví dụ: nếu bạn chạy một giỏ hàng, bạn nên mua thứ gì đó để kích hoạt tất cả các truy vấn database thích hợp từ ứng dụng của bạn.
4. Tắt ghi log truy vấn.
5. Xem log truy vấn và lập danh sách các truy vấn bạn muốn kiểm tra.
6. Tạo file kiểm tra cho mỗi truy vấn bạn muốn kiểm tra.
7. Chạy các bài kiểm tra.
8. Sử dụng kết quả để cải thiện hiệu suất database của bạn.

Để bắt đầu, hãy tạo một bản backup của database nhân viên. Ta sẽ tạo một folder riêng để backup nó:

sudo mkdir /mysqlslap_tutorial/mysqlbackup  cd /mysqlslap_tutorial/mysqlbackup

Tạo bản backup và di chuyển nó vào folder mới:

sudo mysqldump --user sysadmin --password --host localhost employees > ~/employees_backup.sql  sudo cp ~/employees_backup.sql /mysqlslap_tutorial/mysqlbackup/

Đi tới server thử nghiệm MySQL của bạn. Tạo database nhân viên_ dự phòng:

CREATE DATABASE employees_backup;

Đến đây, nếu bạn đang sử dụng một server riêng để thử nghiệm, bạn nên sao chép file backup.sql của nhân viên vào đó. Từ phiên terminal chính của bạn, hãy nhập dữ liệu backup vào database dự phòng nhân viên :

sudo mysql -u sysadmin -p employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql

Trên server database MySQL production của bạn, hãy bật log truy vấn chung MySQL và cung cấp tên file cho nó. Nhật ký truy vấn chung nắm bắt các hoạt động kết nối, ngắt kết nối và truy vấn cho một version database MySQL.

SET GLOBAL general_log=1, general_log_file='capture_queries.log';

Bây giờ chạy các truy vấn mà bạn muốn kiểm tra trên server MySQL production . Trong ví dụ này, ta sẽ chạy một truy vấn từ dòng lệnh. Tuy nhiên, bạn có thể cần tạo các truy vấn từ ứng dụng của bạn thay vì chạy chúng trực tiếp. Nếu bạn có một quy trình hoặc trang web chậm mà bạn muốn kiểm tra, bạn nên chạy qua quy trình đó hoặc truy cập trang web đó ngay bây giờ. Ví dụ: nếu bạn đang chạy một giỏ hàng, bạn có thể cần hoàn tất quy trình thanh toán ngay bây giờ, quy trình này sẽ kích hoạt tất cả các truy vấn thích hợp trên server database .

Đây là truy vấn ta sẽ chạy trên server MySQL production . Trước tiên hãy sử dụng database phù hợp:

USE employees;

Bây giờ hãy chạy truy vấn:

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date;

Sản lượng mong đợi:

489903 rows in set (4.33 sec)

Ta sẽ tắt ghi log chung khi truy vấn hoàn tất:

SET GLOBAL general_log=0;

Lưu ý nếu bạn vẫn đăng nhập, các truy vấn sẽ tiếp tục được thêm vào log , điều này có thể khiến việc kiểm tra khó khăn hơn. Vì vậy, hãy đảm bảo bạn vô hiệu hóa log ngay sau khi kết thúc bài kiểm tra của bạn . Hãy kiểm tra xem file log đã được tạo trong folder / var / lib / mysql chưa :

sudo ls -l /var/lib/mysql/capt*  -rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log

Hãy sao chép file này vào folder thử nghiệm MySQL của ta . Nếu bạn đang sử dụng một server riêng để thử nghiệm, hãy sao chép nó vào server đó.

sudo cp /var/lib/mysql/capture_queries.log /mysqlslap_tutorial/

Sẽ có khá nhiều dữ liệu trong file log này. Trong ví dụ này, truy vấn ta muốn phải ở gần cuối. Kiểm tra phần cuối cùng của file :

sudo tail /mysqlslap_tutorial/capture_queries.log

Sản lượng mong đợi:

         6294 Query show databases          6294 Query show tables          6294 Field List    departments           6294 Field List    dept_emp           6294 Field List    dept_manager           6294 Field List    employees           6294 Field List    salaries           6294 Field List    titles  140930 15:34:52  6294 Query SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date 140930 15:35:06  6294 Query SET GLOBAL general_log=0

Nhật ký này hiển thị các lệnh SQL và dấu thời gian của chúng. Câu lệnh SQL SELECT ở gần cuối file là những gì ta quan tâm. Nó phải giống hoàn toàn với lệnh ta đã chạy trên database production , vì đó là nơi ta nắm bắt nó.

Trong ví dụ này, ta đã biết truy vấn. Tuy nhiên, trong môi trường production , phương pháp này có thể rất hữu ích để tìm kiếm các truy vấn mà bạn có thể không nhất thiết phải biết về nó đang chạy trên server của bạn.

Lưu ý nếu bạn chạy hoặc kích hoạt các truy vấn khác nhau trong khi ghi log , file này sẽ trông hoàn toàn khác. Trong một kịch bản thực tế, file này có thể chứa hàng trăm mục nhập đến từ tất cả các kết nối khác nhau. Mục tiêu của bạn là tìm truy vấn hoặc các truy vấn đang gây ra tắc nghẽn. Bạn có thể bắt đầu bằng cách tạo danh sách mọi dòng bao gồm Truy vấn văn bản. Sau đó, bạn sẽ có một danh sách chính xác những truy vấn đã được chạy trên database của bạn trong quá trình kiểm tra.

Đối với mỗi truy vấn mà bạn muốn kiểm tra, hãy sao chép nó vào một file có đuôi .sql .

Ví dụ:

sudo vi /mysqlslap_tutorial/capture_queries.sql

Nội dung phải là truy vấn MySQL mà bạn muốn kiểm tra, không có bất kỳ ngắt dòng nào và không có dấu chấm phẩy ở cuối:

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date

Tiếp theo, hãy đảm bảo kết quả truy vấn không được lưu vào bộ nhớ đệm. Quay lại phiên MySQL thử nghiệm của bạn. Chạy lệnh sau:

RESET QUERY CACHE;

Bây giờ là lúc để chạy tiện ích mysqlslap với file script. Đảm bảo rằng bạn sử dụng đúng tên file tập lệnh trong tham số –query . Ta sẽ chỉ sử dụng mười kết nối đồng thời và lặp lại thử nghiệm hai lần. Chạy điều này từ server thử nghiệm của bạn:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

Đầu ra điểm chuẩn trông giống như thế này trong hệ thống của ta :

Benchmark         Average number of seconds to run all queries: 68.692 seconds         Minimum number of seconds to run all queries: 59.301 seconds         Maximum number of seconds to run all queries: 78.084 seconds         Number of clients running queries: 10         Average number of queries per client: 1

Vậy làm thế nào ta có thể cải thiện điểm chuẩn này?

Bạn cần quen thuộc với các truy vấn MySQL để đánh giá xem truy vấn đang làm gì.

Nhìn lại truy vấn, ta có thể thấy nó đang thực hiện một số phép nối trên nhiều bảng. Truy vấn hiển thị lịch sử công việc của nhân viên và khi làm như vậy, nó sẽ kết hợp các bảng khác nhau theo trường emp no . Nó cũng sử dụng trường deptno để tham gia, nhưng vì chỉ có một số profile bộ phận, ta sẽ bỏ qua điều này. Vì có nhiều mục nhập emp no trong database , nên thật hợp lý khi giả định việc tạo index trên trường empno có thể cải thiện truy vấn.

Với một chút thực hành, khi bạn đã định vị được các truy vấn đang đánh thuế server (đó là phần mà mysqlslap hỗ trợ!), Bạn có thể đưa ra đánh giá về các truy vấn dựa trên kiến thức về MySQL và database của bạn.

Tiếp theo, bạn có thể cố gắng cải thiện database của bạn hoặc các truy vấn đang được thực thi trên đó.

Trong trường hợp của ta , hãy thêm các index mà ta đã đề cập ở trên. Ta sẽ tạo ra ba chỉ số trên emp không. Một index sẽ được tạo trên trường empno trong bảng nhân viên , một index khác sẽ được tạo trên trường trống không trong bảng deptemp và index cuối cùng sẽ được tạo trên trường emp_no trong bảng tiêu đề .

Hãy đi đến phiên MySQL thử nghiệm của ta và thực hiện các lệnh sau:

USE employees_backup;  CREATE INDEX employees_empno ON employees(emp_no);  CREATE INDEX dept_emp_empno ON dept_emp(emp_no);  CREATE INDEX titles_empno ON titles(emp_no);

Quay lại cửa sổ terminal chính của ta trên server thử nghiệm, nếu ta thực thi mysqlslap với cùng các tham số, ta sẽ thấy sự khác biệt trong điểm chuẩn:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

Benchmark         Average number of seconds to run all queries: 55.869 seconds         Minimum number of seconds to run all queries: 55.706 seconds         Maximum number of seconds to run all queries: 56.033 seconds         Number of clients running queries: 10         Average number of queries per client: 1

Ta có thể thấy rằng có sự cải thiện ngay lập tức về thời gian trung bình, tối thiểu và tối đa để thực hiện truy vấn. Thay vì trung bình 68 giây, truy vấn hiện thực hiện trong 55 giây. Đó là sự cải thiện của 13 giây cho cùng một tải.

Vì sự thay đổi database này tạo ra một kết quả tốt trong môi trường thử nghiệm, nên bây giờ bạn có thể cân nhắc triển khai nó cho server database production của bạn , mặc dù hãy nhớ rằng những thay đổi database thường có những ưu điểm và nhược điểm của chúng.

Bạn có thể lặp lại quy trình kiểm tra các lệnh và cải tiến với tất cả các truy vấn bạn thu thập được từ log của bạn .

Khắc phục sự cố - mysqlslap không hiển thị kết quả

Nếu bạn chạy một lệnh kiểm tra và không nhận được bất kỳ kết quả nào, thì đây là một dấu hiệu tốt cho thấy tài nguyên server của bạn có thể bị sử dụng hết. Các hiện tượng có thể bao gồm thiếu kết quả Điểm chuẩn hoặc lỗi như mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query .

Bạn có thể cần thử kiểm tra với một số nhỏ trong tham số -concurrency hoặc -iterations. Ngoài ra, bạn có thể thử nâng cấp môi trường server thử nghiệm của bạn .

Đây có thể là một cách tốt để tìm ra giới hạn bên ngoài của dung lượng server database của bạn.

Kết luận

mysqlslap là một công cụ đơn giản, nhẹ, dễ sử dụng và tích hợp nguyên bản với database MySQL. Nó có sẵn cho tất cả các version của MySQL từ version 5.1.4.

Trong hướng dẫn này, ta đã thấy cách sử dụng mysqlslap với các tùy chọn khác nhau của nó và chơi với database mẫu. Bạn có thể download các database mẫu khác từ trang MySQL và thực hành với chúng. Như ta đã đề cập trước đây, vui lòng không chạy thử nghiệm trên server database production .

Trường hợp sử dụng cuối cùng trong hướng dẫn này chỉ liên quan đến một truy vấn. Mặc dù ta đã cải thiện phần nào hiệu suất của truy vấn đó bằng cách thêm các index bổ sung vào cả ba bảng, nhưng quá trình này có thể không đơn giản như vậy trong đời thực. Việc thêm các index phụ đôi khi có thể làm giảm hiệu suất hệ thống và các DBA thường cần cân nhắc giữa lợi ích của việc thêm một index bổ sung so với chi phí hiệu suất mà nó có thể phải chịu.

Các kịch bản thử nghiệm trong đời thực phức tạp hơn, nhưng điều này sẽ cung cấp cho bạn các công cụ để bắt đầu thử nghiệm và cải thiện hiệu suất database của bạn.

Tags:

Các tin liên quan

Cách di chuyển database MySQL sang server mới trên Ubuntu 14.04
2014-05-22
Cách tối ưu hóa hiệu suất WordPress với MySQL Replication trên Ubuntu 14.04
2014-05-21
Cách sử dụng profile truy vấn MySQL
2014-04-07
Mở rộng quy mô Ruby on Rails: Thiết lập server MySQL chuyên dụng (phần 2)
2014-02-27
Cách sử dụng HAProxy để thiết lập cân bằng tải MySQL
2013-12-02
Cách sử dụng HAProxy để thiết lập cân bằng tải MySQL
2013-12-02
Cách cài đặt MySQL 5.6 từ kho lưu trữ chính thức của Yum
2013-11-13
Cách tối ưu hóa các truy vấn và bảng trong MySQL và MariaDB trên VPS
2013-11-11
Cách bảo mật sao chép MySQL bằng SSH trên VPS
2013-09-18
Cách tạo và quản lý database trong MySQL và MariaDB trên server cloud
2013-07-29