Month: November 2018

Reducing Azure Functions Cold Start Time

by

You can host a serverless function in Azure in two different modes: Consumption plan and Azure App Service plan. The Consumption plan automatically allocates compute power when your code is running. Your app is scaled out when needed to handle load, and scaled down when code is not running. You don’t have to pay for idle VMs or reserve capacity in advance.

The consumption plan is likely the one you opted to use. You get 1 million free executions and 400,000 GB-s of resource consumption per month and pay-as-you-go pricing beyond that. It can also scale massively. The downside of using the consumption plan is that if there is no activity after five minutes (the default), your code will be unloaded by the Azure Functions host. The next time your function is invoked, your function will be loaded from scratch. I’ve experienced start up times ranging from a few seconds to several minutes.

With version 1.x of Azure functions you can raise the unload functiontimeout to 10 minutes in the host.json settings file.

If you want to keep your functions in memory, you either need to be calling them more frequently than the function timeout setting, or you could create another function in the same app. service plan that gets called periodically using a timer trigger:


using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;

namespace SQLFrontlineLoaderFunctionApp
{
    public static class KeepAliveTimerFunction
    {
        // Timer trigger is in UTC timezone 
        [FunctionName("KeepAliveTimerFunction")]
        public static void Run([TimerTrigger("0 */9 * * * *")]TimerInfo myTimer, TraceWriter log)
        {
            log.Info($"Alive: {DateTime.Now}");
        }
    }
}

This empty function will be called every 9 minutes (“0 */9 * * * *”). You could also keep your functions warm during a certain period during the day. This timer trigger cron expression would run every 9 minutes between 4am and 10am (UTC time zone): TimerTrigger(“0 */9 4-10 * * *”)]

Refs.

Postgresql Unused Indexes

by

To find indexes that have not been used since the last statistics reset with pg_stat_reset(), run this on your production server:

SELECT 
    u.schemaname,
    u.relname AS tablename,
    u.indexrelname AS indexname,
    pg_relation_size(u.indexrelid) AS index_size
FROM 
    pg_catalog.pg_stat_user_indexes u
    JOIN pg_catalog.pg_index i ON u.indexrelid = i.indexrelid
WHERE
    u.idx_scan = 0           -- never been scanned
    AND 0 <> ALL (i.indkey)  -- no index column is an expression
    AND NOT EXISTS           -- index not used to enforce a constraint
       (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = u.indexrelid)
ORDER BY 
    pg_relation_size(u.indexrelid) DESC;

Indexes that are very rarely used can also be good candidates for removal; replace u.idx_scan = 0 in the query with a different threshold, e.g. u.idx_scan < 5. BUT be aware of indexes whose purpose is to support queries which are run infrequently such as monthly reporting queries.

And, always script out your indexes before you remove them.

Further useful index scripts can be found here.