1

In our production servers for SQL Server, we usually split our drives by Software, Logs, Data, and Backups. I've been assigned to help with a MySQL installation (On Windows Server) and I'm not as experienced in setting up the environment with it. We are working with 500 GB.

Are there best practices with the drive assignments? Or does MySQL best manage everything on a single drive? Most of what I find on the web has to do with configuring InnoDB and Partitioning.

0

YOSHINORI MATSUNOBU'S BLOG would be the place to start. He recommends splitting up files that do sequential writes from files that do random reads and writes.

  • Random i/o oriented:
    • Table files (*.ibd)
    • UNDO segments (ibdata)
  • Sequential write oriented:
    • REDO log files (ib_logfile*)
    • Binary log files (binlog.XXXXXX)
    • Doublewrite buffer (ibdata)
    • Insert buffer (ibdata)
    • Slow query logs, error logs, general query logs, etc

I have referred to this blog in some of my older posts

Since you are doing this in Windows, you really need to split up the InnoDB files. Just remember when specifying a Drive to use drive letter, colon, and forward slash or double backslash. See my answer to Change existing datadir path for an example.

0

If you have multiple drives of the same type (spinning vs. SSD), stripe the drives (or use RAID-5 or -10) for optimal performance. Better yet, include a hardware RAID controller with Battery Backed Write Cache. With that, writes are essentially instantaneous (apparently faster than even SSDs).

If you have both spinning and SSD, put sequential things on spinning drives in hopes of minimizing arm motion; put random things on SSDs, which don't care about randomness.

The common pitfall in manually laying out drives is that you run out of space on one drive while having lots of free space on others.

(Yes, Rolando and I disagree.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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