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

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

#41

Post by thesis » Sun, Sep 08, 2013, 4:14 PM UTC

viraptor wrote: post #15

webvet wrote: post #10

Earlier quoted context omitted.

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

Do you have some other measurements? slow_log, queries without indexes, low key cache usage? Load is just one metric and can be very deceiving. Do you have any backup system which hits the disks at the same time, batch jobs, or something similar? Depending on your workload, "load" can vary - it just means the writes are being queued up. It's definitely not a good sign, but try to get more specific. I've seen servers…

Have to second this. You should monitor load for sure... but depending on how many cores you are running a load of 1 or 2 could be absolutely nothing in the grand scheme of things.

Personally, the most important metric for us is average query response time.

Additionally as others have stated where you can use memcache or some other key/value store you should be working on implementing that.

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

#42

Post by IgorPartola » Sun, Sep 08, 2013, 4:18 PM UTC

One of the less documented tuning parameters I found is the threa dcache size. Normally it is a very small value for the number of simultaneous connections you likely support. Increasing it can result in elimination of random pauses/slowdowns.

In general, it is impossible to "tune MySQL" though. You can allow it to use more system resources and in some cases you can get it to be less durable to gain write performance, but to get orders of magnitude increases you need to restructure your data. Try sharing or partitioning data. Increase or reduce the number of indecies. Denormalize data. Archive logs to flat files. Put the OS, /var/lib, and /var/log on separate physical drives. Run MySQL on bare metal since disk IO is often a bottleneck and virtualization can add overhead. Restructure the data. At one point a write heavy app I was optimizing was logging things to the database (that needed to be queries). Each log entry contained extra data we did not query. One speed up that worked was throwing the majority of the extra data into a single gzipped JSON blob. This made it easier for MySQL to fetch fewer pages. Less IO, faster operation.

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

#43

Post by tdonia » Sun, Sep 08, 2013, 4:18 PM UTC

tdonia wrote: post #33

Inline with questions 2 & 3 - what's the bottleneck? reading or writing or both? if you haven't already, it's worth looking into a master (writes) / slave (reads) setup - provides a bit of redundancy and lets you scale reads very easily by adding more slaves. It also helps with writes because it can take a lot of pressure off the master. ...assuming you're webvet.com it looks like you're scaling drupal, so these woul…

...looks like you've (again, assuming webvet.com) also got an openx server. that'll benefit a lot from the master/slave replica. i'd also look at making spc.js non-blocking as that may be why the pages are blank for a few seconds even after content has been delivered...

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

#44

Post by [deleted] » Sun, Sep 08, 2013, 4:21 PM UTC

[deleted]

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

#45

Post by gmuslera » Sun, Sep 08, 2013, 4:32 PM UTC

webvet wrote: post #18

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).

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. full text indexing) MySQL 5.6 already have it in innodb, or you can separate that search to a sphyinx server.

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

#46

Post by webvet » Sun, Sep 08, 2013, 4:37 PM UTC

charlesju wrote: post #19

I have a little bit of experience dealing with large datasets and MySQL. This is going to be a little counter-intuitive to standard startup culture advice, but generally, the engineering effort to optimize databases past the initial live flow is not worth throwing money at the problem. You have to weigh the fixed cost of the optimization and the variable cost of the upkeep of the system against simply patching the sy…

Hey, I'll have to check with the others here, but I think we have not been aware of 1. !!! Will certainly investigate this ASAP - thanks.

2. and 3. - already implemented to the max (of our abilities :)).

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

#47

Post by webvet » Sun, Sep 08, 2013, 4:44 PM UTC

tdonia wrote: post #33

Inline with questions 2 & 3 - what's the bottleneck? reading or writing or both? if you haven't already, it's worth looking into a master (writes) / slave (reads) setup - provides a bit of redundancy and lets you scale reads very easily by adding more slaves. It also helps with writes because it can take a lot of pressure off the master. ...assuming you're webvet.com it looks like you're scaling drupal, so these woul…

Thanks for your input - and no, we're not webvet.com

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

#48

Post by tdonia » Sun, Sep 08, 2013, 4:48 PM UTC

webvet wrote: post #47

tdonia wrote: post #33

Inline with questions 2 & 3 - what's the bottleneck? reading or writing or both? if you haven't already, it's worth looking into a master (writes) / slave (reads) setup - provides a bit of redundancy and lets you scale reads very easily by adding more slaves. It also helps with writes because it can take a lot of pressure off the master. ...assuming you're webvet.com it looks like you're scaling drupal, so these woul…

Thanks for your input - and no, we're not webvet.com

ah ok, cool - good luck!

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

#49

Post by mattjaynes » Sun, Sep 08, 2013, 4:54 PM UTC

Consider that 37Signals probably has a much larger dataset and is still happily scaling vertically due to Moore's law.

http://highscalability.com/blog/2012/1/30/37signals-still-ha...

It's probably the cheapest, quickest, and least risky option if your engineers' time is expensive (which it most likely is).

Of course, you'll ultimately also want to find bottlenecks and tune the DB and cache the hell out of everything, but scaling vertically will at least buy you some good time so you can do that in a more relaxed time-table.

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

#50

Post by webvet » Sun, Sep 08, 2013, 4:55 PM UTC

IgorPartola wrote: post #42

One of the less documented tuning parameters I found is the threa dcache size. Normally it is a very small value for the number of simultaneous connections you likely support. Increasing it can result in elimination of random pauses/slowdowns. In general, it is impossible to "tune MySQL" though. You can allow it to use more system resources and in some cases you can get it to be less durable to gain write performance…

I believe we did tune and tweak the heck out of my.cfg, but will investigate thread_dcache_size all the same.

I also believe we're archiving at least some logs (and such) to flat files already, but will re-visit this soon too.

Thanks for all your input.