How can I check housekeeping progress?

[Russelldust]

I have a few questions with regard to my new PT setup, with PostgreSQL running on a dedicated server.

1. Housekeeping seems to be taking MUCH longer since I moved my server to another room and communicate with it wirelessly instead of via ethernet cable. (As I write this, it's been 'updating cache' for over 3 hours - it used to be more like 1 hour.)

Does it strike you as peculiar that updating the cache should take longer over a wireless connection? (I.e. is it possible I temporarily lost my client-server connection and that has messed things up?)

2. Is there a way to find out whether anything is actually happening 'behind the scenes'? E.g. Can I issue a PostgreSQL command on the server that will tell me what 'stuff' it's currently doing / who's connected / etc?

3. A few days ago, I left my PC to perform housekeeping, but I *think* the (client) PC may have tried to go into standby after I left it for a certain amount of time. I think this caused the network connection to be interrupted, and when I returned to my PC the housekeeping seemed to be continuing indefinitely, and I had to force PT to shut down.

This has made me wonder: is the housekeeping process tolerant to connection loss? E.g. if I communicate with the server wirelessly and have a 'blip' in my connection for whatever reason, could this cause problems?

4. Please can you explain roughly how housekeeping works in terms of the client/server relationship? E.g. is it very much 'hands off', with PT essentially issuing a single 'update cache' command, then simply polling the server to see if it's completed?

[kraada]

(1) If there's significant latency it might matter; are you getting significant numbers of dropped packets via the wireless when pinging your dedicated server? The reason it would matter is that PT3 needs to send four queries to the database server per player - they're not large in absolute terms, but if those packets are getting dropped I don't know how quickly PT3 retries so you might get some delay that way.

(2) Yes, if you up the logging in the postgresql.conf you can use tail and the last file in the pg_log folder to see what's going on - so long as queries are streaming in, your network is not a problem. (For players with large numbers of hands actually crunching the numbers can indeed take a while though; but if it's doing that you should see cpu usage for postgres pretty high - I'd use top for something like that, though if your system isn't Linux you might have better options.)

(3) What stage was it on? If it was on Cluster, Vacuum or Analyze that's run entirely by PostgreSQL - again you can check those logs to see whether the query actually finished or not. (I think even with default logging cluster, vacuum and analyze should be logged, but I'm not 100% sure - I turned up my logging settings a while ago and haven't ever turned them back down; it's too useful to keep it higher.) Literally all that happens during cluster is that PT3 tells PostgreSQL to run the CLUSTER command. There's no more data that goes from PT3 to PostgreSQL until that's done. So if you lose connection it should still go along happily until it's complete.

(4) For C/V/A PT3 sends the appropriate command (CLUSTER, VACUUM or ANALYZE), and that's it. There are a few changes to the structure for the next release that actually should expedite things (some redundant table clustering was removed, we're switching from vacuum full to straight vacuum) so that should be a nontrivial boost for large databases. For update cache though, PT3 needs to send the query for each player for each type of game (holdem, holdem-tourney, omaha, omaha-tourney), so if the network connection got severed, the cache update wouldn't complete and problems would ensue (which would require completely recaching). There are plans for upgrading the update cache function to improve speed and functionality but we are still a little ways away from that.

[Russelldust]

Thanks for the detailed answers.

Well, the log IS being updating periodically, and postgres.exe looks busy on Task Manager, but it's now been 'updating cache' for 7 hours!

I guess it must be a latency issue. I don't get any dropped packets when pinging, but the ping response time is definitely higher than the <1ms it takes to ping something connected by ethernet cable. Ho hum!

I suppose the only way to speed things up would be to install PT on the server as well, and use Remote Desktop whenever I want to perform housekeeping....? Any other suggestions?

Apart from updating the cache and performing imports, are there any other operations that PT performs that are dependent on the latency of the connection to my server?

I'm a tiny bit perplexed as it was suggested here that auto-importing was the only operation where my network connection / latency could prove to be the bottleneck.

Thanks in advance...

[kraada]

7 hours of updating cache is not actually outside the realm of possibility, depending on your database size. I updated a cache on a 15.1M hand database a couple of weeks ago and it took 30 hours. Beta 30.1 introducing omaha and tourney omaha did cause things to take longer - we've optimized a bit to speed things up significantly when there are no omaha (or tourney omaha) hands and that improvement will be available in the next beta.

When you say updated periodically do you mean once a minute, once every 5 minutes, once every 10 minutes . . .? Depending on how many hands you have on players, updating once a minute isn't necessarily a problem. If postgres.exe is working hard while it's not updating that means it's calculating the stats for that player - once the stats are calculated the next player is updated. If postgres.exe was dormant and there was no log update, then I would be worried. Based on your description though, I think it's just the cache update taking a while.

You can certainly install PT3 on your server and run cache update manually via remote desktop to compare though; I would be interested to hear the results of the comparison.

[Russelldust]

What I'm saying is:

Ethernet connection to server --> cache update takes ~1 hour
Wireless connection to server --> cache update took 8 hours

After cache update completed, I tried auto-import over the wireless connection, then stopped import, and now PT has ground to a halt. It imported my files (eventually), but when I told it to stop import (hoping my files would now be moved to the 'processed' folder on my server) ... nothing has happened. In the Import Status window it says 'Bodog: Stopping auto-import' and that's it. I've left it overnight, but none of my hhs have been moved.

PT is knackered now! I can't shut the application down. Clicking the 'X' has no effect (however it hasn't truly crashed, in that the 'X' still changes colour as I hover the mouse over it). If I try to go to a different tab (other than 'Auto Import') it's like I'm running it on a ZX Spectrum - the screen updates a tiny bit every few minutes. Do you think it will be safe to force PT to shut down? It's as though it's doing something behind the scenes...

I have looked in my logs and there's a load of stuff like this (- I'm guesing it's from when I either started or stopped auto-import?)

Code: Select all

2010-01-15 03:43:47 GMT STATEMENT:  COPY tourney_holdem_hand_summary FROM STDIN;
2010-01-15 03:44:05 GMT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2010-01-15 03:44:05 GMT LOG:  unexpected EOF on client connection

However there's nothing in the log since 03:44 (it's now 14:43).

[kraada]

Do you have any sort of firewall set up on the server that could cause the connection to be rejected? Normally that error is a firewall issue.

[Russelldust]

Generally though, there's no problem. The server doesn't 'know' whether I'm talking to it wirelessly or via ethernet cable. (I've been using a hardware wireless access point, connected to the server via ethernet. So the server had the same IP address etc, regardless of whether I was talking to it via ethernet or wifi.)

I've just moved my server back into the same room, plugged it directly into my ethernet switch, and suddenly it runs like a dream.

Even if my wireless connection is a bit shoddy (admittedly network latency looked quite bad - no dropped packets, but ping times >40ms) would you really expect PT to go so utterly pear-shaped?

[kraada]

I didn't think it would matter nearly that much; I am definitely surprised. If keeping PT3 on the server and running housekeeping via remote desktop is a workable solution for you, that may be the easiest way to go (no worries about network latency there!). Updating the cache is definitely the most query intensive part of PT3 though - running the HUD take a lot fewer queries (1 per player per table) and the queries are a lot smaller (as it only needs to get stats your HUd uses), and everything not HUD related is one query getting passed back and forth.

[Russelldust]

...but like I've said, auto-import was also very slow (and indeed PT gave up the ghost when I clicked 'stop import').

So you definitely don't think my biggish wifi latency (>40ms pings) should cause any problems with importing a hundred or so histories when I click auto-import, and then cause major tits-up-ness when I stop import?

For now I've got the server in the same room, but I'll report back if I try wifi again.

[kraada]

If you're importing 100+ histories, you should be using manual import, not auto import, regardless of network configuration. Auto import will choke on too many hand histories - that's what manual import is for. Have you tried manual import via your wireless?