MySQL Benchmark Techniques June 27, 2012 Zhang Lu Sr. Operations Architect zhang.lu@teamaol

1. MySQL Benchmark TechniquesJune 27, 2012Zhang Lu Sr. Operations Architectzhang.lu@teamaol.com

2. Million dollar questions • Why benchmarking is important? • “Benchmarking is the study of a company or organization that excels in all aspects of it’s achievements, then using the data that is collected to improve the performance of their own company” • What is performance? • Performance is your customer’s happiness AOL PowerPoint Template | Confidential

3. Benchmark types • Pre-production • research • Post-production • New hardware rollout • New software (server) rollout • Schema changes (tuning) • Configuration changes (tuning) AOL PowerPoint Template | Confidential

4. The steps • Clone production database • Catch production traffic • Replay (real time or offline) • Comparison and draw conclusion AOL PowerPoint Template | Confidential

5. Catch production traffic – tcpdump • Use tcpdump to capture TCP packets to/from MySQL server port • pt-query-digest to parse tcpdump output • Pros • No interruption to application • Easy to get started • Cons • It takes time to parse the file • Not easy to replay in real time • No information of current DB name at the time of request AOL PowerPoint Template | Confidential

6. Catch production traffic – MySQL Proxy • Setup mysqlproxy to use production DB as backend • Modify lua script to save incoming requests along with request time (offset), response time, etc. • Bounce application to use mysqlproxy port instead of MySQL port • Pros • Flexible • Real time replay is possible • Cons • Additional process needs to be setup (mysqlproxy) • Requires some understanding of LUA language AOL PowerPoint Template | Confidential

7. Replay • True production replay • Same interval between establishing new connections • Same interval between each request • Only difference is the response time of each request • Stress test • No wait between each request • Adjust interval between new connections • Replay each set of files multiple times with overlaps AOL PowerPoint Template | Confidential

8. Using tcpdump and pt-query-digest • tcpdump -s 65535 -x -nn -q -tttt -i any -c 9000000 port 3306 • Packet size 65535 (-s) • Print each packet in HEX (-x) • Do not convert protocol and port to name (-nn) • Quiet – less protocol information (-q) • Print timestamp in default format (-tttt) • Any interface (NIC) (-i) • Capture 9000000 packets and exit (-c) • Only capture traffic in/out port 3306 • pt-query-digest --type=tcpdump --watch-server=10.10.10.123:3306 --print --no-report • Must specify IP:PORT if your DB is not running on 3306 • Use nc to stream data to another host, either save for later use or just pipe to pt-query-digest AOL PowerPoint Template | Confidential

9. Using tcpdump and pt-query-digest - cont • Before replaying, need to split the output from pt-query-digest • Split query into different files based on client IP and port • Reformat meta data in the form that the replay script can understand # Time: 120427 11:07:20.908902 # Client: 172.19.160.8:48366 # Thread_id: 4294967305 # Query time: 0.000154 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SELECT * FROM my_table; # Time: 120427 11:07:20.910313 # Client: 172.19.160.8:48366 # Thread_id: 4294967305 # Query_time: 0.000454 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SELECT * FROM my_table2; AOL PowerPoint Template | Confidential

10. Using mysqlproxy - setup • Configuration file [mysql-proxy] log-file = /var/log/mysql-proxy.log log-level = message proxy-address=:4040 proxy-backend-addresses=dbslab-mtc02.ops.aol.com:3406 admin-address = 127.0.0.1:4041 admin-username = proot admin-password = padmin admin-lua-script = /var/lib/mysql-proxy/lua/admin.lua keepalive = true proxy-lua-script=/var/lib/mysql-proxy/lua/proxy/myproxy.lua AOL PowerPoint Template | Confidential

11. Using mysqlproxy – lua script • Loaded for each connection request • Can be updated on the fly • External modules can be loaded • MySQL DB requests is sequential per connection • It is possible to send the query to another connection other than production, but that means the client response time is doubled • Manipulate incoming requests before send to the server • Manipulate result set before send back to the client AOL PowerPoint Template | Confidential

12. Using mysqlproxy – lua script functions • read_auth_result() function read_auth_result(auth) local time = os.time() local state = auth.packet:byte() con_time = time if state == proxy.MYSQLD_PACKET_OK then myvars.file = proxy.global.file_base .. "." .. time .. "." .. proxy.connection.server['thread_id']; myvars.fh = io.open(myvars.file, "a"); if proxy.connection.client.default_db ~= '' then query_id = query_id + 1 myvars.fh:write("### Start Time Offset=0 Query Time=0 Response Time=0 Query ID=" .. query_id .. " Username=" .. proxy.connection.server['username'] .. "\nUSE " .. proxy.connection.client.default_db .. "\n### End\n") myvars.fh:flush() end end end AOL PowerPoint Template | Confidential

13. Using mysqlproxy – lua script functions (cont’d) • read_query() function read_query( packet ) query = packet:sub(2) q_time = os.time() - con_time myvars.query= query if (string.byte(packet) == proxy.COM_QUIT) then proxy.global.type = proxy.MYSQLD_PACKET_OK myvars.fh:write("### Disconnected\n") myvars.fh:close() return proxy.PROXY_SEND_RESULT elseif string.byte(packet) == proxy.COM_INIT_DB then query_id = query_id + 1 myvars.fh:write("### Start Time Offset=0 Query Time=0 Response Time=0 Query ID=" .. query_id .. " Username=" .. proxy.connection.server['username'] .. "\nUSE " .. query .. "\n### End\n") return elseif string.byte(packet) == proxy.COM_QUERY then proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query, { resultset_is_needed = true } ) return proxy.PROXY_SEND_QUERY end end AOL PowerPoint Template | Confidential

14. Using mysqlproxy – lua script functions (cont’d) • read_query_result() function read_query_result(inj) -- filter out those SHOW commands -- both inj.query_time and inj.response_time are in micro seconds, print using milli seconds if string.find(string.upper(myvars.query), '^SHOW ') then return end query_id = query_id + 1 header = "### Start Time Offset=" .. q_time .. " Query Time=" .. (inj.query_time / 1000) .. " Response Time=" .. (inj.response_time / 1000) .. " Query ID=" .. query_id .. "\n" myvars.fh:write(header) myvars.fh:write(myvars.query .. "\n### End\n") myvars.fh:flush() end • Reference http://dev.mysql.com/doc/refman/5.5/en/mysql-proxy-scripting-structures.html AOL PowerPoint Template | Confidential

15. Replay • Using any language that can talk to MySQL server • Main thread watches the lua generated files • Collect all files in the log directory and sort by created timestamp • When the wait time is up (delta between the timestamps), which is optional, an new connection is established • Each file is processed over one connection • Replay thread • Sends the requests in file to the server based on the interval between each captured queries (optional) • Record query ID along with response time for analysis AOL PowerPoint Template | Confidential

16. Compare – Overall DB time AOL PowerPoint Template | Confidential

17. Compare – Update DB time AOL PowerPoint Template | Confidential