[{"_1":2,"_506":-5,"_507":-5},"loaderData",{"_3":4,"_22":23},"root",{"_5":6},"current",{"_7":8,"_9":10,"_21":8},"development",false,"env",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20},"COMMIT_SHA","8f16fa6f88a8f4ca7ab6820174c34f0788cb0c7c","IMAGE_CDN","https://planetscale-images.imgix.net","IMAGE_CDN_ENABLED","true","INTERNAL_API","https://api.planetscale.com","SENTRY_DSN","https://bd81903b44804e22a06bdc0c1a91b303@o499952.ingest.us.sentry.io/4504531942572032","userSignedIn","routes/blog.author.$slug",{"_24":25,"_30":31,"_496":497,"_3":505},"author",{"_26":27,"_28":29},"slug","florent","name","Florent Poinsard","blogs",[32,307],{"_26":33,"_34":35,"_268":269,"_270":-7,"_271":272,"_279":285,"_286":287,"_293":294,"_296":297,"_298":299,"_300":301},"arewefastyet-query-optimization-with-insights","body",[36,61,73,85,105,118,123,128,133,147,159,170,179,190,201,206,217,222,240,251,263],["SingleFetchClassInstance",37],{"_38":39,"_28":40,"_41":42,"_43":44},"$$mdtype","Tag","p","attributes",{},"children",[45,53,54,60],["SingleFetchClassInstance",46],{"_38":39,"_28":47,"_41":48,"_43":51},"a",{"_49":50},"href","https://vitess.io/blog/2021-07-08-announcing-vitess-arewefastyet/",[52],"Arewefastyet"," is an automatic benchmarking tool for ",["SingleFetchClassInstance",55],{"_38":39,"_28":47,"_41":56,"_43":58},{"_49":57},"https://vitess.io",[59],"Vitess",". It runs automated micro and macro benchmarks daily to monitor the performance of Vitess.",["SingleFetchClassInstance",62],{"_38":39,"_28":40,"_41":63,"_43":64},{},[65,66,72],"Arewefastyet uses PlanetScale for its database, which comes with an in-dashboard query monitoring tool: ",["SingleFetchClassInstance",67],{"_38":39,"_28":47,"_41":68,"_43":70},{"_49":69},"/docs/vitess/monitoring/query-insights",[71],"Insights",". This tool provides a complete overview of your database's performance, including details all the way down to the individual query level.",["SingleFetchClassInstance",74],{"_38":39,"_28":40,"_41":75,"_43":76},{},[77,78,84],"Using Insights, we were able to detect and improve the performance of two slow queries, which resulted in an ",["SingleFetchClassInstance",79],{"_38":39,"_28":80,"_41":81,"_43":82},"strong",{},[83],"85% decrease in query latency",".",["SingleFetchClassInstance",86],{"_38":39,"_28":40,"_41":87,"_43":88},{},[89],["SingleFetchClassInstance",90],{"_38":39,"_28":91,"_41":92,"_43":93},"picture",{},[94],["SingleFetchClassInstance",95],{"_38":39,"_28":96,"_41":97,"_43":104},"img",{"_98":99,"_100":101,"_102":103},"alt","Insights graph showing query latency at 40 and then immediately dropping to around 5","loading","lazy","src","/assets/blog/content/arewefastyet-query-optimization-with-insights/arewefastyet-query-graph.png",[],["SingleFetchClassInstance",106],{"_38":39,"_28":107,"_41":108,"_43":111},"h2",{"_109":110},"id","detecting-the-slow-queries",[112],["SingleFetchClassInstance",113],{"_38":39,"_28":47,"_41":114,"_43":116},{"_49":115},"#detecting-the-slow-queries",[117],"Detecting the slow queries",["SingleFetchClassInstance",119],{"_38":39,"_28":40,"_41":120,"_43":121},{},[122],"Insights includes an overview graph that lets you see several important metrics, such as query latency and number of rows read, at a glance. As you can see in the graph above, our p95 query latency was hovering around 40 ms. We wanted to see if we could improve this, so the first step was to narrow down the latency to the most problematic queries.",["SingleFetchClassInstance",124],{"_38":39,"_28":40,"_41":125,"_43":126},{},[127],"With Insights, this is simple. You can view a list of all queries that ran over a selected time period and sort them based on time per query.",["SingleFetchClassInstance",129],{"_38":39,"_28":40,"_41":130,"_43":131},{},[132],"This table immediately surfaced two queries that were running slowly. Let's examine them now.",["SingleFetchClassInstance",134],{"_38":39,"_28":40,"_41":135,"_43":136},{},[137],["SingleFetchClassInstance",138],{"_38":39,"_28":91,"_41":139,"_43":140},{},[141],["SingleFetchClassInstance",142],{"_38":39,"_28":96,"_41":143,"_43":146},{"_98":144,"_100":101,"_102":145},"Insights list of queries sorted by time per query. Two queries are highlighted in red, signaling a high query latency","/assets/blog/content/arewefastyet-query-optimization-with-insights/arewefastyet-query-list.png",[],["SingleFetchClassInstance",148],{"_38":39,"_28":149,"_41":150,"_43":152},"h3",{"_109":151},"the-benchmark-comparison-query",[153],["SingleFetchClassInstance",154],{"_38":39,"_28":47,"_41":155,"_43":157},{"_49":156},"#the-benchmark-comparison-query",[158],"The benchmark comparison query",["SingleFetchClassInstance",160],{"_38":39,"_28":40,"_41":161,"_43":162},{},[163,164,84],"The first query that showed up is used to return the previous successful run of the micro-benchmarks to compare against the latest run. This comparison is visible on the ",["SingleFetchClassInstance",165],{"_38":39,"_28":47,"_41":166,"_43":168},{"_49":167},"https://benchmark.vitess.io/microbench",[169],"Microbenchmarks page",["SingleFetchClassInstance",171],{"_38":39,"_28":172,"_41":173,"_43":178},"CodeBlock",{"_174":175,"_176":177},"html","

select\n    e.uuid,\n    e.git_ref\n  from\n    execution as e\n  where\n    e.source = :source\n    and e.`status` = :status\n    and e.type = :type\n    and e.git_ref != :git_ref\n  order by\n    e.started_at desc\n  limit\n    :row_count\n

","language","sql",[],["SingleFetchClassInstance",180],{"_38":39,"_28":149,"_41":181,"_43":183},{"_109":182},"the-oltp-and-tpcc-benchmark-query",[184],["SingleFetchClassInstance",185],{"_38":39,"_28":47,"_41":186,"_43":188},{"_49":187},"#the-oltp-and-tpcc-benchmark-query",[189],"The OLTP and TPCC benchmark query",["SingleFetchClassInstance",191],{"_38":39,"_28":40,"_41":192,"_43":193},{},[194,195,84],"This next query is used to return the last runs of OLTP and TPCC benchmarks for X days. The result we get from this query allows us to display the result (qps, tps, latency, etc.) for each benchmark. We then display this information on the ",["SingleFetchClassInstance",196],{"_38":39,"_28":47,"_41":197,"_43":199},{"_49":198},"https://benchmark.vitess.io/cron",[200],"CRON analytics page",["SingleFetchClassInstance",202],{"_38":39,"_28":172,"_41":203,"_43":205},{"_174":204,"_176":177},"

SELECT info.macrobenchmark_id, e.git_ref, e.source, e.finished_at, IFNULL(e.uuid, ''), \" +\n\t\t\"results.tps, results.latency, results.errors, results.reconnects, results.time, results.threads, \" +\n\t\t\"results.total_qps, results.reads_qps, results.writes_qps, results.other_qps \" +\n\t\t\"FROM execution AS e, macrobenchmark AS info, macrobenchmark_results AS results \" +\n\t\t\"WHERE e.uuid = info.exec_uuid AND e.status = \\\"finished\\\" AND e.finished_at BETWEEN DATE(NOW()) - INTERVAL ? DAY AND DATE(NOW() + INTERVAL 1 DAY) \" +\n\t\t\"AND e.source = ? AND info.vtgate_planner_version = ? AND info.macrobenchmark_id = results.macrobenchmark_id AND info.type = ?\n

",[],["SingleFetchClassInstance",207],{"_38":39,"_28":107,"_41":208,"_43":210},{"_109":209},"improving-the-slow-queries",[211],["SingleFetchClassInstance",212],{"_38":39,"_28":47,"_41":213,"_43":215},{"_49":214},"#improving-the-slow-queries",[216],"Improving the slow queries",["SingleFetchClassInstance",218],{"_38":39,"_28":40,"_41":219,"_43":220},{},[221],"Now that we had identified our two problematic queries, it was time to improve them. This step was straightforward. We noticed in both cases that these queries were missing an index.",["SingleFetchClassInstance",223],{"_38":39,"_28":40,"_41":224,"_43":225},{},[226,227,233,234,239],"We started with the OLTP and TPCC benchmark query. We opened a new PlanetScale deploy request to add an index to ",["SingleFetchClassInstance",228],{"_38":39,"_28":229,"_41":230,"_43":231},"code",{},[232],"finished_at"," and ",["SingleFetchClassInstance",235],{"_38":39,"_28":229,"_41":236,"_43":237},{},[238],"status",". Once merged, the deploy request is reflected in the graph below by the purple line with the number 6, meaning deploy request 6.",["SingleFetchClassInstance",241],{"_38":39,"_28":40,"_41":242,"_43":243},{},[244,245,250],"To address the benchmark comparison query, we added an index on ",["SingleFetchClassInstance",246],{"_38":39,"_28":229,"_41":247,"_43":248},{},[249],"started_at",". This update is reflected in deploy request number 7 on the graph.",["SingleFetchClassInstance",252],{"_38":39,"_28":40,"_41":253,"_43":254},{},[255],["SingleFetchClassInstance",256],{"_38":39,"_28":91,"_41":257,"_43":258},{},[259],["SingleFetchClassInstance",260],{"_38":39,"_28":96,"_41":261,"_43":262},{"_98":99,"_100":101,"_102":103},[],["SingleFetchClassInstance",264],{"_38":39,"_28":40,"_41":265,"_43":266},{},[267],"As you can see, these two small additions resulted in an 85% decrease in query latency. With Insights, we were able to detect, debug, and massively improve our queries in just minutes.","body_text","Arewefastyet is an automatic benchmarking tool for Vitess. It runs automated micro and macro benchmarks daily to monitor the performance of Vitess.\nArewefastyet uses PlanetScale for its database, which comes with an in-dashboard query monitoring tool: Insights. This tool provides a complete overview of your database's performance, including details all the way down to the individual query level.\nUsing Insights, we were able to detect and improve the performance of two slow queries, which resulted in an 85% decrease in query latency.\n\nDetecting the slow queries\nInsights includes an overview graph that lets you see several important metrics, such as query latency and number of rows read, at a glance. As you can see in the graph above, our p95 query latency was hovering around 40 ms. We wanted to see if we could improve this, so the first step was to narrow down the latency to the most problematic queries.\nWith Insights, this is simple. You can view a list of all queries that ran over a selected time period and sort them based on time per query.\nThis table immediately surfaced two queries that were running slowly. Let's examine them now.\n\nThe benchmark comparison query\nThe first query that showed up is used to return the previous successful run of the micro-benchmarks to compare against the latest run. This comparison is visible on the Microbenchmarks page.select\n e.uuid,\n e.git_ref\n from\n execution as e\n where\n e.source = :source\n and e.`status` = :status\n and e.type = :type\n and e.git_ref != :git_ref\n order by\n e.started_at desc\n limit\n :row_count\n\nThe OLTP and TPCC benchmark query\nThis next query is used to return the last runs of OLTP and TPCC benchmarks for X days. The result we get from this query allows us to display the result (qps, tps, latency, etc.) for each benchmark. We then display this information on the CRON analytics page.SELECT info.macrobenchmark_id, e.git_ref, e.source, e.finished_at, IFNULL(e.uuid, ''), \" +\n\t\t\"results.tps, results.latency, results.errors, results.reconnects, results.time, results.threads, \" +\n\t\t\"results.total_qps, results.reads_qps, results.writes_qps, results.other_qps \" +\n\t\t\"FROM execution AS e, macrobenchmark AS info, macrobenchmark_results AS results \" +\n\t\t\"WHERE e.uuid = info.exec_uuid AND e.status = \\\"finished\\\" AND e.finished_at BETWEEN DATE(NOW()) - INTERVAL ? DAY AND DATE(NOW() + INTERVAL 1 DAY) \" +\n\t\t\"AND e.source = ? AND info.vtgate_planner_version = ? AND info.macrobenchmark_id = results.macrobenchmark_id AND info.type = ?\n\nImproving the slow queries\nNow that we had identified our two problematic queries, it was time to improve them. This step was straightforward. We noticed in both cases that these queries were missing an index.\nWe started with the OLTP and TPCC benchmark query. We opened a new PlanetScale deploy request to add an index to finished_at and status. Once merged, the deploy request is reflected in the graph below by the purple line with the number 6, meaning deploy request 6.\nTo address the benchmark comparison query, we added an index on started_at. This update is reflected in deploy request number 7 on the graph.\n\nAs you can see, these two small additions resulted in an 85% decrease in query latency. With Insights, we were able to detect, debug, and massively improve our queries in just minutes.","aside","toc",[273,283],{"_43":274,"_109":110,"_277":282,"_279":117},[275,280],{"_43":276,"_109":151,"_277":278,"_279":158},[],"level",3,"title",{"_43":281,"_109":182,"_277":278,"_279":189},[],2,{"_43":284,"_109":209,"_277":282,"_279":216},[],"Optimizing queries in arewefastyet","authors",[288,290],{"_26":27,"_28":29,"_289":-7},"x",{"_26":291,"_28":292,"_289":-7},"harshit","Harshit Gangal","categories",[295],"vitess","excerpt","Learn how we detected and optimized two slow queries in arewefastyet using PlanetScale Insights.","createdAt","2022-10-11T08:00:00Z","meta",{"_302":303,"_304":297,"_305":306,"_279":285},"canonical","https://planetscale.com/blog/arewefastyet-query-optimization-with-insights","description","image","/assets/arewefastyet-query-optimization-with-insights-social-1Oa1ErbJ.png",{"_26":308,"_34":309,"_268":475,"_270":-7,"_271":476,"_279":487,"_286":488,"_293":490,"_296":491,"_300":492,"_298":495},"announcing-vitess-13",[310,321,339,350,355,366,385,396,407,418,436,447,459],["SingleFetchClassInstance",311],{"_38":39,"_28":40,"_41":312,"_43":313},{},[314,315,84],"The Vitess maintainers are pleased to announce the general availability of ",["SingleFetchClassInstance",316],{"_38":39,"_28":47,"_41":317,"_43":319},{"_49":318},"https://github.com/vitessio/vitess/releases/tag/v13.0.0",[320],"Vitess 13",["SingleFetchClassInstance",322],{"_38":39,"_28":323,"_41":324,"_43":327},"Callout",{"_325":326},"type","note",[328],["SingleFetchClassInstance",329],{"_38":39,"_28":40,"_41":330,"_43":331},{},[332,333,84],"This article originally appeared on the ",["SingleFetchClassInstance",334],{"_38":39,"_28":47,"_41":335,"_43":337},{"_49":336},"https://vitess.io/blog/2022-02-22-announcing-vitess-13/",[338],"Vitess blog",["SingleFetchClassInstance",340],{"_38":39,"_28":107,"_41":341,"_43":343},{"_109":342},"major-themes",[344],["SingleFetchClassInstance",345],{"_38":39,"_28":47,"_41":346,"_43":348},{"_49":347},"#major-themes",[349],"Major themes",["SingleFetchClassInstance",351],{"_38":39,"_28":40,"_41":352,"_43":353},{},[354],"In this release, the Vitess maintainers have made significant progress in several areas, including query serving and cluster management.",["SingleFetchClassInstance",356],{"_38":39,"_28":107,"_41":357,"_43":359},{"_109":358},"compatibility",[360],["SingleFetchClassInstance",361],{"_38":39,"_28":47,"_41":362,"_43":364},{"_49":363},"#compatibility",[365],"Compatibility",["SingleFetchClassInstance",367],{"_38":39,"_28":40,"_41":368,"_43":369},{},[370,371,377,378,384],"This release comes with major compatibility improvements. We added support for a large number of ",["SingleFetchClassInstance",372],{"_38":39,"_28":47,"_41":373,"_43":375},{"_49":374},"https://vitess.io/docs/13.0/user-guides/configuration-basic/collations/",[376],"character sets"," and improved our evaluation engine to perform more evaluations at the VTGate level. ",["SingleFetchClassInstance",379],{"_38":39,"_28":47,"_41":380,"_43":382},{"_49":381},"https://vitess.io/docs/13.0/reference/compatibility/query_planner/",[383],"Gen4 planner"," is no longer experimental and we have used it to add support for a number of previously unsupported complex queries.",["SingleFetchClassInstance",386],{"_38":39,"_28":107,"_41":387,"_43":389},{"_109":388},"cluster-management",[390],["SingleFetchClassInstance",391],{"_38":39,"_28":47,"_41":392,"_43":394},{"_49":393},"#cluster-management",[395],"Cluster management",["SingleFetchClassInstance",397],{"_38":39,"_28":40,"_41":398,"_43":399},{},[400,406],["SingleFetchClassInstance",401],{"_38":39,"_28":47,"_41":402,"_43":404},{"_49":403},"https://vitess.io/docs/13.0/user-guides/configuration-basic/vtorc/",[405],"VTOrc"," is now more tightly integrated with other components in Vitess. It has been enhanced with errant GTID detection during emergency failovers. User-initiated emergency failovers are now more robust and should almost always succeed.",["SingleFetchClassInstance",408],{"_38":39,"_28":107,"_41":409,"_43":411},{"_109":410},"multi-column-vindexes",[412],["SingleFetchClassInstance",413],{"_38":39,"_28":47,"_41":414,"_43":416},{"_49":415},"#multi-column-vindexes",[417],"Multi-column Vindexes",["SingleFetchClassInstance",419],{"_38":39,"_28":40,"_41":420,"_43":421},{},[422,423,429,430,435],"While we have had some support for ",["SingleFetchClassInstance",424],{"_38":39,"_28":47,"_41":425,"_43":427},{"_49":426},"https://vitess.io/docs/13.0/user-guides/vschema-guide/subsharding-vindex/",[428],"multi-column vindexes"," since Vitess 5.0, this release brings better support and performance improvements. We can now route to a subset of shards when a partial column list is provided in a ",["SingleFetchClassInstance",431],{"_38":39,"_28":229,"_41":432,"_43":433},{},[434],"WHERE"," clause instead of scattering to all shards.",["SingleFetchClassInstance",437],{"_38":39,"_28":107,"_41":438,"_43":440},{"_109":439},"website-docs",[441],["SingleFetchClassInstance",442],{"_38":39,"_28":47,"_41":443,"_43":445},{"_49":444},"#website-docs",[446],"Website docs",["SingleFetchClassInstance",448],{"_38":39,"_28":40,"_41":449,"_43":450},{},[451,452,458],"Previously, we had only one version of the documentation on the website. Docs for older releases were archived as PDFs, which made it difficult for users to find information relevant to the specific release they might be running. We now have versioned docs on the website for ",["SingleFetchClassInstance",453],{"_38":39,"_28":47,"_41":454,"_43":456},{"_49":455},"https://vitess.io/docs/13.0/",[457],"this release"," and the past two releases. Eventually, we will have documentation for all supported releases.",["SingleFetchClassInstance",460],{"_38":39,"_28":40,"_41":461,"_43":462},{},[463,464,468,469,84],"Please download ",["SingleFetchClassInstance",465],{"_38":39,"_28":47,"_41":466,"_43":467},{"_49":318},[320]," and try it out! Issues can be reported via ",["SingleFetchClassInstance",470],{"_38":39,"_28":47,"_41":471,"_43":473},{"_49":472},"https://github.com/vitessio/vitess/issues",[474],"GitHub","The Vitess maintainers are pleased to announce the general availability of Vitess 13.\nThis article originally appeared on the Vitess blog.\nMajor themes\nIn this release, the Vitess maintainers have made significant progress in several areas, including query serving and cluster management.\nCompatibility\nThis release comes with major compatibility improvements. We added support for a large number of character sets and improved our evaluation engine to perform more evaluations at the VTGate level. Gen4 planner is no longer experimental and we have used it to add support for a number of previously unsupported complex queries.\nCluster management\nVTOrc is now more tightly integrated with other components in Vitess. It has been enhanced with errant GTID detection during emergency failovers. User-initiated emergency failovers are now more robust and should almost always succeed.\nMulti-column Vindexes\nWhile we have had some support for multi-column vindexes since Vitess 5.0, this release brings better support and performance improvements. We can now route to a subset of shards when a partial column list is provided in a WHERE clause instead of scattering to all shards.\nWebsite docs\nPreviously, we had only one version of the documentation on the website. Docs for older releases were archived as PDFs, which made it difficult for users to find information relevant to the specific release they might be running. We now have versioned docs on the website for this release and the past two releases. Eventually, we will have documentation for all supported releases.\nPlease download Vitess 13 and try it out! Issues can be reported via GitHub.",[477,479,481,483,485],{"_43":478,"_109":342,"_277":282,"_279":349},[],{"_43":480,"_109":358,"_277":282,"_279":365},[],{"_43":482,"_109":388,"_277":282,"_279":395},[],{"_43":484,"_109":410,"_277":282,"_279":417},[],{"_43":486,"_109":439,"_277":282,"_279":446},[],"Announcing Vitess 13",[489],{"_26":27,"_28":29,"_289":-7},[295],"Learn about the Vitess 13 release.",{"_302":493,"_304":491,"_305":494,"_279":487},"https://planetscale.com/blog/announcing-vitess-13","/assets/blog/content/announcing-vitess-13/03f9a51517a87aa62a3e4f4c30e4eaa9bbc247dc-2400x1256.jpg","2022-02-22T19:10:00.000Z","pagination",{"_498":499,"_500":8,"_501":8,"_502":499,"_503":504},"page",1,"hasPrev","hasNext","totalPages","url","https://planetscale.com/blog/author/florent","https://planetscale.com","actionData","errors"]

Posts by Florent Poinsard

Optimizing queries in arewefastyet

Announcing Vitess 13