Network freeze

[grinfixer]

We've been experienceing a bunch of "program not responding" errors over the past month or so. When OD logs on they've concluded that someone is running a giant query repeatedly throught the day and when it happens, the server is essentially unavailable. Across our 15 computer network, someone is getting the error message maybe, 30 times daily.

The query that OD support provided is:

Below is the query we are picking up:
SELECT prov.Abbr AS ProvId, prov.LName AS ProvLName, prov.FName AS
ProvFirstName, prov2.Abbr AS ProvId, prov2.LName AS HygLName, prov2.FName AS
HygFirstName, operatory.abbrev AS operatoryid, conf.itemName AS
confirmationName, unsched.itemname AS unschedStatusString, typest.itemName
AS typestring, COALESCE(procdata.txprocs, '') AS txprocs,
COALESCE(procdata.amount, 0) AS amount, appointment.* FROM appointment LEFT
JOIN DEFINITION conf
ON conf.defnum = appointment.confirmed
LEFT JOIN DEFINITION unsched
ON unsched.defnum = appointment.UnschedStatus LEFT JOIN DEFINITION
typest
ON typest.defnum = appointment.appointmenttypenum LEFT JOIN
operatory
ON operatory.operatorynum = appointment.op LEFT JOIN provider prov
ON prov.provnum = appointment.provnum
LEFT JOIN provider prov2
ON prov2.provnum = appointment.provhyg
LEFT JOIN (
SELECT APTNUM, SUM(PROCFEE) AS amount,
group_concat(procedurelog.procnum SEPARATOR ',') AS txprocs
FROM procedurelog
GROUP BY aptnum
) AS procdata
ON procdata.aptnum = appointment.aptnum WHERE appointment.DateTStamp
> '2021-02-22 00:00:00';

Anyone have any idea who might be doing this? I don't know why is happened so suddnly, but it's really bothersome.

In the past we've used: Dentrix eServices, Lighthouse 360, Revenue Well, Yapi, Demand Force, Modento, and Practice Visuals. Modento and Practice Visuals say that it's not their query.

Any input would be really great.

Thanks!

Tom

[jmazzie]

We used to run into a number of issues with having freezing on the database when running larger queries or reports. Our solution was to implement a report server to offload the queries workload allowing the main server to not get bogged down

https://www.opendental.com/manual/reportserver.html

[jordansparks]

Change your mysql password and turn off plugins. This should entirely block any third party. Also, it should be possible to tell which workstation is running the command (I think).

[grinfixer]

Thanks Jordan!

Can OD support help me with that mysql password change?

Yesterday, at the end of the day, I called LocalMed. Even though I don't know a thing about sql, it looked to me as if the query had all the info that local med might want: provider, patient, appointment, confirmation.

They suggested I go to the server and disable LocalMed Connect, which I did.

We'll see what happens today.

Thanks for the help.

[grinfixer]

My hopes for LocalMed being the problem have been dashed. There is no improvement when blocking LocalMed.

If I set up a second server for reports only will this actually help? The data in that query seems nothing like the available on the report server.

The manual says that the report server will provide data for queries involving:
Production and Income
Daily Adjustments
Birthdays
Capitation Utilization
Finance Charge
Insurance Plans
Prescriptions
Receivables Breakdown
Referral Analysis
Daily Writeoffs
Graphic Reports
User Queries
Task Search

Does it seem that the problematic query will be addressed on a reports server?

I'll buy one today if it will fix the problem. How long would data migration and set up require?

Thanks!

[grinfixer]

Here's a 2 minute video of what happens...

https://vimeo.com/548105588/2fdfb33cbb

[jordansparks]

No, setting up a second server for reports won't help if you don't even have control over who is running reports.

[bak2]

We have been having the same issue with the exact same third-party query! The query runs approximately every 18 - 22 minutes, and causes OD to hang up for 45 - 60 seconds at a time. So frustrating! Our office has 10 workstations and runs OD on a Windows server with plenty of computing power.

We suspect it was Weave, which we use for our phone system, appointment confirmations, text messaging, and viewing the schedule on the Weave app. However, we have used a number of other services over the years, including LocalMed Connect, Lighthouse 360*, Dental Intelligence*, Divergent Dental*, Swell, Record Link, and others, so it has been difficult to pinpoint the culprit. Plus, whenever we reach out to the third-party vendors, no one is willing to admit it's their integration software that's causing the issue. (* No longer using these programs)

Changing the password and blocking plugins certainly will correct the problem, but some of these services are useful, and we'd only want to get rid of the one dragging down OD. It would be great if there was a tool that could trap not just the query, but also the application or service that's initiating the query.

Keep us posted if you find the culprit application or service.

[jfd2]

I would say the 2nd server will make a difference if you put all the reporting related functions on the 2nd server. This is like the practice to put OLAP in another server in the industry. Of course, if you can identify the long running application then you might save the 2nd server.

Basically:
1) setup a 2nd mysql server as slave to the 1st server and only use for reporting, search online with master-slave for mysql for details.
2) all your OD clients will direct to the master server since writing happens there.
3) this assumes that all reporting functionalities are read only which might not be true depending on vendors (most likely should be true)
4) practically, you can use the current one as slave and set a new one as the master, or whichever is easier for you.

hope this helps.

[bak2]

Some vendors do need access to the master for updating appointment confirmations or booking online appointments, but certainly when they pull data (with large queries) they could be reading off a slave database.

I encourage anyone using third-party vendors to consider the master-slave setup described by jfd2. Unfortunately, we've given third-party vendors carte blanche access to our OD database, and now we are suffering the effects. It's a challenge to unravel the culprit(s) that are dragging down our OD database.

If we cannot determine the offending vendor and work out a solution, this is our plan:
(1) Temporarily "unplug" all the third parties by changing the mysql password and disabling plugins, as jordansparks mentioned.
(2) Create a master-slave setup.
(3) Invite the third-party vendors to re-integrate using the slave database when pulling data to their servers.

Alternatively, we keep everything the same, but try to muscle our way through it with OD on running on a souped-up dedicated server on SSD's (in the hopes that the complex queries will run in less time). Or look for new vendors.

Thanks!

[jordansparks]

Long term, the API will alleviate issues like this. We'll be able to get simple reports on who is making queries and what kind of load they are causing.

[jfd2]

Another thing might be quick for you is to tune your mysql if you have never done so. Most people use the default parameters which are only for minimum hardware settings and with current box setting there are lot of leeway if you tune it to increase the query performance.

the following three made a lot of difference in most cases in the mysql(d) configuration file:

Code: Select all

 
  key_buffer_size     = 1024M  # or 25% of your RAM
  max_allowed_packet  = 128M
  thread_stack        = 256K
 

Please check online for the best values for your setting.

[bak2]

Thanks for the suggestion!
When the lag was first happening, I followed OD's instructions to update the my.ini file using my-huge.ini example provided.
Our current configuration looks something like this.

[mysqld]
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/mysql/data/"
default-storage-engine=MyISAM
skip-innodb
max_allowed_packet=40M
key_buffer = 384M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
tmp_table_size = 32M
join_buffer_size = 128M

We have 32 GB of RAM in the server, so I'll tweak the max packet allowed, key buffer size, and thread stack to see if things get better.

Thanks again for your help!

[jfd2]

i think the parameter for key_buffer_size is wrong for your configuration and you need to change it ASAP to harness the machine power you have. you put it as key_buffer, i searched my db and there is no key_buffer variable (i am using 8.x through, use this to check: show variables like "key_buffer%";)

So you can change the key buffer to 6G if you want or any over 2G will work since currently your are using 8m which is the default, you also need to take a look at other 2 params too.

Also looks like to me the variables in your configuration file are 32bit time tuning, and you are using a 64bit machine, these variables should be changed now. unless you installed 32bit mysql on 64bit machine. If that is the case, you need to change the mysql installation to 64bit.

Another way to avoid the 2nd box is to add a SSD drive to your server and move the OS and DB to the SSD if you did not do so. that is way faster. Of course I am using linux for all my servers and adding disks is just plug it in. not sure your OS etc.

hope this helps.

[jfd2]

Hello Bak2 and all others have performance issues with mysql server,

As these 2 weeks I have helped some offices to solve the mysql performance issues and I found out that the major problem is the mysql installation. In both 2 cases, they are using mysql 5.5 32bit on a powerful 64bit machine. The issue is obvious and none of your extra memories can be used to power the mysql server. The solution is to ask your IT to reinstall the 64bit mysql of version 5.5 to replace the 32bit one. It is sad to me that majority of opendental users using a 32bit mysql server on a powerful 64bit machine.

the solution, please let your IT to work on it:
1) see if you are not using 64 bit mysql server, in mysql client or OD query, type this:

Code: Select all

 show variables like 'version_compile_machine';

if you do not see 64 in the return, like only "x86", you are using 32bit mySQL server
2) if you decided to solve your performance issue, then move on, otherwise stop. Most likely, any other tuning is futile if 64bit is not installed on the power machine.
3) backup your database into a sql script using mysqldump, either following the following ((not complete) or the https://www.opendental.com/manual/mysql56update.html
4) uninstall the 32bit mysql
5) install 64bt mysql
6) tune the variables as show above.
7) restart and test the new installation.
restore the data and users etc.

If you decided to wait, the good thing is that Opendental is pushing for MariaDB 10.5 which default to 64bit. So I would suggest you guys follow the opendental instructions and upgrade to that ASAP, which will let you use your power of the server machine you paid for. Upon your tuning of the DB, most of your performance issues will disappear magically.

thanks.