We help IT Professionals succeed at work.

is it possible to have innodb_file_per_table on mutiple filesystem

db2inst1
db2inst1 asked
on
Medium Priority
459 Views
Last Modified: 2008-01-09
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
Comment
Watch Question

Top Expert 2006

Commented:
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

Author

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
Top Expert 2006

Commented:
Symlinks are only supported for myisam tables
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-tables.html
Top Expert 2006

Commented:
the separate locations - not with innodb tables, only myisam - also refer to the above article

Author

Commented:
I am looking for workarounds....? mutiple instances?
Top Expert 2006

Commented:
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?

Author

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

Top Expert 2006
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for all the information. I will leave the question open for few more days, and see if there is any other workarounds. :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.