hosuekeeping after Import running really long??

[Kuroth]

Hi all... I imported about 2M hand hist's I had and I left all the Housekeeping boxes checked... So far, just rhe first step (Clustering) has been running now over 11 hrs.... Is this normal??? How long wil the other steps take??? How ling should it run(I have a newere PC etc etc...) Not talking like old Hardware... Nothing else running now but Housekeeping..

I onaly had about 100,000 rows in my DB before the Import... But still 2M really not that big... How long will houskepping take when I get to 20M????

[WhiteRider]

The housekeeping process can take a while on a large database - we can't display an estimate because it's a postgres process, not managed by PT3.
It will probably take a few more hours but I suggest that you let it finish - unoptimized database are significantly slower.
How long it takes really depends on your system, but one thing that will cause it to take significantly longer is if there is not enough space on the hard disk for a complete copy of the database (this means that postgres has to do lots of reshuffling).

If you're going to be importing a lot of obvserved hands you might find this useful - Tutorial: Managing Multiple Databases.

[Kuroth]

Well ok... Its been running now for 24 hrs???? Still just on the first step of housekeeping... That can be right??? Any other suggestings??? I mean I have been down and unalbe to use PT3 now going on 2 days.. Kind of disappointed as this is only 2M hands.. So will House keeping take 3 weeks to run when I have 10M????? I cant afford to be down this long and did not realise PT3 cant handle this kind of data(From the Housekeeping side)

[Kuroth]

I am seeing a lot of this below in the Log when house keeping is running.... Is this normal???? Also my first run of HK never finishd.. I killed it and now have started over.. How many days should I give it???


2010-01-05 16:51:21 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:51:47 CST LOG: checkpoints are occurring too frequently (26 seconds apart)
2010-01-05 16:51:47 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:52:15 CST LOG: checkpoints are occurring too frequently (28 seconds apart)
2010-01-05 16:52:15 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:52:42 CST LOG: checkpoints are occurring too frequently (27 seconds apart)
2010-01-05 16:52:42 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:53:11 CST LOG: checkpoints are occurring too frequently (29 seconds apart)
2010-01-05 16:53:11 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:53:36 CST LOG: checkpoints are occurring too frequently (25 seconds apart)
2010-01-05 16:53:36 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:55:12 CST LOG: checkpoints are occurring too frequently (28 seconds apart)
2010-01-05 16:55:12 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:58:21 CST LOG: checkpoints are occurring too frequently (27 seconds apart)
2010-01-05 16:58:21 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:58:50 CST LOG: checkpoints are occurring too frequently (29 seconds apart)
2010-01-05 16:58:50 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:59:17 CST LOG: checkpoints are occurring too frequently (27 seconds apart)
2010-01-05 16:59:17 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 16:59:44 CST LOG: checkpoints are occurring too frequently (27 seconds apart)
2010-01-05 16:59:44 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 17:00:13 CST LOG: checkpoints are occurring too frequently (29 seconds apart)
2010-01-05 17:00:13 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 17:00:42 CST LOG: checkpoints are occurring too frequently (29 seconds apart)
2010-01-05 17:00:42 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 21: "1218095 800 8 134 1818903828 2009/12/27 00:13:18 2010/01/05 17:02:00 9 0 0 0 8.08 0.00 0.00 0.00 1.0..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 25: "1218100 800 8 134 1818903332 2009/12/27 00:12:52 2010/01/05 17:02:00 9 0 0 0 8.33 0.00 0.00 0.00 1.2..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 32: "1218108 800 8 134 1818902390 2009/12/27 00:12:01 2010/01/05 17:02:00 9 0 0 0 8.83 0.00 0.00 0.00 4.2..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 38: "1218115 800 8 134 1818901719 2009/12/27 00:11:26 2010/01/05 17:02:00 9 0 0 0 8.83 0.00 0.00 0.00 1.0..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 45: "1218123 800 8 134 1818900715 2009/12/27 00:10:32 2010/01/05 17:02:00 9 2 2 0 9.50 10.83 8.83 0.00 9...."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 60: "1218139 800 8 134 1818898681 2009/12/27 00:08:44 2010/01/05 17:02:00 9 4 2 0 10.00 9.50 60.35 0.00 8..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 71: "1218151 800 8 134 1818897179 2009/12/27 00:07:27 2010/01/05 17:02:00 8 2 2 2 13.50 12.50 12.50 12.50..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 77: "1218158 800 8 134 1818896048 2009/12/27 00:06:28 2010/01/05 17:02:00 9 2 2 2 6.35 4.60 0.00 0.00 13...."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:01 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:01 CST CONTEXT: COPY holdem_hand_summary, line 84: "1218166 800 8 134 1818894916 2009/12/27 00:05:30 2010/01/05 17:02:00 9 2 2 2 6.35 49.50 48.25 46.25 ..."
2010-01-05 17:02:01 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:04 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"
2010-01-05 17:02:04 CST CONTEXT: COPY holdem_hand_summary, line 49: "1218523 800 8 133 1818845485 2009/12/26 23:21:46 2010/01/05 17:02:04 9 0 0 0 17.90 0.00 0.00 0.00 0...."
2010-01-05 17:02:04 CST STATEMENT: COPY holdem_hand_summary FROM STDIN;
2010-01-05 17:02:04 CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no"

[WhiteRider]

The "CST ERROR: duplicate key value violates unique constraint "hhs-unique_hand_no" means that you're importing hands which are already in the database and shouldn't be anything to do with housekeeping?

Kraada has been looking at the clustering process and may have a recommendation for you about changing the checkpoint_segments section, although I'm not sure just how much that will speed things up.

When the clustering process is running have a look in Task Manager > Processes and check that at least one of the postgres.exe processes is doing some work (to check that the cluster is still going).
For a large database, though, the initial cluster will take a significant amount of time - once it has been done it shouldn't take anywhere near as long in future because the existing information will already be pretty well clustered.
If you're going to be importing large amounts of observed hands you should consider using multiple databases as described in the tutorial I linked. Once a database has had housekeeping run on it you won't need to do it again if you don't import any more hands to it.

[Kuroth]

Thanks WhiteRider for the Info...

[kraada]

If checkpoints are only running too quickly during housekeeping it's not necessarily a huge concern, but you can set PostgreSQL to spread them out more by editing the PostgreSQL.conf (Start --> Programs --> PostgreSQL --> Configuration Files --> Edit PostgreSQL.conf) and increase the number of checkpoint_segments, and increase the checkpoint_completion_target. Upping the former from 3 to 10 or so will take more hard drive space but is probably worth it; increasing checkpoint_completion_target should spread out the amount of actual disk writing going on (I keep mine at .8 or so). If you're using PostgreSQL 8.4 you shouldn't need to change either of these settings though, they're deprecated in that version. See here for more detail on the configuration settings.

What is the speed of your hard drive? That may well be the overriding factor . . .

[Kuroth]

Thanks guys for all the Info and helping...

It did finally complete.. What I did was I killed both of the HK I had running and then(Not sure if this makes a difference for HK) I upped the Memory settings based on my RAM (4GB)in the Confg file per another post... Then I restarted both HK... On my New PC HK took 5 hrs to complete... On my Older PC it took about 11 hrs.. (Again this was for 2M rows imported all at one time) I can live with that and I am just glad it finished..

So a question... Now that I completed HK for the 2M new Rows, will Future HK after adding 100,000 hands run this long or should it be faster now that the 2M was Clustered, Etc???

How do you check the HD Speed and what is considered a good speed and what is considered slow?


Thanks!

[kraada]

Cluster will take a bit less time next time around, vacuum and analyze are fairly trivial in terms of time, and update cache will take longer.

I'm not certain off the top of my head of a way to check in software; you can probably find out what your hard drive model is via Device Manager or some such, at that point a google search for the device should tell you; you want to look at the RPM value for the hard drive. In shorthand, 5400 is bad, 7200 is okay, anything bigger is better, but they get expensive, and SSDs while still pricey in terms of the storage they give you are a big boost (and on an SSD you do not get any benefit from clustering so you can leave that step out on machines with SSDs).

[Eishoernchen]

I like to post my story, because it helps somebody else.

I had a lot of problems with housekeeping especially with updating cache. The last time i run it, it was running (and doing things in postgres) over a week. I thought that couldn't be the way it was meant to be.
After giving it up i spend some time investigating the tables in the pgadmin (a view tool of the postgres database for windows).
Finally i found out that : All Primary Keys in the Database weren't set. I was just to blind so read the explain of the failling SQL (insert into holdem cache select .........) that is was using temporary file sort. If i had notice this i might had the right clou ...
Well the problem was. I exportet my local Database to my online Database with the pgadmin backup and restore function. And i don't know why, but it didn't export the "constraints" line from the create table statements. So in fact all 30 Tables have no primary key.

I then created a primary key for "holdem_hand_histories" (first made the rows unique) and then exportet my whole database (split it up to two machines). After that i created a new Database through Pokertracker and exportet everything to the new Database.
Now Cache Updating is running like it should be. I can the see the percents of completion in the log file.

What a headdache! But all my fault ..

I suggestion for the Beta: provide the percent of progress in the housekeeping process in user-interface, that would be really helpful.

With regards