Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm in the middle of designing a solution to an issue we're currently experiencing while writing to certain large MyISAM tables. The database in question basically stores information regarding two 'things', users and articles. The majority of the user tables are small but are read from and written to regularly. The majority of the article tables are very large but are pretty much only read from. Lots of the tables are linked through foreign keys (between the user tables, between the article tables and between user and article tables), and there are several views. The server is fairly hefty and has no problem serving the data under normal conditions.

The issue arises when we need to update the article data (which is now required every few days) and on doing so causes a backlog of writes to a few of the larger tables. I had thought about separating the user data and article data into separate databases and then running the article update on a cloud server then migrating the updated data files over to the database server and switching MySQL over to the updated data. I know it's possible to specify the data directory in the my.cnf files all data but haven't been able to find out whether it's possible to specify data directories for specific databases. If not then this idea becomes pretty useless. Any information on either whether this is possible (or not) or any other solutions would be great.

Thanks

p.s. We're running on MySQL 5.0.77

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Rather than retype a lot information, here are some posts I made in the DBA StackExchange before on how to move MyISAM tables to different folder and disks

share|improve this answer
    
Thanks for this it's all very enlightening. Could you tell me whether it's possible to specify the data directory for a specific database rather than an individual table, would symlinking the database data directory work and if so would then changing the symlink so it's pointing to a different directory cause any issues? Many Thanks –  user761389 Mar 8 '12 at 11:43
    
Yes you could shutdown MySQL, set up your symlink, move your data to the symlinked folder, and start MySQL –  RolandoMySQLDBA Mar 8 '12 at 12:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.