Trying to find a way fit an extremely large index in to memory
Fitting the index into RAM is not a useful goal.  Decreasing the number of blocks of the index to use is a useful goal.

"Using intersection" is not as fast as the following composite index.  Both of your queries would benefit from

INDEX(username, bare_peer, timestamp)


When adding it, you can drop i_username since it is a prefix of this.

PARTITIONing, even if possible, is unlikely to improve performance.

Also, normalizing out the 4 names (in the same table?) would shrink this table significantly, thereby helping performance a little.

What version of MySQL?  If it a new one, adding the above index should be relatively non-invasive.

More on designing indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql