Mysqlslap: cara melakukan pengukuran - Revision history

Onnowpurbo: /* CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries */

2015-06-08T00:23:02Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

← Older revision		Revision as of 00:23, 8 June 2015
Line 537:		Line 537:
	Output:		Output:

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

	===Bagaimana kita dapat memperbaiki benchmark?===		===Bagaimana kita dapat memperbaiki benchmark?===
Line 574:		Line 574:
	--query="/mysqlslap_tutorial/capture_queries.sql" --verbose		--query="/mysqlslap_tutorial/capture_queries.sql" --verbose

	~~Benchmark~~		Output:
	~~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~~

	Kita akan melihat bahwa terjadi perbaikan di average, minimum, dan maximum time untuk menjalankan 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~~.		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

			Kita akan melihat bahwa terjadi perbaikan di average, minimum, dan maximum time untuk menjalankan query. Dari yang tadinya rata2 68 seconds, query sekarang di jalankan dalam 55 seconds. Ini perbaikan 13 seconds untuk beban yang sama.

	Karena perubahan database menghasilkan hasil yang baik di lingkungan test, kita dapat menggunakannya di production database server.		Karena perubahan database menghasilkan hasil yang baik di lingkungan test, kita dapat menggunakannya di production database server.

Onnowpurbo: /* Langkah Pertama - Install MySQL di sistem yang akan di test */

2015-06-08T00:17:45Z

Langkah Pertama - Install MySQL di sistem yang akan di test

← Older revision		Revision as of 00:17, 8 June 2015
Line 14:		Line 14:

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

	Biasanya akan di tanya root password.		Biasanya akan di tanya root password.

Onnowpurbo: /* CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries */

2015-06-07T23:48:44Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

← Older revision		Revision as of 23:48, 7 June 2015
Line 532:		Line 532:
	Run di server test:		Run di server test:

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

Line 571:		Line 571:
	Kembali ke terminal, jalankan mysqlslap dengan paramter yang sama, kita akan melihat benchmark yang berbeda.		Kembali ke terminal, jalankan mysqlslap dengan paramter yang sama, kita akan melihat benchmark yang berbeda.

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

Onnowpurbo: /* Troubleshooting - mysqlslap tidak menghasilkan Output */

2015-06-07T23:47:55Z

Troubleshooting - mysqlslap tidak menghasilkan Output

← Older revision		Revision as of 23:47, 7 June 2015
Line 591:		Line 591:

	Jika perintah test tidak menghasilkan outout, ini adalah indikasi bahwa sumber daya / resource pada server sudah habis.		Jika perintah test tidak menghasilkan outout, ini adalah indikasi bahwa sumber daya / resource pada server sudah habis.
	Simptom seperti tidak ada Benchmark output, atau error seperti		Simptom seperti tidak ada Benchmark output, server mungkin hang atau error seperti

	mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.		mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.
Line 599:		Line 599:

	Ini adalah cara yang baik untuk mengetahui limit dari kapasitas database server yang kita miliki.		Ini adalah cara yang baik untuk mengetahui limit dari kapasitas database server yang kita miliki.


	==Penutup==		==Penutup==

Onnowpurbo: /* Penutup */

2015-06-07T23:45:14Z

Penutup

← Older revision		Revision as of 23:45, 7 June 2015
Line 603:		Line 603:
	==Penutup==		==Penutup==

	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~~.		mysqlslap adalah tool yang simple, light-weight dan sangat mudah digunakan yang terintegrasi dalam engine MySQL database.

	~~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.		Pada tutorial ini, kita dapat melihat bagaimana menggunakan mysqlslap dengan berbagai opsi-nya pada database contoh / sample.
			Kita dapat men-download berbagai sample database pada MySQL site dan mem-praktekan-nya.

			Jangan pernah menjalankan / run test di 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.		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~~.		Skenario Real life testing biasanya lebih kompleks, tapi ini harusnya cukup memberikan tool yang dibutuhkan untuk memulai test dan memperbaiki performance database.

	==Referensi==		==Referensi==

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

Onnowpurbo: /* Troubleshooting - mysqlslap Doesn't Show Output */

2015-06-07T23:40:41Z

Troubleshooting - mysqlslap Doesn't Show Output

← Older revision		Revision as of 23:40, 7 June 2015
Line 588:		Line 588:
	Kita dapat mengulangi proses dari perintah test dan improvement di semua query yang kita lihat di log.		Kita dapat mengulangi proses dari perintah test dan improvement di semua query yang kita lihat di log.

	==Troubleshooting - mysqlslap ~~Doesn't Show~~ Output==		==Troubleshooting - mysqlslap tidak menghasilkan 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.~~		Jika perintah test tidak menghasilkan outout, ini adalah indikasi bahwa sumber daya / resource pada server sudah habis.
			Simptom seperti tidak ada Benchmark output, atau error seperti

	~~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~~.		mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.

	~~This can be a good way to find the outer limits of your~~ database server~~'s capacity~~.		Kita dapat mencoba test dengan parameter --concurrency atau --iterations yang lebih kecil.
	~~Conclusion~~		Alternatif lain, kita dapat meng-upgrade test server.

			Ini adalah cara yang baik untuk mengetahui limit dari kapasitas database server yang kita miliki.


			==Penutup==

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

Onnowpurbo: /* Bagaimana kita dapat memperbaiki benchmark? */

2015-06-07T23:37:47Z

Bagaimana kita dapat memperbaiki benchmark?

← Older revision		Revision as of 23:37, 7 June 2015
Line 583:		Line 583:
	Kita akan melihat bahwa terjadi perbaikan di average, minimum, dan maximum time untuk menjalankan 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.		Kita akan melihat bahwa terjadi perbaikan di average, minimum, dan maximum time untuk menjalankan 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~~.		Karena perubahan database menghasilkan hasil yang baik di lingkungan test, kita dapat menggunakannya di production database server.
			Perlu di ingat bahwa perubahan database selalu ada keuntungan dan kerugian.

	~~You can repeat the process of testing commands and improvements with all of the queries you gleaned from your~~ log.		Kita dapat mengulangi proses dari perintah test dan improvement di semua query yang kita lihat di log.

	==Troubleshooting - mysqlslap Doesn't Show Output==		==Troubleshooting - mysqlslap Doesn't Show Output==

Onnowpurbo: /* Bagaimana kita dapat memperbaiki benchmark?= */

2015-06-07T23:35:40Z

Bagaimana kita dapat memperbaiki benchmark?=

← Older revision		Revision as of 23:35, 7 June 2015
Line 544:		Line 544:
	Average number of queries per client: 1		Average number of queries per client: 1

	===Bagaimana kita dapat memperbaiki benchmark?====		===Bagaimana kita dapat memperbaiki benchmark?===

	Kita akan perlu familiar dengan query MySQL untuk menilai apa yang sedang dilakukan saat query.		Kita akan perlu familiar dengan query MySQL untuk menilai apa yang sedang dilakukan saat query.
Line 550:		Line 550:
	Melihat query yang dilakukan, kita dapat melihat bahwa query melalukan sejumlah join pada multiple table. Query memperlihatkan employee job histories dan untuk melakukan itu, query harus join beberapa table by empno field. Query juga menggunakan deptno field untuk join, akan tetapi karena hanya sedikit departement record, kita akan mengabaikan ini. Karena banyak empno entries di database, sangat logical untuk berasumsi bahwa dengan membuat index dari empno field akan meng-improve query.		Melihat query yang dilakukan, kita dapat melihat bahwa query melalukan sejumlah join pada multiple table. Query memperlihatkan employee job histories dan untuk melakukan itu, query harus join beberapa table by empno field. Query juga menggunakan deptno field untuk join, akan tetapi karena hanya sedikit departement record, kita akan mengabaikan ini. Karena banyak empno entries di database, sangat logical untuk berasumsi bahwa dengan membuat index dari empno field akan meng-improve 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.		Dengan banyak praktek, setelah kita menemukan query yang menyebabkan server menjadi lambat (ini dengan pertolongan mysqlslap), kita dapat melakukan assessment tentang query berbasis pada pengetahuan MySQL dan database yang kita miliki.

	~~Next~~, ~~you can try to improve your~~ database ~~or the queries that are being executed on it~~.		Selanjutnya, kita akan berusaha untuk memperbaiki database atau query yang akan di jalankan padanya.

	~~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.		Pada kasus ini, mari kita tambahkan index seperti dijelaskan di atas.
			Kita akan membuat tiga index pada empno.
			Satu index akan dibuat pada empno field di employees table.
			Satu index akan dibuat pada empno field di deptemp table,
			dan index terakhir pada emp_no field pada title table.

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

	mysql -u root -p123456		mysql -u root -p123456
Line 566:		Line 569:
	quit;		quit;

	~~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:		Kembali ke terminal, jalankan mysqlslap dengan paramter yang sama, kita akan melihat benchmark yang berbeda.

	sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=10 --iterations=2 --create-schema=employees_backup \		sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=10 --iterations=2 --create-schema=employees_backup \
Line 578:		Line 581:
	Average number of queries per client: 1		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.		Kita akan melihat bahwa terjadi perbaikan di average, minimum, dan maximum time untuk menjalankan 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.		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.

Onnowpurbo: /* CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries */

2015-06-07T23:30:32Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

← Older revision		Revision as of 23:30, 7 June 2015
Line 527:		Line 527:
	quit		quit

	~~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~~:		Sekarang coba menjalankan mysqlslap dengan script file.
			Pastikan kita menggunakan script file yang benar di parameter --query .
			Kita akan menggunakan hanya 10 concurrent connections dan mengulang dua kali.
			Run di server test:

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

	~~The benchmark output looks like this in our system~~:		Output:

	Benchmark		Benchmark
Line 541:		Line 544:
	Average number of queries per client: 1		Average number of queries per client: 1

	~~So how can we improve this~~ benchmark?		===Bagaimana kita dapat memperbaiki benchmark?====

	~~You'll need a certain amount of familiarity with~~ MySQL ~~queries to assess what the~~ query ~~is doing~~.		Kita akan perlu familiar dengan query MySQL untuk menilai apa yang sedang dilakukan saat query.

	~~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.		Melihat query yang dilakukan, kita dapat melihat bahwa query melalukan sejumlah join pada multiple table. Query memperlihatkan employee job histories dan untuk melakukan itu, query harus join beberapa table by empno field. Query juga menggunakan deptno field untuk join, akan tetapi karena hanya sedikit departement record, kita akan mengabaikan ini. Karena banyak empno entries di database, sangat logical untuk berasumsi bahwa dengan membuat index dari empno field akan meng-improve 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.		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.
Line 554:		Line 557:

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

			mysql -u root -p123456

	USE employees_backup;		USE employees_backup;
Line 559:		Line 564:
	CREATE INDEX dept_emp_empno ON dept_emp(emp_no);		CREATE INDEX dept_emp_empno ON dept_emp(emp_no);
	CREATE INDEX titles_empno ON titles(emp_no);		CREATE INDEX titles_empno ON titles(emp_no);
			quit;

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

Onnowpurbo: /* CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries */

2015-06-07T23:20:00Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

← Older revision		Revision as of 23:20, 7 June 2015
Line 498:		Line 498:


	~~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~~.		Log akan memperlihatkan perintah SQL dan timestamp.
			Perintah SQL SELECT yang ada di akhir file yang kita tertarik.
			Perintah ini yang ingin kita tangkap dan akan di buat replikasi untuk test-nya.
			Dalam contoh ini, kita sudah mengetahui perintah SQL SELECT tersebut yang kita gunakan.
			Tapi di production server yang sebenarnya, kita tidak akan mengetahui dan cara ini sangat bermanfaat untuk mengetahui query bermasalah yang dijalankan pada server production.

	~~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~~.		Perlu di catat, jika kita run / trigger query yang berbeda saat melakukan logging, file log akan sangat berbeda isinya.
			Pada skenario yang real, file log akan berisi banyak catatan / entry dari berbagai sambungan.
			Tugas kita adalah mencari query yang membuat masalah.
			Kita dapat saja mencatat semua query yang ada, me-list-nya dan menjalankan-nya di database yang akan di test.

	~~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~~.		Untuk setiap query yang akan di test, kita copy ke file dengan akhiran .sql.

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

	~~For example~~:

	sudo vi /mysqlslap_tutorial/capture_queries.sql		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:~~		Isi dengan MySQL query yang akan kita gunakan untuk test, tanpa line break tanpa semicolon di akhi

	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		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~~:		Selanjutnya, pastikan hasil query tidak di cached.
			Kembali ke MySQL monitor, jalankan perintah berikut:

			mysql -u root -p123456

	RESET QUERY CACHE;		RESET QUERY CACHE;
			quit

	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:		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 \		sudo mysqlslap --user=root --password=123456 --host=localhost --concurrency=10 --iterations=2 \
	--create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose		--create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose