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 zubs1aa » Wed Jun 16, 2010 2:41 pm

zubs1aa wrote:
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: ????


Also, the 15 page thread suggests creating new tablespaces. How does this differ from the suggestions here.. and should I do both? if so, how/ for what?
zubs1aa
 
Posts: 2219
Joined: Fri Feb 08, 2008 1:52 pm

Re: performance tuning - splitting data and logs

Postby kraada » Wed Jun 16, 2010 2:48 pm

I replied to your PM zubs, but that sounds like a good setup if you have three drives. You'd want other program files and whatnot on C also then, that way any data being read or written from your OS isn't slowing postgres down.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby bdadavies » Thu Feb 17, 2011 8:11 pm

Would there be any noticeable performance increase if the OS and DB are on a single SSD?

Just wondering if the transfer rate of a SSD negates the benefits of splitting.
bdadavies
 
Posts: 110
Joined: Tue Jun 03, 2008 9:09 am

Re: performance tuning - splitting data and logs

Postby kraada » Fri Feb 18, 2011 9:09 am

My first instinct says that the single SSD would be faster, but I haven't got an SSD to test with to be certain. However, you should note that PT3 databases can get large. If you're playing full ring, the rule of thumb you can use is 1M hands = 10G on disk (for 6max it's more like 7G). This is a bit of an overestimate but it should give you an idea if your SSD is going to be large enough to handle your database needs as well as your primary operating system and other data.

If I was choosing between database on SSD vs OS and other stuff on SSD and database on other SATA drive, I'd choose the latter though - the general increase in OS performance, but your mileage may vary.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby agoge » Mon Oct 17, 2011 2:58 pm

kraada wrote: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:



the very last part of this, i have dad a interruption from power(battery dies easily) and my data base got corrupted( cant import, cant find any players says unable to execute query) i can export the hands then re import them into a new data base but this takes about 2 hours all and all. is there a way as u say to roll back?
agoge
 
Posts: 6
Joined: Wed Nov 03, 2010 12:55 am

Re: performance tuning - splitting data and logs

Postby kraada » Mon Oct 17, 2011 3:26 pm

PostgreSQL by default when restarted should attempt to restore the database to a stable state.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: performance tuning - splitting data and logs

Postby agoge » Mon Oct 17, 2011 4:38 pm

ok so it doesnt :( as im getting the errors ideas? im not sure if i mentioned i can stil export from this data base but just cant added to it and cant look at indivual players etc. and cant import. should i post error messsage ? I happens when i loose power this like the 3rd time iv had to make a new data and getting bit length was wondering if there any quicker way. or way to fix the old data base

p.s my PostgreSQL is installed on a external hardrive
agoge
 
Posts: 6
Joined: Wed Nov 03, 2010 12:55 am

Re: performance tuning - splitting data and logs

Postby kraada » Mon Oct 17, 2011 5:49 pm

Attach the last few files in your pg_log directory to a support ticket and we'll look into it for you.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Previous

Return to PostgreSQL [Read Only]

Who is online

Users browsing this forum: No registered users and 4 guests

cron
highfalutin