Database limit?
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Database limit?

  1. #1
    Junior Member
    Join Date
    Aug 2008
    Posts
    14

    Default Database limit?

    Hi,

    Over the last few days I think I hit some sort of database limit in HoldemManager. Performance has dropped like a stone. The machine has become very sluggish and unresponsive. The HUD will only update every 3 to 5 hands, as opposed to every hand as before. The poker client times out, even when playing a single table. The only change to the system is the number of hands in the DB. Shutting down HM, everything responds fine.

    Also I have received the following message a few times:
    "Player Hand Error: Connection establishment timeout increase timeout value in connection string. email support... and let us know the version of postgresql."

    What is the connection string? I could not find a reference in the HM manual or the HM conf file. The closest I find is authentication_timeout in postgresql.conf. I have increased this to 180 seconds from the original 60. Still received the above message.

    Postgres is 8.3.8, it is on a Linux box. I moved it from its original machine to a much faster one yesterday, but that did not help. The current database machine is a Quad Core, 8GB ram, OS and swap are on separate SCSI discs, the database is split over two WD VelociRaptors (indexes on one, data on the other). The connection is gigabit ethernet.

    background:

    I datamine about 50K hands a day and play between 20K - 40K a month. My database has about 500K of my hands and all of 2009 for datamining. I prefer not to name the site.

    With PT I used multiple databases (1 for my hands, monthly ones for datamining) and stopped using the ones that were 3 or 4 months old.

    After loading the hands into HM I always Purge Observed Hands, leaving only the stats. At least once a week I vacuum full and reindex. Even so, the current database size is 63GB in the datadir, exported (pg_dump -C) it is 33GB.

    I attempted to purge the hands but it seems that stats are not purged, and no hands were deleted, so no space was saved.

    I have increased the shared_buffers to 64MB, the work_mem to 2048MB, temp_buffers to 32MB, max_fsm_pages to 409600, maintenance_work_mem to 128MB and wla_buffers to 512KB. This has helped, but performance is still not good.

    I figure I am hitting some limit. This is OK, I know I am abusing the system. What I need to do is really purge all the data (stats included) from say, before October 2009.

    Is there a way to do this?

    I decided to try loading the datamined hands from Oct to present and all my hands into a new DB. I thought it should be possible to have two machines loading the data at the same time, but that caused both machines to generate import errors. Without investigating it seems that there are locking issues. If I have to use only one machine it will take several days to load all the data.

    I am looking for recommendations. I am OK with getting rid of old data. How do I do this?

    Thanks,

  2. #2
    HM Support Patvs's Avatar
    Join Date
    Feb 2009
    Location
    Amsterdam, the Netherlands
    Posts
    33,523

    Default

    The default setting is the HUD updates every 5 seconds.
    Did you ever change this to 1 second (which will cost a lot of CPU power)

    My initial thought is: something is not blocking your SQL connection (data stream to port 5432), but certainly is restraining it. (that's why you get "timeout" errors) This is usually an antivirus/firewall program which is constantly "monitoring" the data.

    Please add Holdem Manager & PostgreSQL (postgresql.exe and pg_ctl.exe from the bin folder) to the exception list and forward (open) port 5432.

    Questions:
    - is your harddisk full?
    -did you do ALL the things from my top 10 performance tips thread (see my signature)
    -did you recently install/update a program that can restrain the SQL data stream?
    -can your crazy setup cause it (for example a missing firmware/chipset driver update)
    -when I backup my 16 GB database in pgAdminIII...--> it only becomes 1 GB .backup file. You don't compress your pg_dump backups?

    After reading your post, it's even more plausible one the your changes to the postgreSQL.conf is causing it. Start by setting it back to DEFAULT: and ONLY change the shared_buffers = MB and effective_cache_size = MB (set this to 2x the size of the shared_buffers)

    Also you should consider, a speed test, to just install SQL 8.4.2 using a more conventional install, not splitting data/index, etc.

    Alternatively, you should consider dumping the VelociRaptors, and just buy one 160 GB Intel X25-M G2 Postville. The 4K read/write speeds will be a lot faster.

    Edit: To purge data (stats included) use the OPTIONS--> Purge Hands (instead of the Observed Hand History Configuration)
    Last edited by Patvs; 12-28-2009 at 04:12 AM.
    Participate in the Beta release of the newest Hold'em Manager version: HM Cloud. Sign-up HERE.

    If you would like to leave some feedback to help us improve the quality of the solutions, and/or the support quality you received, - you can do this here

  3. #3
    Junior Member
    Join Date
    Aug 2008
    Posts
    14

    Default

    Quote Originally Posted by Patvs View Post
    The default setting is the HUD updates every 5 seconds.
    Did you ever change this to 1 second (which will cost a lot of CPU power)
    I do not know. Where is this option? If I did change it, it was a long time ago.

    Quote Originally Posted by Patvs View Post
    My initial thought is: something is not blocking your SQL connection (data stream to port 5432), but certainly is restraining it. (that's why you get "timeout" errors) This is usually an antivirus/firewall program which is constantly "monitoring" the data.

    Please add Holdem Manager & PostgreSQL (postgresql.exe and pg_ctl.exe from the bin folder) to the exception list and forward (open) port 5432.
    HM is installed on a dedicated virtual machine. It does not have a firewall, anti-virus, anti-spyware, etc. It only has WinXP, SP2 with all security patches, Firefox, HM, the poker client, AutoHotKey, BetPot (AHK script) and TableNavigator (AHK Script). Nothing else. This does not apply.

    Quote Originally Posted by Patvs View Post
    Questions:
    - is your harddisk full?
    Less than 6% usage on each drive. These are dedicated to the Postgres HM database.

    Quote Originally Posted by Patvs View Post
    -did you do ALL the things from my top 10 performance tips thread (see my signature)
    1) no SSD
    2) 8 GB RAM
    3) Quad Core
    4) yes
    5) does not apply
    6) does not apply
    7) I have followed advise posted on http://archives.postgresql.org/pgsql-performance/ and from other sources. More below.
    8) Decrease the size of the DB: I would love to. How? VACUUM & REINDEX/Dump & restore do not do it. I need to delete data. Once again, How?
    9) Need to purge the stats data, not just the hands.
    10) logging is off
    11) disks are less than 6% usage
    12) does not apply

    Quote Originally Posted by Patvs View Post
    -did you recently install/update a program that can restrain the SQL data stream?
    No.


    Quote Originally Posted by Patvs View Post
    -can your crazy setup cause it (for example a missing firmware/chipset driver update)
    No.



    Quote Originally Posted by Patvs View Post
    -when I backup my 16 GB database in pgAdminIII...--> it only becomes 1 GB .backup file. You don't compress your pg_dump backups?
    pg_dump has compression options, but I find it much faster not to use them. I have plenty of disk space.

    Quote Originally Posted by Patvs View Post
    After reading your post, it's even more plausible one the your changes to the postgreSQL.conf is causing it. Start by setting it back to DEFAULT: and ONLY change the shared_buffers = MB and effective_cache_size = MB (set this to 2x the size of the shared_buffers)
    As I made most of these changes after the loss of performance, and they helped, this is highly doubtful. The work_mem was 1024MB before and the wal_buffers were default. max_fsm_pages is set this way because Postgres complained about 2 months ago during a VACUUM operation that it did not have enough and said to use this number.

    Quote Originally Posted by Patvs View Post
    Also you should consider, a speed test, to just install SQL 8.4.2 using a more conventional install, not splitting data/index, etc.
    Perhaps, but this still does not explain the rather sudden negative change in performance. 8.3 is used with huge databases and in my reasearch I have not found anything that says 8.4 is much faster.


    Quote Originally Posted by Patvs View Post
    Alternatively, you should consider dumping the VelociRaptors, and just buy one 160 GB Intel X25-M G2 Postville. The 4K read/write speeds will be a lot faster.
    If the problem is with HM and the amount of data, this will only put the problem off. It will not solve it.


    Quote Originally Posted by Patvs View Post
    Edit: To purge data (stats included) use the OPTIONS--> Purge Hands (instead of the Observed Hand History Configuration)
    At the top of the form it says:

    "The options on this form tell HoldemManager which hands should be completely purged from the database. ...however will still appear in HUD stats for the time being until the Cache Rebuilder is completed."

    This implies that the Cache is not deleted. Running it returns "0 hands deleted (0 skipped due to player filter)".

    The database size remains the same.

    So no, stats are not purged by this option.

    Once again, how do I purge old stats?
    ===============================================
    I have loaded about 900K hands into a new database (then purged the observed hands, leaving only my 300K or so and the stats from the datamined hands) on the same machine, in the identical locations. Using the exact same configuration I played for about 2 hours today. Everything ran great. Switched back to the old database with about 500K of my hands and maybe 15meg hands worth of stats (all purged) and it ran very slowly.

    It is a data size problem. I would like to get rid of the stats for all observed hands before October, but there does not seem to be a way other than reload everything from Oct forward into a new DB. HM informs me that this will take around 12 days.

    Is there a way to use two (or more) machines to load data at the same time?

    Where is the "connection string"? You mentioned why I was getting the message but not where to set it.


    How to proceed?

  4. #4
    HM Support Patvs's Avatar
    Join Date
    Feb 2009
    Location
    Amsterdam, the Netherlands
    Posts
    33,523

    Default

    How many hands are in your slow database? - http://www.holdemmanager.net/faq/afm....aspx?faqid=19
    And how many players?

    Update to SP3, Update to .Net Framework 4 beta 2

    Why does defrag your harddisk does not apply?

    Just wait for the Cache Rebuilder.. is will be in a future update and can delete the stats (for a whole month)

    But if you're planning to delete stats anyway, just create a new database and only import recent hands (last three months).

    Which import speeds do you get reimporting Oct forward into a new DB? (if less than 40 hands/s---> buy a fast SSD)
    You have no plans to put the VelociRaptors in RAID0?

    ----

    I have no other clue what else you can do. I'd suggest to ask your questions on a dedicated SQL forum.
    Participate in the Beta release of the newest Hold'em Manager version: HM Cloud. Sign-up HERE.

    If you would like to leave some feedback to help us improve the quality of the solutions, and/or the support quality you received, - you can do this here

  5. #5
    Junior Member
    Join Date
    Aug 2008
    Posts
    14

    Default

    Quote Originally Posted by Patvs View Post
    How many hands are in your slow database? - http://www.holdemmanager.net/faq/afm....aspx?faqid=19
    And how many players?
    Hands = 21167202

    Select count(*) from players returns 421287
    Update to SP3, Update to .Net Framework 4 beta 2
    I'll try the .Net upgrade, but SP3 is way slower than SP2, so no.

    Why does defrag your harddisk does not apply?
    Because the database server is Linux, using reiserfs as the file system. It does not fragment.

    Just wait for the Cache Rebuilder.. is will be in a future update and can delete the stats (for a whole month)
    When will it be released? I really need this. I would be happy to try the beta. Can tell me which tables are involved and I can write the SQL?

    But if you're planning to delete stats anyway, just create a new database and only import recent hands (last three months).
    This is what HM says will take ~7 days. (Sorry that I wrote 12 before, I was thinking about something else and did not catch it)

    Which import speeds do you get reimporting Oct forward into a new DB? (if less than 40 hands/s---> buy a fast SSD)
    Sorry, I have trouble seeing this as not being a joke. When the DB is empty import speeds are 15/s. As it gets more hands it drops to 7. It has always been this way. PT import speed is over 100/s with the same data, on a slower machine, with slower disks. I just figured you were doing more with the data.

    The import speed is OK for the way I work as the nightly import takes about 2 hours (50K at 7/s), which is fine. But it is a bit of a problem for 3 months worth of data.

    Note that HM's HUD has until the recent problems been much faster than the PT HUD. What I figured is that you were optimizing interactive use at the expense of batch processing.

    You have no plans to put the VelociRaptors in RAID0?
    I could try it, but think that separate disks for data and indexes should be faster. At least that was my experience when I worked with commercial databases.

    I have no other clue what else you can do. I'd suggest to ask your questions on a dedicated SQL forum.
    The problem with that approach is that you typically need access to the SQL code. Postgres has some good tuning tools, but need the source code.

    You have not answered my other queries:

    Is there a way to use two (or more) machines to load data at the same time?

    Where is the "connection string"?

    Thanks,

  6. #6
    HM Support Patvs's Avatar
    Join Date
    Feb 2009
    Location
    Amsterdam, the Netherlands
    Posts
    33,523

    Default

    PT3 has faster imports but the "housekeeping" (cluster, update cache) can also take a long time.

    The problem on OUR side is, the HEM developers are NO SQL experts. So I'm sure, they're making mistakes and the import is not very efficient. Having said that..

    Your import speeds of 15/s when the DB in EMPTY with two VelociRaptors (+ custom Linux database server) are the real joke. Any CRAP computer with a new 7200 RPM harddisk (and default setup) can still import with 30-50 hands /s. I've never imported with less than 80 h/s (and I have a CRAPPY SSD.. although two of them in RAID0). I've seen the Intel X25-M G2 import with 120 h/s.

    Just give your 3 months of hands to a friend who CAN import with 100 h/s and let him create a .backup file for you.


    You have not answered my other queries:

    -1 Is there a way to use two (or more) machines to load data at the same time?

    -2 Where is the "connection string"?
    -1 No
    -2 I have no idea what that is...



    Edit: My collegue Fozzy writes: [4:00:02 AM] fozzy: I had a guy with standard desktyop yesterday getting 160 h/s
    Last edited by Patvs; 12-28-2009 at 10:59 PM.
    Participate in the Beta release of the newest Hold'em Manager version: HM Cloud. Sign-up HERE.

    If you would like to leave some feedback to help us improve the quality of the solutions, and/or the support quality you received, - you can do this here

  7. #7
    Junior Member
    Join Date
    Aug 2008
    Posts
    14

    Default

    Hmmm, OK, I have a bunch of machines. I will try various different combinations to see what sort of import speed I get.

    Thanks.

  8. #8
    Junior Member
    Join Date
    Jul 2008
    Posts
    29

    Default

    I have a 3 year old dualcore machine. Nothing special with the harddisk (planning to upgrade to SSD)

    I have 7.5M hands in my database and the database folder is roughly 28gig

    My import speed on my computer was always around 20 hands/s (a little more when the database is empty) with HEM 1.09 and just improved to about 30 hands/s after upgrading to 1.10 Beta 6 (never used it so far with an empty database)

    If you have a database wich contains 50k hands a day and you do not have it older than 3 months you roughly have a database with 4.5M hands. (you mention having several database sizes and I did not find how many hands were actualy in your database so I just use this as a reference because it sounds "normal" to me)

    You should have a way better import speed with your setup. Something is definetly going wrong but since I have the exact same import speed all through HEM 1.09 and now improved with HEM 1.10 I seriously doubt it's HEM giving you a problem.

    Especialy since your setup is A LOT faster than mine.

    Do you import from folder or auto import ? It helped me to use import from folder. It helped me from importing at 12 hands/s to the 20 hands/s. I doubt however that you are using auto import for your nightly import.....

    Also, with your setup, your import speed with an empty database should be WAY FASTER than it currently is.

    You are running a dedicated SQL server machine. I would start out with installing PostgreSQL on your desktop where you also play poker and use that as your database server. Check that out, it should have normal import speeds. If it does, you know your server is giving you troubles.

  9. #9
    Member
    Join Date
    Jul 2008
    Posts
    28

    Default

    Quote Originally Posted by Folding View Post
    HM is installed on a dedicated virtual machine. It does not have a firewall, anti-virus, anti-spyware, etc. It only has WinXP, SP2 with all security patches, Firefox, HM, the poker client, AutoHotKey, BetPot (AHK script) and TableNavigator (AHK Script). Nothing else. This does not apply.
    What do you use for virtualization? What has been assigned to this machine (cpu's, mem, etc)?

    How are the data disks mounted into the virtual machine? (Image files, Logical Volumes, etc)

    Maybe the virtualization is the problem

  10. #10
    Junior Member
    Join Date
    Aug 2008
    Posts
    14

    Default

    Quote Originally Posted by j1nx View Post
    What do you use for virtualization? What has been assigned to this machine (cpu's, mem, etc)?

    How are the data disks mounted into the virtual machine? (Image files, Logical Volumes, etc)

    Maybe the virtualization is the problem
    This is possible. Specifically some problem with .Net on the virtual machine. I have considered this possibility but cannot find any reference to this type of problem with VMware.

    To answer the questions: VMware Worstation 7.0, 2 CPUs, 2 GB memory, 1 SCSI disk, image.

    The database is on a separate Linux box.

    I tried a lot of things then gave up and went back to PT which runs fine on the same VM, accessing the same postgres instance. It runs very fast. Import is between 220 - 250 hands per second, the HUD is much faster than I remember it. DB maintenance takes a bit of time but always finishes overnight. I am missing a few stats, but with a bit of effort can create them, so I guess I will have to.

    When the Cache rebuilder is ready, or there is some other major change, I will try HM again.

    In general I like HM better (but PT is a good product), and until I hit this performance wall was very happy with it.

Similar Threads

  1. Replies: 5
    Last Post: 11-22-2009, 12:34 AM
  2. HUD for Fixed Limit?
    By HPR1978 in forum Share your HUD configurations
    Replies: 6
    Last Post: 07-27-2009, 02:25 PM
  3. Deleting a Limit from database
    By aslobo in forum Manager General
    Replies: 1
    Last Post: 03-03-2009, 11:14 PM

Posting Permissions

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