April 20, 2014

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.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Gunnar says:

    Hi,

    IMHO, I very simply way to get a bit of performance is reducing size. Try to store the data as compact as possible.

    What I miss in MySQL for this is the possibility to use “indexes on virtual columns” or “indexes on functions”.
    For example often I use an “index on soundex(columns) and index on soundex(reverse(columsn))
    Today I have to hold create redundant columns in MySQL to hold soundex of columns to able to create the index.
    I think for many projects I could easely save 25% disk and memory size if I could create the indexes without creating the column.
    Oher RDMS like Postgres support this.

    What is your opinion on this?
    Would you think that support for this will be difficult to add to MySQL?

    Cheers
    Gunnar

  2. peter says:

    Gunnar,

    I agree with you completely. In fact I was proposing for Virtual Columns to be implemented years ago, I guess that Worklog Entry is still located somewhere. It is very neat feature for practical use but it is not ANSI SQL stuff and MySQL has implementing Standard features as priority now.

    I’m also not sure how easy it would be to implement as it would need to be supported in all storage engines, because you basically index something when you do not store the data.

    Well storage engines of course could have a flag to say they do not support indexes on virtual columns

Speak Your Mind

*