Ask HN: Dealing with a huge MySQL database - help

Re:Ask HN: Dealing with a huge MySQL database - help by debaserab2 on Sunday September 08, 2013 @11:37AM (#6349047)

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.

[ Parent | on HN ]

Re:Ask HN: Dealing with a huge MySQL database - help by webvet on Sunday September 08, 2013 @11:52AM (#6349103)

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

[ Parent | on HN ]

Re:Ask HN: Dealing with a huge MySQL database - help by debaserab2 on Sunday September 08, 2013 @11:55AM (#6349114)

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

[ Parent | on HN ]

Re:Ask HN: Dealing with a huge MySQL database - help by webvet on Sunday September 08, 2013 @12:09PM (#6349164)

Development froze almost 2 years ago :) Since then, just the data volume keeps growing.

[ Parent | on HN ]

Re:Ask HN: Dealing with a huge MySQL database - help by cellover on Wednesday September 11, 2013 @12:13PM (#6368067)

Wouldn't it be interesting to unfreeze the development and see if you can't refactor to avoid unnecessary queries? Code topology can heavily affect the amount of queries.

[ Parent | on HN ]

Re:Ask HN: Dealing with a huge MySQL database - help by webvet on Wednesday September 11, 2013 @02:39PM (#6369049)

What I meant was feature development... that's what got frozen long ago... code revision exclusively for performance optimization has been ongoing continually.

[ Parent | on HN ]