On 07/17/2011 09:37 PM, mdxxd wrote:
My DB is big, around 5M hands(big is relative of course), I use complex
HUD(if you know what it is), run complex reports and play 12+ tables.
Complex is relatively complex to other PT users, I don't know how it
compared to other tasks.
That is pretty crazy. Back when I was using Poker Tracker, I was hard
pressed to play 3 tables at once, and after a year of regular play only
had 25K hands of history.
To provide some more context for what this looks like to the rest of the
list here, updates to the hand history come in at a rate that's measured
in hands per hour, with 50 to 100 being a pretty fast table; let's call
it 2 hands per minute. That may not seem like too much, but there's
actually a lot of records being written each time--the data behind a
single hand of play is probably touching 50-ish rows.
And if you update player statistics after each hand, there can easily be
a half dozen queries you have to run all over again to update the
heads-up display. And those fire for every player sitting at the table,
potentially as many as 10. So with 12 tables going at once, 2 hands per
minute at each, 10 players at each table, and 6 HUD queries per player
per hand, that works out to 24 queries per second. Do not scoff at this
workload assuming it will only take a core or two to keep with; if the
HUD data really is complicated, this can add up fast.
And they look like OLAP queries; the only thing that keeps the whole
thing managable is that only a single player is involved in each of
them, so you're usually hitting an index. And the data about the
players currently active tends to get pulled into cache and stay there,
so the working data set at any time isn't too large.
*Will PostgreSQL benefit from using hyperthreading in i7 2600k CPU? Or will
there be no noticeable performance change if I use i5 2500K CPU(i.e no HT,
just 4 cores)?
*Will there be noticeable performance improvements if I OC my CPU from
3.3ghz/3.4ghz(with turbo bust to 3.7ghz/3.8ghz) to 4.5ghz?
Even with 12 tables going, I don't think that a large number of cores is
really necessary here. You want individual cores to be as fast as
possible for this type of application, but it's not too likely you'll be
able to use more than 4 at once usefully. I would guess that using a
smaller number of cores and aiming at a really high clock speed on them
is the better strategy for you.
RAM:
*Will there be noticeable performance improvements if I will use 16GB RAM
over 8GB RAM? I know big servers uses at least 16GB(often 32GB).
*Will there be noticeable change between CAS 9 and CAS 8/7? 1333mhz/1600mgz
ddr3 ram?
You want to use the fastest RAM possible here, that really matters for
what you're doing. I doubt the amount of data being processed at any
time will exceed 8GB; the "hot" set here is only the hand histories for
players who are currently sitting at tables with you. Also, I've found
the 4GB modules normally used reach 16GB total on a desktop system tend
not to be quite as fast as the similar 2GB ones.
You should aim at DDR3/1600 and the lowest CAS you can find. Your
complex HUD updates are for the most part going to be limited by how
fast your CPU can chew through information that's in memory, so this may
end up being the most critical factor to your system performance.
SSD:
Different SSD excel in different areas. I know that for general PC usage, 4K
Q1 random read/write is the most important.
What is the most important for PT3(and PostgreSQL) usage? Random? sequel?
4K/8K/16K / 32K/ +? 4K-64Thrd? 4K QD4/QD16/QD32 ? etc ...
Databases have a mix of sequential and random access for what you're
doing, so you're going to want a balanced drive; no one of these factors
is the obvious important one, they all are.
Note that most inexpensive SSD units can result in database corruption
if your system crashes. See
http://wiki.postgresql.org/wiki/Reliable_Writes for more details. This
is much more important to get right than to worry about the drive
benchmarks. The only inexpensive SSD consumer drive I'm aware of that
works well for PostgreSQL are Intel's recent 320 series. See
http://blog.2ndquadrant.com/en/2011/04/intel-ssd-now-off-the-sherr-sh.html
for details. Putting your main hand histories on one of those would
make it very unlikely that drive I/O speed was ever a limiting factor
for you. Much more likely that you'll have trouble with memory maxing out.
To summarize how I would spec out this sort of system:
-Pick a reasonably priced SSD that's big enough to hold your data.
Consider if you can justify buying 2 and using a RAID-1 to survive a
drive failure.
-Price out the fastest DDR you can find, with 8GB probably being plenty.
-Use your remaining budget to get the fastest individual cores you can
justify, shooting for 4 to 8 of them probably.
Two final notes:
-Make sure to follow the basic database tuning guidelines at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server to get
your configuration in the right area. I think your workload might
really benefit from turning off synchronous_commit in particular, so try
both settings there. It won't kill you to lose a hand or two of history
if you disable that, and the speed improvement could be large.
-If you want to start up a discussion about optimizing your server, that
would be better done on the pgsql-performance list than this one.
--
Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
