Remote Postgresql - hand import extremely slow

[muffinbubble]

Hi,

I'm running PokerTracker 3.08.
Postgresql version as what is installed by PokerTracker.

I own quite a few computers and wanted PokerTracker to be installed on just one machine so I could remotely access it from any PC and retrieve and update my stats. That was the plan anyway.

I set it all up for remote connections and after a few hiccups I got it to connect just fine.

The performance doesn't seem to bad when flicking through the tabs and viewing graphs etc but the hand import is extremely slow. It does about 1 hand every 6 seconds (and that's if I'm lucky).

Whether I do the auto import or import from file/directory the problem is the same.

If I connect to my server and do the import it is almost instant. There are no bandwidth issues etc and the performance on my server is absolutely fine. There are no firewalls etc causing any issues either.

Why is it running so slowly remotely?! I have configured the conf file for Postgresql to allow for more memory etc (although I'm pretty sure this is unnecessary as locally it's instant) and still no good.

I also tried enabling logging - after checking the logs no errors are returned and it seems to run fine, just extremely slowly.

So to summarise, from any computer remotely connecting to the PokerTracker database, the auto-import/hand import is very slow (less than 1 hand per 6 seconds). When on the server locally, it is instant.

Edit: Also I've seen some suggestions about DNS causing issues, this isn't the case either. I've ran several checks on the IP and everything resolves correctly.

Another edit: The database is pretty small too and I've ran vacuum/housekeeping several times - all with no improvements.

Thanks,
Ricky

[kraada]

Network latency is non-trivial for this sort of thing - what kind of network are these computers connected to?

[muffinbubble]

Thanks for the reply.

It is running on a Windows VPS, running on 100MBPS.

Connectivity is fine and the network flies, so I don't think this is the issue. I guess I could try setting it up on another computer on the LAN as a test to see if it's the VPS causing issues, but I'd still like to know the reasons behind it.

Oh, and the computers that are connecting to the VPS are running on a 2MB line. I know that's not the fastest, but when checking our router data flow monitor, hardly any data is exchanged. It's almost as if remote Postgresql isn't using any of the available bandwidth.

I'm not too experienced in Postgresql, but I've used MSSQL several times and remote connections are almost instant, even on our 2MB line and I have MSSQL databases on the VPS running fine.

[kraada]

There aren't any settings that I'm aware of that would make PostgreSQL use more or less bandwidth but I'll look around and see what I can find.

[muffinbubble]

Hi,

Thanks for that.

Well, I just tried running PokerTracker on another computer on our LAN.

It was instant which would imply it is a network issue. I can't understand how PostgreSQL can be so slow remotely vs MSSQL. Any more info on this would be appreciated - to me a hand import shouldn't even be that database intensive, inserting a few hundred rows or querying the database should not be a problem when remotely connecting to the VPS is near instant. I'm confused.

[kraada]

I'm afraid that aspect of PostgreSQL isn't something I have a lot of experience with, so I don't know what would cause the slowdown. You could try asking someone in the PostgreSQL community - you might have better luck there. If you do figure it out (or find a way to speed things up) please do let me know, as I haven't been able to find anything useful on this issue for you.

[muffinbubble]

Ok, I can take a look on that forum. I've done lots of research but not found an answer either.

Ideally I need someone (volunteer please ) to give my VPS details too who are on a nice fast line to see if they experience the same issue. Unfortunately I live in a crappy town with really bad internet speeds.

I can't think what else to do at the mo, but if I come up with anything new or find a fix I'll post.

It does only seem to be the import that's noticable though, when viewing the players in the database it takes a few seconds to retrieve all of them, about the same time it would locally.

[kraada]

That makes me wonder if the issue is with COPY . . . I use a local network for my own setup and it works fine but that's just because Windows is in a VM and I prefer to keep PostgreSQL in Linux. But I haven't tried much using the wireless. I'll make a note to try and give that a whirl and see what happens.

[muffinbubble]

Tomorrow I'll be at work and try another import. It will be a good test as currently I'm connecting wirelessly - at work I'll be on the LAN, again on a 2MB line. However, I'll be on a different network so it can eliminate any possible issues here.

We also have another 8MB line which I'll sneak on to and quicky give it a go. If the hand import quadruples I'll know the problem.

Will be unfortunate if I cannot run PT on a VPS, but what will be will be.

[kraada]

Please keep me posted.