Cleanup - Probably one the most effective but often ignored tips for DB management. Do you really need all that data in real time? Are you querying all of it in your app? If not, it makes sense to archive parts of it to another disk. The size reduction would boost performance tremendously.
Ask HN: Dealing with a huge MySQL database - help
61–66 of 66 posts
Re: Ask HN: Dealing with a huge MySQL database - help
#62Cleanup - Probably one the most effective but often ignored tips for DB management. Do you really need all that data in real time? Are you querying all of it in your app? If not, it makes sense to archive parts of it to another disk. The size reduction would boost performance tremendously.
>Do you really need all that data in real time? Are you querying all of it in your app?
Yes and yes. ALL the data records could potentially be queried via site navigation.
Re: Ask HN: Dealing with a huge MySQL database - help
#63How slow is "slow" for you right now? And writes vs. read? Which MySql storage engine are you using?
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
The spike to 10+ sounds like it might be io blocking rather than CPU (which would be stuff like queries). If you look at your time wait (%wa) in top, and the output of iostat, you should be able to get an idea. If you're on VPS systems with older SATA drives that could be the bottle neck. If the Kernel is waiting for the disk(s) to be able to write more data to the storage bus/buffers it will block the storage process which will result in blocking for any application trying to access storage. As more and more processes block they'll go into poll/sleep loops and all those instructions will seem to spike the CPU load and make the CPU look busy even if the CPU is just sitting there saying "storage is still busy" over and over again. You probably have 1.00 to 2.00 true CPU load (queries, etc) which isn't that bad. But, you generally want to keep your CPU load below 1.00/per cpu. Otherwise, there is CPU level blocking. If you were swapping a lot (which you can also tell from top) that would aggravate and storage subsystem overloading. I would just post the output from top and iostat during one of the spikes here and see what people say rather than hiring an expensive consultant. You could probably find an experienced sysadmin to look at it as a favor, as well. They should be able to tell you quickly if you really need someone to look at your MySQL and app code to address this. Or, if you just need to add more nodes to your MySQL cluster (or look at something else like a NoSQL type setup).
Re: Ask HN: Dealing with a huge MySQL database - help
#64Earlier quoted context omitted.
Unless your development is completely frozen, #2 is never done
Development froze almost 2 years ago :) Since then, just the data volume keeps growing.
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.
Re: Ask HN: Dealing with a huge MySQL database - help
#65Earlier quoted context omitted.
Development froze almost 2 years ago :) Since then, just the data volume keeps growing.
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.
What I meant was feature development... that's what got frozen long ago... code revision exclusively for performance optimization has been ongoing continually.
Re: Ask HN: Dealing with a huge MySQL database - help
#66Earlier 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
The spike to 10+ sounds like it might be io blocking rather than CPU (which would be stuff like queries). If you look at your time wait (%wa) in top, and the output of iostat, you should be able to get an idea. If you're on VPS systems with older SATA drives that could be the bottle neck. If the Kernel is waiting for the disk(s) to be able to write more data to the storage bus/buffers it will block the storage proces…
We've suspected that our problem is mainly of I/O, very close to what you've described above.
>I would just post the output from top and iostat during one of the spikes here and see what people say rather than hiring an expensive consultant.
Thanks for the suggestion. Will try and do just that soon.