Re: how to know whether query data from memory after pg_prewarm

Fabio Pardi <f.pardi@xxxxxxxxxxxx> · Wed, 19 Sep 2018 09:42:05 +0200

    @Thomas, this tool looks very interesting!

      @Jimmy:

        Back to the question,
            you might approach the problem from a
                different perspective..

                If
            you run a query 2 consecutive times, from the second on, you should be at 'full speed'.

                    Therefore if the first run takes X
                                seconds but
                                  the
                                    consecutive runs take only a fraction
                                      of it, then you have
                                        probably cached all the data.

                                          It
                                            might be as simple as
                                              that. But
                                                also take
                                            into account:

                                              - What is
                                                written above
                                                  is true when the size
                                                  of the table fits in RAM

                                                    - No
                                                      other activity is
                                                      going on. If
                                                          you have other
                                                          activity going
                                                          on on the
                                                          disk, it will
                                                          pollute your
                                                          results

                                                  - If you
                                                read from disk, you will see read
                                                  activity on the data
                                                  disk, given that your
                                                  are graphing it. If is
                                                  cached then it does
                                                  not read from disk.

                                                  - Running
                                                    the query with
                                                    'explain' will tell
                                                    you in detail what
                                                    is going on. (Maybe
                                                    you
                                                      forgot to create
                                                      an index on Postgres
                                                        and is there on
                                                        Oracle?)

                                                  - If
                                                  you are doing sorts and the data
                                                    does not fit on
                                                    work_mem then you
                                                      are making use of
                                                      disk space,
                                                      slowing down
                                                      operations

                                                      regards,

                                                      fabio
                                                        pardi

    On 19/09/18 05:29, Thomas Munro wrote:

      On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpokky@xxxxxxx> wrote:

        I use select pg_prewarm('table1','read','main')  to load data of table1 into the memory.
when I use select count(1) from table1 group by aa to query data.
I find the speed of query is not fast, I wonder whether it query data from memory.
And it is slower than Oracle, both of Oracle and Postgresql has same table and count of data.
when pg_prewarm use 'read' mode,  the data is put into the OS cache, how to examine the table which is pg_prewarmed into the OS cache .
I know pg_buffercache ,but it just examine the table in the shared buffer of Postgresql, not the table in the OS cache.

      This is a quick and dirty hack, but it might do what you want:

https://github.com/macdice/pgdata_mincore

Tested on FreeBSD, not sure how well it'll travel.