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

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

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

2015-06-07T23:48:44Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

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

2015-06-07T23:47:55Z

Troubleshooting - mysqlslap tidak menghasilkan Output

Onnowpurbo: /* Penutup */

2015-06-07T23:45:14Z

Penutup

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

2015-06-07T23:40:41Z

Troubleshooting - mysqlslap Doesn't Show Output

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

2015-06-07T23:37:47Z

Bagaimana kita dapat memperbaiki benchmark?

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

2015-06-07T23:35:40Z

Bagaimana kita dapat memperbaiki benchmark?=

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

2015-06-07T23:30:32Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

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

2015-06-07T23:20:00Z

CONTOH: Skenario Benchmarking yang praktis dan Capturing Live Queries

@@ Line 537: / Line 537: @@
 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?===
@@ Line 574: / Line 574: @@
   --query="/mysqlslap_tutorial/capture_queries.sql" --verbose
-Benchmark
+Output:
-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
 Karena perubahan database menghasilkan hasil yang baik di lingkungan test, kita dapat menggunakannya di production database server.

@@ Line 14: / Line 14: @@
   apt-get update
-  apt-get install mysql-server mysql-client
+  apt-get install -y mysql-server mysql-client
 Biasanya akan di tanya root password.

@@ Line 532: / Line 532: @@
 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
@@ Line 571: / Line 571: @@
 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

@@ Line 591: / Line 591: @@
 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.
@@ Line 599: / Line 599: @@
 Ini adalah cara yang baik untuk mengetahui limit dari kapasitas database server yang kita miliki.
 ==Penutup==

@@ Line 603: / Line 603: @@
 ==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.
 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==
 * https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap

@@ Line 588: / Line 588: @@
 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.
-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.
 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.

@@ Line 544: / Line 544: @@
          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.
@@ 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.
-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.
   mysql -u root -p123456
@@ Line 566: / Line 569: @@
   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 \
@@ Line 578: / Line 581: @@
          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.

@@ Line 527: / Line 527: @@
   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.
   sudo mysqlslap --user=root --password=123456 --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:
+Output:
 Benchmark
@@ Line 541: / Line 544: @@
          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.
@@ Line 554: / Line 557: @@
 Let's go to our test MySQL session and execute the following commands:
   USE employees_backup;
@@ Line 559: / Line 564: @@
   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:

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