I started doing some analytics with hive on webrequest data for upload, reporting the queries here for reference. Note that running a query over a month of data took ~1h, writing the query into another table allows for faster querying/processing later.

create table filippo.thumb_pixels_201704  as select regexp_extract(webrequest.uri_path, '.*/.*?(\\d+)px-.*', 1) as pixels, response_size from wmf.webrequest where webrequest_source='upload' and year = 2017 and month = 4  and uri_path like '%/thumb/%px-%';

insert overwrite directory '/user/filippo/thumb_pixel' row format delimited fields terminated by ' ' select pixels, count(pixels) as count, avg(response_size) as avg_size from thumb_pixels_201704 group by pixels;

And a rough estimation of the long tail, note that ~60% of sizes have been requested less than 1000 times in april. Only 4% of sizes are requested more than once per second (on average in april)

stat1004:/mnt/hdfs/user/filippo/thumb_pixel$ cat * | sort -k2 -rn > ~/pixel_count_avgsize
$ wc -l ~/pixel_count_avgsize 
10528 /home/filippo/pixel_count_avgsize
$ for i in 1000 10000 50000 150000 1100000 2200000 ; do
> echo -n "$i => " ; awk "\$2 > $i {print}" ~/pixel_count_avgsize | wc -l ; done
1000 => 3032
10000 => 1847
50000 => 1301
150000 => 984
1100000 => 572
2200000 => 432

Some more frequency distributions of width in pixels vs number of requests to cache_upload during April using bitly's data hacks

Change 351793 had a related patch set uploaded (by Filippo Giunchedi; owner: Filippo Giunchedi):
[operations/software@master] thumbstats: add Hive export

https://gerrit.wikimedia.org/r/351793

Change 351793 merged by Filippo Giunchedi:
[operations/software@master] thumbstats: add Hive export

https://gerrit.wikimedia.org/r/351793

I've extracted some data from the list of thumbnails we are storing in swift and processed it with hive, distribution of size vs number of thumbnails we store (more decent graphs coming)

$ sort -n pixel_count_size | grep -v -- '\N' | awk '{ print $1 " " $2 }' | ~/.local/bin/histogram.py -A --max 5000 -p
# NumSamples = 932405127; Min = 0.00; Max = 5000.00
# 370902 values outside of min/max
# Mean = 146.773189; Variance = 911349252814.819092; SD = 954646.140104; Median 7048.000000
# each ∎ represents a count of 8650312
    0.0000 -   500.0000 [648773431]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (69.58%)
  500.0000 -  1000.0000 [156884854]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (16.83%)
 1000.0000 -  1500.0000 [76993267]: ∎∎∎∎∎∎∎∎ (8.26%)
 1500.0000 -  2000.0000 [24060769]: ∎∎ (2.58%)
 2000.0000 -  2500.0000 [4634774]:  (0.50%)
 2500.0000 -  3000.0000 [18740364]: ∎∎ (2.01%)
 3000.0000 -  3500.0000 [842644]:  (0.09%)
 3500.0000 -  4000.0000 [357358]:  (0.04%)
 4000.0000 -  4500.0000 [459133]:  (0.05%)
 4500.0000 -  5000.0000 [287631]:  (0.03%)

And size vs bytes stored

$ sort -n pixel_count_size | grep -v -- '\N' | awk '{ print $1 " " $3 }' | ~/.local/bin/histogram.py -A --max 5000 -p
# NumSamples = 93158107977469; Min = 0.00; Max = 5000.00
# 1119622544767 values outside of min/max
# Mean = 0.001469; Variance = 9892379536331.593750; SD = 3145215.340216; Median 7048.000000
# each ∎ represents a count of 264775386184
    0.0000 -   500.0000 [12839588070271]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (13.78%)
  500.0000 -  1000.0000 [19220388721260]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (20.63%)
 1000.0000 -  1500.0000 [19858153963807]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (21.32%)
 1500.0000 -  2000.0000 [12166146568579]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (13.06%)
 2000.0000 -  2500.0000 [4265428797593]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (4.58%)
 2500.0000 -  3000.0000 [19725491234058]: ∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎∎ (21.17%)
 3000.0000 -  3500.0000 [1441196798896]: ∎∎∎∎∎ (1.55%)
 3500.0000 -  4000.0000 [787038598971]: ∎∎ (0.84%)
 4000.0000 -  4500.0000 [920468982555]: ∎∎∎ (0.99%)
 4500.0000 -  5000.0000 [814583696712]: ∎∎∎ (0.87%)

What "size" are we talking about when you have a range like 0.0000 - 500.0000?

@Gilles the image width in pixels, i.e. the user-provided size in the url

OK. I'm not sure it makes sense to group them in ranges, we should be looking at widths individually. I.e. 1024 is probably very prevalent, while 1025 isn't, and if you're going by ranges they'd be in the same bucket.

@Gilles indeed for "number of thumbnails stored" I think it makes sense to extract a topN. Anyways I'm still thinking through the criterias that might make sense for deletion, if you have ideas and suggestions please let me know!

So far one thing that jumped my eye is that width >= 2000 pixels is practically never requested, yet sizes >= 2000 pixels account for ~30% of space used on swift, so tackling that might be a good first candidate for space savings.

Indeed, sounds like an easy win

@Gilles indeed ! I'll figure out the best way to get a list of objects to delete and batch-delete

I took another look at the data from a "topN" perspective, comparing widths stored vs widths requested:

Stored in swift:

Widths requested on cache_upload during 201704

Observations:

1. We can stop pre-generating 2880px and 2560px I think, they take up a lot of space but have been requested 1283304 / 7774542 times and (537th place and 334th place)
2. The second-biggest size (1920px) should stay, requested 63241046 times (119th place)

The data is also available on this google spreadsheet

The top 100 most requested sizes represent 91.21% of all requests. The remaining long tail (any size not in that 100 whitelist) represents 68% of the storage size.

It would be interesting to know for the long tail how many requests are complete varnish misses going to swift. We would know exactly how much extra traffic would go to the image scalers if we only stored the 100 most requested sizes in Swift. Can you generate that data for all sizes? (# of full Varnish misses going to Swift for 201704).

In T162796#3248654, @Gilles wrote:

The top 100 most requested sizes represent 91.21% of all requests. The remaining long tail (any size not in that 100 whitelist) represents 68% of the storage size.

It would be interesting to know for the long tail how many requests are complete varnish misses going to swift. We would know exactly how much extra traffic would go to the image scalers if we only stored the 100 most requested sizes in Swift. Can you generate that data for all sizes? (# of full Varnish misses going to Swift for 201704).

Indeed, I'm creating another hive table from webrequest including cache_status which should be able to tell us what happened to a particular thumb

create table filippo.webrequest_upload_pixel_response_cache_201704  as select regexp_extract(webrequest.uri_path, '.*/.*?(\\d+)px-.*', 1) as pixels, response_size, cache_status, uri_path from wmf.webrequest where webrequest_source='upload' and year = 2017 and month = 4  and uri_path like '%/thumb/%px-%';

@Gilles I've added two more sheets for hit and miss+pass from webrequest to the spreadsheet, looks like in April there were ~400M misses for sizes non-top100

400m misses means 154 requests per second. It would at least triple the load on Thumbor. Might be possible if/once we've repurposed all existing image scalers to Thumbor.

Change 353244 had a related patch set uploaded (by Filippo Giunchedi; owner: Filippo Giunchedi):
[operations/mediawiki-config@master] Stop prerendering thumbs at 2560/2880 pixels

https://gerrit.wikimedia.org/r/353244

Change 353244 merged by jenkins-bot:
[operations/mediawiki-config@master] Stop prerendering thumbs at 2560/2880 pixels

https://gerrit.wikimedia.org/r/353244

Mentioned in SAL (#wikimedia-operations) [2017-05-11T13:31:00Z] <addshore@tin> Synchronized wmf-config/InitialiseSettings.php: SWAT: T162796 [[gerrit:353244|Stop prerendering thumbs at 2560/2880 pixels]] (duration: 00m 41s)

List of candidates for deletion: (note some criteria might overlap)

Mentioned in SAL (#wikimedia-operations) [2017-05-25T15:43:56Z] <godog> delete thumbnails with > 2000px for wikivoyage / wikiversity / wikisource / wikiquote - T162796

I started deleting today all thumbnails with widths > 2000px in small containers (i.e. non-commons)

With a container at a time we're issuing around 20 delete/s, meaning ~6h to delete all 460k objects (480GB) for non-commons containers.

For commons we're looking at 23M objects to delete (26TB), at 20 delete/s that's ~13d when done serially per-container, or less when done in parallel.

I suggest to use parquet for analytics-usage tables:

create table filippo.webrequest_upload_pixel_response_cache_201704

STORED AS PARQUET

as select regexp_extract(webrequest.uri_path, '.*/.*?(\\d+)px-.*', 1) as pixels, response_size, cache_status, uri_path from wmf.webrequest where webrequest_source='upload' and year = 2017 and month = 4 and uri_path like '%/thumb/%px-%';

thanks @JAllemandou ! I've converted the tables to use parquet and dropped the old plaintext tables