Re: Building an home computer for best Poker Tracker performance - Mailing list pgsql-general

OK, did I just read Poker Tracker and SSDs in the same email --- my head
is going to explode!

Greg, tell me you didn't get involved with Postgres because of Poker
Tracker.  :-O  :-)

---------------------------------------------------------------------------

Greg Smith wrote:
> 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    greg@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

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +