performance tuning - splitting data and logs

PostgreSQL is the database server used to store information. Do you have a question or are you having problem with PostgreSQL? If so, post them here.

Moderator: Moderators

Re: performance tuning - splitting data and logs

Postby WhiteRider » Sun Aug 23, 2009 5:17 am

You should not touch the pg_xlog folder - the only files you can remove are in the pg_log folder.
I can't remember exactly what the pg_xlog folder files are for, but I'm pretty sure they're important to the running of postgres - kraada will probably be able to tell you why when he comes on later.
WhiteRider
Moderator
 
Posts: 53569
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: performance tuning - splitting data and logs

Postby kraada » Sun Aug 23, 2009 8:33 am

You can find the real nitty gritty about the WAL on this page (you might want to read 28.1 - 28.3 also, though, for background).

The short answer is yes the WAL directory is necessary. The point of the WAL is to ensure database integrity. When PostgreSQL is told to change a database value, first the change is written to the WAL, then the change is committed to the disk. In this way, if a particularly long and complex update is set in motion and then you lose power, the database can be rolled back (or forward) to a usable state. Without the WAL you would risk database corruption if there were ever an interruption in power (for example). I hope that helps somewhat :ugeek:
kraada
Moderator
 
Posts: 54435
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby nelliesman93 » Sun Jun 06, 2010 1:34 am

I followed the quoted instructions and the links were created but I could never get postgreSQL to restart. The pg_ctl.exe starts up, 3 instances of postgres.exe open and then immediately close. I also tried just opening PT3 to reestablish the postgreSQL but it comes up with not connected DB. I removed the link and am back to normal now but what am I missing? Thanks.
- found a simple tool to create the symbolic link (called a junction). Here's the URL to download if from Msoft.
http://technet.microsoft.com/en-us/sysi ... 96768.aspx
- downloaded the junction.exe, put it into the directory c:\program files\utilities
- shut down postgreSQL
- created a pg_xlog directory on the desired drive, in my case f:\pg_xlog (you can call it anything you want)
- copied the files and sub-directory from c:\program files\postgreSQL\data\pg_xlog to f:\pg_xlog then deleted them from their original location
- opened a command window (Start->Run enter cmd and hit return)
- typed the command "c:\program files\utilities\junction" "c:\program files\postgreSQL\data\pg_xlog" f:\pg_xlog (include the")
- typed exit to close the command window
- restarted postgreSQL

If this doesn't work
- if it is running, shutdown postgreSQL
- start another command window
- type cd "\program files\postgreSQL\data" (include ") and hit enter
- type "c:\program files\utilities\junction" pg_xlog
- if it says "No reparse points found." then for some reason the junction was not created, so recheck your work
- if it says "c:\postgreSQL\pg_xlog: JUNCTION", that means the junction was correctly created but something else is wrong. So now type "c:\program files\utilities\junction" -d pg_xlog. This will destroy the junction. Now, restore the files and sub-directories to the original pg_xlog location. You should be able to restart postgreSQL now.
nelliesman93
 
Posts: 115
Joined: Fri Jan 25, 2008 7:08 pm

Re: performance tuning - splitting data and logs

Postby kraada » Sun Jun 06, 2010 8:47 am

Was there any error listed in the pg_log file from the attempt?
kraada
Moderator
 
Posts: 54435
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby nelliesman93 » Mon Jun 07, 2010 12:46 am

kraada wrote:Was there any error listed in the pg_log file from the attempt?


2010-06-06 00:07:53 CDT LOG: database system was shut down at 2010-06-05 23:55:10 CDT
2010-06-06 00:07:53 CDT PANIC: could not open file "pg_xlog/000000010000000200000054" (log file 2, segment 84): Permission denied


This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

2010-06-06 00:07:53 CDT LOG: startup process (PID 1632) exited with exit code 3
2010-06-06 00:07:53 CDT LOG: aborting startup due to startup process failure
2010-06-06 00:07:53 CDT LOG: loaded library "$libdir/plugins/plugin_debugger.dll"
2010-06-06 00:07:53 CDT FATAL: the database system is starting up
nelliesman93
 
Posts: 115
Joined: Fri Jan 25, 2008 7:08 pm

Re: performance tuning - splitting data and logs

Postby kraada » Mon Jun 07, 2010 8:38 am

Is that file in the directory you were trying to move? PostgreSQL does need to be able to find the xlog folder.
kraada
Moderator
 
Posts: 54435
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby nelliesman93 » Sat Jun 12, 2010 12:24 pm

kraada wrote:Is that file in the directory you were trying to move? PostgreSQL does need to be able to find the xlog folder.


No, that file is not listed pg_xlog directory.

I followed the exact instructions in the previous message I posted. All files in the postgreSQL\8.3\data\pg_xlog were copied over to the new drive. I made the link and verified but PT3 does not connect.
nelliesman93
 
Posts: 115
Joined: Fri Jan 25, 2008 7:08 pm

Re: performance tuning - splitting data and logs

Postby WhiteRider » Sat Jun 12, 2010 1:17 pm

If one of the files is missing then it won't work. Kraada isn't working much this weekend and he knows much more about this stuff than me, though, so I'll let him get back to you when he comes on.
WhiteRider
Moderator
 
Posts: 53569
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: performance tuning - splitting data and logs

Postby kraada » Mon Jun 14, 2010 11:08 am

Try running pg_resetxlog (in the PostgreSQL/bin directory) and that might fix things for you. If it gives an error when you run it let me know what the error is.
kraada
Moderator
 
Posts: 54435
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby zubs1aa » Wed Jun 16, 2010 1:49 pm

WhiteRider wrote:Having your database on the fastest drive will give you the best performance. Writing logs to a separate disk will help because all the disk access on the DB drive will be concentrated on database access.


Can someone summarize this out of the bazillion threads in here?

Process for PT3 on C:, DB's on drive D: and logs on Drive F: ????
zubs1aa
 
Posts: 2219
Joined: Fri Feb 08, 2008 1:52 pm

PreviousNext

Return to PostgreSQL [Read Only]

Who is online

Users browsing this forum: No registered users and 1 guest

cron
highfalutin