From: | Fabio Pardi <f(dot)pardi(at)portavita(dot)eu> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: how to know whether query data from memory after pg_prewarm |
Date: | 2018-09-19 07:42:05 |
Message-ID: | b1156598-e80c-34e6-e9c6-f2a9f301b9fa@portavita.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
@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 secondon, 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. Butalsotake 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 sortsand 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(at)126(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Cédric Villemain | 2018-09-19 07:44:06 | Re: how to know whether query data from memory after pg_prewarm |
Previous Message | Thomas Güttler | 2018-09-19 06:30:47 | Replicate Tables from SAP (DB2/HANA) to PostgreSQL |