Ask HN: Dealing with a huge MySQL database - help

Option 4) would be to hire an expert in these matters.

100M rows is not a lot of data nowadays.

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

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 doing heavy network I/O with "normal" load over 5 times the number of cores.

Also, unless you're doing loads of selects and very few modifications, you could gain a lot by switching to InnoDB, or XtraDB, rather than MyISAM.

stackoverflow -> this way

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 system for now.

If you are willing to throw money at the problem, which you seem like you're wavering on, here are some options on how to throw money at the problem:

1. If you are willing to be hands on and maintain your MySQL cluster yourselves, you should move away from EC2 (I am presuming) and to a local collo where you can install super fatty SSD computers with massive RAID setups that will greatly improve your performance per server (on the magnitude of up to 10x)

2. You can do a hybrid solution with your existing infrastructure. It sounds like a lot of the data is not necessarily going to be used that much (tracking all events probably, from what I am assuming, or something big data intensive like that), you can throw that into a separate database infrastructure.

If my assumption is correct, I am presuming you are using a large portion for the database for stat tracking, and not user information, then the "proper" way to throw it somewhere is to put it into S3 as fat logs that you can later use something like map reduce to process. Other options for more efficient storage are Redis and MongoDB.

3. We have taken this to the extreme and actually moved all of our live data to DynamoDB. It is a NoSql database storage system operated by Amazon. This has let us concentrate on features and the user experience. The cost is our database is probably 5-10 times more expensive than a self rolled Redis SSD backed equivalent hosted at our local collo.

------

Just some other general database scaling advice for MySQL. The steps to scaling your MySQL database for live use are as follows:

1. Do not use any relational calls on your data. Relational calls are useful for databases that aren't used to manage live web transactions as it can seriously back up your system.

2. Put indexes on all of your heavily used columns for finding data.

3. Look at the slow queries and optimize those queries out.

4. Call up Percona and ask for a full consultation to make sure your configs and systems are set up properly for MySQL.

http://www.percona.com/products/mysql-consulting/overview

5. Email me if you have more questions charlesju gmail