Fun IoT projects to do at home during #JulyOT

by Scott Muniz | Jun 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Like this wallpaper? Download it below!

As they find themselves spending more time at home, devs are finding creative ways to strike a new work-life balance using the Internet of Things. Here are the stories of three IoT hobbyists and their home projects that will inspire your creativity too.

IoT plant Sensor  – Jim Bennett (@JimBobBennett)

Jim Bennett’s AgroHack is made for the gardener at heart. In its most basic form, it combines a Raspberry Pi and a Grove moisture sensor to determine if his plants need water. Jim then sends this telemetry to Azure IoT Central and hooks this all up to an LED light so his potted rosemary plant can tell him when it is thirsty.

As useful as the device is for communicating with a house plant, Jim can also hook it up to Azure Stream Analytics in order to gather additional humidity, pressure, and temperature readings and compare these against official weather service forecasts. This way, if the tomatoes in his raised garden need water but the meteorological service says it will rain in the evening, Jim knows he doesn’t have to do anything about it.

This isn’t all Jim gets up to while getting creative with his at-home set-up. He also built his own home office IoT Busy light that uses Azure Logic Apps to talk to his Office 365 calendar so people in the house know when he is on a Teams call and needs a bit of quiet. He is also known for combining IoT and Ugly Sweaters, an underappreciated corner of the IoT.

If you want to build your own Agrohack, or see more of Jim’s cool projects, visit his blog.

IoT and barbeque – Cam Soper (@camsoper)

As great as IoT is for monitoring moisture, it also excels at monitoring heat levels. A good Kansas City barbecue should smoke for about five hours at a constant temperature of 225 degrees. But how do you keep your temperature constant?

Cam Soper hooked up a Raspberry Pi to his newly bought smoker and wrote Project Inferno, a C# app running on .NET Core, to automatically adjust the temperature. As any software developer knows, it makes more sense to learn a new technology and build a complex software solution to fix a hardware problem than to replace the hardware itself. ;)

Cam used Azure App Service, Azure Relay Hybrid Connections, and Azure Bot Service to make it easy to work with his device. Cam realized that using curl commands to control his invention would not appeal to the non-programmers in his family, so he created a bot interface that others could interact with when they wanted to set the barbecue temperature and monitor it. Cam even uses the Raspberry Pi’s CPU thermometer to check that the IoT device doesn’t overheat when the smoker is on and that it doesn’t get too cold when it starts to rain or snow.

IoT PresenceLight – Isaac Levin (@isaacrlevin)

Like many who work from home, Isaac wanted to find a better way to balance his work and home life. He needed to find a way to let his family know when he was on a call (and more importantly when he wasn’t) so they could go on living their lives without worrying about interrupting him. With the help of IoT, Isaac created a status light outside his home office door.

Isaac Levin’s PresenceLight is coded around an interesting feature in Microsoft Teams known as Presence. It turns out that Presence, which is that red, yellow, and green indicator in your Teams that tells everyone if you are busy or not, is surfaced through Microsoft Graph APIs. Isaac created a .NET Core app to connect his own Outlook account to a LIFX IoT light outside his office door that effectively shows everyone in the house his Teams status. This is known in the IoT world as an “elegant solution.” Isaac found one piece of information he was interested in and created the shortest path to make his availability status known to the “” – aka his family. You can almost see the moment the lightbulb went on in his head.

For a more detailed walk through of how Isaac built this project, follow his blog. 

What’s next

When reading about home IoT projects, there’s a strong temptation to extend them or even combine them. Cam’s bot interface, for instance, would possibly be even more useful to his family if it were

If ideas are lighting up in your head right now, you may want to start by reading the guides each of these developers has written explaining the necessary IoT components and the required code (linked above). If you are fresh to the world of IoT, you can also get started with this Develop IoT solutions with Azure IoT Central on Microsoft Learn. And when you become a pro, be sure to take the Microsoft IoT Certification exam!

We want to hear about your own IoT explorations and ideas. Please share your home IoT projects using the hashtags #JulyOT and #IoTDevs. Being at home can be a great reason to do amazing things!

PS, looking for a new desktop wallpaper? We’ve got you covered! Download it below ⬇

Fun IoT home projects to do during #JulyOT

by Scott Muniz | Jun 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Like this wallpaper? Download it below!

As they find themselves spending more time at home, devs are finding creative ways to strike a new work-life balance using the Internet of Things. Here are the stories of three IoT hobbyists and their home projects that will inspire your creativity too.

IoT plant Sensor  – Jim Bennett (@JimBobBennett)

Jim Bennett’s AgroHack is made for the gardener at heart. In its most basic form, it combines a Raspberry Pi and a Grove moisture sensor to determine if his plants need water. Jim then sends this telemetry to Azure IoT Central and hooks this all up to an LED light so his potted rosemary plant can tell him when it is thirsty.

As useful as the device is for communicating with a house plant, Jim can also hook it up to Azure Stream Analytics in order to gather additional humidity, pressure, and temperature readings and compare these against official weather service forecasts. This way, if the tomatoes in his raised garden need water but the meteorological service says it will rain in the evening, Jim knows he doesn’t have to do anything about it.

This isn’t all Jim gets up to while getting creative with his at-home set-up. He also built his own home office IoT Busy light that uses Azure Logic Apps to talk to his Office 365 calendar so people in the house know when he is on a Teams call and needs a bit of quiet. He is also known for combining IoT and Ugly Sweaters, an underappreciated corner of the IoT.

If you want to build your own Agrohack, or see more of Jim’s cool projects, visit his blog.

IoT and barbeque – Cam Soper (@camsoper)

As great as IoT is for monitoring moisture, it also excels at monitoring heat levels. A good Kansas City barbecue should smoke for about five hours at a constant temperature of 225 degrees. But how do you keep your temperature constant?

Cam Soper hooked up a Raspberry Pi to his newly bought smoker and wrote Project Inferno, a C# app running on .NET Core, to automatically adjust the temperature. As any software developer knows, it makes more sense to learn a new technology and build a complex software solution to fix a hardware problem than to replace the hardware itself. ;)

Cam used Azure App Service, Azure Relay Hybrid Connections, and Azure Bot Service to make it easy to work with his device. Cam realized that using curl commands to control his invention would not appeal to the non-programmers in his family, so he created a bot interface that others could interact with when they wanted to set the barbecue temperature and monitor it. Cam even uses the Raspberry Pi’s CPU thermometer to check that the IoT device doesn’t overheat when the smoker is on and that it doesn’t get too cold when it starts to rain or snow.

To learn more about this or Cam’s other projects, please read his excellent blog. 

IoT PresenceLight – Isaac Levin (@isaacrlevin)

Like many who work from home, Isaac wanted to find a better way to balance his work and home life. He needed to find a way to let his family know when he was on a call (and more importantly when he wasn’t) so they could go on living their lives without worrying about interrupting him. With the help of IoT, Isaac created a status light outside his home office door.

Isaac Levin’s PresenceLight is coded around an interesting feature in Microsoft Teams known as Presence. It turns out that Presence, which is that red, yellow, and green indicator in your Teams that tells everyone if you are busy or not, is surfaced through Microsoft Graph APIs. Isaac created a .NET Core app to connect his own Outlook account to a LIFX IoT light outside his office door that effectively shows everyone in the house his Teams status. This is known in the IoT world as an “elegant solution.” Isaac found one piece of information he was interested in and created the shortest path to make his availability status known to the “” – aka his family. You can almost see the moment the lightbulb went on in his head.

For a more detailed walk through of how Isaac built this project, follow his blog. 

What’s next

When reading about home IoT projects, there’s a strong temptation to extend them or even combine them. Cam’s bot interface, for instance, would possibly be even more useful to his family if it were

If ideas are lighting up in your head right now, you may want to start by reading the guides each of these developers has written explaining the necessary IoT components and the required code (linked above). If you are fresh to the world of IoT, you can also get started with this Develop IoT solutions with Azure IoT Central on Microsoft Learn. And when you become a pro, be sure to take the Microsoft IoT Certification exam!

We want to hear about your own IoT explorations and ideas. Please share your home IoT projects using the hashtags #JulyOT and #IoTDevs. Being at home can be a great reason to do amazing things!

PS, looking for a new desktop wallpaper? We’ve got you covered! Download it below ⬇

Connection Pooling for MySQL with the Heimdall Proxy

by Scott Muniz | Jun 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Background

In using databases as a key component of internet infrastructure, IT departments are finding unexpected problems in particular when using DBaaS (Database-as-a-Service).  One of these challenges is in connection management.  There are three areas where connection management can be a problem:

1. CPU overhead when an application “thrashes” connections rapidly by opening, closing and authenticating connections;
2. Memory overhead when applications hold open long-lived connections that are often idle, which would be better used as block cache or may require a larger instance size than CPU requirements dictate
3. Noisy neighbor congestion for a multi-tenant database. Limiting the number of active connections on a per-customer basis ensures fairness.

Solution

The Heimdall Proxy was designed for any SQL database including Azure Database for MySQL and Azure SQL Data Warehouse (SQL DW) for connection pooling such as:

1. General connection reuse: New connections leverage already established connections to the database to avoid connection thrashing. This results in  lower CPU utilization;
2. Connection multiplexing: Disconnects idle connections from the client to the database, freeing those connections for reuse for other clients.  This dramatically reduces connection counts to the database, and frees memory to allow the database to operate more effectively;
3. Tenant Connection Management: The combination of 1) Per-user and per database connection limiting and 2) Multiplexing control the number of active queries a particular tenant can use at a time. This protects database resources and helps ensure the customer SLA (Service-level Agreement) is met and not disrupted by a busy neighbor using the same database.

Figure 1: Heimdall Proxy Architecture Diagram

The Heimdall Proxy provides better control over database resources, providing more efficient and consistent behavior. As a result, users will reduce their database instance size and/or support higher customer density on the same database.  In this blog, we explain how these functions work and are configured.

Basic Connection Pooling

A basic connection pooler opens a number of connections upfront (the pool), then as an application needs a connection, instead of opening a new connection, it simply borrows a connection from the pool and returns it as soon as it is not needed.  For most pools to be effective:

• The application is aware pooling will be used, and does not leave connections idle, but instead opens and closes them as needed;
• All connections leverage the same properties, such as the database user and catalog (database instance);
• State is not changed on the connection.

For a typical application server environment (e.g. J2EE), basic pooling is supported.  In other environments, where pooling was not part of the initial design, simply inserting a connection pooler can cause more overhead than expected:

• When multiple users are connecting, and each user rarely uses more than a few connections (e.g. Data Analytics):  This may open a set of connections per user or close connections that are retrieved from the pool that do not match the desired properties and open new ones. This results in a large amount of connection thrashing (e.g. Apache Tomcat pooling and most other poolers). 
• When many catalogs are used: In order to avoid changing the connection state, a discrete pool per catalog is created allowing an appropriate connection to be reused. This avoids triggering a USE statement before each new request.
• When attempting to constrain total connections to the database and on a per-user basis

Figure 2: Basic Connection Pooling 

For basic connection pooling, an active (green) front-side connection is paired with a back-side connection as shown in Figure 2 above. Additionally, you may have idle (red), unassigned connections in the backend for new connections. As such, you are NOT reducing the total number of connections, but are reducing the thrashing that occurs as the connections are opened and closed.  The main benefit of basic pooling is lower CPU load.

To configure connection pooling on Heimdall Central Console, select the Data Source tab. Click the checkbox to turn on Connection Pooling showed below:

Connection Multiplexing

Beyond basic pooling, there is connection multiplexing, which does not associate a client connection with a fixed database connection. Instead, active queries or transactions are assigned to an existing idle database connection, or else a new connection will be established. If a client connection is idle, no resources are used on the database, reducing connection load and freeing memory. Shown in Figure 3 below, only active connections (green) are connected to the database via the connection pool while the idle connections (red) are ignored. 

Figure 3:  Connection Multiplexing 

Multiplexing is a much more complicated technology than basic pooling. Therefore, many factors need to be accounted for.  In the following situations, multiplexing will halt, and a connection will remain pinned, including:

1. If a transaction starts, then the connection mapping will remain until the transaction completes with a commit, rollback, or the client connection is terminated;
2. If a prepared statement occurs on a connection, this makes the connection stateful, and will remain pinned to the database until the connection is terminated;
3. If a temporary table is created, the connection will remain pinned until the table is deleted.

To configure multiplexing on the Heimdall Central Console, go to the VirtualDB tab. And under Proxy Configuration, just click Multiplex option shown below:

In the event that special queries break multiplexing logic, and multiplexing needs to be disabled on the connection, go to the Rules tab for more granular control (along with other pool behaviors). For example, you can add the below rules to:

1. Disable multiplexing when locking tables
2. Enable multiplexing when unlocking tables

Tenant Connection Management

The third use-case helps ensure SLAs by enforcing per-tenant limits on connections and when combined with multiplexing, total active queries.  This prevents one user from saturating the database, ensuring fairness of resources for others.  A second tier of pool management is activated, that of “user pools”.  

In the Data Sources tab, the pool can be managed at two tiers: the user level and the database.  You can limit each user to a number of total connections and idle connections. Use the  icon to add limits as shown below:

Shown above, the total connections allowed to the database across all users is 1000, but each user is only allowed 100, and of those, only 10 can be idle.  Excess idle connections will be disposed of. Each time a connection is returned from the pool, there is a chance the connection will be closed: A value of 1000 means that there is a 1/1000 chance that the connection will be closed. This behavior is different from most connection poolers that set an absolute connection age which for large deployments can result in a stampede of many connections closing and reopening at once.

Figure 4:  Multi-tenancy with Pooling, Multiplexing and Per-tenant connection limits

Figure 4 shows two tenants (with unique usernames or catalogs), allowing only active connections (green) to the database when multiplexing is enabled.  If Tenant A attempts to perform a third query (blue) while two are active, it will be queued until one of the current active queries completes.

The net result of the combination of 1) Pooling and 2) Multiplexing, and 3) Per-tenant limits is that no single tenant can saturate database capacity, resulting in the SLAs of other customers failing.  Further, as beyond a certain point, adding execution threads to the database will result in negative performance. This control can improve overall performance in many cases, allowing more capacity during peak load.

Use Cases

Magento

Magento is an e-commerce package written in PHP.  Since PHP does not support efficient connection pooling due to its processing model, each page-view opens a connection to the database, requests all the data it needs, then closes the connection.  For every page-view, it results in a very high amount of connection thrashing against the database and can consume a large percentage of the database CPU.  With the Heimdall proxy, basic connection pooling alone can reduce the load on the database by up to 15% percent.  

Slatwall Commerce

Slatwall is an eCommerce platform written in Java, and is natively designed to use pooling.  Although, under heavy load, it can result in the saturation of the allowed connections on MySQL (at most 7000).  In order to support larger user-loads, the Heimdall proxy can reduce the connection load by an order of magnitude, resolving connection limits on the database, and allowing the CPU load to be the limiting factor in larger deployments.  Per the developer of Slatwall, connection offload with multiplexing and pooling resulted in a 10x reduction in connections to the database.

Complementary Features

While Heimdall proxy provides connection management for databases, there are other features provided that further improve SQL scale, performance and security:

1. Query Caching: The best way to lower database CPU is to never issue a query against the database in the first place.  The Heimdall proxy provides the caching and invalidation logic for Amazon ElastiCache as a look-aside results cache. It is simple way to improve RDS scale and improve response times without application changes;

1. Automated Read/Write split: When a single database becomes too expensive to upgrade, or there is already a standby reader that is sitting idle, separating read and write queries can be used to offload write queries to an alternate server, improving resource utilization. Moreover, replication lag detection is supported to ensure ACID compliance.

1. Active Directory/LDAP integration: By authenticating against LDAP, the Heimdall proxy manages connections for a large number of users, and synchronizes the authentication credentials into the database.  In environments that require database resources to be accessible to many users in the enterprise, while providing data security, this feature is easy to administer, while preventing individual users from over-taxing resources.

Next Steps

Deployment of our proxy requires no application changes, saving months of deployment and maintenance. To get started, you can download a free trial on the Azure Marketplace, or contact Heimdall Data at info@heimdalldata.com. 

Resources

• Blog: Using the Heimdall Proxy to Split Reads and Writes for MySQL
• Blog:  Automated Query Caching for MySQL
• Heimdall Data technical documentation
• Contact: info@heimdalldata.com

Heimdall Data, a Microsoft technology partner, offers a database proxy that intelligently manages connections for SQL databases. With the Heimdall’s connection pooling and multiplexing feature, users can get optimal scale and performance from their database without application changes.