Imported 1.5 Million hands.. REALLY slow..

[kraada]

You wouldn't need to reinstall anything.

The best move would be to backup the database as it stands now, create a new database with a TABLESPACE using the new hard drive, then set your database to use that tablespace. It will take a while as it has to move all of the data over, but after that you should be all set.

See this post for most of the walkthrough - once the tablespace is created you should be able to just run the query:
ALTER TABLE 'DBName' SET TABLESPACE spare_disk;

(replacing DBName with your database name) and that should be all you need to do.

[genemish]

hmmm, i see...

how long is a "while"?

i really dont mind starting "clean".. i am thinking of creating a new DB on the new HDD (there is a way to point the location, right?) and then reimporting all hands...

thoughts?

[kraada]

That will work too.

To be honest, I don't know exactly how long it would take. At a minimum, it would take as long as it takes for your primary HD to read and your SSD to write all of the data in the database. If any extra processing needs to happen, it may take longer.

If you decide to just start clean, just follow the linked tutorial completely.

[genemish]

ok, strange things happening now..

Remember how it took 25+ hours (5 for import and 20 for housekeeping) to import 1.5MM hands (hence the thread).. during this process, i NEVER saw the postgres process even show up in the task manager...

Yesterday, i installed PT3 on my top of the line dell laptop (with SSD), and it took 5.5 hours for same process (2 for import and 3.5 for housekeeping) , during which the postgres process was going nuts in task manager.

soooo....

overnight, i received a 2nd batch (also 1.5mm) of hands to import and started on the SSD laptop first -- as expected, the postgres process was there all the time and importing took 4 hours and housekeeping is currently wrapping up (already on updating cache) and its taking a total of 8 hours. Yes, it's longer than 1st batch, but i am guessing that's because of presence of existing data which increases clustering size..

i started the same batch on the desktop (cause of this thread) and importing took 7.5 hours and clustering is at 3 hours at this point.. HOWEVER, i do not see the postgres process in the task manager..

---

my thoughts -- i must've botched up the PT3 and/or Postgres installation on my desktop. why is postgres process not showing up during housekeeping?????

my plan -- i did further research and figured that the WD VelociRaptor is very close in performance to SSD. especially dollar for dollar. So I will purchase this drive, remove PT3 and Postgres completely from my machine (uninstall, registry,etc)... and then reinstall everything on the new HD directly. (yes, i'll back up the "processed" folder and will reimport it after i am done)..

your thoughts?

[kraada]

That sounds like a good plan. I'm not certain why postgres isn't showing up in the processes tab for you - it certainly should be. Actually, what anti-virus/firewall software are you using?

[genemish]

That sounds like a good plan. I'm not certain why postgres isn't showing up in the processes tab for you - it certainly should be. Actually, what anti-virus/firewall software are you using?

Desktop (the machine with the issue) is Windows 7 with AVG antivirus

Laptop is Windows Vista (64) with AVG antivirus

both are using standard windows firewall.

I do have to mention an important observation that after PT3 was installed on the desktop and during the 1st attempt to import the 1st batch, i did notice the postgres.exe process (multiple processes, to be exact).... this is b4 the power outage which prompted for me to restart it and then this thread came to life ... eversince, i never see this process..

so i guess clean remove/reinstall is necessary.

[kraada]

It should certainly fix things and is probably the easiest solution at this point. AVG isn't known to cause any problems with PT3 (but others are so it seemed worth checking).

[genemish]

It should certainly fix things and is probably the easiest solution at this point. AVG isn't known to cause any problems with PT3 (but others are so it seemed worth checking).

ok, besides backing up the "processed" folder, please answer the following

1. how do i backup the newly created HUD (that i finally like!!!)
2. how do i backup the custom form "hands k"
3. any guides for step-by-step clean removal of PT3 and PostgresSQL? I will obviously remove the Add/Remove, reboot, clean any files under Program files, reboot again, clean registry of any items, reboot again -- anything else?

Gene.

[kraada]

Backup these files then after you uninstall and reinstall, restore them to their proper locations and that will backup your HUD, custom stats, custom reports and buddy list.

For reinstalling PostgreSQL, see this guide (and make sure you install PostgreSQL 8.3.8).

[woopwoop]

ok, strange things happening now..

Remember how it took 25+ hours (5 for import and 20 for housekeeping) to import 1.5MM hands (hence the thread).. during this process, i NEVER saw the postgres process even show up in the task manager...

Yesterday, i installed PT3 on my top of the line dell laptop (with SSD), and it took 5.5 hours for same process (2 for import and 3.5 for housekeeping) , during which the postgres process was going nuts in task manager.

soooo....

overnight, i received a 2nd batch (also 1.5mm) of hands to import and started on the SSD laptop first -- as expected, the postgres process was there all the time and importing took 4 hours and housekeeping is currently wrapping up (already on updating cache) and its taking a total of 8 hours. Yes, it's longer than 1st batch, but i am guessing that's because of presence of existing data which increases clustering size..

i started the same batch on the desktop (cause of this thread) and importing took 7.5 hours and clustering is at 3 hours at this point.. HOWEVER, i do not see the postgres process in the task manager..

---

my thoughts -- i must've botched up the PT3 and/or Postgres installation on my desktop. why is postgres process not showing up during housekeeping?????

my plan -- i did further research and figured that the WD VelociRaptor is very close in performance to SSD. especially dollar for dollar. So I will purchase this drive, remove PT3 and Postgres completely from my machine (uninstall, registry,etc)... and then reinstall everything on the new HD directly. (yes, i'll back up the "processed" folder and will reimport it after i am done)..

your thoughts?

some numbers after a recent re-import of all my hands:
reading from a samsung spinpoint F1 1TB hdd
writing to a western digital 300gb velociraptor

1st database:
importing 1.2M hands: 2h26m
housekeeping for that: 1h28m

2nd database:
importing 2.45M hands: 4h22m
housekeeping for that: 3h40m

3rd database:
importing 754k hands: 1h17m
housekeeping for that: 52m