5

Does mysql support multiple file systems for a single database with most of the tables being on MyISAM?

Context: we have a 1.5TB mysql database, which is increasing at the rate of 200GB per month. The storage is directly attached, whose slots are almost full. I can add another DAS, and increase the file system. But resizing volume, resizing file system, etc are getting messy. Is there a concept of "tablespace, datafile" (like in oracle) in MySql world?

Or how you guys manage mysql db with these kind of constraints?

4 Answers 4

2

The short answer is "no". Sorry.

One workaround I've done is placing the innodb datafiles on one device, and the datadir on another. That has the effect of being a poor mans way of splitting your innodb and myisam tables across different storage. But it does not help you in a one-giant-table situation.

Similarly 5.1's native partitioning feature only breaks the table up into multiple files, but they all still live in the datadir.

If you expect this growth to continue indefinitely there's no way around it, you need to design an abstraction layer across multiple mysql servers. If you think it will taper you can probably get away with a large DAS or entry level SAN.

1

With MySQL 5.1+ you can partition across multiple disks. As long as the new storage appears as a logical drive, you should be able to use it for a partition.

The difficult part is probably going to be figuring our how to logically partition the database so you don't quickly run into storage constraints in the future.

Realistically, if your DB is growing at this rate, I would probably be looking for a large SAN or other multi-TB storage system, to prevent having to upgrade for a long time. I wouldn't want to be manipulating that amount of data very often. Something in the 30 - 50Tb range would last a long time at your growth rate.

1

That's a rather confused question. MySQL doesn't support file systems, that's the job of the operating system. In short, if the OS supports a particular file system and is itself supported by MySQL then MySQL will happily run on that file system. The DB engine used (MyISAM, Innodb, etc.) don't affect this at all.

Instead of adding storage in the ad-hoc manner you describe you should backtrack a bit and start with an actual plan. Work out how much storage space this DB will require and then set it up accordingly, using the RAID method most appropriate to the way the DB is used.

1
  • 3
    Confused question, maybe. But when look at how oracle solves this problem, the question doesn't appear confused. For instance, one can create an oracle schema (similar to mysql database) with default tablespace. This tablespace is a logical construct to all but oracle, but which consists of many file systems or raw volumes etc. I have this context in my mind when I asked the question I did.
    – RainDoctor
    May 4, 2010 at 18:48
0

i would shard that scary thing. otherwise you'll have really hard time taking backup of it and especially restoring or maybe adding an index/column.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .