Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: Six PostgreSQL questions from a pokerplayer

    I use poker software (HoldemManager) to keep track of the statistics (and show nice graphs) of millions of poker hand histories.
    This software (also PokerTracker 3) imports all the poker hands in PostgreSQL. The software runs on Windows) only.
    All of its users have NORMAL PCs. From single-core laptops, to a quadcore desktop at best.

    Questions:

    -1
    "POSTGRESQL uses a multi-process model. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs.
    However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs."
    I can see two databases in my pgAdmin: postgres and HoldemManager. All the poker data (about 30 GB of data) is in the HoldemManager database.
    Does the quote above (if true?) means, having a 2 Ghz single core or a Xeon 2x quadcore (8x 2 Ghz cores) will make no real difference for my performance?
    And the real performance increase is only for professional servers running multiple databases? Will I greatly benefit from having quad instead of a single-core system?

    -2 In the recent 8.3 vs 8.4 benchmarks, 8.4. was much faster than 8.3 running on a 16 and 32 core server (with 64GB RAM).
    With 8 cores, they were about the same speed. Does this mean on a normal single core computer, there will be NO NOTICABLE performance increase in 8.3 vs 8.4 and even 8.2?

    -3
    "With PostgreSQL, you could easily have more than 1GB per backend (if necessary) without running out of memory, which significantly pushes away the point when you need to go to 64-bit.
    In some cases it may actually be better to run a 32-bit build of PostgreSQL to reduce memory usage. In a 64-bit server, every pointer and every integer will take twice as much space as in a 32bit server. That overhead can be significant, and is most likely unnecessary."
    I have no idea what the maximum amount of RAM is, my database uses. But what exactly "will take twice as much space"?
    Does this mean a simple database uses double the amount of RAM on a 64 bit system? And it's probably better for my 30 GB database to
    run a 32-bit build of PostgreSQL to reduce memory usage?

    -4 One a scale from 1 to 10, how significant are the following on performance increase:
    -[ ] Getting a faster harddisk (RAID or a SSD)
    -[ ] Getting a faster CPU
    -[ ] Upgrading PostgreSQL (8.2 and 8.3) to 8.4
    -[ ] Tweaking PostgreSQL (increasing # shared_buffers, wal_buffers, effective_cache_size, etc.)
    -[10!] Something else?
    -[ ] Does NOT effect me, but I was wondering what a switch from Windows to LINUX/Solaris does for professional server users in terms of performance.


    -5 The IO operations/s performance of your harddisk vs read/write speeds vs access time? What is more important?
    With 4 regular harddisks in RAID0 you get great read/write speeds, but the SSDs excel in IO/s and a 0.1ms access time.
    What is the most usefull for which situations?


    -6 The 8.4.0-1 one-click installer automatically set the encoding to UTF8. With the other installers, I was able to
    change the encoding to SQL_ASCII during the installation process. How do I solve this after I've installed 8.4.0-1?
    (I was unable to delete the postgres database, so I couldn't create a new one with the right encoding in 8.4.0-1)

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Each database CONNECTION can use one processor. As new connections are created by the postmaster, it assigns them over the range of processors. So, in a situation where your server is handling many concurrent connections, the load balancing is distributed with the connections.

    Does this mean a simple database uses double the amount of RAM on a 64 bit system?
    Not unless the data is only integer. Other, multi-word data types (text, datetime, etc., are split up saved to as many words as are needed to hold the data. A 16 character text (assuming 2-byte encoding) would take 32 bytes to store the value in memory. With 32 bit words, this means that you would need 8 32-bit words to store the string (256 bits.) To store those same 256 bits with 64-bit words would require 4 words. The same amount of memory. Some additional memory may be needed at word boundries though, when data slightly exceeds a 64 bit boundry...

    Integers though, require 32 bits in a 32-bit memory space, and 64 bits in a 64-bit memory space.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2009
    Posts
    6
    Quote Originally Posted by Patvs
    I use poker software (HoldemManager) to keep track of the statistics (and show nice graphs) of millions of poker hand histories.
    This software (also PokerTracker 3) imports all the poker hands in PostgreSQL. The software runs on Windows) only.
    All of its users have NORMAL PCs. From single-core laptops, to a quadcore desktop at best.
    I too am a poker player (on Stars 4-tabling right now, so I have time) and I use Poker Tracker with Postgres.

    Questions:

    -1

    I can see two databases in my pgAdmin: postgres and HoldemManager. All the poker data (about 30 GB of data) is in the HoldemManager database.
    Does the quote above (if true?) means, having a 2 Ghz single core or a Xeon 2x quadcore (8x 2 Ghz cores) will make no real difference for my performance?
    And the real performance increase is only for professional servers running multiple databases? Will I greatly benefit from having quad instead of a single-core system?
    While this is unfortunately true, you can still benefit from a dual. A lot of your CPU usage will be in the application, not in the database.

    -2 In the recent 8.3 vs 8.4 benchmarks, 8.4. was much faster than 8.3 running on a 16 and 32 core server (with 64GB RAM).
    With 8 cores, they were about the same speed. Does this mean on a normal single core computer, there will be NO NOTICEABLE performance increase in 8.3 vs 8.4 and even 8.2?
    For stuff like HEM & PT, you can ignore this stuff. The difference is slight as HEM/PT/HUD suck so much CPU themselves and while you may think your database is huge, it is small by server standards.

    -3

    I have no idea what the maximum amount of RAM is, my database uses. But what exactly "will take twice as much space"?
    Does this mean a simple database uses double the amount of RAM on a 64 bit system? And it's probably better for my 30 GB database to
    run a 32-bit build of PostgreSQL to reduce memory usage?
    Go to Windows Task Manager and click on the "Processes" tab, then add it up. My DB uses ~200MB. But like the other guy said, it's mostly just integer data types, which are common in PT and probably in HEM as well.

    I would not bother with a 64 bit OS as it is a waste. Max RAM, on the other had, will do you lots of good.

    -4 One a scale from 1 to 10, how significant are the following on performance increase:
    -[2] Getting a faster harddisk (RAID or a SSD)
    -[3] Getting a faster CPU
    -[5] Upgrading PostgreSQL (8.2 and 8.3) to 8.4
    -[4 ] Tweaking PostgreSQL (increasing # shared_buffers, wal_buffers, effective_cache_size, etc.)
    -[10!] Something else?
    -[ ] Does NOT effect me, but I was wondering what a switch from Windows to LINUX/Solaris does for professional server users in terms of performance.
    [1] Getting a 2nd disk and carefully moving tables to it.

    I'm not 100% sure about Postgres performance per the OS, but I doubt it will be much, if anything. For you, avoid it. Unix (even Linux and especially Solaris) is not for the uninitiated.

    -5 The IO operations/s performance of your harddisk vs read/write speeds vs access time? What is more important?
    With 4 regular harddisks in RAID0 you get great read/write speeds, but the SSDs excel in IO/s and a 0.1ms access time.
    What is the most useful for which situations?
    This is a more complicated question than you probably think. However, for HEM & PT, I would guess IO is more important because of the HUD. Also, RAID 01/10 (mirroring) will give you two logical platters and the option of moving a table to a different disk.

    -6 The 8.4.0-1 one-click installer automatically set the encoding to UTF8. With the other installers, I was able to
    change the encoding to SQL_ASCII during the installation process. How do I solve this after I've installed 8.4.0-1?
    (I was unable to delete the postgres database, so I couldn't create a new one with the right encoding in 8.4.0-1)
    While I don't know the answer (this is a bitch of an area), the fact that you said you tried to delete the postgres database concerns me. I've been a DBA on Informix and SQL Server for 10+ years and I wouldn't try to delete the host database lightly. Check the help files carefully.


    Hope this is enough. If you need more, I'm not on this forum often, but I'm on 2+2 all the time. You can PM me there.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I've been told that the major performance boost with linux is its handling of multiple concurrent connections.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    May 2009
    Posts
    6
    Quote Originally Posted by loquin
    I've been told that the major performance boost with linux is its handling of multiple concurrent connections.
    Doesn't that require the application to be coded to use multiple threads?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •