Just more info:

I have determined it's not likely a report per se. I reviewed our logs in detail this weekend and there weren't any reports running that caused the slow down. However, there are some timing entries that show that a slow-down is happening (e.g. requests that are usually sub-second take 10-30 seconds during this time frame).

I'm going to need to add more logging or some other tooling to figure out what is happening.

Kevin

Kevin


The other thing that's been found to cause problems is dear old AV s/w. Check to make sure your database directory is left out of it.

I've just had a nasty thought - Windows updates - are they occurring at the time the problem manifests

Now we're moving away from it being a report my "suspicion tree" goes

h/w
data
s/w

If its primarily one site and others are running well then h/w may well be the problem - a duff NIC in the server can stuff things for all clients. A wobbly hard drive can also cause problems. It can try and read data many times before succeeding which can cause slow downs. Sometimes I've found edge cases in the data (or a bit of corruption that doesn't affect the table) which cause a nasty effect



Roy Lambert

Roy Lambert wrote:

>The other thing that's been found to cause problems is dear old AV s/w. Check to make sure your database directory is left out of it.
>

That's the first thing we do on install - and when anyone reports a slow-down. From our experience, the slow down is endemic to the whole system until we exclude that directory. I've not seen intermittent issues when A/V is on. (NOTE: We have also seen issues with incremental backup systems that cause issues with the database; and we disable those)

>I've just had a nasty thought - Windows updates - are they occurring at the time the problem manifests
>
Good thought. I'm pretty sure this customer does there updates manually, but will check.

>Now we're moving away from it being a report my "suspicion tree" goes

h/w
>
I try to NOT blame external forces until I'm really sure it's not something in the code. In all of my years developing, I've only had a handful of times when it was buggy h/w or even OS. Most other times it's "Whoops - look at how stupid my code is".

>Sometimes I've found edge cases in the data (or a bit of corruption that doesn't affect the table) which cause a nasty effect
>
Always a possibility.

Thanks Roy. Really appreciate the feedback!

Kevin

Kevin

"Whoops - look at how stupid my code is".

When did you see my code?

Roy

Hey Kevin, nothing to add... except 15GB and 100 users! WOW...

Biggest EDB installation I've heard...


Regares,
Mario

Kevin,

<< Is there any way to determine who has a particular table locked? We use the KeepTablesOpen = true property on the sessions, so looking at the ServerSessionsLock table shows everyone has the table locked (type='Shared'). We rarely use exclusive locks - generally only for schema updates. >>

If there aren't any transaction locks (LockType='Transaction'), then the issue isn't due to transactions, and the problem is elsewhere.

Are there any other types of locks present when you query this table ?

<< I've also experienced issues viewing the ServerSessionsLock table when executing a long running report. I get an error indicating that it cannot lock the session manager; so that makes it even tougher to see what's going on. >>

I'm aware of this issue, and am trying to come up with a solution for it.  The issue is that the session needs to be locked (internal lock, not a database lock) in order to capture all of the locking information, but if a session is taking a long time executing something on the server, then the lock cannot be acquired.

<< Another theory is that deadlocking is occurring.  Is there any way to detect a deadlock situations? >>

It's always possible that there could be a row lock deadlock.  Are you locking/updating/deleting multiple rows at various times ?

<< We are running the 64-bit server, version 2.24.0.2. >>

2.25 includes new tracing functionality in the server.  You can see how this is configured here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Starting_Configuring_Server

Just search for "trace" on that page.  You can configure the tracing so that it uses X number of trace files of X size, and then starts over.  The tracing is also designed to work under heavy load, and offloads the persistence of the tracing to a separate thread.  So, no worries if you've got a very busy system.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

>2.25 includes new tracing functionality in the server.  You can see how this is configured here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Starting_Configuring_Server

Just search for "trace" on that page.  You can configure the tracing so that it uses X number of trace files of X size, and then starts over.  The tracing is also designed to work under heavy load, and offloads the persistence of the tracing to a separate thread.  So, no worries if you've got a very busy system.
>
Thanks for the info Tim. And thank you for adding this functionality!

One _minor_ quirk, it looks like the trace file is "UCS-2 Little Endian" encoded. I've been using VS Code for my text editor and it doesn't like that format I have other editors in which I can open it (notepad!!), so no big deal.

Tim Young
Elevate Software
www.elevatesoft.com

Kevin,

<< One _minor_ quirk, it looks like the trace file is "UCS-2 Little Endian" encoded. I've been using VS Code for my text editor and it doesn't like that format I have other editors in which I can open it (notepad!!), so no big deal. >>

I'll make sure that it adds the Unicode byte prefix to the trace files.  I'm doing a new EDB build soon, so I'll include this change with it.

Tim Young
Elevate Software
www.elevatesoft.com

More information...

So we just experienced our 'slow down' again; and it appears that one session had a transaction lock on a set of app-critical tables; which affected editing obviously.

One thing I did notice was that the session was disconnected. My guess is that something occurred on the workstation so that the transaction did not complete. As I watched the sessions, the session eventually was released and shortly after, the transaction was released. Things returned to normal.

Here's my thoughts as to why:
* There might be some code that forgot to commit/rollback the transaction (checking on that now)
* Workstation lost connection for a minute, thus losing the session and leaving the transaction hanging (is this possible)?

Any other possibilities?

Thanks,
Kevin

And some more

There is only one transaction in the code that I'm using the table that is locked; and transactions are handled correctly there. It's a piece of code that is executed often, so it feels like the code is generally solid - except in some extreme circumstance.

Is there any way to monitor for long running transactions? This particular transaction is generally < 2 seconds for this particular property. If we see a transaction taking 10 seconds or more; it would at least give us the chance to be alerted about it and possibly just kill the session. I didn't see any timestamps related to transactions in the lock tables.

Thanks again for everyone's help so far.
Kevin