# Find Queries with huge Result-Set **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 29, 2017, 3:22am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514 "2017-03-29T03:22:58Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Futureweb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/futureweb/32/1034_2.png) [@Futureweb](https://forums.percona.com/u/Futureweb)\ **Post date:** [March 29, 2017, 3:22am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514/1 "2017-03-29T03:22:58Z")
Hello, we are using MySQL as DB for our CMS. Lately I noticed a massive growth in Traffic between our HTTP \<–\> DB Server. Normally we got about 10MBit/s but since a few Days it’s over 100MBit/s … My first guess would be a Query which delivers a really huge Result-Set back to the HTTP Server - but I’m unable to find out which Query this could be (hundreds of Queries / Second) Already searched hours for an Answer - but no luck so far … so I hope someone here can help me finding the Bogus Query … 😉 Thank you, bye from sunny Austria Andreas Schnederle-Wagner ---
**Author:** ![eroomydna](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/eroomydna/32/941_2.png) [@eroomydna](https://forums.percona.com/u/eroomydna)\ **Post date:** [March 29, 2017, 3:44am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514/2 "2017-03-29T03:44:12Z")
Andreas, thanks for posting. I’d like to direct you firstly to the slow log facility inside MySQL. With this enabled at the right rate, you can capture large or long running queries. Once you’re confident that the log has the right sized sample to provide some insight, it’s time to work with the Percona Toolkit script called pt-query-digest. This tool will analyse your slow file and produce a report to show you a break down of what’s contained in the file. This will reveal queries that may need some tuning. There’s plenty of scope for researching options and variable of pt-query-digest, however it’s quite likely that even the defaults will provide you with some low hanging fruit. Whilst this blog post is a little old, it still holds truth. [url][https://www.percona.com/blog/2011/12/29/identifying-the-load-with-the-help-of-pt-query-digest-and-percona-server/[/url]](https://www.percona.com/blog/2011/12/29/identifying-the-load-with-the-help-of-pt-query-digest-and-percona-server/%5B/url%5D) I would recommend reviewing this and translating to tasks for you to identify what’s occurring on the server. BR Andrew ---
**Author:** ![Futureweb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/futureweb/32/1034_2.png) [@Futureweb](https://forums.percona.com/u/Futureweb)\ **Post date:** [March 29, 2017, 4:34am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514/3 "2017-03-29T04:34:47Z")
Hey Andrew, thank you for your really fast Answer! 😉 Unfortunately on this Server MariaDB is in use (as it’s delivered with RHEL) - so no “log\_slow\_verbosity=full:” which outputs the “Bytes\_Sent” is available! ☹ Will propose swap to Percona Server the next Meeting because of the extended debug abilities … (but this won’t be possible in the short term) Any other ideas on how to find out which Query is going bogus here? Thank you, bye from Austria Andreas ---
**Author:** ![Futureweb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/futureweb/32/1034_2.png) [@Futureweb](https://forums.percona.com/u/Futureweb)\ **Post date:** [March 30, 2017, 1:57am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514/4 "2017-03-30T01:57:03Z")
Hey mirfan, using Version 10.1.21 on this Server. Unfortunately (if it didn’t overlook something) it only supports “innodb,query\_plan,explain” for Slow Log Verbosity! :-/ Already analyzing Slow Log Output with pt-query-digest - but it seems the traffic generating Query isn’t within the Top XX Queries of that Server … :-/ Andreas ---
**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\ **Post date:** [March 30, 2017, 3:41am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514/5 "2017-03-30T03:41:59Z")
Yes, for MariaDB it only supports “query\_plan, innodb, explain”. However, you can filter output from Rows\_sent column to get an hint which query generates huge result set. ---
**Author:** ![Futureweb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/futureweb/32/1034_2.png) [@Futureweb](https://forums.percona.com/u/Futureweb)\ **Post date:** [April 3, 2017, 5:43am UTC](https://forums.percona.com/t/find-queries-with-huge-result-set/5514/6 "2017-04-03T05:43:07Z")
Hi [mirfan](https://percona.vanillacommunities.com/profile/x/x/8144), thx for the Tip with rows\_sent … this did the trick! 😉 bye from Austria Andreas