November 24, 2014

MySQL – to use or not to use

Reading this slashdot article today and two CIO magazine articles linked from it.

Such discussions started at right place at right time always attract a lot of flamers and can be fun to read.

What hit me this time is quality of the articles in CIO magazine. If this is what managers suppose to use to make their “informed” decisions about products, not a big surprise huge portion of IT budgets are wasted. It looks like someone who has not got a clue is writing for someone who does not even pretend. I see zero “meat” – mostly using of marketing materials. This applies both to pro-MySQL and against-MySQL articles.

The funniest argument for me was the age of the product. Where does this “older is better” comes from ? Oh yes I know, it comes from the point of view market old timers try to show the value of their technology to their customers and shareholders.

In reality however most of 30 year code would ether have to be scrapped and rewritten or it would become passive being hard to maintain and extend. Over time systems tend to get entropy it is as true as people aging as they are getting older. You can postpone things by healthy coding techniques as you can do by healthy living but you can’t stop the process completely.

Take a look at MaxDB for example, which has extremely old code base (with a lot written in pascal).

Indeed you can apply age argument to the code which is few months/couple of years old – indeed this may be not battle tested enough or just too dynamic in development (nothing stops you from starting to actively break old code though). Indeed there are some problems which may happen only once or twice a year in production but 10 years is enough by far.

As old time MySQL User I should say MySQL stability have not much improved (really going up and down) since MySQL 4.0. This even applies to Innodb tables which were already on par with general MySQL level of stability in MySQL 4.0

But now get back to the question of using MySQL.

I’m obviously biased and focused more on Web applications rather than Enterprise applications (though these are merging a lot these days). From my standpoint the question is when or how to use MySQL not just if it should be used or not.

There are many applications when MySQL works great, when there are some of applications when MySQL can be made to work and there are finally cases when MySQL limitations are not worth the trouble – would require too complex application changes or some other ugly workarounds.

In very many cases the problem of MySQL use is not problem of the product but problem of state of mind – you just develop applications for Oracle and MySQL differently and if you’re try to apply same patterns to MySQL as you used for Oracle or move existing applications without changes you may well run into trouble. PostgreSQL is probably closer to Oracle in this state of mind thing.

It is interesting one of mentioned reasons to use MySQL was MySQL simplicity and I’m afraid this is what is gradually leaving us.
MySQL 4.0 was indeed simple – you could not do anything complicated with it and you could get to know pretty much all features within rather short time. MySQL 5.0 with spice of enterprise features is far from being simple and MySQL 5.1+ with wide choice of storage engines are getting even more complicated.

It is true so far it is different degree of complexity than many other DBMS but it is increasing.

On the other hand many successful MySQL users would use few new technologies besides Stock MySQL 4.0 toolset. For example Google still has a lot of stuff on MySQL 4.0… looking at the patches they designed themselves such as Semi-synchronous replication, advanced Monitoring and scaling features some users need other features than MySQL adding in newer MySQL releases.

In other cases I can see people using for example SubQueries and Partitions and information schema for ease of management but rarely I would see large amount of big post MySQL 4 features used at the same time.

This is of course only technical side. There are obviously a lot of other factors but they are more obvious.

For Web shops and for most of enterprises you can use MySQL for free same as Linux. You do not have to pay for licenses and all
unless you want some extra services. You can use Community version and solve things yourself or use third party support, which indeed works as MySQL is OpenSource and nothing stops you from fixing bugs yourself or adding your own features. Again it is very similar as Linux – if you do not want to pay for RHEL you can use CentOS, Fedora or Debian,Ubuntu.

Most articles speaking about MySQL for Enterprises speaking about MySQL with full pack of services from MySQL AB. Obviously this is how MySQL AB wants its product to be seen so whomever uses MySQL buys services but it is important to understand this is not really required. Of course many enterprises may need that because of their internal policies and state of mind, but this may not have yet adapted to open source software world, at least when it comes to the classic enterprises.

Having said that I’m not advocating against buying services from MySQL. They indeed may be more efficient for you than having your own tool set and DBAs which can resolve all MySQL issues with help of Google rather than MySQL Support Team. You also may want to buy some services from MySQL simply so MySQL Development is continued. My point is simply you do not have to do if you do not have money for it or if you do not need them.

So this is fun articles and discussion to read and it brings up many thoughts but I better stop right here and go back to real work :)

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. TradePriceTiles says:

    Not sure about all the MySQL knockers on here – its a free, open source product. granted to me postgres is better, as is SQL Server but i think thats just because i know them inside out.
    I’ve been running sites (self hosted for 2 years) on MySQL without a hitch and now i’m on a hosted vps frankly its someone else’s problem!
    I agree with your statement about adapting your coding style for MySQL, i hated it for a start as i was still trying to code for SQL Server.
    Anyway, great post sorry for the late comment!

  2. Rafe says:

    I don’t know about the complexity bit. MySQL can do a lot more than it used to, but I just built and installed MySQL 5.0 on a FreeBSD box and migrated my old 4.1 database over to it with no more trouble than I used to have with version 3.2.x.

  3. peter says:

    Rafe,

    This is right. MySQL is easy to install and get going and installation is as easy with 5.0 as it was with 3.23.

    What I mainly mean is feature set – MySQL 5.0 has a lot of complex stuff such as triggers, views, stored procedures.
    True you do not have to use them. However if you’re newcomer and charged with supporting MySQL 5.0 applications you may end up learning a lot of stuff even if you used MySQL a lot before.

  4. MySQL is NOT easy to install.

    when you dont know it, you cant install it.

    I am one of these Dummies.

    See you,

    peter :-)

  5. Nils says:

    MySQL 5.0 sure was a milestone and really brought up many new and great featured, mostly those the guys claiming MySQL is a “toy database” used as argument. This with Subqueries being an exception, but most people I know who needed subqueries just didn’t know how to do proper joins ;) Mostly, the new features in MySQL 5.0 seem to be unnoticed by the public and I’ve not yet seen widespread use. I think many projects need compatibility to MySQL 4.1 or even 4.0, especially open source applications.

    Also I think many of those who say that MySQL lacked triggers, stored procedures, views and so on didn’t really want to use it – it is not a real showstopper – they just wanted to say why they like their favourite rdbms more. I didn’t see many cases where the featureset of the DBMS were the showstopper, rather the creativity and skills of the developers. And then you can still hire a consultant ;)

    Speaking of simplicity, the fine thing is from what I see now, you can live fine without partitioning, views, triggers, stored procedures for most applications – but if you need them, they are there.

    I recently started to upgrade my old 4.1 servers to 5.0. One thing I learned the hard way is that you should dump and restore the tables, rather than letting the upgrade script upgrade them, it seemed to be far slower and raised innodb tablespace size severely.

    The reasons for the upgrade where:
    – MySQL 4.1 has been out of life for a long time now, and no recent debian packages where provided, with 4.1.14 being the newest
    – I want to use profiling to check for possible optimizations
    – I want to use triggers for some stuff that is currently being done by cronjobs (creating some search index tables on slaves)

    I also started linking in the google performancetools tcmalloc library, will be interesting to see the speed impact.

  6. Pavel says:

    I agree. We are using MySQL 4.0 with over 5000 q/s traffic (simple inserts/updates). Now it runs smoothly on three years old dual Xeon/3GHz @ 30-40% CPU time. When we tried to upgrade to MySQL 5.x we lost about 60% of performace. No way to upgrade.

    Because it is unsupported we have to compile from source now. Or have to find another solution (another simple database engine).

  7. Greg says:

    As ever Peter, I love reading your blog. In this article, you state 3 categories: “There are many applications when MySQL works great, when there are some of applications when MySQL can be made to work and there are finally cases when MySQL limitations are not worth the trouble” – I’m curious what kinds of project you’d put in each. Actually, I’m curious what kinds of project you’d put into each of the last two.

  8. peter says:

    Pavel,

    Do you have a test case where MySQL 5.0 is 60% slower. If yes please report it as a bug and let me know. Making regressions public is one of best ways to get attention to them and have them fixed.

  9. peter says:

    Greg,

    We all know projects when MySQL works great. MySQL can be made to work in many cases when complex reporting queries is needed as MySQL often fails to optimize subselects properly only able to use nested loops join method this where a lot of problems happen. Same about lack of parallel query execution. Yet another showstopper I’ve seen is Innodb scaling issues.

    Second and Third classes are generally different by the state of the project and development team rather than workload. What is more complicated for you to invest time changing application and working around MySQL limitations or change the database engine all together ? People which were raised on MySQL typically choose to stick to MySQL and find workarounds, in case application is portable and not MySQL specific and development team is not highly invested in MySQL you would find using other DBMS being better option.

    For example O’reilly is typically using MySQL but has PostgreSQL in massively parallel setup for reporting application because application can run both MySQL and PostgreSQL and it is too much trouble to optimize queries automatically generated by third party application.

  10. Tiles says:

    When something goes wrong with MySQL I’ve found it just bombs and leave everything in a mess. It does not clean up after itself at all.

Speak Your Mind

*