Up until now, I have been using MySQL because it is free and many (many, many,...) web sites use it for their database back end.
But I seem to be testing its limits - at least on the hardware I'm running it on.
I have been steadily growing a Java app (JEE5) using Apache OpenJPA to connect to the database.
When I first started, the EJB3 and JPA were still in development. EJB2 was too complicated for the benefit that I thought that I would get so I 'faked it' and created my own psuedo EJB app using: JDBC, servlets, servlets pretending to be EJBs, and POJOs.
The database performance was fairly bad because I have -severely- interlinked data model. So getting the data to display anything typically involves at least 8 tables linked heavily and repeatidly.
Here is a try at describing something similar to my structure. If you want a headache, try drawing out the relationship diagram.
- Master entity (1 -> M) Component
- Master entity (1 -> 1) Category
- Master entity (1 -> 1) Sub-Category
- Master entity (1 -> 1) Sub-Sub-Category
- Master entity (1 -> 1) Creator (User)
- Master entity (1 -> 1) Owner (User)
- Category (1 -> M) Sub-Category
- Sub-Category (1 -> M) Sub-Sub-Category
- User (1 -> 1) Department
- Component (1 -> 1) Component Class
- Component (1 -> M) Component Attribute
- Component Class (1 -> M) Class Attribute
- Class Attribute (1 -> M) Attribute Value (list of possible)
- Component Attribute (1 -> 1) Attribute
- Component Attribute (1 -> 1) Value
- Attribute (1 -> M) Attribute Value (list of possible)
JPA rescued me. Just changing my data access to use JPA increased the performance of my app about ten-fold. And, it simplified my code - I was in love.
Later EJB3 session beans took the place of my fake EJBs (servlets). And I was in love again.
But now, I have millions of rows in over a hundred tables. Linked together in ways that I could probably not even write JDBC to access anymore. And the data is stored in MySQL.
Don't get me wrong - I really do like MySQL. It has been good to me.
But my database with data and indexes is approaching three gig. As in three billion bytes - and MySQL is starting to have some trouble.
So I think I might need to stray.
Breaking up is hard to do though.