Sudden DB Slowdown

[evanski]

Hi,

My PT4 DB slowed down considerably at some point in the last 48 hours. Hand import and HUD functions seem normal and unaffected. But applying quick filters to the graph page is now incredibly slow to respond and load (similar outcomes with reports, etc).

It's a large, 24GB database. Previously, I'd open the graphs page and apply a quick filter (or a non quick filter), and it would be relatively instantaneous if filtered for today, might take 5 seconds or so to load when filtered for a month, and might take 10-30 seconds to load if filtered for the most recent year (~200k hands). Now it's 10 seconds, 1 minute, and 10+ minutes.

The change seemingly happened overnight. I haven't updated PT4 or changed anything that I'm aware of.

Hardware is the same as it's always been. I have 30GB of free harddrive space. I've tried:

Restarting PT4
Restarting computer
Running PT with no other software or computer demands
Vacuum/analyze function
Full cache rebuild

None seemed to have an effect. I also created a new database and imported only the most recent 12 months of hands (resulting in a 7.5GB DB size), and the problem remains.

What can I try next?

[evanski]

Update: I also cleaned up my hard drive and opened up a significant amount of disk space to ensure that wasn't the bottleneck. No change.

[evanski]

Update 2: Clustered and reindexed, no change (both processes completed pretty quickly, inside of 20 minutes, in case that's significant).

Also tried tuning postgresSQL.

And I should note that I have task manager open while I'm trying to get PT4 to do stuff, and the computer doesn't seem in any way strained -- CPU, memory, disk, and GPU are all at or near baseline levels, and I can complete other tasks in other programs without issue simultaneously.

[Flag_Hippo]

What version of PostgreSQL are you using?

[evanski]

Version 9.0.10

[evanski]

Update 3: I updated pokertracker to the latest version, backed up my databases, uninstalled and reinstalled postgresSQL to version 16, and recovered the databases. All of that went smoothly, and relatively quickly, but the slowdown issue remains and is unchanged.

Any other thoughts or ideas?

It's not just filters, any sort of data loading is incredibly slow. Just trying to pull up a graph of the last year of tournaments and profits (with all EV options and such disabled) takes several minutes.

I also tried backing up the database and recovering it on my laptop, which has far inferior tech specs, and it runs fine.

[Flag_Hippo]

Strange issues like this can be caused by security software interfering with PostgreSQL so I'd recommended temporarily uninstalling any in use, restarting your computer and then seeing if things are back to normal. If that doesn't change anything turn on 'Enable Logging' in PokerTracker 4 via 'Configure -> Options' and then restart PokerTracker 4. Next reproduce this issue by loading a report in PokerTracker 4 and when that populates create a support bundle via the 'File -> Create Support Bundle' menu option in PokerTracker 4. When that file has been saved attach your support bundle to a Support Ticket where a technician can follow up with you. In addition to the support bundle please also attach your 5 latest PostgreSQL log files to the ticket from here:

C:/Program Files/PostgreSQL/16/data/pg_log

Guide: PostgreSQL log files

[evanski]

There's no security software (other than whatever is natively built into windows).

A strange turn of events. I turned logging on to create the requested support bundle. The first graph/report I loaded took the usual 10 minutes.

Every graph/report since then has loaded in the (previously) typical 5-10 seconds.

It seems fixed?

I don't really understand what happened, but I'll hold off on submitting a ticket for now.

The issue was persistent across multiple restarts, different versions of PT4, different versions of SQL, and a lot of troubleshooting efforts. Is there some way that turning the logging setting on could have caused the change?

[Flag_Hippo]

I don't see why that would change anything but it's possible that the regular shortcut isn't running with the same permissions as the logging enabled shortcut on that computer.