Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: Dealing with a huge MySQL database - help

news.ycombinator.com

Post reply on HN
Print view View original

51–60 of 66 posts

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

#51

Post by janlin1999 »

It sounds like your database is roughly the same size as the database that we work with, and MySQL performance has been a recurring issue for us. Assuming that you've done a reasonable job of option 2, I gravitate towards option 1 and maybe option 4 (depending on how confident you are of your team's technical capabilities and how expensive consultants are relative to additional hardware).

These are intermediate steps that have helped, giving us some time to figure we whether want to migrate to a different database:

1) We started using InnoDB a few years ago.

2) One of our common queries joined parts of multiple large tables; we essentially cached the useful parts of the join and put the results into a memory table.

3) Our non-production server is under a lot of load from our back-end processes, and we decided to move the database to a RAM disk, which greatly improved performance. It's important to note that, while inconvenient, a power outage is not catastrophic in our case (e.g. we don't deal with financial transactions); we do daily backups to non-volatile memory. For around $5,000, you can build your own server that has over 250 GB of RAM and then put it into a co-location facility.

We seriously considered migrating to Postgres, but #3 has bought us some time on that front. It might be that we eventually still migrate, but we prefer to do it after the company is on better financial footing. It might also be that while we wait, other less painful options open up over time (e.g. maybe MariaDB will suffice).

I hope the situation resolves well.

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

#52

Post by webvet »

netaustin wrote: post #6

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.

1) Yes, and no 2) Both (3 servers) 3) We've analyzed/optimized this to the max (of our abilities)

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

#53

Post by webvet »

apalmblad wrote: post #39

Hmm. Bringing in a consultant is probably your best bet. Database optimization needs to generally take into account YOUR data and usage patterns. If you want to learn a bit yourself, pick up a copy of "High Performance MySQL." I've found that to be the most useful MySQL scaling book I own. Figure out your slow queries. Run "SHOW PROCESSLIST" on the server and see what's typically running. Look at the actual queries -…

>Database optimization needs to generally take into account YOUR data and usage patterns.

I'd say this is quite true.

>I've found that an unfortunate number of people aren't aware of "EXPLAIN" and its use to help figure out query issues. Learn and use it.

Yes, we discovered this some years ago.

Thanks for you input.

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

#54

Post by webvet »

geophile wrote: post #13

What exactly is getting slow? Inserts? Updates? Queries? Which ones? Have you looked at the slow query log? Have you checked the plans for the slow queries? Are these MyISAM tables or InnoDB? 150GB and 100M records is not huge, not even close. You are looking for a quick fix and there isn't one. You need to start with (2).

> 150GB and 100M records is not huge, not even close.

Close to what, if I may ask?

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

#55

Post by nfailor »

webvet wrote: post #54
geophile wrote: post #13

What exactly is getting slow? Inserts? Updates? Queries? Which ones? Have you looked at the slow query log? Have you checked the plans for the slow queries? Are these MyISAM tables or InnoDB? 150GB and 100M records is not huge, not even close. You are looking for a quick fix and there isn't one. You need to start with (2).

> 150GB and 100M records is not huge, not even close. Close to what, if I may ask?

not even close to 'huge,' although my english syntax is a little rusty.

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

#56

Post by webvet »

nfailor wrote: post #55
webvet wrote: post #54

Earlier quoted context omitted.

> 150GB and 100M records is not huge, not even close. Close to what, if I may ask?

not even close to 'huge,' although my english syntax is a little rusty.

Regardless of your English syntax (rusty or not), what would you define as 'huge' for a MySQL app?

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

#57

Post by petervandijck »

webvet wrote: post #54
geophile wrote: post #13

What exactly is getting slow? Inserts? Updates? Queries? Which ones? Have you looked at the slow query log? Have you checked the plans for the slow queries? Are these MyISAM tables or InnoDB? 150GB and 100M records is not huge, not even close. You are looking for a quick fix and there isn't one. You need to start with (2).

> 150GB and 100M records is not huge, not even close. Close to what, if I may ask?

Agree. 1B rows is big. 100B rows is huge. 100M rows is large, but totally doable.

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

#59

Post by webvet »

gmuslera wrote: post #45
webvet wrote: post #18

Earlier quoted context omitted.

Yes, we enable slow query logs from time to time, look at those and try to optimize for those. MyISAM tables. We've been cycling frequently (and painfully) through (2) (and less frequently through (1)) for the best part of three years now, so please don't accuse us at least of looking for a quick fix :)

With MyISAM tables you could have integrity problems. And if you are mixing innodb and myisam you are splitting buffers memory that well could go to just one kind (i.e. most memory for innodb) if you switch to that. If innodb is slow in insertions, you could try using TokuDB as storage engine, that is pretty fast in that and have a bunch of advantages. And if you are using MyISAM for some particular feature (i.e. ful…

Thanks. We'll check out TokuDB.
Post reply on HN