After Using API MySQL Stops Responding to Requests

[mandmdiet]

We're trying to troubleshoot a problem that we recently ran into with one of our clients but are having a hard time getting a good picture of what's happening and are hoping someone on this forum can help us know the best place to look to start troubleshooting.

The issue we're having is that for one of our clients after our application begins making API calls through the Open Dental API they are no longer able to use Open Dental. The application freezes, then has to be forcibly quit to close. When they reopen the application the problem persists. In order to fix the problem we have to restart the MySQL service. Once this is done Open Dental begins working normally again.

We looked at the system more closely and can see that the neither the CPU, Memory or Physical Disk of the machine are being taxed leading up to or while the issue is occurring. We have had ~40 other users using the software for several months without this or any other issues really.

We're still in our investigation phase and would like to know what tools we can use to investigate what might be causing this. The users is on MySQL 5.5 and their Open Dental version 23.3.64.0. They are our only user on this version of Open Dental if that means anything.

Our next step is to go through all of our API requests with a fine-toothed comb and see if any API call separately causes this problem to occur, however, it seems more likely that any one single API call on its own would not cause a degraded MySQL experience.

Thank you for your help!

[SLeon]

Good afternoon,

Some anti-virus software applications will cause problems with the eConnector ( eConnector Troubleshooting).

You can check the logs for the eConnector (or the API Service, if using instead) to see if anything is listed there.

You might also use the Slow Query Log Tool in your troubleshooting. This may or may not get you anywhere, based on your description of the machine' CPU usage, etc.

My general questions are:

1. Is this a new customer of yours or did they suddenly start experiencing this issue?
2. If existing, did they recently update or make any other server changes leading up to this issue?
3. Do they have triggers in their database or is anything else unusual about their MySQL setup?

If this doesn't help, I recommend calling support so our team can get more specific information about the dental office. I will continue to think on this, and see if any other troubleshooting steps come to mind.

[mandmdiet]

Thanks SLeon, this is very helpful and we we will look into these. I'll keep checking back here for any additional thoughts you may have and if we need further help we will contact Open Dental support.

Here are the answers to your questions:

1. This is a new customer
2. N/A
3. I don't know much about their database. I can look to see if there are triggers and generally for anything else unusual.

[mandmdiet]

SLeon,

Incidentally, for Antivirus, they are using Trend Micro and they are also using something called PRTG for network monitoring.

[SLeon]

Are you still experiencing this issue?

SLeon,

Incidentally, for Antivirus, they are using Trend Micro and they are also using something called PRTG for network monitoring.

I cannot speak to either of those causing problems with the eConnector specifically, but it is worth checking. Does the office experience slowness with any eServices (eConnector reliant products)? If not, and with Open Dental behaving in the way you describe, this does sound like an SQL-related issue. You will probably need to work directly with the office/support to troubleshoot this.

[mandmdiet]

SLeon,

We haven't worked on this issue again since my last message but I was just about to look at the things you have brought up.

Does the office experience slowness with any eServices (eConnector reliant products)?

What other eConnector reliant products could we check for this? I'm not familiar with any that they might be using that OD makes available to them.

Thanks

[SLeon]

You can see a list of eServices in our manual. However, because you describe the slowness only while using your application, I hypothesize it is more likely MySQL related than eConnector.

[mandmdiet]

One other general question for you. I'm not a MySQL expert so there are some things that are new to me, however with other database engines that I know well, like MS SQL, they have tools for shrinking or optimizing table space that, unless you run them regularly, can cause the database to slow down and have issues. It looks like for MySQL there is something called OPTIMIZE TABLE that does this same thing. Does OD recommend to their users that they run OPTIMIZE TABLE? In looking at this database I just don't know if we should try running that to see if it reduces the size of the table and improves the performance.

[SLeon]

Database health/support is outside of my expertise. I can point you to our MySQL manual page and suggest having the office call support.