We have been dealing with a huge MySQL database on one of our projects.
Total byte size: > 150GB, Record count: > 100M (and growing daily)
Primary issue is of performance (this is a live site), currently hosted on a (fairly powerful) VPS.
Not having had in-house prior experience with such data volumes on MySQL, we hacked our own table partitioning architecture of sorts and also optimized the queries, table structures etc. to the max of our abilities. All this has been working reasonably well for us so far.
But now, with rising traffic levels, things are beginning to slow down again.
At this point, we're considering the following 3 options:
1) Throw more hardware resources at it
Pro: Instant gratification :) Cons: Cost + vicious cycle(?)
2) Try to optimize the database/config, table structures and queries further
Pro: Always useful Cons: Time + Effort
and
3) Migrate to PostgreSQL (or something else?)
Pros/Cons: Still studying
What do HNers with experience in such matters suggest? We're open to all suggestions. Thanks in advance.
Total byte size: > 150GB, Record count: > 100M (and growing daily)
Primary issue is of performance (this is a live site), currently hosted on a (fairly powerful) VPS.
Not having had in-house prior experience with such data volumes on MySQL, we hacked our own table partitioning architecture of sorts and also optimized the queries, table structures etc. to the max of our abilities. All this has been working reasonably well for us so far.
But now, with rising traffic levels, things are beginning to slow down again.
At this point, we're considering the following 3 options:
1) Throw more hardware resources at it Pro: Instant gratification :) Cons: Cost + vicious cycle(?)
2) Try to optimize the database/config, table structures and queries further Pro: Always useful Cons: Time + Effort
and
3) Migrate to PostgreSQL (or something else?) Pros/Cons: Still studying
What do HNers with experience in such matters suggest? We're open to all suggestions. Thanks in advance.