Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

is it possible to have innodb_file_per_table on mutiple filesystem

We are on RH Linux MySQL 5.0.18

With innodb_file_per_table enabled, is there a way to isolate the data files to different filesystem? Right now all the .idb files goes into data directory and we end up having one big filesystem.

I take it symbolic linking doesn't work for innodb?

I can't think of anything better than having mutiple mysqld running to isolate the database and datadirectory. Any better options?

Thanks
0
db2inst1
Asked:
db2inst1
  • 5
  • 4
1 Solution
 
Raynard7Commented:
Yes you can have it in separate files, in the my.cnf you could do

[mysqld]
innodb_file_per_table

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

0
 
db2inst1Author Commented:
I do have it in seperate files, but is there a way to have it in seperate locations not just in the data dir.

For example

all the files from database1 should go to directory /mysql/data/database1
0
 
Raynard7Commented:
Symlinks are only supported for myisam tables
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-tables.html
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Raynard7Commented:
the separate locations - not with innodb tables, only myisam - also refer to the above article
0
 
db2inst1Author Commented:
I am looking for workarounds....? mutiple instances?
0
 
Raynard7Commented:
not sure - normally if I am running out of room just add another two HDD's to the raid.

what exactly are you wanting to acheive?
0
 
db2inst1Author Commented:
I didn't really like the idea of having one big filesystem.. Would like to isolate the tables atleast at database level for better I/O. Its a shared env, didn't want one busy db to impact others..

0
 
Raynard7Commented:
Unfortunatley innodb operates as one "tablespace" and operates across that space. Its a limitation of what it does and how it does it - is there any reason that you need innodb over myisam? becasue that way you can easily span your databases across different locations and use symlinks.

Now - I have read that you can symlink any particular table individually but not as a whole database.

I would think that IO is not going to be your bottleneck anyway - I would consider that processor speed / usage - would be this problem.  As I mentioned before maybe a RAID would be a better solution I use raid 10 and never have IO bottlenecks.

I think however that it may be for this reason that hosting companies seem to be disabling INNODB databases almost globally.
0
 
db2inst1Author Commented:
Thanks for all the information. I will leave the question open for few more days, and see if there is any other workarounds. :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now