Ask HN: Dealing with a huge MySQL database - help • Hacker News Board

Ask HN: Dealing with a huge MySQL database - help

#1

Post by webvet » Sun, Sep 08, 2013, 3:05 PM UTC

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.

Re: Ask HN: Dealing with a huge MySQL database - help

#2

Post by CWIZO » Sun, Sep 08, 2013, 3:14 PM UTC

Do you have any sort of caching in your app? Memcache, for instance, can go a long way in helping you scale.

Re: Ask HN: Dealing with a huge MySQL database - help

#3

Post by sgaither » Sun, Sep 08, 2013, 3:16 PM UTC

How slow is "slow" for you right now? And writes vs. read? Which MySql storage engine are you using?

Re: Ask HN: Dealing with a huge MySQL database - help

#4

Post by Joyfield » Sun, Sep 08, 2013, 3:17 PM UTC

You give us two values (150GB and 100M+ rows). That is not enough to give you ANY suggestions other than give us more info.

Re: Ask HN: Dealing with a huge MySQL database - help

#5

Post by webvet » Sun, Sep 08, 2013, 3:21 PM UTC

CWIZO wrote: post #2

Do you have any sort of caching in your app? Memcache, for instance, can go a long way in helping you scale.

We don't have Memcache (or anything similar) presently, though I do believe we looked at it sometime last year. Will get a re-check done on this - thanks. :)

Re: Ask HN: Dealing with a huge MySQL database - help

#6

Post by netaustin » Sun, Sep 08, 2013, 3:24 PM UTC

Here are some guiding questions:

1) Do you actively need all 100M records, or is there a period after which you can archive them?

2) Have you partitioned data across multiple servers, or just multiple tables on one server?

3) What are the nature of your expensive queries? Are you generating reports? User dashboards?

I think a general idea of what your application is doing would help us provide some guidance.

Re: Ask HN: Dealing with a huge MySQL database - help

#7

Post by a3n » Sun, Sep 08, 2013, 3:24 PM UTC

"Not having had in-house prior experience with such data volumes on MySQL ... At this point, we're considering the following 3 options:"

4. Hire or contract a consultant/expert. Part of their assignment could be to teach you.

Re: Ask HN: Dealing with a huge MySQL database - help

#8

Post by ainsej » Sun, Sep 08, 2013, 3:24 PM UTC

Caching is definitely a must have, both an external cache (Memcache, Redis) and tuning MySQL's own caches in the configuration, using InnoDB and MySQL means you can configure a large buffer in the MySQL config to speed things up considerably

Disk speed could also be a huge issue, especially on a VPS where resources are shared between many virtual machines, consider migrating your database off to a dedicated server (Preferably one with an SSD), depending on where you are in the world OVH might be a good choice.

Re: Ask HN: Dealing with a huge MySQL database - help

#9

Post by mjhea0 » Sun, Sep 08, 2013, 3:24 PM UTC

In my experiences, the size of the database nor the number of records really matter. It's the number of queries that you are handling at one time that can really affect performance.

Sure, if you have a single table with a ton of records this can slow things down - but not nearly as much as dealing with a high number of queries.

I highly recommend this book - http://www.amazon.com/dp/0596101716/?tag=stackoverfl08-20

Yes, caching can really help. Logically placed indexes can too. Switching to postgres will not help. The bottleneck will still be there. The key is to find that before you start tuning/optimizing: http://www.singlehop.com/blog/analyzing-mysql-performance-an...

Good luck!

Re: Ask HN: Dealing with a huge MySQL database - help

#10

Post by webvet » Sun, Sep 08, 2013, 3:25 PM UTC

sgaither wrote: post #3

How slow is "slow" for you right now? And writes vs. read? Which MySql storage engine are you using?

Let me try to answer: Slow - as in average Top loads (on CentOS) staying between 1 and 2 pretty much throughout the day; closer to 2 rather than to 1, peaking ALL the way up to 10 and even beyond several times a day, for periods as long as 15 to 30 minutes.

Storage engine: MyISAM