|
|
#1
02-16-2007, 03:10 PM
|
|||
|
|||
Making PT go VROOOOOOOOOOOM
Mods, this thread is being posted here because I had several requests from people in an SSNL thread.
Disclaimer: BACKUP your DB before making ANY significant modifications to your PT DB. I will not be held responsible for any loss of data. Repost from my blog: If you haven't upgraded to postgres, go here. Always back up your databases before making any significant changes. How to make a backup: Before fiddling around with any settings, back your database up. This can be accomplished by going to Utilities->PostGreSQL Database Functions...->Backup. The compression is insane. My 4 Gb database was compressed down to 230Mb, so don't be alarmed if you get super compression. The steps are pretty self explanatory. Be warned, the conversion process takes quite a while if you have a maxed out Access database. I'd recommend leaving it for over night. Assuming you've either installed postgres or already had it going, the rest is how to get the best performance out of your database. A database without size limitations can obviously get quite cumbersome as it gets large, so tuning performance is key. Basically, your ability to read your information from PokerAceHUD or GameTime+ will NOT be affected by PokerTracker's poor performance, however, your ability to manually look up information will be compromised as the database gets large. It can literally take over a minute on a decent system to load the player list. Hardware: (skip if you are already competent in this area) ************************************************** ********************************** First thing is first. The best way to increase any database performance is via hardware. In terms of hardware and where to spend your money, you want to buy a nice hard drive first, then memory, then a processor. The type and speed of your hard drive is the number one factor in how well your database will perform. Accessing information from a hard drive is the slowest operation in a computer's pipeline, so even a small percentage gain in speed will yield much larger time savings than if you were to upgrade your memory or CPU. Ideally you would want some super crazy 15,000 RPM SCSI drives in RAID 10. For the rest of us a nice 7200 RPM SATAII 16Mb cache hard drive will suffice. Next you want to make sure you have adequate memory. I run with 2Gb and a 3.2 Ghz HT processor. It's probably about par for the course as far as the average system these days. Just remember, when it comes to overall performance of your machine, memory is the best upgrade you can make. These gains in performance will trickle down to database functions, just remember that your hard drive is the biggest bottleneck when it comes to database speed. ************************************************** ********************************** Once postgres is set up Before fiddling around with any settings, back your database up. This can be accomplished by going to Utilities->PostGreSQL Database Functions...->Backup. The compression is insane. My 4 Gb database was compressed down to 230Mb, so don't be alarmed if you get super compression. Clustering As mentioned above, the hard drive is by far the slowest component in the information pipeline/heirerarchy of any computer system. That being said, if you can limit the times you go to disk to read or write data, you gain in performance. Clustering is a concept that involves putting data that is likely to be accessed simultaineously very near to eachother. When data is accessed from a hard drive, it is accessed in chunks, not just one item at a time. So when you load your player list in PokerTracker, you are grabbing many players at a time. Assuming you could predict which information was to be accessed first you could structure the database so that those items would be stored in sequential order on the hard drive, instead of being scattered, leading to the least amount of disk accesses possible. To make a long techno-babblish story short, some dude figured out a nice way to do this for us. His explanation isn't all that elegant, so let me break down what you need to do to get some nice performance gains from PT. Access the DOS command prompt: Go to Start->Run-> type in "cmd" -OR- Press windows key+r and type "cmd". Generally the windows key is the key to the right of the left CTRL button (has a Windows logo on it). -NEXT- Make sure you have the correct pathname for your postgresql psql application. Generally speaking it's going to be c:\program files\postgresql\8.0\bin\psql. THis is a utility that will help us accomplish our goal of clustering. Next, go into PokerTracker. Go to File->Maintain Database Names and see what the name of the postgres database is. IMPORTANT!!! This name is the name in the RIGHT COLUMN called DB File/DB Name/DSN. Confusingly it is NOT the name under the "Name" column. We're almost ready: Copy and paste the following lines JUST AS THEY ARE (quotes and all). Make sure you substitute YOUR_DB_NAME with the name found in the Maintain Database Names right-hand column. You do not need to copy and paste this line by line, just paste the whole thing into the command line and it will execute sequentially. These commands can take anywhere from 2 minutes to 1 hour to complete depending on the size of your DB. "c:\program files\postgresql\8.0\bin\psql" -d "YOUR_DB_NAME" -U postgres -c "cluster session_idx_02 on session;" "c:\program files\postgresql\8.0\bin\psql" -d "YOUR_DB_NAME" -U postgres -c "cluster player_winnings_idx_02 on player_winnings;" "c:\program files\postgresql\8.0\bin\psql" -d "YOUR_DB_NAME" -U postgres -c "cluster player_idx_02 on players;" "c:\program files\postgresql\8.0\bin\psql" -d "YOUR_DB_NAME" -U postgres -c "cluster game_idx_01 on game;" "c:\program files\postgresql\8.0\bin\psql" -d "YOUR_DB_NAME" -U postgres -c "cluster game_players_idx_05 on game_players;" Other Tweaks Congrats on getting this far. DBAs (Data Base Administrators) make [censored] bling doing some of this stuff, so don't be shocked to learn that it's sort of complicated. There are some other ways to get better performance out of the DB. Personally, I've had less success with these than I did with clustering, but none-the-less, if you are interested you can give them a go. First, it is important to note that many programs have .conf files. These are text files that have data that function as inputs to a program. This is what they did before the days of GUI programming and in cases where a nice GUI is unnecessary, the practice is still used. That being said you can access postgres' .conf file by going to Start->All Programs->PostGRESQL 8.0->Configuration Files-> (there should be 3). To learn more about postgresql.conf check out the link. This will walk you through what some of the settings do. Backup your databse before fiddling with any of them. If you are feeling confident, read this guide to tuning postgres performance via the conf file. If you have any questions, hit me up and I will do my best to help (I will be out of town all weekend). 
|
|
#7
02-16-2007, 03:25 PM
|
|||
|
|||
|
Re: Making PT go VROOOOOOOOOOOM
[ QUOTE ]
[ QUOTE ] [ QUOTE ] do you do housecalls? [/ QUOTE ] Yes, but it has nothing to do with IT. [/ QUOTE ] vpip/pfr/agf? [/ QUOTE ] 20/19/10000 TAG on the streets, LAG in the sheets.
|
|
#8
02-16-2007, 03:37 PM
|
|||
|
|||
|
Re: Making PT go VROOOOOOOOOOOM
Thanks, MasterLJ. I was planning on doing that cluster/defrag thing, but it looked more complicated than I felt like dealing with. I am gonn give it a try this weekend. Great thread!
|
|
#10
02-16-2007, 04:29 PM
|
|||
|
|||
|
Re: Making PT go VROOOOOOOOOOOM
make sure to update to the latest postgres too
|
 |
|
|
Linear Mode
