PDA

View Full Version : HM1 doesnt display hands section (query is running at database side)



ristorothe
09-03-2012, 03:42 AM
When I run a report (without any filters) for example "By stakes" then summary data comes quickly. But those last 100 hands that appear in the bottom of holdem manager are coming with ~1-2 minute delay sometimes the hands are not even coming.

It all started after I purged all my Fulltilt hands (2M) and run under database maintaince->vacuum with options analyze and full were selected. I am playing on Stars right now.

When I turn on logging database command then I see from dbtrace.log file that:
09/02/12 14:02:48 HM COMMAND: select * from gametypes
09/02/12 14:02:48 HM COMMAND FINISHED: select * from gametypes
09/02/12 14:02:48 HM COMMAND: select gt.gametypedescription as "gametypedescription",(case when ....
09/02/12 14:02:48 HM COMMAND FINISHED: select gt.gametypedescription as "gametypedescription",.....
09/02/12 14:02:48 HM COMMAND: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin...

As you see the HM COMMAND FINISHED for the last query that displays hands did not appear. Also in Windows task manager I see that postgres is consuming 25% of CPU and if I take that las query and try to run it through PGAdminIII then it wont return output (doesn't hang but query is running endless). Also this action adds another process of postgres.exe that consumes 25% of CPU.

Full query:
select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion, GT.PokerGame,PH.holecard1int,PH.holecard2int,PH.ho lecard3int,PH.holecard4int,PKH.FlopCard1int,PKH.fl opcard2int,PKH.flopcard3int,PKH.TurnCardint,PKH.Ri verCardint,PH.flopplayerActionType_ID,PH.turnplaye rActionType_ID,PH.riverplayerActionType_ID,PH.NetA mountWon,GT.BigBlind,PH.PositionType_ID,PH.preflop action_id,PH.didpfr,PH.didvpip,(case when PH.StreetWentAllIn > 0 then (Select SklanskyBucks from AllInSituations where ph.playerhand_id = allinsituations.playerhand_id) else 0 end) as SklanskyBucks,(case when PH.StreetWentAllIn > 0 then (Select EquityPct from AllInSituations where ph.playerhand_id = allinsituations.playerhand_id) else 0 end) as EquityPct,getwinningplayerjoincashver2(ph.pokerhan d_id) as winningplayerjoin from playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) join pokerhands_hero pkh on pkh.pokerhand_id = ph.pokerhand_id join playerhandscashmisc_hero phmisc on phmisc.playerhand_id = ph.playerhand_id join gametypes gt on gt.gametype_id = ph.gametype_id left join playerhandsflop_hero flop on ph.playerhand_id = flop.playerhand_id left join playerhandsturn_hero turn on ph.playerhand_id = turn.playerhand_id left join playerhandsriver_hero river on ph.playerhand_id = river.playerhand_id left join allinsituations_hero ev on ph.playerhand_id = ev.playerhand_id where (ph.player_id = 158041) and (ph.gametype_id = 1 or ph.gametype_id = 2 or ph.gametype_id = 9 or ph.gametype_id = 10 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 23 or ph.gametype_id = 24 or ph.gametype_id = 32 or ph.gametype_id = 38 or ph.gametype_id = 39 or ph.gametype_id = 40 or ph.gametype_id = 41 or ph.gametype_id = 42 or ph.gametype_id = 43 or ph.gametype_id = 44 or ph.gametype_id = 52 or ph.gametype_id = 53 or ph.gametype_id = 64 or ph.gametype_id = 65 or ph.gametype_id = 66 or ph.gametype_id = 67 or ph.gametype_id = 68 or ph.gametype_id = 69 or ph.gametype_id = 70 or ph.gametype_id = 75 or ph.gametype_id = 77 or ph.gametype_id = 78 or ph.gametype_id = 82 or ph.gametype_id = 85 or ph.gametype_id = 86 or ph.gametype_id = 87 or ph.gametype_id = 92 or ph.gametype_id = 94 or ph.gametype_id = 104 or ph.gametype_id = 123 or ph.gametype_id = 126) and ph.handtimestamp > to_timestamp('09/01/2012 02:14:35','mm/dd/yyyy hh24:mi:ss') and gt.pokergame = 1 and GT.GameTypeDescription = '$0.25/0.5 ZOOM NL' and GT.PokerGame In (1) order by PH.HandTimeStamp desc limit 100

netsrak
09-03-2012, 05:57 AM
The database may still be slow because some reorganisation is still running in the background after your purge.
Another thing you can try is a reindex of your database (same place in pgadmin where you did the vacuum, just select the REINDEX option).

ristorothe
09-03-2012, 10:31 AM
I purged hands some weeks ago. So all processes that worked background hould be finished right now. Also I reindexed database through PGAdminIII. But no help.

I restarted service, HM1 is shut down, I copied the query from dbtrace.log to PGAdminIII and run the query. Then I waited some minutes for output (that never came) and saw that in Windows Task Manager shows one postgres.exe on 25% CPU.

Patvs
09-03-2012, 05:28 PM
Update: http://www.holdemmanager.com/Downloads/HmUpdate_Release_1.12.11_26aug2012.exe


Which Operating System, Firewall, and Anti-Virus are you using?

Which PostgreSQL version are you using?

ristorothe
09-03-2012, 06:06 PM
Some time after occurred I did that update.
Windows 7 64bit
Firewall and Virus protection are managed through Microsoft System Center Endpoint. They were on and they are on.
Postgresql is 8.4

It is one query that drives postgres mad. Other queries are working fine.

netsrak
09-04-2012, 04:17 AM
Please try a backup / restore of your database via pgadmin.
If that fails I have no other ideas than creating a new database

ristorothe
09-04-2012, 04:19 AM
Restoring database took 6 hours and after that I quited... I have I think max 3M hands (with purged hands), so it should no take so much time.

Found a temporary solution. I removed 3 columns from Options->Settings->Hands->View columns:
- WinnerCards
- WinnerWon
- WinnerName

I can live without those...

Patvs
09-04-2012, 10:11 PM
Thanks for letting us know that solved the issue for you.
Removing the 3 columns is listed as a 'performance increase' tip at this thread:
http://forums.holdemmanager.com/manager-general/11194-top-10-hem-performance-increase-tips.html