How much of our internet infrastructure will be underwater in 15 years?

I follow Alexandra Dechamps Sonsino on twitter, and I learn a colossal amount from what she share, but some recent links she shared really got me thinking. I’ve written previously about how tech and the internet plays havoc with our climate because it relies on fossil fuels. It looks like the climate is wreaking havoc right back.

TLDR: Burning fossil fuels to run the internet our worsens climate change. Now rising sea levels look like they’ll swamp our infrastructure back.

This piece from last year in the National Geographic is eye-opening, about how rising sea levels are affecting the operation of the internet. Because commercial firms don’t disclose this, the authors ended up needing to scrape loads of pages to get an idea where all that infra was, and found out this:

Cities like New York, Miami, and Seattle are likely to see up to 12 inches of extra water by 2030—well inside the time range of a mortgage on a house, or the planning horizon for big public infrastructure projects. A foot of extra water wending through some of those cities, the researchers say, would put about 20 percent of the nation’s key internet infrastructure underwater.

They name specific companies in the paper, like AT & T, Century Link and so on, whose infrastructure is at risk. Above a certain size of company, there are climate related financial disclosures it really should be sharing, for the benefit of investors, suppliers,, customers and so on, and there are companies who are doing this.

One good example is Etsy, who last year started integrating environmental reporting with financial reporting.

Here’s what they say, specifically, referring to the Sustainability Accounting boards Standards:

Discussion of the integration of environmental considerations into strategic planning for data center needs. Etsy’s goals include powering our operations with 100% renewable electricity by 2020, and reducing the intensity of our energy use by 25% by 2025.

These goals are included as key considerations as we plan for our computing needs, and have been a focus of our sustainability efforts. When transitioning to a cloud computing infrastructure, we selected Google Cloud Platform, a partner that shares our commitment to 100% renewable electricity. Their highly efficient datacenters are expected to help us save significant energy. Moreover, moving to flexible cloud-based infrastructure should enable us to reduce major idle time and associated energy consumption.

In 2018, Etsy entered into a virtual power purchase agreement for solar energy in Virginia. Once operational, this project is expected to provide us with renewable attributes to apply to our operations and computing infrastructure, furthering our goals of creating a cleaner internet and reducing our impact on the planet. We actively monitor and manage energy consumption from our computing infrastructure.

In 2018, our colocated data centers accounted for 68% of total energy consumed, or 7330 MWh.

From Etsy’s SASB section of their SEC filing for 2018

The paper cited though, Lights Out: Climate Change Risk to InternetInfrastructure goes further. It literally shows where there is projected flooding, and where there is infrastructure where the flooding will happen:

I’m not aware of much in the way of publicly accessible data listing this, and I’m not aware of research like this outside of the states.

It seems kind of useful to know how much of the biggest machine on earth, that many of we use rely on every day, will be underwater in the next few years though, surely?

If you’re working in this field, I’d love to chat. Better yet, come say hi in ClimateAction.tech.

Outcomes, goals, objectives

This diagram from Jamie Arnold turned up in my timeline yesterday, and it I liked it so much, that seemed worth a quick write up:

Outcomes, goals and objectives – way to talk about what you’re doing

I think a nice way to frame it:

A Goal in this case is broad direction you’re heading in. Head in this direction!

An Outcome is the benefit from achieving this goal. This is where you arrive

The Objective is the specific, measurable, time-bound thing you’d need to meet reach this outcome

A Deliverable is a thing, like an artefact or similar, you might produce, that ideally would achieve the objective or goal.

Why I like it

I like it, as it’s bit more detailed than when people try to come up with missions vs visions for working out what they’re trying to do, and provides a nice way to go from something high level and aspirational, to something you can point for working on.

Mission – the reason you exist, the thing you to reach the state you describe in your vision.

Vision – the ideal state you’d be in, if what you’re doing works out.

An example from the mission and vision statement for Climate Action Tech, a group I work with, might help in this case:

Mission: We empower technology professionals to take climate action

Vision: Everyone is working on the climate crisis at all levels, and driving industry and society toward a sustainable future.

Climate Action Tech mission and vision statement as of Jun 2019

Complementary tools

It also seems to be complementary to Impact Mapping, which is another fairly useful tool for framing what you’re looking to do on a project, or inside an organisation. I like this, as they’re explicit about who you’re aiming some kind of intervention or initiative at

Here’s the original tweet where I saw it:

Notes as I learn about tuning MySQL

I’ve been doing some work with MySQL again with the Green Web Foundation. This has involved working with relatively large sets of data, so to make some queries run faster, I’ve found myself looking at the settings it uses by default, and having to understand what queries are doing under the hood. These are some of my notes, mainly for my future self.

Like earlier Postgres, earlier MySQL has smalll memory defaults

When I was last working with servers directly, and having to fiddle around with settings, I learned that early versions of Postgres has defaults to be really conservative about how much RAM the server would assume it could access on a machine.

This is good news in terms of not eating up all your memory, but less good for actual performance, you’d often end up with RAM lying around unused, when it could be put to work making things run faster.

The story is similar with MySQL – if you’re using a version of MySQL like 5.6 or earlier,and no one’s been playing with the settings, you maybe using the default tiny amount of memory, and relying on reads or writes to disk, slowing everything down.

Different storage engines need different kinds of settings

One key difference between the two is that MySQL has the idea of swappable storage engines, where as with PostGres, you only have the one.

What’s more over the last 10 years or so, the old default storage engine for MySQL, MYISAM, given way to the more capable InnoDB storage engine.

This is good if you have new projects, but less so for old ones. What’s more, if you have some tables which use MYISAM, and some which use InnoDB, you now need to thinkg about two sets of settings to tweak.

I’ll list the key settings in each case that I’ve found.

MYISAM

key_buffer_size – MySQL relies on this setting to work out how much space it has to use for storing parts of table’s indexes in memory to quickly find stuff. This isn’t the full picture though – as this only covers indexes.

If there’s actual data that needs to be kept in cache, it relies on whatever the server operating system uses to cache files, to avoid reading from a disk. If you’re using Linux, this means the disk caching that it uses by default to keep commonly used data in memory anyway. But this also means that you need to leave space for it.

InnoDB

innodb_buffer_pool_size – by comparison, InnoDB uses single setting to let you explicitly decide how much RAM you want to allocate as a buffer for indexes and data. This is likely the single most important thing to change if you’re using primarily InnoDB tables. There’s

It also outlines why a mix of MYISAM and InnoDB tables can be a pain – you now have two sets of knobs to twiddle for performance, when it would be so much nicer to just have one set.

Global vs per-thread settings

Like Postgres MySQL supports lots of clients reading or writing at the same time to a given database via a pool of connections. And in addition to setting global settings like innodb_buffer_pool_size, or key_buffer_size, you there are also per client settings – these decide how much memory might either be allocated for a client to have to available for various queries and so on.

In my use case, I’m needing to do a bunch of sorts, and groupings, which rely on creating temporary tables when working. One important setting in this case is tmp_table_size, which decides how much memory is allocated for making temporary tables to speed these actions up (see more in the docs).

This piece from Percona’s blog on MySQL memory usage, even though it’s 13 years old now was pretty helpful.

Understanding queries with EXPLAIN

Just like Postgres, you can add EXPLAIN at the beginning of any queries to get a better understanding of what they’d be doing under the hood then the query runs.

This post from sitepoint explains how to use it – it’s nowhere near as comprehensive as Postgres’s version of EXPLAIN, but it at least tells you how MySQL will try to find the rows you care about.

Setting session level settings for a client

I mentioned before about per client settings, in addition to global settings.

With MySQL, you can set things like read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size, but make them only apply for a given session – this is useful in the case of you having a loads of normal kinds of requests and queries you need to support, but there also being occasional jobs where you it would be really helpful to have much more memory available to make big sorts, and joins and so on.

For this, you can set it in query, like so:

SET SESSION sort_buffer_size = 4 * 1024 * 1024

At the end of the session it’ll be unset, which is useful, as otherwise setting these values globally can overwhelm a server, when you have 300 requests suddenly using this massivelt greedy default.