4

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?

Arrix
  • 225
  • 1
  • 5

1 Answers1

4

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.

Undo
  • 291
  • 6
  • 17
pQd
  • 29,561
  • 5
  • 64
  • 106
  • Thanks pQd, I see several operational benefits too. One of my colleague suggests that splitting can make the index B-tree smaller and make query cache last longer. But I think the saving is too trivial to make a difference. On the other hand, more tables also add more overheads. – Arrix Jan 16 '12 at 07:37
  • Not to mention complicating every single query. – adaptr Jan 16 '12 at 08:09
  • 1
    i got one more idea - i've edited the answer. – pQd Jan 16 '12 at 09:07
  • 1
    One more to add; locks (say, from an `ALTER TABLE`) will be more localized. – Shane Madden Jan 16 '12 at 18:10