Mysqlslap: cara melakukan pengukuran

mysqlslap dapat meng-emulasi sejumlah besar koneksi klien meng-hit server database pada saat yang sama. Parameter pengujian dapat beban sepenuhnya dikonfigurasi dan hasil dari tes berjalan yang berbeda dapat digunakan untuk menyempurnakan desain database atau sumber daya perangkat keras.

Dalam tutorial ini kita akan belajar bagaimana menggunakan mysqlslap untuk menguji beban database MySQL dengan beberapa query dasar dan melihat bagaimana benchmarking dapat membantu kita dalam menyempurnakan query tersebut. Setelah beberapa demonstrasi dasar, kita akan menjalankan melalui skenario pengujian yang cukup realistis di mana kita membuat copy dari database yang sudah ada untuk pengujian, mengumpulkan pertanyaan dari log, dan menjalankan tes dari script.

Jika anda tertarik untuk pembandingan server database tertentu, anda harus menguji pada server dengan spesifikasi yang sama dan dengan salinan dari database anda yang terinstal.

Jika anda ingin menjalankan melalui tutorial ini untuk tujuan belajar dan melaksanakan setiap perintah di dalamnya, kami sarankan setidaknya 2 GB Droplet. Karena perintah dalam tutorial ini dimaksudkan untuk memaksa server, anda mungkin menemukan mereka akan time out pada server yang lebih kecil.

Sampel output dalam tutorial ini diproduksi dalam berbagai cara untuk mengoptimalkan contoh untuk mengajar.

Langkah Pertama - Install MySQL di sistem yang akan di test

Instalasi MySQL di Ubuntu relatif sederhana hanya menggunakan perintah,

apt-get update
apt-get install mysql-server mysql-client

Biasanya akan di tanya root password. Jika kita masih belajar dapat memasukan root password

123456

Agar lebih aman lakukan secure installation

mysql_secure_installation 

Jawab

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Change the root password? [Y/n] n
 ... skipping.

Remove anonymous users? [Y/n] 
 ... Success!

Disallow root login remotely? [Y/n] Y
 ... Success!

Remove test database and access to it? [Y/n] Y
 ... Success!

Reload privilege tables now? [Y/n] Y
 ... Success!

Untuk menjalankan MySQL Server cukup menggunakan perintah

/etc/init.d/mysql restart

Langkah Kedua - Install Sample Database

Kita perlu mengambil contoh database untuk test.

apt-get install bzip2
mkdir -p /mysqlslap_tutorial
cd /mysqlslap_tutorial
sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

Kita menggunakan database employees dengan 3 juta entry jadi cukup mendekati kondisi di lapangan.

cd /mysqlslap_tutorial/employees_db
ls -lh

total 161M
-rw-r--r-- 1 501 staff  752 Mar 30  2009 Changelog
-rw-r--r-- 1 501 staff 6,4K Okt  9  2008 employees_partitioned2.sql
-rw-r--r-- 1 501 staff 7,5K Peb  6  2009 employees_partitioned3.sql
-rw-r--r-- 1 501 staff 5,6K Peb  6  2009 employees_partitioned.sql
-rw-r--r-- 1 501 staff 3,8K Nov 28  2008 employees.sql
-rw-r--r-- 1 501 staff  241 Jul 30  2008 load_departments.dump
-rw-r--r-- 1 501 staff  14M Mar 30  2009 load_dept_emp.dump
-rw-r--r-- 1 501 staff 1,1K Jul 30  2008 load_dept_manager.dump
-rw-r--r-- 1 501 staff  17M Jul 30  2008 load_employees.dump
-rw-r--r-- 1 501 staff 111M Jul 30  2008 load_salaries.dump
-rw-r--r-- 1 501 staff  21M Jul 30  2008 load_titles.dump
-rw-r--r-- 1 501 staff 3,8K Mar 30  2009 objects.sql
-rw-r--r-- 1 501 staff 2,2K Jul 30  2008 README
-rw-r--r-- 1 501 staff 4,4K Mar 30  2009 test_employees_md5.sql
-rw-r--r-- 1 501 staff 4,4K Mar 30  2009 test_employees_sha.sql

Masukan database tersebut ke mysql

cd /mysqlslap_tutorial/employees_db
sudo mysql -u root -p123456 -t < employees.sql

Akan muncul

+-----------------------------+
| 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 |
+------------------+

Login ke MySQL dan cek tabel / data yang ada

mysql -u root -p123456

Lakukan

show databases;

Output

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

use employees;
show tables;

Output:

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

describe titles;

Output:

+-----------+-------------+------+-----+---------+-------+
| 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.00 sec)

select count(*) from titles;

Output:

+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.14 sec)

select count(*) from employees;

Output:

+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.11 sec)

Cek data-data lainnya, untuk keluar ketik

quit;

Langkah ketiga - Menggunakan mysqlslap

Opsi / Fitur mysqlslap bisa di peroleh dari

mysqlslap --help

Beberapa yang penting

--user 	MySQL username to connect to the database server
--password 	Password for the user account. It's best to leave it blank in command line
--host 	MySQL database server name
--port 	Port number for connecting to MySQL if the default is not used
--concurrency 	The number of simultaneous client connections mysqlslap will emulate
--iterations 	The number of times the test query will be run
--create-schema 	The database where the query will be run
--query 	The query to execute. This can either be a SQL query string or a path to a SQL script file
--create 	The query to create a table. Again, this can be a query string or a path to a SQL file
--delimiter 	The delimiter used to separate multiple SQL statements
--engine 	The MySQL database engine to use (e.g., InnoDB)
--auto-generate-sql 	Lets MySQL perform load testing with its own auto-generated SQL command

CONTOH: Benchmark menggunakan Auto-generated SQL dan Data

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

Output:

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

Coba 50 concurrent connections, gunakan auto-generated query run 10 kali:

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

Benchmark
	Average number of seconds to run all queries: 0.137 seconds
	Minimum number of seconds to run all queries: 0.117 seconds
	Maximum number of seconds to run all queries: 0.159 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0

Coba menggqunakan auto-generated SQL query untuk membuat tabel dengan 5 numeric kolom dan 20 character setiap kolom. Kita akan mensimulasi 50 client connection dan test diulangi 100 kali,

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

Jika kita berada di terminal MySQL monitor, padea saat test ini berjalan kita dapat melihat,

show databases;

Output:

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

Hasil benchmark

Benchmark
	Average number of seconds to run all queries: 0.400 seconds
	Minimum number of seconds to run all queries: 0.349 seconds
	Maximum number of seconds to run all queries: 0.713 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0

pada saat test ini selesai, mysqlslap akan membuang database test-nya

show databases;

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

CONTOH: Benchmarking dengan Custom Queries

Auto-generated SQL baik untuk meng-evaluasi sumber daya fisik pada server. Ini akan sangat baik untuk mengetahui level beban yang dapat di terima server.

Jika kita ingin melakukan troubleshoot performance untuk aplikasi spesifik yang tergantung database, dan kita ingin melakukan real query test. Query ini kemungkinan akan datang dari web atau server aplikasi.

Kita akan menggunakan database employees di --create-scheme untuk melakukan query ke table "dept_emp",

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

Jika berjalan dengan baik, maka akan keluar kira-kira,

Benchmark
	Average number of seconds to run all queries: 10.401 seconds
	Minimum number of seconds to run all queries: 9.613 seconds
	Maximum number of seconds to run all queries: 13.575 seconds
	Number of clients running queries: 50
	Average number of queries per client: 1

(Catatan: Jika query membuat hang atau lebih dari 10 menit tanpa output, coba kurangi --concurrency and/or --iterations, atau coba di server yang lebih besar)

Selanjutnya, kita akan menggunakan multiple SQL statements di --query parameter. Terminasi setiap query menggunakan titik koma (semicolon). Kita memberitahukan kode terminasi melalui opsi --delimiter :

sudo mysqlslap --user=root --password=123456 --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

Walaupun menggunakan iterasi yang sama, kinerja perintah multiple SELECT akan jauh lebih lambat (averaging 11.6 seconds vs. 10.4 seconds):

Benchmark
	Average number of seconds to run all queries: 11.640 seconds
	Minimum number of seconds to run all queries: 11.395 seconds
	Maximum number of seconds to run all queries: 13.239 seconds
	Number of clients running queries: 20
	Average number of queries per client: 5

Production SQL statement bisa menjadi sangat rumit. Akan lebih mudah untuk menambahkan complicated SQL statement ke script yang akan di cobakan untuk test. Kita cukup memberitahukan mysqlslap untuk membaca script tersebut.

Untuk memberikan contoh, mari kita membuat script file untuk perintah SQL sebagai berikut

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/

File select_query.sql mempunyai lima SELECT statements.

mysqlslap dapat mem-paralel query. Kita dapat men-set jumlah query untuk setiap test client. mysqlslap menset ini melalui opsi --number-of-queries option. Jadi jika kita mempunyai 50 connection and 1000 query, setiap client akan menjalankan 20 query.

Kita juga akan menggunakan switch --debug-info, yang akan memberikan indikasi sumber daya komputasi yang digunakan.

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

Setelah perintah ini selesai, kita akan melihat hasil yang menarik:

Benchmark
	Average number of seconds to run all queries: 117.846 seconds
	Minimum number of seconds to run all queries: 115.245 seconds
	Maximum number of seconds to run all queries: 120.448 seconds
	Number of clients running queries: 20
	Average number of queries per client: 50

User time 128.85, System time 73.92
Maximum resident set size 827056, Integral resident set size 0
Non-physical pagefaults 11192754, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 314596, Involuntary context switches 6382

Disini waktu yang dibutuhkan untuk melakukan query MySQL adalah 117.8 seconds, mendekati 2 menit. Ini jelas akan mempengaruhi RAM dan CPU di mesin virtual, ini juga karena banyak query dar client yang di ulang dua kali.

Kita juga melihat banyak non-physical page faults. Page fault terhadi ketika data tidak ada di memory dan system harus mengambilnya ke swap file di disk. Kita juga melihat informasi yang berkaitan dengan CPU, terlihat cukup banyak context switch.

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

So far in our examples, we have been running queries against the original employees database. That's something DBAs certainly wouldn't want you to do. And there's a good reason for it. You don't want to add load your production database and you don't want to run test queries that might delete, update, or insert data into your production tables.

We'll show you how to make a backup of a production database and copy it to a testing environment. In this example it's on the same server, but you ideally you would copy it to a separate server with the same hardware capacity.

More importantly, we'll show you how to record queries live from the production database and add them to a testing script. That is, you'll get queries from the production database, but run tests against the test database.

The general steps are as follows, and you can use them for any mysqlslap test:

1. Copy the production database to a test environment.
2. Configure MySQL to record and capture all connection requests and queries on the production database.
3. Simulate the use case you are trying to test. For example, if you run a shopping cart, you should buy something to trigger all the appropriate database queries from your application.
4. Turn off query logging.
5. Look at the query log and make a list of the queries you want to test.
6. Create a test file for each query you want to test.
7. Run the tests.
8. Use the output to improve your database performance.

To start, let's create a backup of the employees database. We will create a separate directory for its backup:

sudo mkdir /mysqlslap_tutorial/mysqlbackup
cd /mysqlslap_tutorial/mysqlbackup

Create the backup and move it to the new directory:

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

Go to your MySQL test server. Create the employees_backup database:

CREATE DATABASE employees_backup;

At this point, if you are using a separate server for testing, you should copy the employeesbackup.sql file over to it. From your main terminal session, import the backup data into the employeesbackup database:

sudo mysql -u root -p123456 employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql

On your production MySQL database server, enable the MySQL general query log and provide a file name for it. The general query log captures connection, disconnection, and query activities for a MySQL database instance.

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

Now run the queries that you want to test on the production MySQL server. In this example we will run a query from the command line. However, you may want to generate queries from your application instead of running them directly. If you have a slow process or website page that you want to test, you should run through that process or access that web page now. For example, if you are running a shopping cart, you may want to complete the checkout process now, which should trigger all the appropriate queries on the database server.

This is the query we will run on the production MySQL server. First use the right database:

USE employees;

Now run the 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;

Expected output:

489903 rows in set (4.33 sec)

We will turn off general logging when the query completes:

SET GLOBAL general_log=0;

Note that if you leave logging on, queries will continue to be added to the log, which may make testing harder. So, make sure you disable the log right after finishing your test. Let's check that the log file was created in the /var/lib/mysql directory:

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

Let's copy this file to our MySQL test directory. If you're using a separate server for testing, copy it to that server.

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

There should be quite a bit of data in this log file. In this example, the query we want should be near the end. Check the last part of the file:

sudo tail /mysqlslap_tutorial/capture_queries.log

Expected output:

        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

This log shows SQL commands and their timestamps. The SQL SELECT statement near the end of the file is what we are interested in. It should be exactly the same as the command we ran on the production database, since that's where we captured it.

In this example, we already knew the query. But, in a production environment, this method can be very useful for finding queries that you may not necessarily know about that are running on your server.

Note that if you ran or triggered different queries while logging, this file will look completely different. In a real scenario this file could be inundated with hundreds of entries coming from all different connections. Your goal is to find the query or queries that are causing a bottleneck. You can start by making a list of every line that includes the text Query. Then you'll have a list of exactly what queries were run on your database during the test.

For each query that you want to test, copy it into a file that ends with a .sql extension.

For example:

sudo vi /mysqlslap_tutorial/capture_queries.sql

The contents should be the MySQL query you want to test, without any line breaks and without a semicolon at the end:

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

Next, make sure the query results are not cached. Go back to your test MySQL session. Run the following command:

RESET QUERY CACHE;

Now it's time to run the mysqlslap utility with the script file. Make sure you use the correct script file name in the --query parameter. We will use only ten concurrent connections and repeat the test twice. Run this from your test server:

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

The benchmark output looks like this in our system:

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

So how can we improve this benchmark?

You'll need a certain amount of familiarity with MySQL queries to assess what the query is doing.

Looking back at the query, we can see it's doing a number of joins across multiple tables. The query shows employee job histories and in doing so, it's joining different tables by the empno field. It's also using the deptno field for joining, but since there are only a few department records, we will ignore this. Since there are many empno entries in the database, it's logical to assume that creating indexes on the empno field could improve the query.

With a little practice, once you've located queries that are taxing the server (that's the part that mysqlslap helps with!), you'll be able to make assessments about the queries based on your knowledge of MySQL and your database.

Next, you can try to improve your database or the queries that are being executed on it.

In our case, let's add the indexes we mentioned above. We will create three indexes on empno. One index will be created on the empno field in the employees table, another index will be created on the empno field in the deptemp table, and the last one will be created on the emp_no field in the titles table.

Let's go to our test MySQL session and execute the following commands:

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);

Coming back to our main terminal window on the test server, if we execute mysqlslap with the same parameters, we will see a difference in the benchmark:

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

We can see that there is an immediate improvement in the average, minimum, and maximum time to execute the query. Instead of an average 68 seconds, the query now executes in 55 seconds. That's an improvement of 13 seconds for the same load.

Since this database change produced a good result in the test environment, you can now consider rolling it out to your production database server, although keep in mind that database changes typically have trade-offs in their advantages and disadvantages.

You can repeat the process of testing commands and improvements with all of the queries you gleaned from your log.

Troubleshooting - mysqlslap Doesn't Show Output

If you run a test command and don't get any output, this is a good indication that your server resources could be maxed out. Symptoms may include a lack of the Benchmark output, or an error like mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.

You may want to try the test again with a smaller number in the --concurrency or --iterations parameter. Alternately, you can try upgrading your test server environment.

This can be a good way to find the outer limits of your database server's capacity. Conclusion

mysqlslap is a simple, light-weight tool that's easy to use and that integrates natively with the MySQL database engine. It's available for all editions of MySQL from version 5.1.4.

In this tutorial we have seen how to use mysqlslap with its various options and played around with a sample database. You can download other sample databases from the MySQL site and practice with those too. As we mentioned before, please don't run tests on a production database server.

The last use case in this tutorial involved only one query. While we improved the performance of that query somewhat by adding extra indexes to all three tables, the process may not be so simple in real life. Adding extra indexes can sometimes degrade system performance and DBAs often need to weigh the benefits of adding an extra index against the performance cost it may incur.

Real life testing scenarios are more complex, but this should give you the tools to get started with testing and improving your database performance.

Referensi

• https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap