Appt Module Speed/MySQL performance

[atd]

Question for other Open Dental users...How long does it take for the appointment module to come up when you switch from another module? I'm finding it takes a minimum of 3 seconds, but sometimes I get a "not responding" message and it can take 15 seconds or longer. Once I stay in the appointment module, switching to a different day seems to perform slightly better - but 5 seconds isn't uncommon. Anyone else experiencing the longer lag times or is it something with our network/database?

I've been optimizing the MySQL database every other week, things move at a snails pace if I wait longer than 2 weeks. We have a powerful database server, so I don't think we have a hardware issue - but sometimes I see the MySQL process spike all 8 CPU's up to the max, then drop way back down. Could there be something in the Open Dental program code that needs to be optimized, that might be causing this? Does MySQL have performance issues after it gets to a certain size? Our database is about 1GB right now, but we're looking to convert 2 more databases in the next year or so which will more than double the size and number of users accessing. I don't think I can move forward with that until these performance issues are resolved. I'm getting phone calls from users on a regular basis about how slow things are - already have had 3 today.

One other thing I noted - in MySQL admin it shows the "datalength" for etransmessagetext as 713MB. Am I interpreting this correctly, does that one table account for 3/4 of the database size?

Anyone have any ideas? I'm at a loss as to what to tell users when they call.

[drtech]

mine is instantaneous, but then again my database is only 1/10th the size of yours...

[wjstarck]

A couple of troubleshooting ideas to consider-

-might be a memory leak. When you restart the server, is the performance OK and then degrade over time?
-might be some corrupted MySQL tables. You might want to check with one of the MySQL admin tools. I use NaviCat for this
-as always, could be router, switch or bad patch cable. Probably unlikely if the rest of your networking is working OK

[Rickliftig]

This may be all too obvious, but - are you running at gigabit speed on your network? It has been an issue here at times.

[afeuer]

hmmm, something is funny about that. Your database is larger than most people's but not unreasonable. I've run open dental on some pretty old hardware before and not had an issue. It is possible that it's a network issue, but I can run open dental over a VPN from my house and it still only takes a couple of seconds to load each module.

I would run some tests. I would make a different computer the server and see if other computers can access it faster that way. Oh, and you aren't running your computers via WIFI are you?

-Adam

[jordansparks]

I assume you are already familiar with this page:
http://www.opendental.com/manual/troubl ... wness.html
And that you have already edited your my.ini file.

What version are you using again?

Go to Tools, Misc Tools, Clear Duplicate Blockouts. Let me know if that helps.

Yes, that one table can become massive. But it shouldn't have any effect on your speed because it is only used when sending claims.

[atd]

Yes, I've been running the database optimization tool on a bi-weekly basis. Do you know of a way to script this so I can set it up to run regularly without my having to work evenings/weekends?
No, I hadn't seen the rest of that page on changing the .ini file - it must be fairly new, because I've read every page of the manual in the past.

Last night I made that .ini change, ran database optimization, ran database maintenance and the clear duplicate blocks tool as you suggested. Today the appointment screen is refreshing immediately! I guess I should have done one thing at a time to figure out which of those things makes the biggest difference. I only have 1/3 of the users working Fridays as the rest of the week, so Monday will be a better test - but so far it's looking very promising!

[atd]

Sorry forgot to mention that I'm on version 7.0.41. Looking forwarding to upgrading to 7.1 for those great new features!

[jordansparks]

I should mention that I'm really not an expert on those my.ini settings. Most of that was taken from a template provided by mysql, and then we added a few items that looked important. My point is that it can still be tweaked for your situation. There is a chance that it will make things worse instead of better, and if that happens, changing it back to the way it was might help. For example, you could have multiple versions of them, all named slightly differently. And then get comfortable replacing my.ini with one of your prepared ones. We do hope to become experts on the my.ini settings and write pages of instructions in our manual as we learn. We hope to have a series of my.ini files for a user to pick from. But... I hope that the one change you made is all it will take.