Обсуждение: how to know whether query data from memory after pg_prewarm
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.
On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpokky@126.com> 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
intothe 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.
--
Thomas Munro
http://www.enterprisedb.com
@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
@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@126.com> 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.
Le 19/09/2018 à 05:29, Thomas Munro a écrit :
> On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpokky@126.com> 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
intothe 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.
You can use pgfincore extension for that purpose, and more.
https://github.com/klando/pgfincore/blob/master/README.md
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
On Wed, Sep 19, 2018 at 7:44 PM Cédric Villemain <cedric@2ndquadrant.com> wrote:
> Le 19/09/2018 à 05:29, Thomas Munro a écrit :
> > On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpokky@126.com> 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_prewarmedinto 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.
>
> You can use pgfincore extension for that purpose, and more.
>
> https://github.com/klando/pgfincore/blob/master/README.md
Yes, if you only want to know *how many* pages are in the OS page
cache. pgdata_mincore shows you which PG blocks are in the page cache
in the same format as pg_buffercache, which is useful for studying
double buffering effects. Maybe I should turn it into a patch for
pgfincore...
--
Thomas Munro
http://www.enterprisedb.com
But I use windows server 2012R.
pgfincore can not run on the windows.
Is there some replacements in windows system?
At 2018-09-19 15:44:06, "Cédric Villemain" <cedric@2ndQuadrant.com> wrote: >Le 19/09/2018 à 05:29, Thomas Munro a écrit : >> On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpokky@126.com> 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. > >You can use pgfincore extension for that purpose, and more. > >https://github.com/klando/pgfincore/blob/master/README.md > > >-- >Cédric Villemain +33 (0)6 20 30 22 52 >http://2ndQuadrant.fr/ >PostgreSQL: Support 24x7 - Développement, Expertise et Formation