best os for postgresql

[zedjr]

What is best os for postgresql. RIght now i ahve vista 64 bit ultiamte and it seems i cant set teh shared buffers any higher than 1gb. I have 12gb of ram and want to set it much higher. THEre doesnt seem to be anywhere to change teh kernel or i am lost in finding that info. so am wondering what is best os to use as have read linux is better but which version exactly?

[kraada]

Personally I use Ubuntu 9.04 - I've used RedHat (6.2 mostly), and Debian (through Etch, I think I started with Woody?). You'd probably get along well with Ubuntu, having started in the Windows environment - it keeps things easier and tends to just work (I've gotten lazy, it's nice to have USB devices just work when I plug them in for the first time .

You can grab a free live cd to boot into and give it a try without installing anything to see if it works for you. I then run Windows (and PokerTracker 3) in VMWare - though if you have a separate machine, you can just network your postgres database and you'll get a significant performance boost from that setup (even having the DB in Linux does seem to help, especially if you put the database on a dedicated hard drive).

I don't know much about your RAM/Vista issues - it really should be taken care of invisibly for you - you do need to up the shmmax in linux, but it's an easy fix (see this post).

[rabbitlover]

Personally I use Ubuntu 9.04 - I've used RedHat (6.2 mostly), and Debian (through Etch, I think I started with Woody?). You'd probably get along well with Ubuntu, having started in the Windows environment - it keeps things easier and tends to just work (I've gotten lazy, it's nice to have USB devices just work when I plug them in for the first time .

You can grab a free live cd to boot into and give it a try without installing anything to see if it works for you. I then run Windows (and PokerTracker 3) in VMWare - though if you have a separate machine, you can just network your postgres database and you'll get a significant performance boost from that setup (even having the DB in Linux does seem to help, especially if you put the database on a dedicated hard drive).

I don't know much about your RAM/Vista issues - it really should be taken care of invisibly for you - you do need to up the shmmax in linux, but it's an easy fix (see this post).

Ok ,kraada, this response really has me thinking. If I have a dedicated box with just Ubuntu (no Windows), 2 fast HD's, quad core, 8 gig ram....then this should be a fast and reliable set up, right?
Does a full install of PT3 get done on the primary drive here, including postgres; or is there some breakup between the postgres db's and whatever else is part of the PT3 install.

The actual poker clients would be on another Windows machine.

Get me pointed in the right direction with this please; I think this is an excellent way to dispense with the Windows overhead for the machine carrying the load.

Thanks

[kraada]

That setup will do quite well, though you need PT3 installed on the Windows machine and since the PT3 processing will then still be on your Windows machine a good machine there will help, though then it's really just processing power and RAM then the HDD speed matters for the postgres machine.

With a dedicated PostgreSQL machine and 10k rpm hard drives setup on 100mbit local network you're going to be getting at least 100h/sec on import, probably more like 150, and if you take the two hard drives you talked about elsewhere and put them in a RAID configuration, you could even do as well as doubling that, but I'm going by anecdotes on that - I haven't done any testing of machines and setups that powerful personally.

[rabbitlover]

That setup will do quite well, though you need PT3 installed on the Windows machine and since the PT3 processing will then still be on your Windows machine a good machine there will help, though then it's really just processing power and RAM then the HDD speed matters for the postgres machine.

With a dedicated PostgreSQL machine and 10k rpm hard drives setup on 100mbit local network you're going to be getting at least 100h/sec on import, probably more like 150, and if you take the two hard drives you talked about elsewhere and put them in a RAID configuration, you could even do as well as doubling that, but I'm going by anecdotes on that - I haven't done any testing of machines and setups that powerful personally.

Ok, follow thru with me and see if I have the mechanics of this sorted out.
PT3 is installed on Windows machine; while PostgreSQL is installed seperately on the machine with the Ubuntu OS and is installed on the fastest drive (10k WD). (Seems like OS could be on the slower of the two drives).
Database(s) are then created on the fast drive. And PT3 on the Windows machine is calling up the info from the databases on the Ubuntu box.

As for RAID setup, I guess I originally planned to store backups on a partition of the 2nd HD (7200rpm); not having done a RAID setup my knowledge is limited; but it's my understanding the big speedup comes from storing data from/for a given database across both HD's. I can understand how this speeds up imports, but how how are things like housekeeping and defragging impacted with PT data scattered across two drives?

[kraada]

Ok, follow thru with me and see if I have the mechanics of this sorted out.
PT3 is installed on Windows machine; while PostgreSQL is installed seperately on the machine with the Ubuntu OS and is installed on the fastest drive (10k WD). (Seems like OS could be on the slower of the two drives).
Database(s) are then created on the fast drive. And PT3 on the Windows machine is calling up the info from the databases on the Ubuntu box.

That's exactly right.

As for RAID setup, I guess I originally planned to store backups on a partition of the 2nd HD (7200rpm); not having done a RAID setup my knowledge is limited; but it's my understanding the big speedup comes from storing data from/for a given database across both HD's. I can understand how this speeds up imports, but how how are things like housekeeping and defragging impacted with PT data scattered across two drives?

RAID is a complicated beast, and I'll admit to not understanding it fully. There are several kinds of RAID, I think RAID 0 is likely all that this relatively simple setup would need (more than that takes up extra hard drive space and needs more hard drives, and I don't know if the benefit would be sufficient). In RAID 0 data is distributed across two disk drives which are used in concert to achieve significantly improved performance. See the wikipedia article on RAID for the nitty gritty if you're interested in more detail. I'm not sure how much you're going to be trying to get out of your setup also; if you're going to be having many databases of multiple millions of hands, RAID might be an investment worth looking into. If you're not doing any heavy datamining though, while it would still give you a performance boost, I'm not sure you'd honestly need it.

[skyd1v3r]

Check the net for a company (or brand, or whatever) called superspeed.
Their software superspeed.supercache allows to create a cache of a partition in ram, provided you have sufficient free ram.
So if your Database fit´s somewhere in those 12 GB you could speed up read access by factor WHAM whit moving it to a dedicated parttion. There is also an option to keep in RAM the most recently used whatever GB or the most frequently used part. (As lookup tables, indeces and whatever postgres relies on are most frequently used, this option works great.)

Not directly applieing to you, yet of interest:
I am runnig a WinXP (32bit) with 8GB ram. While XP can only access about 3GB of Ram, the other 5 GB left can be accessed by superspeed´s software. Wait. That´s worth an. exclamation mark.
While XP can only access about 3GB of Ram, the other 5 GB left can be accessed by superspeed´s software!