Database architecture for interactions between users (8B rows, 1TB of data)
Something wrong with that!  There is no column counter??

But, assuming you meant one of the existing counters...


Having the counter in an index, and updating the counter frequently --> double the work.  Once to update the table, once to update the index.
Since the first part of the PRIMARY KEY is user_id_to, you have a very good way of locating the counter given the user_id_to.  So, DROP that index.  This will speed things up considerably.


Something else to check...  innodb_buffer_pool_size should be about 70% of RAM.  What are the value of those?

PARTITIONing is very unlikely to help.  Given a good index, partitioning is no faster because it is essentially turns a 1-step process (look up in the index) into a 2-step process: first find the partition ("pruning"), then use the index in that partition.

If possible, use either LOAD DATA or "batch" INSERTs.

What do the INSERTs look like?  Perhaps INSERT ... ON DUPLICATE KEY UPDATE?  For batching, that might be best done by creating a tmp table, then using IODKU with a SELECT on the tmp table.  More on high speed ingestion.

Some things look fishy, please provide specifics on the inserts, updates, and selects.