If you are pushing the limits of the hardware, you should consider "sharding". For your application, it seems quite practical to put some users on one server, some on another. And keep adding servers as your business grows.
- Only two things (that I can think of) correlate to index size versus RAM size:
table scans (reading entire tables) and UUIDs.
- UUIDs and hashes (MD5, etc) can be a performance killers. Please provide details on how they are being used. This is a case where performance degrades when the index becomes bigger than RAM.
- 80% writes -- as in someone backs up a bunch of files; the table records the meta info. Is there a
- Please provide
SHOW CREATE TABLE for the main table(s), so I won't have to come up with questions one at a time.
- And let's see the typical queries (read and write).
- You really have 500K users on one machine?
- If 5K reads/sec is peak, what is writes/sec? Generally writes are tougher to scale.
I envision that one of your tables is indexed by sha2, and you use it for deduping the files as they come in. Quite possibly this is the most critical performance issue, but it would mostly be for reads, not writes. There are a few things that can be done to mitigate the issues; they mostly have to do with shrinking the table size. Again,
SHOW CREATE TABLE is needed.