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

21–30 of 66 posts

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

#21

Post by nla »

Without doing an analysis, which is really warranted, here are some coarse grain recommendations:

1) Get off VPS if you can. If you can't check out linode.com; they offer better price/performance than most cloud services.

2) Take a look at the Percona build of MySQL - it is faster, especially with more cores.

3) Use in memory tables if possible

4) Switch to InnoDB or XtraDB (in the case of Percona) tables... MyISAM implements table level locking, whereas InnoDB and XtraDB implement only row level locking.

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

#22

Post by debaserab2 »

There is no silver bullet.

1) Analyze your indexes on all tables. Start with the high volume insert/read tables.

2) Look at your slow query log. Pick the slowest three queries. Optimize them. Setup a recurring meeting with your team to do this every week.

3) Start looking at what data can be flattened and is less important and could be stuck in a key value datastore.

4) Consider loading a replica slave server. Off load some of high volume read queries to the replica. Also serves as redundancy.

5) Consider where it may make sense to move tables to InnoDB. Table-level locking can be extremely time consuming when traffic is high. Row-level locking can help here.

6) Figure out what your hardware upgrade path looks like. You will have to do this eventually. Identify when you might hit a hardware ceiling with your hosting provider and get a plan in place. Coordinate with your marketing/sales team and upgrade before you hit a traffic peak, not during.

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

#24

Post by Demiurge »

Have you considered rewriting MySQL in Go?

jk. What is the real nature of the queries, what kind of caching is being used? MySQL should not really get hit that often if you have 150GB of data and a site, unless it's not getting content but running some sort of deep processing. In such a case MySQL seems like the wrong tool for the job.

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

#25

Post by WestCoastJustin »

I would not migrate to postgres without knowing the issue first. Here are some suggestions: Enable replication master/slaves+, assuming you have a read heavy architecture. Enable the "The Slow Query Log" [1], which will give you something like /var/log/mysql-slowquery.log, then you can look in here to find queries that take a long time. Find out what the bottleneck is, I'm willing to best you have limited I/O and that is the source of your issues, can you use memcached [2] to limit the database hit rate? You need to remove/limit direct access to the database when people get duplicate data out.

Before you start to optimize I would profile things to get a baseline for how many select/inserts you are doing and how long they are taking, and system load, etc. Based off your 100M statement, you are doing roughly 1157.4 inserts/s. Maybe that's 250 inserts/s during a slow time and 3,000 inserts/s at peak, but it would be nice to know.

  100,000,000 (inserts) / 86,400 (seconds/day)
  = 1157.4 (inserts/s)
I guess it all depends on the insert size and index but you should be able to scale this. If you cannot then you need to partition/shard your data. After reading all this, you are probably thinking, I should just throw more hardware at the problem ;)

ps. Have you stopped to ask if you really need all this data?

[1] http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

[2] http://memcached.org/

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

#26

Post by patrickg_zill »

My first recommendation would be to get a dedicated server with a decent amount of RAM (32GB or more) and put MySQL on that.

1. Greater memory bandwidth 2. dedicated disk resources 3. you can ensure that extra RAM is used as read and write cache.

I am not the cheapest provider by far, and I rent these kind of systems for $200 a month. If you go with someone else the price could be under $100 a month.

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

#27

Post by webvet »

debaserab2 wrote: post #22

There is no silver bullet. 1) Analyze your indexes on all tables. Start with the high volume insert/read tables. 2) Look at your slow query log. Pick the slowest three queries. Optimize them. Setup a recurring meeting with your team to do this every week. 3) Start looking at what data can be flattened and is less important and could be stuck in a key value datastore. 4) Consider loading a replica slave server. Off lo…

Thanks. 1) and 2) - we've done to the max. 4) already in place, with some differences.

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

#28

Post by debaserab2 »

webvet wrote: post #27
debaserab2 wrote: post #22

There is no silver bullet. 1) Analyze your indexes on all tables. Start with the high volume insert/read tables. 2) Look at your slow query log. Pick the slowest three queries. Optimize them. Setup a recurring meeting with your team to do this every week. 3) Start looking at what data can be flattened and is less important and could be stuck in a key value datastore. 4) Consider loading a replica slave server. Off lo…

Thanks. 1) and 2) - we've done to the max. 4) already in place, with some differences.

Unless your development is completely frozen, #2 is never done

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

#29

Post by amirouche »

webvet wrote: post #14
Joyfield wrote: post #4

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

Please let me know what more info would be helpful and I'll be happy to provide the same.

are queries uniform over the data. For instance most of queries conerns only a little fragment of one table for instance most recent rows. Can you shard them ?
Post reply on HN