MySQL 1 schema, 1 filesystem
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...
"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.