spec for dedicated PostgreSQL server

[Russelldust]

I guess it depends how you define "slower"?

Sure, it will be slower. But if the CPU usage is minimal, and hard disk access is the bottleneck, it may not be perceptibly slower...?

I was hoping you guys could tell me.

[kraada]

It's honestly not something I've tested extensively. There are times though when CPU usage does matter: if you're doing housekeeping for the cache update bits, CPU usage is the bottleneck. When you're playing at the tables and just using the HUD, disk access time is the bottleneck. It depends exactly where you're looking to get things streamlined. If you're worried about the ability to use the HUD while mass multitabling, I don't think virtualization will matter much for you. But I'll try and get an opinion from one of the developers for you.

[Russelldust]

Brillo-pads - much appreciated.

I'm interested in all those things really.

* I don't presently use the HUD but I'm planning to start using it soon. I play 4-8 tables and definitely don't want any unnecessary slowdowns. As you say, I shouldn't have any virtualisation-related problems since disk access is the bottleneck.

* I'd like to make the PT application itself nice and slick. As I mentioned, navigating the tournament tabs can take in the order of 10-25 seconds for the queries to complete and the data to appear. I'd really like to make this faster if possible.

* I find cache updates increasingly slow (an hour plus). It's not a MASSIVE issue (much less important than the issues above), but 'would be nice' if it were faster.

I would consider hitting virtualisation on the head if it will make any of these things significantly faster.

[kraada]

I've been pushing for other performance improvements on the backend - splitting your database off to a separate machine will help shave a few seconds off of the report loading time, but as your database grows that time will increase and there really isn't much that can be done about it right now. We will be looking into performance improvements and hopefully getting some after Beta 30's release.

With 4-8 tables you shouldn't have much of any problem on modern hardware either way, though.

[Russelldust]

Okay, a few more questions about my prospective server (thanks for your patience with this!):


A. Kraada you said "RAM is useful but only up until a point".

Could you put a figure on it? I mean, is 2GB overkill? (for a server dedicated to running PostgreSQL)


B. We've talked about CPU power on the server (e.g. the possibility that the CPU performance may matter during cache updates).

Earlier you said "you could probably run PT3 on any old machine (that meets minimum spec)". When you said "PT3", did you mean the client software?

As for my server, I'm guessing there's no special reason to go dual-core? I have noticed in Task Manager there are several instances of postgres.exe, but I only ever see one at once thrashing the CPU. Does PT3 benefit much from multiple cores on the server side?

(Remember it'll be a dedicated server, so PostgreSQL is only competing with Windows XP itself... cue jokes about Windows...)


C. Back to network latency once more. You suggested "even 10BaseT is overkill". Would you be averse to wireless?

Obviously modern wifi is much faster than 10BaseT, but I'm guessing there are potential latency issues. Would you lean heavily towards wired networking, or would you think it's a non-issue?

This gets back to my original question about whether network latency is, fundamentally, an issue with PT3? I mean, more extremely, do you think PT3 would run acceptably over the internet, or would certain operations slow to a crawl? (E.g. because there are lots of short database queries involved?)


D. What do you think of this as a candidate hard disk for the main database on the server?

It's not as fast as the VelociRaptor for example, but I could buy 4 of these for the same price as that disk! (I could RAID1+0 them - I already have a RAID interface). The review says the access times are super-quick - isn't that the crucial figure when performing database queries?

[kraada]

(A) Assuming you have an x64 system (that can thus use more than 3G of ram), I'd say 4 should be plenty, but if you're going to use it for other stuff, 8 is good too. Beyond that other limitations are going to kick in first.

(B) You can't quote me out of context; I said if your database is only 8G in size - if you're looking at only having a few hundred thousand hands tops, you don't really need to worry much. There just isn't that much work to do. This includes both the postgres server and PT3 client.

(C) If you've got 802.11g you should be fine, though I'm always a bit leery of using wireless on a computer I'm playing on. But then, that's likely my inner paranoia coming out.

(D) A RAID array of those disks should work nicely.

[Russelldust]

(A) Cool, I was thinking of using my XP x64 system so that's what I'll do. I'll install 4GB if you think that will give a noticeable improvement over 2GB.

(B) Ok, I'm still not fully clear though... will a fast CPU, and/or dual cores, give any noticeable benefit on the server-side? (Assume that my database will grow much larger than 8GB in the next year or so.)

If you were selecting a CPU for the server, would you spend the same money on a faster single-core CPU rather than a dual-core CPU? (As I mentioned it doesn't LOOK like the PostgreSQL side of things is heavily multi-threaded in Task Manager, despite the many postgres.exe processes...)

(C) Do you think PT would run like crap over the internet? (e.g. with a network latency in the tens of milliseconds?) Are there any operations (e.g. opening certain tabs within PT) where many small queries are issued over a short space of time, causing network latency to become the bottleneck?

I'm not just asking this as a pure thought exercise - I'm trying to get a feel for the nature of PT's communication with the database back-end! Though I am also considering the situation where I play poker away from home...

(D) Am I right in thinking hard disk access time (as opposed to raw throughput) is the the crucial figure when performing database queries?

I promise I will stop bothering you soon. I have a much clearer idea about what I'm doing now and why I'm doing it, so thanks a heap!

[WhiteRider]

I don't have a definitive answers - Kraada (or others) will probably be able to give you more conclusive answers later on...

A. I would definitely go for 4GB - RAM is pretty cheap these days and will give good performance/$.

B. I think that a dual-core will be better. The separate postgres processes can run on separate cores.

C. Over a broadband connection I expect that the only operation which might cause significant issues would be auto importing, espeically using the HUD.

D. I think you're right - again, Kraada may be able to give you more conclusive answers.

[kraada]

WhiteRider is pretty much correct on all points; it may not look like PostgreSQL is multithreaded but each process that splits off for each database connection should be running as its own thread as far as I understand it. My other worry with respect to (C) is that if you have multiple large databases on the server, PT3 might actually time out on some of the connections waiting for the data to arrive while playing over broadband, depending on the quality of your broadband, size and number of your databases, and the number of statistics you display in your HUD.

[Russelldust]

Thanks for the clarification.

I'd have thought the question about server-side multithreading has more to do with PT than PostgreSQL?

I.e. if PT only issues one query at a time (and waits while PostgreSQL responds) then although PostgreSQL theoretically has multiple processes, only one of its processes will ever be non-idle at any one time.

This seems to be supported by my observation that only one process seems to be doing anything. (But I stress I haven't performed thorough scientific tests!!)

I presume the idea of PostgreSQL's multiple-database-connection model is for applications where PostgreSQL is serving MANY users simultanously. But there is only one user in my case. Unless PT's architecture is specifically geared towards exploiting PostgreSQL's multiple processes, OR unless Postgre's individual processes do clever multithreaded stuff, I don't see how the server will benefit from multiple CPU cores...?