Home / server / Questions / 1077347
In Process
yield
Asked: 2021-09-13 07:32:28 +0800 CST

MySQL 1 schema, 1 filesystem

  • 772

This is a ''general'' question. Hear me out.

Let's say I have a MySQL standalone or even a 3 or 5 nodes cluster. Would it be a good practice to have 1 filesystem per schema ?

For example, schema{1..5} would go in /var/lib/mysql/data/schema{1..5}

And I am not talking about RAID level under theses filesystems here... Just, plain FS. Let assume I use XFS here.

What gain would I potentially gain from it ?

  • Easier FS backup or even (LVM) backup/snapshot ?
  • Performance ?
  • Faster node data replication ?

What else...

mysql filesystems mysql-replication schema

1 Answers

  1. "1 filesystem per schema" on a single physical drive -- NO!

    • No performance advantage.
    • No replication advantage.
    • A big maintenance DISadvantage when one schema starts to exceed its filesystem. Then you have to stop everything and repartition the disk!
    • LVM snapshot is fast anyway; being "faster" is not worth the other issues.
    • RAID striping gives some performance advantage without the other issues. But that assumes you have multiple physical drives.
    • If you expecting to have more than a terabyte in one schema, then say so. That could lead to a different discussion.
    • 1