Solved

is it possible to have innodb_file_per_table on mutiple filesystem

Posted on 2006-11-07
9
430 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
0
Comment
Question by:db2inst1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17895756
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
 
LVL 7

Author Comment

by:db2inst1
ID: 17895781
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
 
LVL 35

Expert Comment

by:Raynard7
ID: 17895782
Symlinks are only supported for myisam tables
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-tables.html
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

Expert Comment

by:Raynard7
ID: 17895788
the separate locations - not with innodb tables, only myisam - also refer to the above article
0
 
LVL 7

Author Comment

by:db2inst1
ID: 17895857
I am looking for workarounds....? mutiple instances?
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17895892
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
 
LVL 7

Author Comment

by:db2inst1
ID: 17896030
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
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17896085
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
 
LVL 7

Author Comment

by:db2inst1
ID: 17934853
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP & MySQL - Rounding Results from a Select Query 3 42
Creating Functions in phpMyAdmin 8 36
Complex SQL statement in VB.NET 7 40
mysql qry 1 26
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question