MySQL Drive Storage Allocation

Question

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.

Answer 1

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

• Jul 29, 2015 : Should the Redo log on a production MySQL server be configured to another location?
• Jan 27, 2015 : what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata
• Jun 04, 2014 : Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?
• Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
• Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?

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.

Answer 2

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.)