Is it beneficial to split huge MySQL database into several databases to increase performance?

There is a substantial MySQL database with data weighting hundreds of gigabytes. It experiences performance problems. Vertical and horizontal partitioning of tables are possible roads to increase performance.

In your opinion would splitting this database into several databases be beneficial to increase performance a开发者_开发百科s well?

The project is based on Rails

---

For InnoDB storage type you can specify to store each table in separate file

http://www.electrictoolbox.com/mysql-innodb-one-file-per-table/

This will help your file system with caching strategies for your tables.

---

"It experiences performance problems" is a symptom, not the root. It is time to do some database analysis and figure out the bottlenecks. Find the queries which take the longest to run (are they queries on a table, on twelve tables, are they updates, etc etc), and see what you need to do. How much control do you have over the database? That'll also determine what you can and can't do, of course.

If some subset of the data is accessed more often, maybe you can pool that in shadow tables - smaller tables which will be faster to access.

You can index more / less / other things.

Plenty of choices! But what is really wrong? :)

---

you might want to take a look at your storage type. depending on your database usage you might want to switch to another storage engine. http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

you also might consider using memcached to serve your data from the memory. there is also a good explaination on the mysql docs: http://dev.mysql.com/doc/refman/5.0/en/ha-memcached.html