search

opensourceautomation / Open-Source-Automation

Open Source Automation is a home and commercial automation engine
www.opensourceautomation.com
GNU Lesser General Public License v3.0
97 stars 62 forks source link

DB performance #355

Closed VaughnRupp closed 7 years ago

VaughnRupp commented 7 years ago

The database performance analysis showed big view getting hit an insane amount.

VaughnRupp commented 7 years ago

The event that runs in MySQL every second had many inefficient calls in it.

The main culprit was the biggest view in OSA the osae_v_objects view. Many routines we using that view just to get a single piece of information, which was way to expensive and over worked the DB. Every one of those calls has now been replaces with a special task-built view or direct SQL so no more is read than necessary.

For example, the plugin properties on the SYSTEM object are checked and updated every second, well, counting the plugs hit the osae_v_object view 4 times. All of those calls for counting plugins have been moved to efficient functions.

This concluded my performance fixes for this release. I did not record before or after metrics, I just knew these calls were expensive and bad and hopefully help the system run cooler and on lower powered devices.

Vaughn

VaughnRupp commented 7 years ago

Since this is all SQL, I will have to do another DB Diff to get it committed to Git and on the test machine to make sure I didn't break anything.