การทดสอบประสิทธิภาพของ MySQL ด้วย mysqlslap

by | 13 Sep 2016 | Resources

ในปัจจุบันมีการใช้งาน database กันอย่างแพร่หลายเป็นวงกว้าง โดย database ที่ได้รับความนิยมเป็นอันดับต้นๆ นั่นก็คือ MySQL

สำหรับในบนความนี้ จะกล่าวถึงเครื่องมือที่จะใช้ในการตรวจสอบประสิทธิภาพ การทำงานของ MySQL server คือ mysqlslap ซึ่งเป็นโปรแกรมที่เริ่มมีการใช้งานตั้งแต่ MySQL 5.1.4 ขึ้นไป โดยใช้ในการจำลองการใช้งาน สำหรับ MySQL เช่น กรณีที่มี connection หรือ มีการเรียกใช้งาน query จำนวนมาก ทั้งนี้เป็นการเตรียมการรองรับ connection ที่สูงขึ้น หรือหาแนวทางการแก้ไขปัญหาสำหรับกรณี ที่ database มีการเรียกใช้งานทรัพยากรสูงเกินกว่ากว่าระบบรองรับได้ สาเหตุอาจเกิดได้จากการออกแบบ database ที่ไม่สอดคล้องกับการใช้งาน ซึ่งอาจส่งผลกระทบกับระบบโดยรวมได้

สำหรับ mysqlslap นั้น จะสามารถวิเคราะห์และแสดงผล ตัวอย่าง ค่าเฉลี่ย, ค่าสูงสุดของเวลาที่ใช้ในการประมวลผลตามคำสั่งนั้นๆ ในบทความนี้ จะนำเสนอการทดสอบประสิทธิภาพของ database โดยใช้ mysqlslap ด้วยคำสั่ง พื้นฐานต่างๆ และแสดงผลการดำเนินการ

เตรียมการทดสอบด้วย

หมายเหตุ

  • เพื่อป้องกันผลกระทบจากการทดสอบ ผู้ทดสอบควรเลือกทดสอบบน server ที่ไม่มีการใช้งานจริง เนื่องจากการรับ connection และ query จำนวนมากในขณะทดสอบ อาจส่งผลกระทบต่อผู้ใช้งาน server ในขณะนั้นได้
  • กรณีที่ต้องการทดสอบโดยใช้คำสั่งที่ซับซ้อน อาจะจำเป็นจะต้องใช้ resource ที่เพิ่มสูงขึ้น

ติดตั้งโปรแกรมเพื่อการทดสอบ

# mkdir /mysqlslap_test/
# cd /mysqlslap_test/

ติดตั้ง yum repository ของ mysql

# rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Retrieving http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Preparing...                          ################################# [100%]
Updating / installing...
  1:mysql-community-release-el7-5 ################################# [100%]

ตรวจสอบการติดตั้ง repository

# ls -la /etc/yum.repos.d/mysql*
-rw-r--r-- 1 root root 1209 Jan 29  2014 /etc/yum.repos.d/mysql-community.repo
-rw-r--r-- 1 root root 1060 Jan 29  2014 /etc/yum.repos.d/mysql-community-source.repo

ติดตั้ง mysql server และตรวจสอบ status เมื่อมีการติดตั้งเรียบร้อย

# yum –y install mysql-community-server
# systemctl status mysqld.service
  mysqld.service - MySQL Community Server
  Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
  Active: inactive (dead)

สั่ง start service กำหนดให้ MySQL server  เริ่มทำงานตั้งแต่มีการเปิดเครื่อง และกำหนดความปลอดภัยเบื้องต้นเพิ่มขึ้นให้กับ MySQL server

# systemctl start mysqld.service
# systemctl enable mysqld.service
# mysql_secure_installation
…
Enter current password for root (enter for none): [Enter ผ่านไป]
…
Set root password? [Y/n] Y
New password:  [ใส่ password ใหม่ที่ต้องการ]
Re-enter new password: [ใส่ password ใหม่ที่ต้องการอีกครั้ง]
Password updated successfully!
Reloading privilege tables..
... Success!
…
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
Thanks for using MySQL!

หากแสดงข้อความดังกล่าว แสดงว่าดำเนินการเรียบร้อยแล้ว

ทดสอบ login จากนั้นตรวจสอบและปรับ connection ของ MySQL เพิ่มขึ้นเพื่อการทดสอบใช้งาน

# mysql -h localhost -u root -p
Enter password: ใส่ค่า password ที่มีการกำหนดไว้ก่อนหน้านี้
Welcome to the MySQL monitor.  Commands end with ; or \g ...
mysql>

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

mysql> show variables like "max_connections";  ตรวจสอบค่า
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set global max_connections = 200;  กำหนด connection ค่าเป็น 200
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "max_connections"; ตรวจสอบค่าอีกครั้ง
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set (0.00 sec)

# และแก้ไขไฟล์  /etc/my.cnf โดยกำหนดไว้ภายใต้ section [mysqld]

# vi /etc/my.cnf
[mysqld]
...
max_connections = 200
...

เมื่อสามารถ login ได้เรียบร้อย เราจะสร้าง user สำหรับใช้ทดสอบแทนการใช้งาน root user ดังนี้

mysql> create user mysqladmin identified by 'Password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to mysqladmin;
Query OK, 0 rows affected (0.00 sec)

ติดตั้ง database เพื่อใช้ในการทดสอบ โดยลองใช้ database ในการทดสอบ จาก MySQLtutorial.org

# cd /mysqlslap_test/
# wget http://www.mysqltutorial.org/wp-content/uploads/downloads/2016/05/
mysqlsampledatabase.zip
# yum -y install unzip
# unzip -x mysqlsampledatabase.zip

เริ่มติดตั้ง database classicmodels

# mysql -u mysqladmin -p  <  mysqlsampledatabase.sql
Enter password: ใส่ค่า password ที่มีการกำหนดไว้ก่อนหน้านี้

เข้าตรวจสอบ เมื่อติดตั้ง database classicmodels เรียบร้อย

# mysql -u mysqladmin –p
Enter password: ใส่ค่า password ที่มีการกำหนดไว้ก่อนหน้านี้
…
mysql> use classicmodels;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

#แสดง table ต่างๆ
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers            |
| employees            |
| offices              |
| orderdetails         |
| orders               |
| payments             |
| productlines         |
| products             |
+-------------------------+
8 rows in set (0.00 sec)

#ตรวจสอบรายละเอียดของ table
mysql> describe payments;
+----------------+---------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| customerNumber | int(11)    | NO   | PRI | NULL |       |
| checkNumber | varchar(50)   | NO   | PRI | NULL |    |
| paymentDate | date       | NO   | | NULL |       |
| amount      | decimal(10,2) | NO   |     | NULL |    |
+----------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#ออกจากการใช้งาน
mysql> quit;

จาก output ด้านบน จะเห็นได้ว่ามีการติดตั้ง database : classicmodels เรียบร้อยแล้ว และขั้นตอนต่อไป เราจะทำการทดสอบเรียกใช้งาน database

การทดสอบด้วย mysqlslap

ก่อนเริ่มการทดสอบ เราจะขอแนะนำ option บางส่วนสำหรับ mysqlslap เพื่อใช้ในการทดสอบ

-u, --user คือ user สำหรับติดต่อ database
-p, --password คือ password ของ user ที่ใช้ในการติดต่อ database
-P, --port คือ port ที่ใช้สำหรับเชื่อมต่อ database
-h, --host คือ server ที่ต้องการเชื่อมต่อ   
-v, --verbose คือ ให้แสดงรายละเอียดต่างๆ เพิ่มเติมทาง output
-a, --auto-generate-sql คือ กำหนดให้ mysql สร้างคำสั่งเองเพื่อการทดสอบขึ้นเอง
--number-char-cols คือ จำนวนของ VARCHAR column ที่ใช้กำหนดสำหรับ option :  --auto-generate-sql
--number-int-cols คือ จำนวนของ INT column ที่ใช้กำหนดสำหรับ option : --auto-generate-sql
-c, --concurrency คือ จำนวนของ client ที่ต้องการให้ระบบจำลอง เพื่อการทดสอบเรียกใช้งาน
-i, --iterations คือ จำนวนครั้งที่ต้องการให้ เรียกคำสั่งเพื่อทดสอบ
-F, --delimiter คือ ค่าที่ใช้ในการแยกชุดคำสั่งของ mysql
--debug-info คือ ให้แสดงรายละเอียดเพิ่มเติม เช่น memory, cpu
--engine คือ ประเภทในการ create table เช่น innodb หรือ myisam
--create-schema คือ database ที่ต้องการทดสอบ
--number-of-queries คือ ค่าจำกัดการ query ข้อมูลต่อ client
--query คือ คำสั่งที่ต้องการเรียกใช้งาน ซึ่งสามารถกำหนดเป็นชุดคำสั่งหรือเป็นชื่อไฟล์ได้

เริ่มต้นการทดสอบ

ตัวอย่างที่ 1 : สำหรับการเริ่มต้น จะเป็นการจำลองการทดสอบเสมือน client 1 เครื่อง จากการสร้างคำสั่ง SQL ขึ้นโดยอัตโนมัติ

# mysqlslap --user=mysqladmin --password --host=localhost  --auto-generate-sql --verbose
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.010 seconds
    Minimum number of seconds to run all queries: 0.010 seconds
    Maximum number of seconds to run all queries: 0.010 seconds
    Number of clients running queries: 1
    Average number of queries per client: 0

จะเห็นได้ว่า ค่า output ที่ออกมาจะแสดงถึงสถิติของเวลาที่ดำเนินการ query ข้อมูล เช่น

  • ค่าเฉลียของการ run คำสั่ง คือ 0.010 วินาที
  • ค่าน้อยที่สุดของการ run คำสั่ง คือ 0.010 วินาที
  • ค่าสูงที่สุดของการ run คำสั่ง คือ 0.010 วินาที
  • จำนวน client ที่มีการเรียกใช้งาน และ จำนวน query เฉลี่ยต่อ 1 client  
  • ตัวอย่างที่ 2 : จะมีการเพิ่มจำนวน client เป็น 10 connection และมีการเรียกคำสั่งจำนวน 100 ครั้ง

โดยในการทดสอบ ยังคงเป็นการสร้างคำสั่ง SQL ขึ้นโดยอัตโนมัติเช่นตัวอย่างแรก

# mysqlslap --user=mysqladmin --password --host=localhost  --concurrency=10 --iterations=100
 --auto-generate-sql --verbose
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.027 seconds
    Minimum number of seconds to run all queries: 0.022 seconds
    Maximum number of seconds to run all queries: 0.038 seconds
    Number of clients running queries: 10
    Average number of queries per client: 0

จากค่า output ที่ออกมาจะแสดง เช่น ค่าเฉลี่ยของ query ข้อมูลจาก 10 connection จะใช้เวลาสูงขึ้นในการดำเนินการ จากเดิมที่มีการใช้งานเพียงเครื่องเดียวเล็กน้อย ทั้งนี้การใช้งาน option –auto-generate-sql จะเป็นการสร้าง table ขนาดเล็กเพื่อทดสอบเท่านั้น ผู้ทดสอบสามารถกำหนดค่าของ table โดยใช้ option –number-char-cols และ –number-int-col ได้ ดังตัวอย่าง

ตัวอย่างที่ 3

# mysqlslap --user=mysqladmin --password --host=localhost  --concurrency=10 --iterations=100 
--auto-generate-sql --number-char-cols=50 --number-int-cols=5 --verbose
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.117 seconds
    Minimum number of seconds to run all queries: 0.100 seconds
    Maximum number of seconds to run all queries: 0.187 seconds
    Number of clients running queries: 10
    Average number of queries per client: 0

*ทั้งนี้การทดสอบเพิ่มจำนวน connection หรือ iteration ที่สูงขึ้น จะส่งผลต่อการเรียกใช้ resource ของ server ที่เพิ่มขึ้น*

หลังจากที่ได้มีการทดสอบด้วย การสร้างคำสั่งขึ้นโดยอัตโนมัติแล้ว ตัวอย่างต่อไป จะเป็นการทดสอบด้วย การกำหนดชุดคำสั่ง mysql ขึ้นเอง โดยจะเรียกใช้งาน database: classicmodels ซึ่งมีการติดตั้งไปก่อนหน้านี้

ตัวอย่างที่ 4 :

# mysqlslap --user=mysqladmin --password --host=localhost --concurrency=10 --iterations=1 
--create-schema=classicmodels --query="SELECT * FROM orderdetails;" --verboseEnter password:
Benchmark
    Average number of seconds to run all queries: 0.025 seconds
    Minimum number of seconds to run all queries: 0.025 seconds
    Maximum number of seconds to run all queries: 0.025 seconds
    Number of clients running queries: 10
    Average number of queries per client: 1

ตัวอย่างที่ 5

# mysqlslap --user=mysqladmin --password --host=localhost --concurrency=10 --iterations=1 
--number-of-queries=50 --create-schema=classicmodels --query="SELECT * FROM orderdetails;" 
--verbose
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.161 seconds
    Minimum number of seconds to run all queries: 0.161 seconds
    Maximum number of seconds to run all queries: 0.161 seconds
    Number of clients running queries: 10
    Average number of queries per client: 5

จากตัวอย่างที่ 4 และ 5 นั้น จะเห็นว่า มีการกำหนดค่าต่างๆ เช่นเดียวกัน โดยจะมี option ที่แตกต่างกันคือ –number-of-queries เท่านั้น ซึ่งการกำหนด number-of-queries จะมีผลให้เครื่อง client มีการ query ข้อมูลต่อ client เพิ่มขึ้น ดังนั้นค่าเวลาที่ได้จึงเพิ่มสูงขึ้นไปด้วย

จากตัวอย่างที่ผ่านมาจะเป็นการกำหนดคำสั่งเพียงคำสั่งเดียว แต่สำหรับ server ที่ใช้งานจริง จะมีรูปแบบชุดคำสั่งที่ซับซ้อนเพิ่มขึ้น ซึ่งเราสามารถเพิ่มชุดคำสั่งได้ เช่น

ตัวอย่างที่ 6 : เพิ่มชุดคำสั่งที่ใช้งานเป็น 2 ชุดคำสั่ง

# mysqlslap --user=mysqladmin --password --host=localhost --concurrency=10 --iterations=1 
--number-of-queries=50 --create-schema=classicmodels --query="SELECT * FROM orderdetails;
SELECT * FROM payments;" --verbose
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.169 seconds
    Minimum number of seconds to run all queries: 0.169 seconds
    Maximum number of seconds to run all queries: 0.169 seconds
    Number of clients running queries: 10
    Average number of queries per client: 5

ทั้งนี้หากมีชุดคำสั่งที่ต้องทดสอบเพิ่มมากขึ้น ผู้ทดสอบสามารถสร้างไฟล์ ชุดคำสั่งของ mysql ขึ้นก่อน แล้วจึงทำการสั่ง คำสั่งผ่าน command line เช่น

#echo "SELECT * FROM orderdetails;SELECT * FROM payments;SELECT * FROM orders;SELECT 
customers.customerNumber, customers.customerName, payments.checkNumber 
FROM customers, payments WHERE customers.country='USA' and customers.customerNumber = 
payments.customerNumber;”  > /mysqlslap_test/test_query.sql

และทำการทดสอบด้วยคำสั่งดังนี้

# mysqlslap --user=mysqladmin --password --host=localhost --concurrency=50 --iterations=10 
--number-of-queries=1000 --create-schema=classicmodels --query="/mysqlslap_test/
test_query.sql" --verbose --debug-info
Enter password:
Benchmark
    Average number of seconds to run all queries: 3.152 seconds
    Minimum number of seconds to run all queries: 2.803 seconds
    Maximum number of seconds to run all queries: 3.569 seconds
    Number of clients running queries: 50
    Average number of queries per client: 20

User time 8.28, System time 1.95
Maximum resident set size 12824, Integral resident set size 0
Non-physical pagefaults 227342, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 43983, Involuntary context switches 4

จากตัวอย่างนี้จะมีการใช้ option debug-info เพิ่มเติม ซึ่ง option นี้จะแสดงค่าของ CPU และ RAM  ของ server ที่มีการเรียกใช้งาน และจะเห็นได้ว่าเมื่อมีการเพิ่มจำนวน connection และ number-of-queries ผลที่ได้คือเวลาที่เพิ่มขึ้นเป็น 3.152  วินาที โดยค่า Non-physical pagefaults จะอ้างอิงส่วนของ memory ว่าเพียงพอหรือไม่ และ context switches จะอ้างอิงข้อมูลของ CPU

ทั้งนี้ตัวอย่างที่นำเสนอไปด้านบน ผู้ดูแลระบบสามารถนำไปประยุกต์ ใช้ในการทดสอบเปรียบเทียบ โดยมีแนวทางดังนี้

  1. สามารถตรวจสอบหา คำสั่งต่างๆ ของ MySQL จาก server ที่ใช้งานได้จาก
    • คำสั่ง “mysqladmin pro –v” จะแสดง process ของ mysql ทีมีการทำงานอยู่ในขณะนั้น และเป็นคำสั่งในรูปเต็ม
    • สามารถเปิด slow query log เพื่อตรวจสอบหาคำสั่งที่ใช้งาน และนำมาใช้ในการทดสอบได้
  2. ทดสอบโดยการเปลี่ยน engine ที่ใช้งานระหว่าง innodb และ myisam ทั้งนี้ประเภทของ database อาจมีความเหมาะสมที่แตกต่างกันไป ซึ่งขึ้นอยู่กันการใช้ออกแบบและการใช้งาน
  3. ทดสอบทำ index หรือ key กับ field ที่มีการเรียกใช้งานบ่อย ซึ่งหลังจากการดำเนินการดังกล่าว การทำงานอาจเร็วขึ้นหรือไม่เปลี่ยนแปลง ทั้งนี้ขึ้นอยู่กับการออกแบบ database และคำสั่งที่เรียกใช้งาน
  4. เพื่อให้เห็นผลการทดสอบที่ชัดเจน ควรเลือกเครื่อง server สำหรับทดสอบให้มี spec ที่ใกล้เคียงกับเครื่องที่มีการใช้งานจริง

ทั้งนี้เมื่อมีการปรับค่าต่างๆ  ที่เกี่ยวข้องแล้ว ผู้ดูแลระบบสามารถทดสอบประสิทธิภาพของ mysql ก่อนและหลังดำเนินการ เพื่อปรับปรุงให้ระบบดีขึ้นได้

ข้อมูลเพิ่มเติม: http://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html