Thread: Moving pg_xlog

Hi All,

I have been reading about increasing PostgreSQL performance by relocating the
pg_xlog to a disk other than the one where the database resides.  I have the
following pg_xlogs on my system.

/raid02/databases/pg_xlog
/raid02/rhdb_databases/pg_xlog
/raid02/databases-8.0.0/pg_xlog
/var/lib/pgsql/data/pg_xlog

The second and third entries are from backups that were made before major
upgrades so I am expecting that I can blow them away.

The first entry is in the directory where my databases are located.

I have no idea why the forth entry is there.  It is in the PostgreSQL
installation directory.

Here is my filesystem.
# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda6              9052552   2605292   5987404  31% /
/dev/sda1               101089     32688     63182  35% /boot
none                   1282880         0   1282880   0% /dev/shm
/dev/sdb2             16516084     32836  15644256   1% /raid01
/dev/sdb3             16516084   1156160  14520932   8% /raid02
/dev/sda5              2063504     32916   1925768   2% /tmp
/dev/sda3              4127108    203136   3714324   6% /var
/dev/cdrom              494126    494126         0 100% /mnt/cdrom

Can I

1) stop the postmaster
2) rm -rf /var/lib/pgsql/data/pg_xlog
3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog
4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog
5) start postmaster

If I can do that and place the pg_xlog in the installation directory will I
create any installation issues the next time I upgrade PostgreSQL?

TIA

Kind Regards,
Keith

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> I have been reading about increasing PostgreSQL performance by relocating the
> pg_xlog to a disk other than the one where the database resides.  I have the
> following pg_xlogs on my system.

> /raid02/databases/pg_xlog
> /raid02/rhdb_databases/pg_xlog
> /raid02/databases-8.0.0/pg_xlog
> /var/lib/pgsql/data/pg_xlog

> I have no idea why the forth entry is there.  It is in the PostgreSQL
> installation directory.

It's there because the RPM sets up a database under /var/lib/pgsql/data.

> 1) stop the postmaster
> 2) rm -rf /var/lib/pgsql/data/pg_xlog
> 3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog
> 4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog
> 5) start postmaster

Put the xlog anywhere BUT there!!!!!!!!!

> If I can do that and place the pg_xlog in the installation directory will I
> create any installation issues the next time I upgrade PostgreSQL?

Oh, the installation will be just fine ... but your database will not
be after the upgrade wipes out your WAL.  Put the xlog under some
non-system-defined directory.

            regards, tom lane

On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote
> "Keith Worthington" <keithw@narrowpathinc.com> writes:
> > I have been reading about increasing PostgreSQL performance
> > by relocating the pg_xlog to a disk other than the one
> > where the database resides.  I have the following pg_xlogs
> > on my system.
> >
> > /raid02/databases/pg_xlog
> > /raid02/rhdb_databases/pg_xlog
> > /raid02/databases-8.0.0/pg_xlog
> > /var/lib/pgsql/data/pg_xlog
> >
> > I have no idea why the forth entry is there.  It is in the PostgreSQL
> > installation directory.
>
> It's there because the RPM sets up a database under /var/lib/pgsql/data.
>
> > 1) stop the postmaster
> > 2) rm -rf /var/lib/pgsql/data/pg_xlog
> > 3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog
> > 4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog
> > 5) start postmaster
>
> Put the xlog anywhere BUT there!!!!!!!!!
>
> > If I can do that and place the pg_xlog in the installation
> > directory will I create any installation issues the next
> > time I upgrade PostgreSQL?
>
> Oh, the installation will be just fine ... but your database will not
> be after the upgrade wipes out your WAL.  Put the xlog under some
> non-system-defined directory.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Thanks Tom.  I am glad I asked before I leaped. 8-0

Is there a convention that most people follow.  It would seem that anywhere in
the installation directory is a bad idea.  From what I have read on other
threads it does not want to be in the database directory since in most cases
that would put it on the same disk as the database.

I am assuming due to lack of reaction that the symbolic link is not an issue.
 Is there a cleaner or more appropriate way of moving the pg_xlog.

Finally, am I correct in assuming that as long as the postmaster is shut down
moving the log is safe?

Kind Regards,
Keith

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote
>> Put the xlog anywhere BUT there!!!!!!!!!

> Is there a convention that most people follow.  It would seem that
> anywhere in the installation directory is a bad idea.  From what I
> have read on other threads it does not want to be in the database
> directory since in most cases that would put it on the same disk as
> the database.

I don't know of any fixed convention.  The way I would be inclined to do
it, given that I wanted the data on disk 1 (with mount point /disk1) and
xlog on disk 2 (with mount point /disk2) is to create postgres-owned
directories /disk1/postgres/ and /disk2/postgres/, and then within those
put the data directory (thus, /disk1/postgres/data/) and xlog directory
(/disk2/postgres/pg_xlog/).  Having an extra level of postgres-owned
directory is handy since it makes it easier to do database admin work
without being root --- once you've created those two directories and
chown'd them to postgres, everything else can be done as the postgres user.

Now that I think about it, you were (if I understood your layout
correctly) proposing to put the xlog on your system's root disk.
This is probably a bad idea for performance, because there will always
be other traffic to the root disk.  What you are really trying to
accomplish is to make sure the xlog is on a disk spindle that has no
other traffic besides xlog, so that the disk heads never have to move
off the current xlog file.  The xlog traffic is 100% sequential writes
and so you can cut the seeks involved to near nil if you can dedicate
a spindle to it.

> I am assuming due to lack of reaction that the symbolic link is not an issue.
>  Is there a cleaner or more appropriate way of moving the pg_xlog.

No, that's exactly the way to do it.

> Finally, am I correct in assuming that as long as the postmaster is shut down
> moving the log is safe?

Right.  You can move the data directory too if you want.  AFAIR the only
position-dependent stuff in there is (if you are using tablespaces in
8.0) the tablespace symlinks under data/pg_tblspc/.  You can fix those
by hand if you have a need to move a tablespace.

            regards, tom lane

Keith Worthington wrote:

>On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote
>
>
>>"Keith Worthington" <keithw@narrowpathinc.com> writes:
>>
>>
>>>I have been reading about increasing PostgreSQL performance
>>>by relocating the pg_xlog to a disk other than the one
>>>where the database resides.  I have the following pg_xlogs
>>>on my system.
>>>
>>>/raid02/databases/pg_xlog
>>>/raid02/rhdb_databases/pg_xlog
>>>/raid02/databases-8.0.0/pg_xlog
>>>/var/lib/pgsql/data/pg_xlog
>>>
>>>I have no idea why the forth entry is there.  It is in the PostgreSQL
>>>installation directory.
>>>
>>>
>>It's there because the RPM sets up a database under /var/lib/pgsql/data.
>>
>>
>>
>>>1) stop the postmaster
>>>2) rm -rf /var/lib/pgsql/data/pg_xlog
>>>3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog
>>>4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog
>>>5) start postmaster
>>>
>>>
>>Put the xlog anywhere BUT there!!!!!!!!!
>>
>>
>>
>>>If I can do that and place the pg_xlog in the installation
>>>directory will I create any installation issues the next
>>>time I upgrade PostgreSQL?
>>>
>>>
>>Oh, the installation will be just fine ... but your database will not
>>be after the upgrade wipes out your WAL.  Put the xlog under some
>>non-system-defined directory.
>>
>>            regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>>
>
>Thanks Tom.  I am glad I asked before I leaped. 8-0
>
>Is there a convention that most people follow.  It would seem that anywhere in
>the installation directory is a bad idea.  From what I have read on other
>threads it does not want to be in the database directory since in most cases
>that would put it on the same disk as the database.
>
>
>
We tend to use somthing that associates the WAL with the appropriate
cluster, like

/var/lib/CLUSTER for the data
/var/lib/CLUSTER_WAL for WAL files.

>I am assuming due to lack of reaction that the symbolic link is not an issue.
> Is there a cleaner or more appropriate way of moving the pg_xlog.
>
>
>

A symbolic link is the standard way to do it.

>Finally, am I correct in assuming that as long as the postmaster is shut down
>moving the log is safe?
>
>

You are correct.  Moving the WAL files with the postmaster running would
be a very bad thing.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Tom Lane wrote:
...

>Now that I think about it, you were (if I understood your layout
>correctly) proposing to put the xlog on your system's root disk.
>This is probably a bad idea for performance, because there will always
>be other traffic to the root disk.  What you are really trying to
>accomplish is to make sure the xlog is on a disk spindle that has no
>other traffic besides xlog, so that the disk heads never have to move
>off the current xlog file.  The xlog traffic is 100% sequential writes
>and so you can cut the seeks involved to near nil if you can dedicate
>a spindle to it.
>
>
I certainly agree with what you wrote. But my understanding is that if
you only have 2 arrays, then moving xlog onto the array not on the
database is better than having it with the database. It isn't optimum,
but it is better. Because that way there isn't as much contention
between the database and xlog.

John
=:->

John A Meinel <john@arbash-meinel.com> writes:
> Tom Lane wrote:
>> Now that I think about it, you were (if I understood your layout
>> correctly) proposing to put the xlog on your system's root disk.
>> This is probably a bad idea for performance, ...

> I certainly agree with what you wrote. But my understanding is that if
> you only have 2 arrays, then moving xlog onto the array not on the
> database is better than having it with the database. It isn't optimum,
> but it is better. Because that way there isn't as much contention
> between the database and xlog.

If the machine isn't doing much else than running the database, then
yeah, that may be the best available option.  If there are other things
going on then you have to think about how much competition there is for
the root disk.

But the impression I had from the OP's df listing is that he has several
disks available ... so he ought to be able to find one that doesn't need
to do anything except xlog.

            regards, tom lane

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Himanshu Baweja <himanshubaweja@yahoo.com> writes:
> My database has two scsi disks....
> my current configuration has pg_xlog on disk1 and data on disk2....
> the machine is used for database only....
> now did some logging and came to a conclusion that my disk2(data disk) is getting used around 3 times more than
disk1(pg_xlog)....

> so wht is recommended... move some of the data to disk1 so that both disks are equally used... by creating
tablespacesor let my configuration be whts its currently... iowait is one of the bottlenecks in my application
performance.....

It seems highly unlikely that putting more stuff on the xlog disk will
improve performance --- at least not if your bottleneck is update speed.
If it's a read-mostly workload then optimizing xlog writes may not be
the most important thing to you.  In that case you might want to ignore
xlog and try something along the lines of tables on one disk, indexes
on the other.

            regards, tom lane

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com