Horton

Thread

  1. Can you please suggest me some links where I can learn:

    M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> — 2013-09-05T05:13:19Z

    Hello Friends,

I want start contributing in Postgres in code level. I read some ppts and some tutorials in postgres manual.

Can you please suggest me some links where I can learn:

1.       Transaction Isolation in Database

2.       Query procession and possible optimizations.

3.       How to improve query and its performance.

4.       MVCC in detail.


Regards
Tarkeshwar

  2. Re: Can you please suggest me some links where I can learn:

    John R Pierce <pierce@hogranch.com> — 2013-09-05T05:25:46Z

    On 9/4/2013 10:13 PM, M Tarkeshwar Rao wrote:
>
> Can you please suggest me some links where I can learn:
>
> 1.Transaction Isolation in Database
>

the documentation

> 2.Query procession and possible optimizations.
>

the source code.     if there were any easy optimizations, I'm sure 
they've already been taken.

> 3.How to improve query and its performance.
>

see #2 above.

> 4.MVCC in detail.
>

see answer to #1, and the source code if you want more detail.


-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

  3. Question About WAL filename and its time stamp

    ascot.moss@gmail.com <ascot.moss@gmail.com> — 2013-09-05T13:19:41Z

    Hi,

From the pg_xlog folder, I found some files with interesting time stamps: older file names with newer timestamps, can you please advise why?

Set 1: How come 0000000400000F490000008D is 10 minutes newer than 0000000400000F490000008E?
-rw------- 1 111 115 16777216 Sep  4 15:28 0000000400000F490000008C
-rw------- 1 111 115 16777216 Sep  4 15:27 0000000400000F490000008D <===
-rw------- 1 111 115 16777216 Sep  4 15:17 0000000400000F490000008E <====
-rw------- 1 111 115 16777216 Sep  4 15:26 0000000400000F490000008F
-rw------- 1 111 115 16777216 Sep  4 15:27 0000000400000F4900000090

Set 2: why files,  0000000400000F48000000FD,  0000000400000F48000000FE and 0000000400000F4900000000, are not reused?
1) -rw------- 1 postgres postgres 16777216 Sep  4 23:07 0000000400000F48000000FA
2) -rw------- 1 postgres postgres 16777216 Sep  4 23:08 0000000400000F48000000FB
3) -rw------- 1 postgres postgres 16777216 Sep  4 23:09 0000000400000F48000000FC  <===
4) -rw------- 1 postgres postgres 16777216 Sep  4 14:47 0000000400000F48000000FD  <====
5) -rw------- 1 postgres postgres 16777216 Sep  4 14:46 0000000400000F48000000FE
6) -rw------- 1 postgres postgres 16777216 Sep  4 14:46 0000000400000F4900000000

regards

  4. Re: [PERFORM] Can you please suggest me some links where I can learn:

    Kevin Grittner <kgrittn@ymail.com> — 2013-09-05T15:39:32Z

    M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote:

> I want start contributing in Postgres in code level.

Welcome!  You should probably start with this page and its links:

http://wiki.postgresql.org/wiki/Developer_FAQ

> Can you please suggest me some links where I can learn:
> 1.       Transaction Isolation in Database

http://www.postgresql.org/docs/current/interactive/transaction-iso.html

> 2.       Query procession and possible optimizations.

http://www.postgresql.org/docs/current/interactive/overview.html

http://www.pgcon.org/2011/schedule/attachments/188_Planner%20talk.pdf

http://www.justin.tv/sfpug/b/419326732

> 3.       How to improve query and its performance.

http://wiki.postgresql.org/wiki/Performance_Optimization

> 4.       MVCC in detail.

http://www.slideshare.net/profyclub_ru/mvcc-unmaskedbruce-momjian

-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  5. Re: [PERFORM] Can you please suggest me some links where I can learn:

    Merlin Moncure <mmoncure@gmail.com> — 2013-09-05T22:42:19Z

    On Thu, Sep 5, 2013 at 10:39 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
> M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote:
>
>> I want start contributing in Postgres in code level.
>
> Welcome!  You should probably start with this page and its links:
>
> http://wiki.postgresql.org/wiki/Developer_FAQ
>
>> Can you please suggest me some links where I can learn:
>> 1.       Transaction Isolation in Database
>
> http://www.postgresql.org/docs/current/interactive/transaction-iso.html
>
>> 2.       Query procession and possible optimizations.
>
> http://www.postgresql.org/docs/current/interactive/overview.html
>
> http://www.pgcon.org/2011/schedule/attachments/188_Planner%20talk.pdf
>
> http://www.justin.tv/sfpug/b/419326732
>
>> 3.       How to improve query and its performance.
>
> http://wiki.postgresql.org/wiki/Performance_Optimization
>
>> 4.       MVCC in detail.
>
> http://www.slideshare.net/profyclub_ru/mvcc-unmaskedbruce-momjian

Another high priority thing to check out is the README files in the
code (this is mentioned tangentially in the developer FAQ).
Personally  (and I'm no expert) I find some parts of the code much
easier to dive into without a lot of surrounding context than others
-- it could take a lifetime to learn it all.  My advise is to start
small and pick a very specific topic and focus on that.

merlin

  6. Re: [PERFORM] Can you please suggest me some links where I can learn:

    John R Pierce <pierce@hogranch.com> — 2013-09-05T22:53:38Z

    On 9/5/2013 3:42 PM, Merlin Moncure wrote:
>   My advise is to start
> small and pick a very specific topic and focus on that.

my advise is to first familiarize yourself with the package from the 
user perspective before even thinking of diving in and making any 
changes.    I say this, because the OP's questions seemed to suggest 
they knew very little about PostgreSQL.



-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

  7. Re: Question About WAL filename and its time stamp

    Suzuki Hironobu <hironobu@interdb.jp> — 2013-09-06T02:17:16Z

    Hi,


(13/09/05 22:19), ascot.moss@gmail.com wrote:
> Hi,
>
>  From the pg_xlog folder, I found some files with interesting time stamps: older file names with newer timestamps, can you please advise why?
>
> Set 1: How come 0000000400000F490000008D is 10 minutes newer than 0000000400000F490000008E?
> -rw------- 1 111 115 16777216 Sep  4 15:28 0000000400000F490000008C
> -rw------- 1 111 115 16777216 Sep  4 15:27 0000000400000F490000008D <===
> -rw------- 1 111 115 16777216 Sep  4 15:17 0000000400000F490000008E <====
> -rw------- 1 111 115 16777216 Sep  4 15:26 0000000400000F490000008F
> -rw------- 1 111 115 16777216 Sep  4 15:27 0000000400000F4900000090

WAL files will be recycled.
For example:

sampledb=# select pg_xlogfile_name(pg_current_xlog_location());
      pg_xlogfile_name
--------------------------
  000000010000000000000004
(1 row)

-rw-------.  1 postgres postgres 16777216 Sep  6 10:55 
000000010000000000000001
-rw-------.  1 postgres postgres 16777216 Sep  6 10:56 
000000010000000000000002
-rw-------.  1 postgres postgres 16777216 Sep  6 10:57 
000000010000000000000003
-rw-------.  1 postgres postgres 16777216 Sep  6 10:58 
000000010000000000000004  <--- current WAL

After a few minutes,

sampledb=# select pg_xlogfile_name(pg_current_xlog_location());
      pg_xlogfile_name
--------------------------
  000000010000000000000006
(1 row)

-rw-------.  1 postgres postgres 16777216 Sep  6 11:01 
000000010000000000000004 <-- Time of the last write.
-rw-------.  1 postgres postgres 16777216 Sep  6 11:02 
000000010000000000000005
-rw-------.  1 postgres postgres 16777216 Sep  6 11:02 
000000010000000000000006 <-- current WAL
-rw-------.  1 postgres postgres 16777216 Sep  6 10:55 
000000010000000000000007 <-- old name is 000000010000000000000001
-rw-------.  1 postgres postgres 16777216 Sep  6 10:56 
000000010000000000000008 <-- old name is 000000010000000000000002
-rw-------.  1 postgres postgres 16777216 Sep  6 10:57 
000000010000000000000009 <-- old name is 000000010000000000000003


Timing of recycling depends on the situation. If the time stamp of 
current WAL file is the most recent compared with other WAL files, there 
is no contradiction.
(I wonder that the time stamp of 0000000400000F490000008C is the most 
recent.)



> Set 2: why files,  0000000400000F48000000FD,  0000000400000F48000000FE and 0000000400000F4900000000, are not reused?
> 1) -rw------- 1 postgres postgres 16777216 Sep  4 23:07 0000000400000F48000000FA
> 2) -rw------- 1 postgres postgres 16777216 Sep  4 23:08 0000000400000F48000000FB
> 3) -rw------- 1 postgres postgres 16777216 Sep  4 23:09 0000000400000F48000000FC  <===
> 4) -rw------- 1 postgres postgres 16777216 Sep  4 14:47 0000000400000F48000000FD  <====
> 5) -rw------- 1 postgres postgres 16777216 Sep  4 14:46 0000000400000F48000000FE
> 6) -rw------- 1 postgres postgres 16777216 Sep  4 14:46 0000000400000F4900000000

This is the specification of WAL. This specification changes from 9.3.


regards

  8. Re: Question About WAL filename and its time stamp

    Haribabu kommi <haribabu.kommi@huawei.com> — 2013-09-06T05:22:28Z

    On 05 September 2013 18:50 ascot.moss wrote:

>From the pg_xlog folder, I found some files with interesting time stamps: older file names with newer timestamps, can you please advise why?

>Set 1: How come 0000000400000F490000008D is 10 minutes newer than 0000000400000F490000008E?
>-rw------- 1 111 115 16777216 Sep  4 15:28 0000000400000F490000008C
>-rw------- 1 111 115 16777216 Sep  4 15:27 0000000400000F490000008D <===
>-rw------- 1 111 115 16777216 Sep  4 15:17 0000000400000F490000008E <====
>-rw------- 1 111 115 16777216 Sep  4 15:26 0000000400000F490000008F
>-rw------- 1 111 115 16777216 Sep  4 15:27 0000000400000F4900000090

>Set 2: why files,  0000000400000F48000000FD,  0000000400000F48000000FE and 0000000400000F4900000000, are not reused?
>1) -rw------- 1 postgres postgres 16777216 Sep  4 23:07 0000000400000F48000000FA
>2) -rw------- 1 postgres postgres 16777216 Sep  4 23:08 0000000400000F48000000FB
>3) -rw------- 1 postgres postgres 16777216 Sep  4 23:09 0000000400000F48000000FC  <===
>4) -rw------- 1 postgres postgres 16777216 Sep  4 14:47 0000000400000F48000000FD  <====
>5) -rw------- 1 postgres postgres 16777216 Sep  4 14:46 0000000400000F48000000FE
>6) -rw------- 1 postgres postgres 16777216 Sep  4 14:46 0000000400000F4900000000

In postgres every checkpoint end, it recycle or remove the old xlog files. During the recycle process it creates next set of xlog files
Which will be used later by database operations. The files FC, FD, FE and 00 are recycled files. Now the FC file is in use because of this
Reason the time stamp is different.

Regards,
Hari babu.