Home / server / Questions / 350216
Accepted
Arrix
Asked: 2012-01-16 22:47:56 +0800 CST

What's the advantage of splitting a mysql table into smaller ones without physical distribution?

  • 772

I've seen such design in my company.

A big mysql table is split into tens of smaller ones of identical schema on the same server in the same db, without any physical distribution, no innodb_file_per_table, no partitioning, etc. The data is distributed evenly and the read/write access pattern is uniform across all tables.

What's advantage of doing so performance-wise?

performance mysql database sharding

1 Answers

  1. Best Answer

    If your reads/writes are distributed evenly, I think there's no performance advantage. If you were write heavy and writing to, for example, just 2-3 of the tables - then you could benefit (see slides 28-34 of this presentation).

    A few ideas about operational benefits:

    • It'll be easier to scale out/shard once there's too much data or too much traffic.
    • Depending on how your backups are made, it might be quicker to recover something if you can determine in which of the tables lost data was located.

    Edit: one more thought: if the queries are executed in parallel, there might be performance benefits - you can get more IOPS from your RAID, and better localisation of multi-core system if the working set fits in the memory.

    • 4