spec for dedicated PostgreSQL server

[Russelldust]

I'm thinking of setting up a dedicated server for my PT3 database.

At the moment I have PostgreSQL running in a virtual machine (albeit on the same physical machine), so I'm familiar with the process of setting up PT3 to access a 'remote' database.

I'm wondering how great the benefits of moving my VM to a dedicated PC might be, and how I should specify the server hardware?

I've had a good look through these threads, which seem to be relevant:
http://www.pokertracker.com/forums/view ... 17&t=24006
http://www.pokertracker.com/forums/view ... 15&t=12136
http://www.pokertracker.com/forums/view ... =17&t=7444

As I understand, the main bottleneck in PT3 (once configured optimally) is hard disk access speed at the server-side - is that right?
Therefore, should I focus on packing my server with super-fast disks until the computer sounds like a hairdryer?

[Russelldust]

And secondly, how much RAM is it worth installing on the PostgreSQL server?

Will PostgreSQL literally use all the memory available?

I already know about the tuning wizard and I know you can allocate all your memory to it, but what I mean is, in real terms, will PT3 actually benefit from adding more and more RAM to the server?

Like, if my database is 8GB in size on my hard disk, is it worth installing 8GB+ of memory so that it caches the whole damn thing in memory? Or would that be pointless?

[kraada]

First, your general reading of the situation is correct; hard drive access times are the enemy.

RAM is useful but only up until a point. If your database is only 8G in size, then there's no point in worrying about hardware at all - you could probably run PT3 on any old machine (that meets minimum spec) without much trouble then. (Rule of thumb for full ring: 100K hands = 1G, so that's an 800k hand database or so.)

You'll definitely get the most bang for your buck by upping hard drive speeds. You have two routes: you can go SSD, which will give you the best performance, but also cost the most and you'll have less space available, or you can get 10K RPM disks which cost less but won't give you quite as much speed. Either, of course, will be a significant improvement over a setup where you had the computer running PT3 also running the PostgreSQL server.

[Russelldust]

Thirdly: is there a downside to porting PostgreSQL to a separate server, i.e. creating a new bottleneck in communication between PT3 and the database server?

Even though my database is clustered/cached/etc, even though I've optimised the memory usage on my VM using the tuning wizard, and even though my PT3 db is not especially gargantuan (about 5GB on my hard disk), I still feel PT is a bit sluggish. Obviously this is a 'how long is a piece of string' thing. But for example, when I navigate from tab to tab (e.g. from 'hands' to 'tournaments', then to 'vs Player', under Tournaments) there is a considerable delay (anything from 5-20 seconds) while it says 'Loading...', before any data appears.

I can understand the queries are non-trivial, I just wonder sometimes is it's possible I've created a bottleneck by having the db reside on a virtual machine (thus PT has to communicate via an extra 'virtual' layer of IP). And hence, I wonder if I might be wasting my time configuring the dedicated server? Could the network bottleneck get even worse...?

I read (when hunting through your PostgreSQL threads for info) somebody saying their network resource usage is almost zero, suggesting the actual bandwidth used (between client and server) is minimal. However, it doesn't mean there isn't latency (when PT3 repeatedly exchanges messages, sends SQL queries, or whatever).

I suppose the answer I am looking for is along the lines of, either:

"No, PokerTracker is not network intensive. Most queries to the PostgreSQL server take a substantial time to complete, thus the overhead of communication between client and server is relatively tiny, compared to the time taken by PostgreSQL to perform the requests."

or:

"Yes, PokerTracker's speed is severely constrained by the communication channel between the PT software and the db server. Sometimes PT sends a very large number of small queries over a short space of time, and so the network latency adds considerably to the completion time."

[Russelldust]

Either, of course, will be a significant improvement over a setup where you had the computer running PT3 also running the PostgreSQL server.

Gotcha, thanks for the clarification kraada.

Actually, I already have dedicated hard disks for my VM: Windows/applications are on one disk; the PT database itself is on a hard disk of its own. So, apart from the fact the hard disks are nothing special, I should already be seeing some of the benefits of "having the computer running PT3 not also running the PostgreSQL server", right?

Put another way, if I loaded my main PC with super-fast disks and set it up optimally (with PT3 & PostgreSQL co-residing), it would be pretty much just as fast as if I had a dedicated server with fast disks...?

I'm guessing that since the PostgreSQL side of things is not especially RAM or CPU intensive (as the bottleneck is elsewhere), there is no intrinsic benefit of putting the server on dedicated hardware?

(As it happens, it's a logistically appealing solution. E.g. I can throw loads of disks in my server and put it in a different room, without worrying about noise levels. And I already have too many disks in my main desktop PC!)

[kraada]

Network latency will not be an issue assuming you're using any reasonable LAN setup. (I haven't gone to great lengths to test this, but I'm reasonably certain even 10BaseT is overkill for our purposes.)

The virtualization may be slowing things down for you but it's unclear from what you've told me exactly how much. If you're running Windows in the VM and PostgreSQL outside of the VM in a native capacity things will definitely be faster than PostgreSQL being inside the VM (as the extra layer of virtualization will slow things down, albeit not a whole lot), even if you have proper hard disk setup. We're not talking huge performance increases, but it is a performance increase nonetheless.

Incidentally, as it occurs to me, you should make sure you're running Beta 29.1, not Build 5.2; a fix went into Beta 29.1 that results in the player list loading an order of magnitude or so faster when you start getting large numbers of players in your database (this is especially useful if you play large tournamnets).

As it sounds like it's otherwise appealing to you, splitting things into having a dedicated PostgreSQL server will be the easiest way to go and it should provide you some added benefit, especially if you're running PostgreSQL natively on the server (which I presume is the plan).

[Russelldust]

The virtualization may be slowing things down for you but it's unclear from what you've told me exactly how much. If you're running Windows in the VM and PostgreSQL outside of the VM in a native capacity things will definitely be faster than PostgreSQL being inside the VM (as the extra layer of virtualization will slow things down, albeit not a whole lot)

I do have PostgreSQL running inside the VM (that's basically what the VM is dedicated to).

The idea is, I can easily port the VM to any physical machine. The thinking was:
* No need ever to have to reconfigure PostgreSQL again (if I decided to move my db to a different machine).
* Easy backup of whole VM, database, PostgreSQL configuration, everything - just copy the virtual hard disk files to backup drive.
* If I go away for a few days with my laptop, I can temporarily port the VM by simply copying the VHDs onto the laptop. Super-portable PT database!

...splitting things into having a dedicated PostgreSQL server will be the easiest way to go and it should provide you some added benefit, especially if you're running PostgreSQL natively on the server (which I presume is the plan).

Hmm, you've got me thinking now. Actually no, it wasn't the plan (but it is an option I'll consider, if virtualisation could be holding things back).

I didn't really think that virtualisation would slow things down much, if the main bottleneck is the hard disk access. Naturally the VM will consume more CPU power (as all CPU instructions have to be interpreted), but as the hard disk increasingly acts as a bottleneck, should the negative effects of virtualisation not tend towards zero?

Please could you clarify how the virtualisation layer might slow things down? Are you purely talking about the CPU speed, or do you think stuff like IP might also be significantly less efficient through the extra layer of virtual hardware?

[Russelldust]

I am using beta v29.1, btw.

And yes, I have a lot of players in my database. It takes quite a while for the player list to appear these days - there are about 55,000 players in my db. I'm sure that's a much smaller number than many other tournament players' databases (as I don't play all that many tourneys with huge fields).

[kraada]

If you made your PostgreSQL VM some form of Linux I think it would tend to work better; PostgreSQL was originally designed for Unix and then ported to Windows, so I believe it is still going to be a bit faster/more stable on a Linux VM. Other than that, it probably won't make much of a difference.

For what it's worth, also, in the next PT3 Beta we will be rolling out a new backup mechanism which will make everything much easier to keep backups of.

[WhiteRider]

I'm really not an expert in this stuff, but surely having postgres in a VM has to be slower..?