System size and Performance Optimization
Performance optimization is never ending story, you can virtually always find something else to optimize but while on generic system, not tuned by expert you often can get significant performance increase in the matter of hours further performance improvements become more and more time consuming and expensive and gains smaller.
This observation does not only apply to MySQL but to systems running other databases or no database at all as well.
So how does System size come in play here ? Well it is system size and scale what may define how much performance you need to squeeze out, and also how your scaling is set up.
For smaller systems lets say running couple of MySQL servers running another couple can be cheaper than serious application changes and this can be good way to go if you’re not expecting major growth.
This however implies two things – first you can scale system by adding more servers, because scaling up and purchasing more and more powerful servers will not tall you far, especially as MySQL Server may have trouble scaling itself. The second thing – it should be throughput rather than latency problems – for example if your search is taking 10 seconds for the single user on idle seconds you need to fix it and simply adding more boxes may not be enough.
The growth expectation is always interesting trade off. Working with startups We often ask them if they would like me to help them to design architecture which is very easy to implement or which would be efficient and scalable ? Typically we settle on some balance but it does not change the fact – scalable architectures may be more complex to implement. This of course does not mean all simple architectures scale badly – for many tasks simple architectures can be good enough, but it should be right simple architectures. In some cases you might wish to implement simple architecture which you’ll have to redo in the 6 months – just to show proof of concept, see first happy customers and raise investment or fell assured this project is worth to spend savings on. In others you prefer to do things as good as you can from the start. I’m not saying “right” as experience gained during the project development and new features typically require some changes.
If system will be huge from the start, like if Yahoo develops new service and will link it from the front page, or if it is expected to grow quickly it is worth to spend time and money implementing things as optimal as possible. If you’re using 50 5000$ database servers to run the system even 10% performance increase mean 25K$ savings in hardware cost alone not to mention rack space cost, power cost, staff costs and other components of TCO. If you’re MySQL Customer you can also save on support and possibly Enterprise Subscription by keeping number of servers low.
How do we approach it in our practice ? We figure out application size and ask about expected growth, to help to access if minor face lifting such as MySQL Server settings tuning, changing indexes and queries is enough to deliver performance required in cost efficient way, or if we should do some more complex things such as major schema changes and architecture overhaul, adding/changing caching infrastructure and other things.
Of course it is not black and white, typically there are many alternatives and we typically can offer list of items which can be done to the system together with implementation complexity and expected performance impact.
del.icio.us
digg