Optimizing PT performance for BIG databases...remote database?

[jukofyork]

[ QUOTE ]
[ QUOTE ]
I think the main problem with PT is that their is some fundamental problem with it's design (either in it's schema or in the use of PowerBuilder code for it's parsers).

I agree that when PT was first designed then the sheer amount of data generated by datamining was never foreseen, but there seems to be very little work aimed at improving this aspect over the last 2 years.

The PAHUD and SS meta databases seem to be able to get round the slowness of retrieval, but by far the most frustrating part is the time it takes to import the hands (plus the strange exponential performance decay).

I have several million hand histories now collected and cannot face importing them into PT because of the stupid amount of time it would take to do this. The last time I tried, I borrowed a cutting edge machine (with raid drives and extreme amounts of memory) and it took something like 3 days to import 700k hands into a PGSQL DB. Then when I moved it back to my machine it was totally unusable and I had to give in (pre meta databases).

I then moved to using PokerManager. Since using PM I quite happily imported and used a 3 million hand DB without any of the problems I had with PT (no exponential import times, no machine lockups for 40 mins, no unusable DBs, overall 3-4x faster imports, smaller DB size, etc).

A) If more people were to look into using PM then I'm sure it would in turn become more popular and Ben (the SS author) would be more willing to work on it and update/improve it. Ultimately we would all benefit from this, yet everybody seem to be happy to keep using PT with all of the flaws.

B) Alternatively (if it is within copyright law), I am sure somebody could use the PT schema (if it is not the schema itself which is flawed...) and write much faster parsers for getting the data into the DB in the first place.

C) If outside of copyright law or the schema itself is fatally flawed, then perhaps somebody could sit down and completely redesign a "database" or schema. I'm not even convinced that using commercially available DBs is necessary and just creates unnecessary bloat.

Poker Tracker may have cornered the market when it comes to personal hand history analysis, but I feel there is a HUGE market (of frustrated PT users) just waiting for a decent solution to observed hand history import and retrieval.

Juk [img]/images/graemlins/smile.gif[/img]

[/ QUOTE ]

PM looks good; I'll keep it in mind for some use in some of the analysis I hope to do on the larger databases. Do you know if there are any plans for any HUDs in the future to use/support it?

[/ QUOTE ]
I think it comes down to the fact that Ben spent ages getting it to the stage where it is now, but there was just an overall lack of interest in it (I agree it needs polishing and can be improved on, but on the other hand I can understand that if their is no real PM user-base then their is no incentive to do this...).

I looked into it and did manage to figure out how to query the FireBird DB from C/C++ and it looks like it wouldn't actually be that hard to get GT+ to use the PM DB rather than the PT DB (I have some code snippets that will let me send queries to the DB and Ben published a list of helpful queries in the PM forum to help).

I think it's a chicken and egg problem. Everything supports PT because it got in their first, so they are tied to using PT. Nobody wants to give up their tools which rely on PT, but I'm pretty some nearly everybody would appreciate being able to handle much more data without all the problems associated with PT.

Juk [img]/images/graemlins/smile.gif[/img]

[Entity]

juk,

Do you use a HUD at all? I saw that PM had one included, but people seemed to be complaining about how well it worked. With a HUD, it seems like it'd be quite a good product.

Rob

[jukofyork]

[ QUOTE ]
Do you use a HUD at all? I saw that PM had one included, but people seemed to be complaining about how well it worked. With a HUD, it seems like it'd be quite a good product.

[/ QUOTE ]
Yep, I did use the PM HUD but agree it needs some work (and is Party only atm), but I'm not really one for using vast amounts of stats whichever HUD I use (VPIP/PFR/AGR/WtSD/#Hands).

I'm pretty sure if more people tried PM and took an interest, then it could develop into a much better product than either PT (or PO) for dealing with large amounts of data.

Juk [img]/images/graemlins/smile.gif[/img]

[_dave_]

Hi all,

just found this interesting tit-bit on the PT Postgres forum:
http://www.pokertracker.com/forum/viewtopic.php?t=15282

Good news: Looks like PT import may get near a 50% boost in speed if this is accurate - I just made some small import logs myself and it looks accurate, but I didn't really do a great deal of study on the matter. It was only posted today, but it seems like a fairly simple change for PT Pat to make [img]/images/graemlins/smile.gif[/img]

Bad news: I'm gonna have to go in to work tomorrow and scour the database functions we use and purge all occurences of SELECT MAX(..., fun times [img]/images/graemlins/smile.gif[/img]


Network setup - FWIW I think ideal setup would be three computers on the gigabit LAN

Computer 1 - Playing machine, decent spec - (Your current playing machine sounds overly capable). PT installed but only for session review etc. No PT imports runnning on this machine, HH folder shared on network. use VMs to mine hands with space capacity.

Computer 2 - Postgres server, Linux? decent CPU, fast disk (iRAM?), plenty fast RAM. Prolly can run a VM or 2 on this also.

Computer 3 - PT import machine, Very fast single core CPU (Maximum GHz, PT won't use a second core - overclock the P4 maybe?), and pull played/mined hands from network share, import to DB on Computer 2.

Would likely be a nice setup [img]/images/graemlins/smile.gif[/img]

dave.

[Entity]

[ QUOTE ]
Hi all,

just found this interesting tit-bit on the PT Postgres forum:
http://www.pokertracker.com/forum/viewtopic.php?t=15282

Good news: Looks like PT import may get near a 50% boost in speed if this is accurate - I just made some small import logs myself and it looks accurate, but I didn't really do a great deal of study on the matter. It was only posted today, but it seems like a fairly simple change for PT Pat to make [img]/images/graemlins/smile.gif[/img]

Bad news: I'm gonna have to go in to work tomorrow and scour the database functions we use and purge all occurences of SELECT MAX(..., fun times [img]/images/graemlins/smile.gif[/img]


Network setup - FWIW I think ideal setup would be three computers on the gigabit LAN

Computer 1 - Playing machine, decent spec - (Your current playing machine sounds overly capable). PT installed but only for session review etc. No PT imports runnning on this machine, HH folder shared on network. use VMs to mine hands with space capacity.

Computer 2 - Postgres server, Linux? decent CPU, fast disk (iRAM?), plenty fast RAM. Prolly can run a VM or 2 on this also.

Computer 3 - PT import machine, Very fast single core CPU (Maximum GHz, PT won't use a second core - overclock the P4 maybe?), and pull played/mined hands from network share, import to DB on Computer 2.

Would likely be a nice setup [img]/images/graemlins/smile.gif[/img]

dave.

[/ QUOTE ]

3 comps is overkill, unfortunately, for my office. I'll probably just have the Import comp be the same as the SQL server unless there really seem like there will be issues with doing that. Having the SCSI 15kRPM HD should really help speed things up I think.

Rob

[rvg72]

[ QUOTE ]
and write much faster parsers for getting the data into the DB in the first place.

[/ QUOTE ]

Brilliant idea. I'll start tonight.

rvg

[_dave_]

[ QUOTE ]

3 comps is overkill, unfortunately, for my office. I'll probably just have the Import comp be the same as the SQL server unless there really seem like there will be issues with doing that. Having the SCSI 15kRPM HD should really help speed things up I think.

Rob

[/ QUOTE ]

3 comps is indeed a bit of overkill - so long as the play computer is not doing the import I expect it will be smooth runnings [img]/images/graemlins/smile.gif[/img]

dave.

[Spechel EDD]

I'm kinda having the same problem. After I start up I get this error constantly Import Finished With Errors - The import finished but there were errors. Open the Utilities/Error Log Display/Maintenance window to see the errors - 114 file(s) read - 0 new hand(s) were imported.

should I just purge all the errors or should I be doing something else?

Also when I try to compact the database it it starts making a backup of the database and then the error comes up: Compact failed! Make sure you dont have the database open in another program (like Gametime+ or PokerAce Hud)

I dont have any of those things open and I even have party closed. What should I do about this? This is my first database and I'm still learning everything about PT. Thanks for any help.

[Entity]

[ QUOTE ]
[ QUOTE ]
and write much faster parsers for getting the data into the DB in the first place.

[/ QUOTE ]

Brilliant idea. I'll start tonight.

rvg

[/ QUOTE ]

This would be nice.

[Shroomy]

[ QUOTE ]
I think the main problem with PT is that their is some fundamental problem with it's design (either in it's schema or in the use of PowerBuilder code for it's parsers).


[/ QUOTE ]

It appears that there is a leak in their code, I think they are not po0ling their db connections or not releasing them them somehow. There is a limit where it just hits a wall and db connections and memory goes up and performance goes down, but I haven't really figured it out since i cant do anything about it anyways.

The schema's allright, a few little things that get into any software, especialy when you figure they added on sites and functionality with upgrades.

There are lots Id do differently, but when I look at any db that I designed, by the time I get to release, Id say the same thing. BUT if just a few indexes and constraints were added i think it would help a lot, but you would have to know the exact t-sql the PT client uses.


enough ramblings .. thanks juk for the link (again)