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,