We did some profiling of our node MQTT workers using the --prof flag today, and we found that our workers were spending an excessive amount of time handling MQTT connection authorization. It turns out that a few users were hammering our servers with failed auth attempts, so we decided to implement a rate limit for MQTT connection attempts.

The new connection rate limit allows for 50 connection attempts in a 15 minute window, and attempts past the limit will be rejected for the remainder of the window. If you would like to comment on this change, please visit our IO forum.

Hello all you lovely makers! I am very excited to introduce myself: my name is Adam Bachman, I am here to write code and chew bubblegum, and I am all out of bubblegum! As of May 23, 2016, I am the newest member of the Adafruit team and will mainly be working with Justin, Todd, Tyler, and the rest of the incredibly talented folks making Adafruit IO the best it can be.

I’ve been a resident of Baltimore City, making my way through the world as a software developer for the last 10 years. By day building software for Fortune 500’s, startups, bootstrapped artist-loving theater makers, and a few in between. On my own time I’ve been an Adafruit customer since 2007, started a hackerspace in 2009, and I’m super into Processing and creative code in general.

You can follow me on Github or Twitter if you like, though I can’t promise to be half as interesting as the people at the middle of this incredible enterprise :D

ENOUGH ABOUT ME! I look forward to connecting with anyone crossing boundaries from software to hardware and back again. Artists, makers, hobbyists, professionals, first-timers, long-timers, indie, corporate, all of you, everyone, cats, maybe not so much dogs, whatever! I am psyched to get started, let’s make amazing things!

We’ve made some more backend changes to how the AIO Keys are generated and maintained. If you run into any authentication or strange AIO Key issues please let us know as soon as possible in the IO forum or Issues Tracker.

Most of the changes were security enhancements, but we’re laying the groundwork for more features specifically related to the AIO Keys, including added administration functionality.

We started to notice some speed issues yesterday with certain queries in one of the largest tables (~50 million rows) in our PostgresSQL database, and we wanted to share some of the results of the investigation.

During our tests today, we noticed that the slow queries ordered by id DESC were running in about 5 seconds, and queries that were ordered by created_at DESC were running in 0.1 milliseconds. Multicolumn indexes were present for both queries to use, and EXPLAIN ANALYZE showed that the queries hitting those indexes with an index scan.

This led to the obvious question: wtf?

After some investigation, we found that the order of columns in the index were reversed. One had the columns defined as (feed_id, created_at DESC) and the other had the columns defined as (id DESC, feed_id). After looking at the PostgresSQL docs for multicolumn indexes, the issue was clear.

It turns out that you need to define your indexes with a constraint (feed_id) first, so that you are only scanning a portion of the index. By defining id first, the full index was scanned, which resulted in very slow queries. We switched the order of the columns in the index to (feed_id, id DESC) and the queries are now running in the ~0.1 ms range, so you should notice that things are now much more responsive.

Please check out the relevant docs in the PostgresSQL 9.5 manual if you would like more info about multicolumn indexes.

We are considering a change to the way IO handles the retain flag on MQTT publish packets. The retain flag is used to allow the MQTT broker to retain the sent value, and will tell the server to pass it to any new subscribers as soon as they connect. We found that this feature was a little hard for people to understand at first, so we decided to force the retain flag to true for users by default. This change ensured that users always got the current state of their IO feeds as soon as they connected, without having to send a HTTP request to the REST API for the current value.

The decision to force the retention of values caused issues with some users, because they lost the option to decide which values were cached. We are planning to revert this change, and give people the option to cache the values using the retain flag.

In addition to this change, we are thinking of extending the meaning of the retain flag, and also allowing users to decide which values are logged to the database. So for example, if you wanted to push out temperature values every second to your connected devices, but only wanted to log the temperature value every minute, you could do so by only setting the retain flag to true for the values you wish to log. This should allow for greater flexibility when logging, and will result in increased speed for feeds that are focused on realtime interaction. We welcome your feedback in the IO forum.

If you would like to read more about the MQTT retain flag, please check out this article on HiveMQ’s blog.

Here are the stats for the past week:

* 29.03 million inserts of logged data in the last 7 days
* 10,969 users
* 8,675 online feeds (24,277 feeds total)