Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives.

Question : Is it possible to improve performance by attaching multiple drives of reduced space, because this way more heads will be available to read data?

Like replacing 2TB 7.2k RPM drive with 4 500GB 7.2k RPM drives?

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

This could depend a great deal on the storage engine.

For MyISAM, I think it would be a great idea because you can make data as contiguous as a you want. That would benefit queries involving bulk operations and large range scans. The toolset for compressing and repairing MyISAM to improve the table format can also make access a little better. IMHO concurrent INSERTs could thrive in such a disk environment.

InnoDB is a completely different story.

If you have innodb_file_per_table disabled, data can be scattered throughout ibdata1 and thus scattered amongst the multiple disks. You could run OPTIMIZE TABLE against an InnoDB table and make the data and index pages contiguous, but that could cause two problems:

  1. Makes the ibdata1 grow
  2. Deteriorate performance over time

Don't forget the four types of data that reside in ibdata1:

  • Table MetaData
  • Table Data Pages
  • Index Data Pages
  • MVCC Data

There is constant writing of MVCC Data to protect data integrity. There is also constant interaction with ib_logfile0 and ib_logfile1 (redo logs). There is constant data flushing every 10 seconds from the InnoDB Buffer Pool back to the . Overall, it is safe to say that the spread of data across multiple disks and the I/O performance can show marginal-to-modest performance improvement until there is enough fragmentation within ibdata1.

Now, if innodb_file_per_table is enabled, things could be a little messier. Here is why:

Once you cleanup ibdata1 and separate all InnoDB tables from ibdata1, you will have a smaller ibdata1. However, each access to an InnoDB table requires navigating its use through ibdata1 ALL THE TIME via Table Metadata. You literally cannot semantically separate the table metadata (ibdata1) from the table (.ibd). Wherever ibdata1 lives, disk I/O must always pass through ibdata1.

Overall effect: Table fragmentation is limited to .ibd files and can be reduced and reorganized with OPTIMIZE TABLE, but no additional I/O performance enhancement is really achieved.

What about putting all .ibd files into a different set of disks apart from ibdata1 and using symlinks? According to Percona, that creates more mayhem.

CONCLUSION

When it comes to using multiple drives over a single drive, I highly favor MyISAM over InnoDB.

share|improve this answer
add comment

Yes, multiple drives will provide better throughput thanks to the wonderful technology we call RAID. For a database you'll want RAID 10 (striped mirrors).

With 4(N) drives you would double(N/2) your write speed and quadruple(N) sequential read throughput. Seek times will stay roughly the same though, and you'll lose half the space to mirroring.

share|improve this answer
    
Actually, if throughput for large blocks is a significant factor in a relational database, you are clearly doing something wrong. Mirrored disks will indeed give you a significant boost during read operations, but striping is something between ineffective and counterproductive. –  Simon Richter Oct 31 '11 at 14:03
    
Please do elaborate.. –  eevar Nov 8 '11 at 16:27
    
The speed gain in a striping setup results from having to reposition the heads less often during large reads/writes, however databases usually access small areas, so seek times remain the limiting factor. The mirror allows us to select a disk that is near or idle during reads, so while seek time for individual blocks remains the same, the cumulated seek time for a list of blocks is lower. –  Simon Richter Nov 8 '11 at 16:45
    
Striping keeps a 1:1 mapping between disks and blocks. For individual data page accesses there is a chance that you may gain performance if the mirror keeping the stripe currently accessed has an idle disk, but any access over a stripe boundary (which is likely during an index scan) will require multiple disks to seek in order to make the entire index available. As you would optimize your database to keep indexes for common queries, striping is likely to slow down common and speed up uncommon queries. –  Simon Richter Nov 8 '11 at 16:52
    
I'm sorry, but I've never seen any significant increase in seek times due to striping. Wikipedia suggests the opposite effect. –  eevar Nov 10 '11 at 15:45
show 2 more comments

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.