PT3 performance tuning 2 HDDs

[guanie]

so postgresql installed on usb drive and pt3 on the laptop local drive?

my 2 db combined size is around 20 gb. the active db is around 10gb. should i have the active db on the local drive instead and the other db on the usb drive? will only be writing new hands on the active db, the other db is just for the hud.

[kraada]

If you want to have one database on one drive and one on another you'll need to use a Tablespace as explained earlier in this thread. If you want to put them both on one drive (whichever drive it may be) you can just install PostgreSQL to that drive and it should work fine (though obviously if you use the USB drive you'll need to have it connected any time you want to run PT3).

[manuausruben]

i tried to follow the steps on the first site, but i dont have the file "psql to template1" or "psql to 'postgres'".

i use postgres 8.4, want can i do?

[kraada]

There's another option to get you to the SQL shell; I've used it I just can't remember the name off the top of my head. What are the options you see in your start menu?

[manuausruben]

documentation
application stack builer
pgadmin III reload configuration
restart server
SQL SHELL (psql)
start server
stop server


"SQL SHELL (psql)" might be the right one. but after the step



"Code: Select all
\cd D: "

occurs an error

[kraada]

Don't put in the "Code Select All" bit, that's just to show you it's a code block; put in everything inside the code block. The SQL Shell is the one you want

[manuausruben]

Don't put in the "Code Select All" bit, that's just to show you it's a code block; put in everything inside the code block. The SQL Shell is the one you want

im not a "level 8" (-;

[Bangwhiz]

I believe I may have contributed the link to the Wiki on disabling simple file sharing in XP home a long time ago. Unfortuately the wiki must have changed because now, as one reviewer of the Wiki on disabling simple file sharing commented, it should be entitled "Setting File Permissions in XP Home".

Here is a simple procedure you can follow to Disable Simple File sharing on your XP Home computer that works. Restart your computer and boot into safe mode by pressing F8 during start up and selecting safe mode from the top of the list of options. Press yes when asked if you want to use Safe Mode. When it finishes booting in Safe Mode go to My Computer.

Select your C drive and then right click on "properties". I am referring to Drive C itself, not the view with all drive C's root folders. Then click on the Security Tab. Then click on your user name/Administrators which should be the first listing in the list of users. Check the box labeled "Full Control" and click apply. Your computer will then give you full control of all folders on Drive C. You may get a message "Not Responding" when you do this - do not click end now. It takes a while for the action to complete and Windows can interpret this as "not responding". When permissions have been changed on all your drive C folders the "not responding message" will disappear. Then Click OK and return to the "My Computer" screen.

If you are going to run your database on two disk drives follow this same porcedure on your D: drive, or whatever drive you are going to use to store your Postgres PT3 database. Then follow the rest of the procedures shown in the Postgres FAQ for installing Postgres on two disk drives.

[mrsfishnet]

Hey,

I followed the instructions in the second post in this thread and everything seems to be ok.
I only had to change the second code input i changed it in: "CREATE DATABASE pt3_newjan TABLESPACE spare_disk;
i also changed the 3rd code so it would match the 2nd code like this: "\connect pt3_newjan;

I changed it, because there were already some databases with that type of name from last year and some wrong attempts that time
It was just for me so i actually know which db will be the correct one with the tablespace.
If i am not allowed to do it like that; just say so!

So i finished everything i needed to do. so from now on all my hands should be stored on my other HD. which is perfectly fine for me.
BUT
I deleted all databases in pokertracker because i want to start fresh with the faster tablespace system.

I made a backup of my old DB and i want to load it into the new "tablespace database" here my problem arises.
If i use "restore button" it creates a new DB but not in the "tablespace DB" so it is still slow!

How can I import my old database into the new "tablespace DB"?
Do I really have to import them manually? I have about 2million hands, so it would take a long time.
Please let me know the fastest way to fix it please. and how to do it.

Thanks

[kraada]

No, you don't have to do that - you can actually move existing databases to your new tablespace, it's just a bit tedious. But it's a lot less tedious than reimporting everything.

Restore your database.

Then at the psql prompt after you've connected to your database run the following commands:

ALTER DATABASE "Your Database Name" SET TABLESPACE spare_disk;

that will set the global tablespace for that database to the new disk. Now we just need to move what already exists to the new tablespace.

\d will list all tables in the database. For each table in the list:

ALTER TABLE table_name SET TABLESPACE spare_disk;

Depending on the table size it can take a little bit to complete each table. Once all of those are done (you can cut some time off if you never play omaha or never play tournaments by not moving those tables if you really want), go into PT3 and run the Reindex from housekeeping. It'll take about an hour but I believe it'll move your indexes to the new tablespace as well - if you want to do that by hand, \di lists indexes - but I think you'll see why this way is easier

(Oh and you can call databases whatever you want - and if you called your tablespace something other than spare_disk, replace spare_disk with your name for it in the above commands.)