Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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 7:37
    
Not to mention complicating every single query. –  adaptr Jan 16 '12 at 8:09
1  
i got one more idea - i've edited the answer. –  pQd Jan 16 '12 at 9:07
1  
One more to add; locks (say, from an ALTER TABLE) will be more localized. –  Shane Madden Jan 16 '12 at 18:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.