I wonder if this box is too small...

I am starting to think that I will need to take another look at the database that I am using.

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.