DB-murdering API query (index suggestions needed)

[Daniel Axtens]

> Today, the DB behind patchwork.kernel.org was in a semi-permanent state 
> of suffering due to someone trying to suck down all patches in the 
> linux-arm-kernel project. This is what the API request looked like:
>
> GET /api/1.1/patches/?project=62&before=2019-11-01T00:00:00&per_page=100&page=6150 
>
> The query behind this takes about 1 minute to run on a 20-core HT Xeon 
> system and requires creating a huge temporary file (there are 18375 
> patches in that project).

Ouch, I'm sorry to hear that.

>
> So, two questions, really:
>
> 1. Any indexes we can put in place to make this query perform better?

We have a bunch of db magic contributed by Stewart that will hit 2.2.

Stewart, do you happen to know if any of your magic will affect API
queries? They're advertised as affecting the general listing of patches
in the UI, I'm not sure if they also affect this.

If not, we can definitely have a look at getting an index or rate
limiting/authentication thingy in for 2.2.

Regards,
Daniel

> 2. Is there a way to disable anonymous API access?
>
> -K
> _______________________________________________
> Patchwork mailing list
> Patchwork at lists.ozlabs.org
> https://lists.ozlabs.org/listinfo/patchwork