FAQ
I'm about to buy a new desktop home computer and my main concern is best
Poker Tracker performance.
Poker Tracker uses PostgreSQL so I thought this will be the best place to
ask.

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.

I have questions about CPU/RAM/SSD:
CPU:
*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?

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?

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 ...

*I will use windows 7(x64)

Any other recommendations? (regards to hardware, I will look for tuning
after i'll buy the computer).

Thanks.


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Building-an-home-computer-for-best-Poker-Tracker-performance-tp4597798p4597798.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Search Discussions

  • Craig Ringer at Jul 18, 2011 at 2:28 am

    On 18/07/2011 9:37 AM, mdxxd wrote:
    CPU:
    *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)?
    On a typical desktop system, hard disk I/O is the limiting factor rather
    than CPU, so I imagine you wouldn't see much difference between the two.

    PostgreSQL can only use one CPU core (or thread on HT CPUs) per query.
    If you run few big queries, dual core might even be enough. If you run
    lots and lots of tiny queries, you might benefit from lots of CPUs.
    Examine your usage on your current system - see pg_stat_activity - to
    get some idea what your usage is like.
    *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?
    Again, it depends on whether your queries are limited by hard disk or
    CPU. If they're limited by hard disk access, it won't make much
    difference at all.
    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).
    Depends on how big your database is. You want your database and indexes
    to fit entirely in RAM if possible. If it fits in 8GB but not in 16GB,
    the difference will be absolutely enormous. If it fits in 8GB then you
    won't see much difference going up to 16GB.

    Check your current total database size, including indexes, and work out
    how much RAM you need. Allow for growth.

    See the user manual for how to get your database size. Do *NOT* just get
    the size of the pg data directory.
    *Will there be noticeable change between CAS 9 and CAS 8/7? 1333mhz/1600mgz
    ddr3 ram?
    Probably only if your database is fully cached in RAM and the queries
    are moving lots of data rather than doing complex processing on smaller
    amounts of data.

    The usual rule of thumb on this list is that more RAM is better than
    faster RAM, but I know Greg Smith's testing has found that RAM speed and
    in particular how the CPU accesses the RAM does matter.

    See his book "PostgreSQL high performance" for a bit more information.
    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 ...
    Most consumer quality SSDs will eat your data the first time you have a
    power cut. Be careful to get one with a supercapacitor or battery
    backup. See past mailing list discussion for which SSDs are safe.

    If your database fits entirely in RAM and you don't write to it
    especially fast you might not even need an SSD. A RAID 1 array of
    reasonable hard drives might be quite good enough.
    *I will use windows 7(x64)

    Any other recommendations? (regards to hardware, I will look for tuning
    after i'll buy the computer).
    In general, Pg is faster on linux.

    --
    Craig Ringer

    POST Newspapers
    276 Onslow Rd, Shenton Park
    Ph: 08 9381 3088 Fax: 08 9388 2258
    ABN: 50 008 917 717
    http://www.postnewspapers.com.au/
  • Scott Marlowe at Jul 18, 2011 at 2:31 am

    On Sun, Jul 17, 2011 at 7:37 PM, mdxxd wrote:
    I'm about to buy a new desktop home computer and my main concern is best
    Poker Tracker performance.
    Poker Tracker uses PostgreSQL so I thought this will be the best place to
    ask.

    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.

    I have questions about CPU/RAM/SSD:
    CPU:
    *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?
    Most likely 4 cores is plenty, unless pokertracker can run > 1
    connection, since PostgreSQL basically runs one process for each
    connection. The difference from 3.7 to 4.5GHz is likely to be
    minimal, but if you run queries that take more than a few seconds then
    it might make a bit of a difference.
    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?
    Unless each record is huge, I can't see 5M records needing more than 8
    Gig, even with other stuff running. The change in ram will be pretty
    much linear assuming the CPU can take advantage of the faster RAM.
    I.e. if a CPU has a max bus frequency of 1333MHz going to 1600 MHz
    memory won't make any real difference.
    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 ...
    SSDs can make a HUGE difference if you're doing a lot of random
    writes. I'm guessing your data will fit in memory, so there's no big
    advantage for read performance. Note that most consumer SSDs and many
    professional ones are not reliable in the event of a crash, so take
    nightly backups so you can fix things if your machine crashes hard and
    corrupts the db. If you're doing mostly reads, then the SSD will be
    no big advantage.
    *I will use windows 7(x64)

    Any other recommendations? (regards to hardware, I will look for tuning
    after i'll buy the computer).
    I'd expect a core i5 2500k to be plenty fast for pgsql performance.
  • Greg Smith at Jul 19, 2011 at 8:58 am

    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
  • Bruce Momjian at Jul 20, 2011 at 12:52 am
    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. +
  • Greg Smith at Jul 20, 2011 at 5:56 am

    Bruce Momjian wrote:
    Greg, tell me you didn't get involved with Postgres because of Poker
    Tracker. :-O :-)
    Nah, both came out of my working on stock trading systems. I just wrote
    a bit more about this whole subject at
    http://blog.2ndquadrant.com/en/2011/07/pushing-allin-with-postgresql.html
    if anyone would like to see what the actual queries look like against
    the Poker Tracker database.

    One of the things you have to come to grips with when writing a trading
    system is that you can never assume something won't happen just because
    it's really unlikely. Traders have adopted the term "black swan" to
    talk about these sort of completely unexpected things that destroy their
    bankroll. On-line poker is quite good at teaching you this lesson
    quickly, whereas traders might go a decade or more before encountering a
    similarly unlikely event.

    Lose an ace-high flush to a straight flush; lose 4 of a kind to a higher
    4 of a kind; rare, but I watched them both happen to me on multiple
    times when playing. If I'd bet so much that I'd have been wiped out by
    either event, even though I was *sure* I would win that hand, I'd have
    been done for. And a couple of times, I've seen the most rare thing of
    all: the initial 3 cards come out, and I have a hand where I can only
    lose if the other player gets the exact two cards they need. For example:

    Greg: KK
    LuckyBum: QQ
    Flop: K82

    The only way the other player can win here is if the remaining cards are
    QQ, giving them 4 of a kind over my full house at the end of the hand.
    Assuming no other players are involved, that's 45 cards left in the
    deck, and the odds of them getting one of those two followed by the
    other are 2/45 * 1/44. And yet I've lost this sort of 990:1 long shot
    multiple times. It definitely gives you a better gut feel for
    "gambler's ruin", one that translates back into stock trading--and into
    thinking about how to really achieve high-availability for a computing
    system, too.

    --
    Greg Smith 2ndQuadrant US greg@2ndquadrant.com Baltimore, MD
  • Stuart Cooper at Jul 20, 2011 at 6:14 am
    Nice to see some poker being discussed on this list. Much more
    reputable than Stock Trading.

    I was once on the LuckyBum side of this in a Casino Limit game.

    Opponent: AT
    Stuart the LuckyBum: 88
    Flop: ATT

    Myself and opponent checked that flop, I bet the turn 8 and was
    raised, the river 8 saw
    the betting go up and up and up and up (it's limit remember but
    unlimited limit sized raises are allowed on the river if the action is
    heads up).
    Opponent was pretty surprised to lose, after his third river raise I
    began to consider he might even have hold TT for better quads
    than mine.

    It's nice to get Perfect/Perfect once in your life.

    We've all got a lot more time for stock trading, PostgreSQL and other
    pursuits after the last two months in the Poker world.
  • Greg Smith at Jul 20, 2011 at 8:41 am

    On 07/20/2011 02:14 AM, Stuart Cooper wrote:
    Nice to see some poker being discussed on this list. Much more
    reputable than Stock Trading.
    If the casinos did something like what traders call "front-running",
    they'd all be put in jail the next day for cheating their customers.
    Just a day in the life of many trading firms though.
    Opponent: AT
    Stuart the LuckyBum: 88
    Flop: ATT

    Myself and opponent checked that flop,
    Since they decided to slow-play after flopping three of a kind in a
    limit game, they deserved to lose the hand. Never should have let you
    see the next card for free; they got greedy and paid for it.
    Opponent was pretty surprised to lose, after his third river raise I
    began to consider he might even have hold TT for better quads
    than mine.
    I know that sinking feeling well. My last one involved hitting an
    Ace-high flush on the river, only to see a fourth of that suit appear on
    the river. When those ragged cards sorted in my head for the first
    time, I realized my suddenly excited opponent must have just finished
    his straight flush. Oops. At least I made him pay to see the winning card.

    I used to average around 150 hands per hour; that rate can show you one
    of these 990:1 shots every 7 hours of play. Leaves one with a healthy
    respect for the sharp pointy bit on the end of the bell curve, after
    you've been stabbed with it a few times you start to remember it's there.

    --
    Greg Smith 2ndQuadrant US greg@2ndquadrant.com Baltimore, MD
  • Mdxxd at Jul 21, 2011 at 3:03 pm
    Thanks for all of the info guys. Sadly, i'm still a little confused :)
    What i'm looking for is building a fast and reliable PC for my purposes
    while getting the best bang for buck. I don't mind paying 500$, 1000$ or
    2000$ if its WORTH it(i.e i'm looking for the point where paying extra won't
    effect my needs much).
    Sure, getting an i7, 32gb 2133mhz cl7, 2*SSD in raid 1, an extrnal UPS for
    protection etc will be best, but is it necessary for me or is it an
    overkill?
    CPU:
    I'm still not sure what should I get. If i understand correctly Greg
    message, despite my workload, i7 won't have much benefit for me and I should
    get i5(i7=4 cores with HT=8, i5=4 cores)?
    RAM:
    From Scot/Craig I understood that I'm going to want to have my whole DB fit
    into my RAM. But I don't think it is possible, even with 16GB RAM. From Greg
    I understood that I only need the data that is being processed to fit into
    my RAM. So i'm a bit confused. Should I get 8GB or 16GB?
    SSD:
    This one is the most tricky. I read that consumer SSD is not reliable
    enough(can have BSOD, can cause in data corruption, in case of power
    shutdown data might be lost etc), also I read that SQL will tear-up a
    consumer SSD. Therefore i'm not sure what should I do. Getting 2 7200 rpm
    hdd's in raid 0 will still cause big bottleneck right? Getting an SSD and
    UPS for power shutdown protection should cut it or is it still not reliable
    enough and prone to failure(or slowing down due to massive write)? What
    should I do?

    Thanks.

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/Building-an-home-computer-for-best-Poker-Tracker-performance-tp4597798p4619783.html
    Sent from the PostgreSQL - general mailing list archive at Nabble.com.
  • Craig Ringer at Jul 22, 2011 at 3:21 am

    On 21/07/11 23:03, mdxxd wrote:

    I'm still not sure what should I get. If i understand correctly Greg
    message, despite my workload, i7 won't have much benefit for me and I should
    get i5(i7=4 cores with HT=8, i5=4 cores)?
    Probably, yeah. To be sure, you should *measure* your current workload.
    Get an idea of how many concurrent queries you have running and whether
    they're limited by CPU time or disk access.

    You're trying to ask for an answer without providing all of the
    question. The whole question is "Given my current workload, which looks
    like <this>, THEN what kind of computer should I get?"

    Great hardware for one workload can be crappy hardware for another workload.

    Greg knows about Poker Tracker, so he's probably going to be much more
    able to guestimate what you need without sufficient information. Follow
    his advice when in doubt.
    From Scot/Craig I understood that I'm going to want to have my whole DB fit
    into my RAM. But I don't think it is possible, even with 16GB RAM. From Greg
    I understood that I only need the data that is being processed to fit into
    my RAM. So i'm a bit confused. Should I get 8GB or 16GB?
    To answer this accurately, you need to know more about your database.

    How big is it right now? In bytes on disk? use pg_database_size() :

    http://www.postgresql.org/docs/current/interactive/functions-admin.html


    Are all the tables used frequently? Or are some of them "history" tables
    that are less used? You can use some of the statistics PostgreSQL
    collects to find out more about your use.

    http://www.postgresql.org/docs/current/static/monitoring-stats.html


    What you need for best performance is to make sure that all the
    _heavily_ _used_ parts of the database fit in RAM. This might be all the
    database, or it might only be a small fragment of it.

    If you cannot achieve that, then you need to make sure that your indexes
    fit in RAM and that you have fast disks. Whether you need fast
    sequential access, fast random access, or both depends a lot on your
    query patterns.
    SSD:
    This one is the most tricky. I read that consumer SSD is not reliable
    enough(can have BSOD, can cause in data corruption, in case of power
    shutdown data might be lost etc), also I read that SQL will tear-up a
    consumer SSD.
    Greg notes that the Intel 320s are a safe(ish) consumer SSD, but they're
    buggy at the moment - see Greg's post today - so they should be used in
    RAID 1 or with extremely frequent backups.

    As for SQL "tearing up" an SSD: It has nothing to do with SQL. The point
    is that SSDs have a LIMITED WRITE LIFE. You can only write and overwrite
    so much data before they'll die. Exactly when is unpredictable, but
    it'll happen sooner the more you write to them.

    A read-only SQL database won't affect an SSD at all. A read-mostly
    database probably won't generate anywhere near as much write activity as
    your regular Windows updates, program updates, work with documents,
    temporary file access, etc. It's only when working with databases that
    are quite write-heavy (especially REwrite activity) that can cause
    issues. Even then it's hard to kill most SSDs within a couple of years
    unless you're unlucky.

    The answer here is: Get ones that're power-loss safe like the Intel
    320s, run them in RAID 1, and keep backups.
    Therefore i'm not sure what should I do. Getting 2 7200 rpm
    hdd's in raid 0 will still cause big bottleneck right?
    You haven't provided enough data to answer that.

    If your DB can fit in RAM, or at least the heavily used parts can, then
    a single 5400RPM disk might be good enough for a low write load! If it
    doesn't fit in RAM, there's no such thing as storage that is too fast.
    Getting an SSD and
    UPS for power shutdown protection should cut it or is it still not reliable
    enough and prone to failure(or slowing down due to massive write)? What
    should I do?
    Keep good backups.

    --
    Craig Ringer
  • Serraios at Oct 4, 2012 at 7:58 pm
    Hi, this is my first post so forgive me if I ve done something wrong, because
    this is my first experience with a forum post which works through a mailing
    list.

    I am bumping this topic because as a long time pokertracker user who's tried
    to maximize the performance of his system for years, this topic has been the
    most useful in terms of understanding the type of things that would help on
    that front. It's become a bit of an ancient scroll full of wisdom if you
    will.

    Having said that, I ve got certain issues with my setup and I would like to
    hear the answers of the people here. Let me get into it:

    *1. My Hardware Setup.* I ve got an Intel i7 860 with 16 GB of RAM, an Intel
    X-25M G2 160GB disk and a 5750 Radeon card.

    *2. My Software Setup.* I am using Windows 7 x64, but the database -and my
    poker playing- is installed in a Virtualbox VM running Windows XP x64. I
    have assigned 1 processor (out of 16) and 12 GB of RAM in that machine.

    *3. My Database and its uses.* Before I came to post this, I checked its
    size through the PG admin and it's 31 GB. In contrast to the OP, I usually
    play around 18 tables, but they can sometimes -not often- get as high as 24.
    I also looked into my HUD and apparently, it displays 12 statistics for each
    player.

    *4. What prompted me to consider a hardware upgrade.* My Intel drive has
    gotten full, so I ve decided to upgrade to either a 240GB Intel 520 or the
    new 256GB Samsung 840. This will allow me to move the VM to the new extra
    drive probably giving me some extra speed boost. However, the fact that my
    motherboard only supports SATA 2 and 16GB of memory has me wondering whether
    I should upgrade my motherboard as well. If I do upgrade, I am also
    wondering whether the 32GB of RAM supported by the 1155 platform is going to
    be enough or whether I should buy a 2011 based one allowing for 64GB of RAM.
    All other things being equal, I d rather not. Another option is to wait
    until the Hasswell CPUs come out.

    *5. Why do I need so much memory? * Well, sometimes I dabble with photoshop
    - photoshop CS6 is the next thing I am getting after the upgrade- and 4 GB
    for my main OS are less than I would prefer. I am also interested in adding
    a third VM that's going to use another 2-4 GB. Last but not least, each time
    I ve added more memory, postgresql performance has improved. So, should I go
    with an 1155 motherboard, I will have 8GB allocated to my main OS, 4GB
    allocated to may secondary VM and 20GB allocated to my poker playing VM.
    Besides the fact I am not sure whether 8GB is enough for my main OS -and in
    any case you can always turn the VM off-, I am wondering if 20GB is enough
    to be completely carefree.

    This question is prompted by the advice in this thread that my memory
    allocation should be larger than my database size. Since my database is
    31GB, do I really need more than 32GB of memory for my poker VM, if I want
    to have top notch performance?

    *6. It's been already said that it depends on how much of your database you
    put to use. * I wish I could understand how to use the tools suggested in
    this thread in order to measure that question. What I do know is this. My
    database goes back to 2010. When I am at a table, most players are players
    that started playing recently. However, there are other regular players with
    whom I have playing history going back to 2-3 years. So I am guessing that I
    am using the full breadth and depth of the database.

    *7. Overall, I am pretty pleased by the performance of the database, but as
    things stand, there are two issues:* The first is that when the number of
    tables get high -let's say around 24- I am experiencing lag, less so now
    that I ve got 12 GB or RAM, more pronounced when it was 8GB of RAM. Needless
    to say, experiencing lag when you ve got 24 tables open is the last thing
    you want to be happening, precisely because you ve got less time available
    to make decisions to begin with.

    The second issue is that the more tables I ve got open, the more behind
    stats tend to be. Not by much, but enough that on some occasion it can make
    a difference in your decision making. For example, if I or some of my
    opponents have played 25 hands in a table, the HUD may be showing stats for
    22 or 23 hands. Which doesn't sound like much, but it such a small sample
    size, it can make the stats -and thus the resulting decision making- be
    skewed greatly in one or the other direction

    *8. That pesky CPU. * As I ve said I ve only got 1 CPU out of 16 devoted to
    my poker playing VM. Why? Pokertracker has a housekeeping function which
    allows you to optimize the database. So I ve ran several experiments and I
    noticed that the less cpus I assigned, the faster the process finished. For
    example with one CPU assigned to the VM, the operation ends in something
    like 4 min. But with 3 or 4 it takes up to 15 minutes. I am guessing that
    this has to do with the turbo mode of the 860, but I can't tell for certain.
    I do not know however, whether the increased speed in this scenario where I
    am doing one thing at a time is relevant in a scenario where I am running
    the poker client and a multitude of tables at the same time.

    9. *What about the GPU.* In the past, I ve upgraded my graphics card and I
    ve seen a decrease in lag. Supposedly, the gpu shouldn't matter, but besides
    my experience I know that if I turned the hud off while continuing to
    import, any lag I experience simply vanishes.

    *
    10. So to sum up.* Given all the information I presented above:

    a. What amount of memory is optimal for my 31GB database regardless of cost?

    b. How many cpu processors should I assign to my VM?. Should I try assigning
    2-4 CPUs for actual play? And if I do, is there an objective way to measure
    performance?

    c. Given the kind of different traits that postgresql demands from hard
    drives in terms of sequential and random writes and reads, which SSD should
    I get, the Samsung 840 or the Intel 520?



    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/Building-an-home-computer-for-best-Poker-Tracker-performance-tp4597798p5726666.html
    Sent from the PostgreSQL - general mailing list archive at Nabble.com.
  • Craig Ringer at Oct 5, 2012 at 3:45 am

    On 10/05/2012 03:58 AM, serraios wrote:

    *2. My Software Setup.* I am using Windows 7 x64, but the database -and my
    poker playing- is installed in a Virtualbox VM running Windows XP x64. I
    have assigned 1 processor (out of 16) and 12 GB of RAM in that machine.
    Well there's your first problem. Get rid of the virtualisation.
    *4. What prompted me to consider a hardware upgrade.* My Intel drive has
    gotten full, so I ve decided to upgrade to either a 240GB Intel 520 or the
    new 256GB Samsung 840. This will allow me to move the VM to the new extra
    drive probably giving me some extra speed boost.
    Nowhere near as much as getting rid of the VM entirely would probably
    give you.
    This question is prompted by the advice in this thread that my memory
    allocation should be larger than my database size. Since my database is
    31GB, do I really need more than 32GB of memory for my poker VM, if I want
    to have top notch performance?
    That depends on the query patterns and how much of the data in the db is
    "hot" - ie continually queried.

    If you're really interested in that you can check out the buffer
    statistics, etc.

    http://www.postgresql.org/docs/current/static/pgbuffercache.html

    You'll also want to look at index utilisation etc using the Pg
    statistics views.

    http://www.postgresql.org/docs/current/static/monitoring-stats.html
    *8. That pesky CPU. * As I ve said I ve only got 1 CPU out of 16 devoted to
    my poker playing VM. Why? Pokertracker has a housekeeping function which
    allows you to optimize the database. So I ve ran several experiments and I
    noticed that the less cpus I assigned, the faster the process finished. For
    example with one CPU assigned to the VM, the operation ends in something
    like 4 min. But with 3 or 4 it takes up to 15 minutes. I am guessing that
    this has to do with the turbo mode of the 860, but I can't tell for certain.
    More likely virtualisation issues and/or contention if the app's badly
    designed.
    a. What amount of memory is optimal for my 31GB database regardless of cost?
    The lesser of ∞ and the most memory you can possibly convince your
    computer to use when you put an insane amount in. For a 31GB DB plus
    other programs I imagine 48 to 64GB would be enough of it that Windows
    would be unable to find enough to do with it.

    Your ideal is to have **everything** you might possibly access on your
    disk cached in RAM, plus enough space for an active working set that
    it'll never push any of the cached stuff out of RAM.

    Of course, you might find that 1/2 or 1/4 that amount still gives you
    80% of the performance. It's really hard to say, that's why the rule of
    thumb is "as much RAM as you can afford".
    b. How many cpu processors should I assign to my VM?. Should I try assigning
    2-4 CPUs for actual play? And if I do, is there an objective way to measure
    performance?
    Get rid of the VM.

    --
    Craig Ringer

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 18, '11 at 1:38a
activeOct 5, '12 at 3:45a
posts12
users8
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase