Major Performance Issues, continued

[atd]

I've posted different topics in the past related to Open Dental performance and continue to struggle with this. I've been working on this for months, have tried at least a dozen different things to improve performance, but it is still a major issue for our users. We have 3 of our 5 clinics on Open Dental and converting the last 2 is on hold until this is resolved. We currently have approximately 35 users on Open Dental.

After all we've tried, I think we've narrowed it down to either the MySQL database itself (problem with settings?) or some query in the Open Dental program that causes the problem. Is there a MySQL expert out there that could help us with this? I'm not a trained database administrator, but looking at the MySQL admin tool I see a few of things that don't look right to me:
1) When Open Dental is running slow, the Hitrate on the Key Efficiency is maxed out at 100% for a long period of time.
2) I see huge spikes in the Number of SQL Queries. Today the max # of queries at one time is 1,338. Is that normal?
3) I've enabled the slow query log in the my.ini file. There are a few queries that repeatedly come up in that log. Could those be the cause, or just what happens to be running when the database slows to a crawl for some other reason?

I've made changes to the my.ini file based on the database server specs. It's a virtual server running Windows Server 2008 R2 64 bit, 6GB of RAM, 4 processors. These are my current settings - does anything look wrong?
[mysqld]
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.0/"
datadir="//atdfile/OD/mysql/data/"
skip-innodb
key_buffer = 32M
max_allowed_packet = 16M
table_cache = 2048
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 64M
query_cache_limit = 2M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
tmp_table_size = 64M
join_buffer_size = 128M
log_warnings
log_slow_queries
long_query_time = 2
log_long_format

[mysqld_safe]
open-files-limit = 8192

Any help would be appreciated.

[jordansparks]

I didn't know you were still having problems.
I can think of 3 possibilities:
1. The version of MySQL or the version of the connector is buggy or not performing well. In version 7.4, due to be released in a week, we have replaced the version 1.0.9 connector with a 6.3.4 connector.
2. Open Dental is sending too many queries. The log would allow us to determine this.
3. You set the server variables badly because you don't understand them. An expert in MySQL could help with this, possibly paid support from MySQL.

I think it's #1. That connector is over 3 years old, but we continued to use it because a number of later connectors were too buggy. The newer connector handles newer versions of MySQL better and it also does a better job managing connection pooling. We may decide to use the 6.2 connector instead, but the advantages over the 1.0.9 would be the same. The jump in versions isn't as big as the numbers imply. They jumped straight from 1.0 to 5.0 in an attempt to mirror the MySQL version numbers.

Once you start using the new connector, you can also upgrade from MySQL 5.0.22 to 5.1.51 or newer.

[Justin Shafer]

Hmmm... looks like you did a good job increasing the stock settings....

Try max-connections?

try setting that to like 100 and then 200 and then 300, etc....

Looks like MySql recently had to up the default setting from 100 to 150 in MySQL 5.1...
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html

Oh it looks like this is ONLY in 5.0... Guess you have to upgrade. Guess he cant just toss in the lastest MySQL.Data.dll file..... ?????

Dang big differences in 7.2.45 and 46...

[atd]

I didn't know you were still having problems.

There have been a few changes that I thought fixed things, but then the problem reoccurs. I've been trying to eliminate everything on our network as a possible problem first.

I was just about to upgrade to 7.2. I haven't been running beta versions for awhile, but will give it a try since this is urgent.

Where in the log do I look to see if Open Dental is sending too many queries? As I mentioned, at one point there were over 1,300 queries running, so I'd like to look into this further. Just point me in the right direction, or do you need a copy of it?

3. You set the server variables badly because you don't understand them.

Are you saying they look like they're set wrong, or just that it's a possibility? I looked at the settings in "my-huge.ini" which it says is for large systems with 1G-2G of memory and also at "my-innodb-heavy-4G.ini" which is for 4GB of RAM, then when through each variable and tried to find the right value to use. I don't know how current those recommendations are. Now that I look at the description again the second ini file is for few connections - but I don't know what they would consider "few".

Yes, I'm trying to track down a MySQL expert. Anyone have any recommendations?

[atd]

One query I see frequently in the slow query log is related to insurance plans. We have a large number of identical plans (3,000+ in some cases). The query starts with the following and then has pages and pages of "OR PlanNum=X" following this:
SELECT DISTINCT PlanNote FROM insplan WHERE PlanNum=14 OR PlanNum=18 OR PlanNum=27.........

I'm not sure where this query is being used, but it is also repeatedly in the slow query log:
SELECT patient.PatNum, patient.Guarantor FROM patient,procedurecode,procedurelog,claimproc WHERE claimproc.procnum=procedurelog.procnum AND patient.PatNum=procedurelog.PatNum AND procedurelog.CodeNum=procedurecode.CodeNum AND claimproc.NoBillIns=0 AND procedurelog.ProcFee>0 AND claimproc.Status=6 AND procedurelog.procstatus=2 AND procedurelog.ProcDate >= '2009-10-13' AND procedurelog.ProcDate <= '2010-10-13' GROUP BY patient.Guarantor;

[jordansparks]

Ah. Now we're getting somewhere. It could be the identical insurance plans. We will begin working on optimizing that part of the database for many patients on one plan.

Wait a minute. You have a "slow query log"? What's that?

7.2 is not beta.

[jordansparks]

As for that second query, it's already a known cause of slowness. See http://www.opendental.com/manual/troubl ... wness.html
Under Settings and Tools,
Uncheck the box in Module Setup for "Show ! at upper right of appts for ins not sent".

[atd]

Sorry, I meant to say I'd upgrade to 7.4 when it's available next week. I know 7.2 isn't beta, just upgraded tonight.

I turned on the slow query log by adding the lines below to the my.ini file. I think the time could probably be increased to 3 or 4, but it was at 2 in the example I found so I left it.
log_slow_queries
long_query_time = 2
log_long_format

I will try turning off the alert for insurance not sent - although I sure like that feature and will miss it!

[jordansparks]

Very nice find on the slow query log. Added that to the troubleshooting page and we will surely make heavy use of it when programming.

[Justin Shafer]

Good morning all....For a real mysql expert why not just go to the internet and look for one? Maybe Peter Zaitsev? Jeremy D. Zawodny? Derek J. Balling? Odd.. Let us know what you find out.. That would be annoying. I bet upgrading will help.. but you never know...

[atd]

You can also add more to the slow query log:
log-queries-not-using-indexes
log-slow-admin-statements
See http://dev.mysql.com/doc/refman/5.0/en/ ... y-log.html for more info.

[enamelrod]

im not sure if your performace is related but when i unchecked this box. performance did increase

its under setup/ modules. then its the 6th line and says " show ! at upper right...." uncheck it if its checked and see if that solves your problem. We regulary have to check the bock when we are doing insurance items but uncheck it when we are working through the day. This issue has been around for awhile. I thought I was the only one with this problem