Handling extreamly large database, any advice?

[Kalledrengen]

Hey,

I'm a big newb in this area and would love if you could link to guide/explain any advice you may have.

- If I wanna load several million hands (datamined, not my own) into PT3, how should I go around this?

How much does 1 or 2+ million hands slow PT3 in generel when loading/checking out your own stats ect? Browsing the different option in PT3 I was wondering if "create new database" and "set active database" if that's something that should be used for datamined hands in large amount? If that would be a good idea a quick explanation on how to do it would be cool.

Again, I'm really clueless and haven't tried this before so just wanna make sure I do everything correct.

Regards

Kalle

/edit
Also, is there a faster way to load xml HH's? Quickly tried to load just a few to see and it said it would take like 3-4 hours to load (is a pretty huge hh amount though, like 500k+ I think). But I'm guessing I'm just gonna have to be patient? ^^

[Guest]

Loading about 150k HH cost me about 35 minutes, so 2 hours sounds reasonable for 500k HH's.

And basically the performance of the database is very dependent on the power of the CPU you are using. Single core high gigahertz processors perform better then dual core ones because PostgreSQL does not use dual cores, at least when you're using Windows XP or Vista. Linux x86 or Solaris SPARC based servers perform better because they have more advanced and efficient multithreading and memory handling than Windows 2003 server. All local installations (database and poker client on the same desktop) can suffer a lot from large databases because of the IO involved.

Basically there are too many variables to give one definitive answer, I'd say just give it a try and see what happens

[WhiteRider]

Your thought about importing the observed hands into a separate database is a good idea, though - then when you want to analyse your own stats in the main app you won't have such a large DB to deal with.
You can set the HUD to use stats from multiple databases (HUD Options tab of HUD stats config window).

[Kalledrengen]

Your thought about importing the observed hands into a separate database is a good idea, though - then when you want to analyse your own stats in the main app you won't have such a large DB to deal with.
You can set the HUD to use stats from multiple databases (HUD Options tab of HUD stats config window).

Sounds good that the HUD is capable of using stats from multible databases. The other stuff you hit spot on what I was thinking, just wasen't sure if I should create new DB ect, but now I know it's an option, thanks

gvdbilt:
I do use single core but only have I believe a 3000 ghz (or whatever it is). But if you say 150 takes 35 min for you then my times sounds alright aswell. I was just wondering if I had missed some option for loading large databases

Thanks for your replies guys, appriciated!

Kalle

[WhiteRider]

The general suggestion for large amounts of datamining is to have 'rolling' observed hands databases.
So, for instance, create a new database each month, and have the HUD using the latest 2 DBs and remove use of the older ones each time you start a new one.

[keggler]

because PostgreSQL does not use dual cores, at least when you're using Windows XP or Vista. Linux x86 or Solaris SPARC based servers perform better because they have more advanced and efficient multithreading and memory handling than Windows 2003 server.

i beg to differ, postgres will make use of a dual core for multiple incoming queries (although i don't know if the PT app exploits this...)

however, if only a single database connection is active, it normally can only use one core. postgres does not use multi-threading to allow a single process to fork into separate tasks.

postgres' memory/buffers/cache handling relies a great deal on the operating system's effectiveness in these areas... although there are parameters for the postgres settings that can be tweaked...

a good amount of RAM (2-4GB) and a fast hard-drive sub-system (SATA or UDM6) should see you ok on a database of this size.

scaling up the hard-drive sub-system to say, 2.5" SAS 15,000 RPM drives would probably be overkill mind you...

[Kalledrengen]

The general suggestion for large amounts of datamining is to have 'rolling' observed hands databases.
So, for instance, create a new database each month, and have the HUD using the latest 2 DBs and remove use of the older ones each time you start a new one.

Ah smart, didn't consider this, thanks.

Btw when I installed PT3 I just installed the PostgreSQL thingy on my C: drive which has very little space on it actually (10gb just for windows and a few programs ect). However I have noticed while loading hands into PT3 that the postgreSQL has a folder "C:\Programmer\PostgreSQL\8.3\data\base" where there is like 5-8 folders with just numbers. Inside 2 of them are a lot of very big files and the "base" folder itself is already taking up 1.3 gb space.

My poker tracker is installed on the D: drive which still has plenty of free GB space so it's a little annoying that my C: drive is storing those big files because I'm soon out of space on my C:. Can I delete those files in the "base" folder or are they needed? I have the option "Move pocessed files to D:\programs\pokertracker3\processed" activated so I'm a little confused as to what those files in the "base" folder are. However that option is activated on the poker skins I play but perhaps not necessarily on manual import which I've done? I'm guessing the HH's I've manually imported are the ones that are taking up the space on the C: perhaps? I can't find them in the "D:\programs\pokertracker3\processed" but on the other hand the files in the "C:\Programmer\PostgreSQL\8.3\data\base" aren't xml files but just some weird files I dunno what is.

I hope you can understand what I'm saying as I it even sounds a little confusing to me, but do I need to reinstall postgreSQL on my D:driver in order to fix this problem or is there another way aorund it?

Thanks

[keggler]

those folders with just the numbers are the actual database datasets; your poker data - DON'T just move them manually to the d:\ drive...

you need to manually create a new database on the d:\ drive and then re-populate it with the data you have accumulated in the c:\ database - yes all 1.2 gig of it...

then you can safely delete the c:\ database and reclaim the space...


susan

[Kalledrengen]

By manually creating a new database on the D:\ drive I can see 2 options.

1. Reinstall the PostgreSQL and install it in on the D:\ drive this time.
2. Putting the datamined hands into one of my poker site HH folders, since it's only via auto import I'm capable of selecting where PT3 should place the processed files.

I'll try option 2 later today I think and then just change the "move processed file to:" option so that I keep the datamined hands in different folders aswell. Just gotta remember to switch back to the original folder when I start playing so I don't have my own HH's all over the place

[WhiteRider]

The hand history files stored in the 'move processed files to' are nothing to do with your database (which is the 'number' files in the postgres folder, and which you shouldn't touch).
This is just to stop PT3 having to re-read files it has already processed next time you start auto import.
You don't NEED to keep these files, but they are useful as a backup, so you could zip/compress them and store them somewhere safe.

As susan said, you should NOT do anything to the files in the postgres folders.

If you want to move your databases to another drive, have a look at the links at the end of the Database / Postgres FAQ.
To my mind, if you have all the processed hand history files the simplest way to do this is to uninstall postgres and re-install it on the other drive, then re-import all your hand history files. If you do this you should use MANUAL import as it is MUCH faster than auto import. You can just import from the 'processed' folder. Total uninstall/reinstall of pt3 (you don't need to uninstall PT3, but this explains how to completely uninstall postgres)

However, one of the postgres experts said a while ago that the best way to do it is to leave postgres on your c: drive and just tell it to create databases on the other drive - I think that is in the thread linked at the end of the database FAQ I linked above.