1

We are planing to test the performance of the new DB server. For that we have the below the plan:

  1. get production sql queries

  2. replay those sql queries against the different DB

I was planing to use pt-log-player to play the sql queries and use the pt-query-digest to analyze the result files which gets generated from pt-log-player after execution. So my questions are:

  1. how to get the production query in the first place. enabling slow log query is not an option due to performance reason (as our DB admin denied about that)
  2. Why pt-log-player is deprecated, was there any bug or whats the reason behind.
  3. Or someone can suggest me a complete different toolset to do the perf test. Thanks.
Trying
  • 203
  • 1
  • 3
  • 8

2 Answers2

1

1) Although I prefer tcpdump myself I would ask what is the reason not to enable slow logs because it is not that big of an overhead. With reasonable thresholds it's actually very useful.

If you want to be super cautious you can safely enable it with a conservative long_query_time and slowly dropping it. The variable is dynamic so you can change it on the fly if you experience issues. Also depending on the flavor of your server (Percona for example) you may be able to set log_slow_rate_limit which was designed for exactly this purpose.

Another way to do it would be to use tcpdump which you can do on the server, on a shadow server (if your networking setup supports it) or on the application server as well. Then you can use pt-query-digest to extract the queries from it:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

This is a very versatile solution so it's worth to get familiar with.

More info can be found on https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

2) This is just an assumption but there are many-many ways to execute/replay queries so I think that's the reason for the dropped support.

3) If you have your queries you can also use percona playback, mysqlslap, sysbench or any other tools to execute them as you wish. If you want to write your own stress tool I have a web request playback code example which can give you some ideas.

Community
  • 1
Károly Nagy
  • 3,020
  • 1
  • 13
  • 13
  • Actually i am inclined towards tcpdump. I am able to capture the packets through tcpdump from the existing PROD DB but how will i reply those against the new PROD DB which i want to do the perf test? – Trying Jun 21 '16 at 19:26
  • The simplest method is to simply pipe it to mysql: cat mysql.queries.sql | mysql -h new_db_name -u user_name -ppassword But of course this is going to be single threaded execution. You can split the files into multiple smaller ones and iterate over with putting the execution in the background: for f in $(ls mysql.queries.sql.*); do cat mysql.queries.sql | mysql -h new_db_name -u user_name -ppassword & ; done;. Or take a look at this script if you prefer python: https://github.com/charlesnagy/web-toolkit/blob/master/captest/wt-capacity-test.py You need to change it to mysql of course. – Károly Nagy Jun 22 '16 at 07:59
  • I am little confused. From the above example tcpdump should create a file mysql.tcp.txt correct? How will you divide that and how you will run those against mysql because those will be in some bytes? – Trying Jun 23 '16 at 09:05
0
  1. Grab a snapshot of the data.
  2. Turn on the "general log" to get a complete listing of the queries.
  3. After a while, turn off the general log. (It fills up the disk rapidly.)

The tricky part is to do steps 1 and 2 'simultaneously'. If you have LVM set up on the server, you can come close to "instantaneous".

The reason for getting the data snapshot exactly when you turn on the log is that otherwise queries could fail because of the changes (inserts, deletes, updates, etc) that happen.

As for the DBA refusing to turn on the slowlog -- well, he is being unnecessarily paranoid. (In my previous job, it was standard procedure to keep the slowlog on on all of the thousands of servers.)

The general log has some I/O impact, and a non-trivial risk of filling up the disk but that can be monitored.

The Slowlog is the best way (in my opinion) of finding the "quick fix" to performance issues. If the DBA will turn it on for just one hour, you may be able to give back to him an improvement that will cut the CPU and/or I/O usage in half. (One time, I saw the CPU drop from 100% to 2% -- it was a one-line change on one SELECT: WHERE DATE(col) = '...' --> WHERE col = '...')

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • isn't it little risky if we set long_query_time to 0.0. That means log all the queries. "In my previous job, it was standard procedure to keep the slowlog on on all of the thousands of servers." are you saying that you have set long_query_time to 0.0 for those servers? – Trying Jul 03 '16 at 12:15
  • No, it was set to 2 seconds. This was sufficient to catch most things that are likely to go wrong. Even a 1-second query that is run often enough will occasionally take 2 seconds, thereby making itself known. – Rick James Jul 03 '16 at 22:28
  • No that was not my intent of the question. Actually what i want to understand is if i will make long_query_time=0 (because i want to capture all the queries and then i will reply to a different mysql host) then it will log every query to the slow log query hence it can spike the query response time correct? – Trying Jul 04 '16 at 05:47
  • Yes 0 logs every query. – Rick James Jul 04 '16 at 17:28