Profile what the production server is doing
Is there a tool which does this for PostgreSQL? Take a "snapshot" of what the server is doing about 10 times per second. Write this to a file. After N hours you can aggregate the file. What does the server do most of the time? Which tables/index gets used the most. Before optimizing a database, I would like to know what is going on in the production system. I know that there are internal tables like pg_stat_statements. But I guess doing a snapshot every N millseconds will present a better picture of what is going in in real life. Is there already a tool which goes this way? Or is there a better way? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programming-guidelines
Re: Profile what the production server is doing
Hi, On Mon, Jul 23, 2018 at 1:18 PM, Thomas Güttler wrote: > Is there a tool which does this for PostgreSQL? > > Take a "snapshot" of what the server is doing about 10 times per second. > Write this to a file. > After N hours you can aggregate the file. > What does the server do most of the time? > Which tables/index gets used the most. > > Before optimizing a database, I would like to know what is going > on in the production system. > > I know that there are internal tables like pg_stat_statements. > But I guess doing a snapshot every N millseconds will present a > better picture of what is going in in real life. > > Is there already a tool which goes this way? You can look at powa (https://powa.readthedocs.io/) which aims to provide this kind of information.
Re: Profile what the production server is doing
I'm biased, but I think VividCortex (my company's product) is amazing at this. On Mon, Jul 23, 2018 at 7:18 AM Thomas Güttler wrote: > Is there a tool which does this for PostgreSQL? > > Take a "snapshot" of what the server is doing about 10 times per second. > Write this to a file. > After N hours you can aggregate the file. > What does the server do most of the time? > Which tables/index gets used the most. > > Before optimizing a database, I would like to know what is going > on in the production system. > > I know that there are internal tables like pg_stat_statements. > But I guess doing a snapshot every N millseconds will present a > better picture of what is going in in real life. > > Is there already a tool which goes this way? > > Or is there a better way? > > Regards, >Thomas Güttler > > -- > Thomas Guettler http://www.thomas-guettler.de/ > I am looking for feedback: > https://github.com/guettli/programming-guidelines > >
Re: Profile what the production server is doing
pgobserver might do that as well, particulary useful for functions performances. https://github.com/zalando/PGObserver On Mon, Jul 23, 2018 at 1:18 PM, Thomas Güttler < [email protected]> wrote: > Is there a tool which does this for PostgreSQL? > > Take a "snapshot" of what the server is doing about 10 times per second. > Write this to a file. > After N hours you can aggregate the file. > What does the server do most of the time? > Which tables/index gets used the most. > > Before optimizing a database, I would like to know what is going > on in the production system. > > I know that there are internal tables like pg_stat_statements. > But I guess doing a snapshot every N millseconds will present a > better picture of what is going in in real life. > > Is there already a tool which goes this way? > > Or is there a better way? > > Regards, > Thomas Güttler > > -- > Thomas Guettler http://www.thomas-guettler.de/ > I am looking for feedback: https://github.com/guettli/pro > gramming-guidelines > >
