perform_spin_delay() vs wait events

Hi,

The lwlock wait queue scalability issue [1] was quite hard to find because of
the exponential backoff and because we adjust spins_per_delay over time within
a backend.

I think the least we could do to make this easier would be to signal spin
delays as wait events. We surely don't want to do so for non-contended spins
because of the overhead, but once we get to the point of calling pg_usleep()
that's not an issue.

I don't think it's worth trying to hand down more detailed information about
the specific spinlock we're waiting on, at least for now. We'd have to invent
a whole lot of new wait events because most spinlocks don't have ones yet.

I couldn't quite decide what wait_event_type to best group this under? In the
attached patch I put it under timeouts, which doesn't seem awful.

I reverted a4adc31f690 and indeed it shows SpinDelay wait events before the
fix and not after.

Greetings,

Andres Freund

[1] https://postgr.es/m/20221027165914.2hofzp4cvutj6gin%40awork3.anarazel.de