Re: Including Snapshot Info with Indexes - Mailing list pgsql-hackers
| From | Gokulakannan Somasundaram |
|---|---|
| Subject | Re: Including Snapshot Info with Indexes |
| Date | |
| Msg-id | 9362e74e0710212350i15920f7egbfc275b59cf571d@mail.gmail.com Whole thread Raw |
| In response to | Re: Including Snapshot Info with Indexes (Martijn van Oosterhout <kleptog@svana.org>) |
| List | pgsql-hackers |
Hi,
I have tested with makeing this change and it is showing useful readings. The point of introducing the indexes with snapshot is that it should reduce the number of logical I/Os.(It may be from memory / from hard disk). Logical I/Os are potential Physical I/Os.
I will submit my initial patch today.
Thanks,
Gokul.
I have tested with makeing this change and it is showing useful readings. The point of introducing the indexes with snapshot is that it should reduce the number of logical I/Os.(It may be from memory / from hard disk). Logical I/Os are potential Physical I/Os.
On 10/20/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
If they have dropped the pin to allow other accesses, then the buffer may lose its place in memory. So it might become a physical I/O, of course at a lower probability. But still if we think of this from SQL tuner's perspective, he is going to change the query slightly, or add/remove indexes in order to verify whether he has improved the Query performance. Can we say that he has improved the performance 99% of the time, if the SQL fired has reduced the logical I/Os?
In this case we may not know which rows of the table are in which block. Say we fire a query, which does index scan. it might have referred to some table block. We can't say for sure that if i change some value in the index scan, it won't touch the same table block. This solution is perfect, if we have to do a Load Test / Performance Test. But for SQL tuning, running a Load test is slightly costly.
Even, if the statistic doesn't become useful in some cases, we can safely ignore it. On Sat, Oct 20, 2007 at 09:24:07AM +0530, Gokulakannan Somasundaram wrote:
> Hi,
> I think i have a initial Implementation. It has some bugs and i am working
> on fixing it. But to show the advantages, I want to show the number of
> Logical I/Os on the screen. In order to show that, i tried enabling the
> log_statement option in PostgreSQL.conf. But it shows only the physical
> reads. What i wanted was a Logical reads count( No. of ReadBuffer calls,
> which is stored in ReadBufferCount variable). So i have added this stats to
> the bufmgr.c(function is BufferUsage, i suppose) to show Logical Reads and
> Physical Reads. Is this a acceptable change?
I'm not sure if the number of logical reads is really a useful
measurement. I can imagine there are places that deliberatly read the
block "logically" a few times but drop the pin in between to allow
others access. This will skew your results as in actual usage only the
first is likely to generate a real I/O.
If they have dropped the pin to allow other accesses, then the buffer may lose its place in memory. So it might become a physical I/O, of course at a lower probability. But still if we think of this from SQL tuner's perspective, he is going to change the query slightly, or add/remove indexes in order to verify whether he has improved the Query performance. Can we say that he has improved the performance 99% of the time, if the SQL fired has reduced the logical I/Os?
If your problem is cache it seems to me you should test with a table
larger than your shared buffers and perhaps even larger than your total
memory, since this is the case we're actually interested in.
In this case we may not know which rows of the table are in which block. Say we fire a query, which does index scan. it might have referred to some table block. We can't say for sure that if i change some value in the index scan, it won't touch the same table block. This solution is perfect, if we have to do a Load Test / Performance Test. But for SQL tuning, running a Load test is slightly costly.
I will submit my initial patch today.
Thanks,
Gokul.
pgsql-hackers by date: