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.
p.s. We're running on MySQL 5.0.77