Обсуждение: Guide to Posting Slow Query Questions
Regarding the wiki page on reporting slow queries: https://wiki.postgresql.org/wiki/Slow_Query_Questions We currently recommend EXPLAIN ANALYZE over just EXPLAIN. Should we recommend EXPLAIN (ANALYZE, BUFFERS) instead? I know I very often wish I could see that data. I don't think turning buffer accounting on adds much cost over a mere ANALYZE. Also, an additional thing that would be nice for people to report is whether long running queries are CPU bound or IO bound. Should we add that recommendation with links to how to do that in a couple OS, say, Linux and Windows. If so, does anyone know of good links that explain it for those OS? Cheers, Jeff
On Wed, Sep 12, 2012 at 7:00 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > Regarding the wiki page on reporting slow queries: > We currently recommend EXPLAIN ANALYZE over just EXPLAIN. Should we > recommend EXPLAIN (ANALYZE, BUFFERS) instead? I know I very often > wish I could see that data. I don't think turning buffer accounting > on adds much cost over a mere ANALYZE. Given the amount of version 8 installs out there the recommendation should be qualified with version >9.0. Otherwise a strong +1 > Also, an additional thing that would be nice for people to report is > whether long running queries are CPU bound or IO bound. Should we add > that recommendation with links to how to do that in a couple OS, say, > Linux and Windows. If so, does anyone know of good links that explain > it for those OS? I don't have any links for OS level monitoring, but with version 9.2 track_io_timing would do the job. Ants Aasma -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de
On Wed, Sep 12, 2012 at 11:40 PM, Ants Aasma <ants@cybertec.at> wrote: > On Wed, Sep 12, 2012 at 7:00 PM, Jeff Janes <jeff.janes@gmail.com> wrote: >> Regarding the wiki page on reporting slow queries: >> We currently recommend EXPLAIN ANALYZE over just EXPLAIN. Should we >> recommend EXPLAIN (ANALYZE, BUFFERS) instead? I know I very often >> wish I could see that data. I don't think turning buffer accounting >> on adds much cost over a mere ANALYZE. > > Given the amount of version 8 installs out there the recommendation > should be qualified with version >9.0. Otherwise a strong +1 Edit made. > >> Also, an additional thing that would be nice for people to report is >> whether long running queries are CPU bound or IO bound. Should we add >> that recommendation with links to how to do that in a couple OS, say, >> Linux and Windows. If so, does anyone know of good links that explain >> it for those OS? > > I don't have any links for OS level monitoring, but with version 9.2 > track_io_timing would do the job. I don't know how to advice people on how to use this to obtain information on a specific query. Would someone else like to take a stab at explaining that? Thanks, Jeff
On Wed, Sep 26, 2012 at 11:11 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Wed, Sep 12, 2012 at 11:40 PM, Ants Aasma <ants@cybertec.at> wrote: >> I don't have any links for OS level monitoring, but with version 9.2 >> track_io_timing would do the job. > > I don't know how to advice people on how to use this to obtain > information on a specific query. Would someone else like to take a > stab at explaining that? I added a line suggesting that 9.2 users turn it on via SET track_io_timing TO on; Ants Aasma -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de
On Sun, Oct 7, 2012 at 7:43 AM, Ants Aasma <ants@cybertec.at> wrote: > On Wed, Sep 26, 2012 at 11:11 PM, Jeff Janes <jeff.janes@gmail.com> wrote: >> On Wed, Sep 12, 2012 at 11:40 PM, Ants Aasma <ants@cybertec.at> wrote: >>> I don't have any links for OS level monitoring, but with version 9.2 >>> track_io_timing would do the job. >> >> I don't know how to advice people on how to use this to obtain >> information on a specific query. Would someone else like to take a >> stab at explaining that? > > I added a line suggesting that 9.2 users turn it on via SET > track_io_timing TO on; That was easy. I thought there was more to it because I didn't get any IO timing output when I tried it. But that was just because there was nothing to output, as all data was in shared_buffers by the time I turned the timing on. Thanks, Jeff