performance tuning - splitting data and logs

[zubs1aa]

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?

[kraada]

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.

[bdadavies]

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.

[kraada]

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.

[agoge]

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

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?

[kraada]

PostgreSQL by default when restarted should attempt to restore the database to a stable state.

[agoge]

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

[kraada]

Attach the last few files in your pg_log directory to a support ticket and we'll look into it for you.