Tuesday, February 2, 2016

Looking for the smallest possible MySQL Footprint

UPDATE: Starting with MySQL 8.0.16 we have introduced the new minimal tar ball distribution. Take a look here.

MySQL is known and famous for it’s simplicity and small size, especially compared to other RDBMSs. But what if you want to deploy on tiny hardware? I mean something even smaller than RaspberryPi?
I tested three steps to make the MySQL footprint as small as possible. All my tests were compiled for Oracle Linux 7 on x64 platform. I did not test any ARM cross compile. And these are the steps:
  1. Compile my own binary
  2. Remove all unnecessary tools/files
  3. Strip symbol information from binary

Let’s take a closer look at the tree steps.

Compile my own binary

MySQL is available as a source release. Using that you can configure the make process. That is documented pretty well in the Reference Manual. By switching off some options I was able to reduce the binary size from 240MB to 216MB. I switched off some performance_schema features, removed some storage engines that are irrelevant in most environments anyway (like ARCHIVE, NDB, EXAMPLE, …) and I removed all options for profiling. The final CMAKE statement is at the bottom of this post.

Remove unnecessary tools

I removed scripts and binaries from the distribution. Ted has written an interesting blog post about this. The remaining share directory contains some SQL scripts for installing additional tools. You need these at most once during setup and never again. So let’s remove these. If you are happy to live without textual error messages you can also remove the errmsg-utf8.txt file as well and all translations in the country specific subdirs. And if you can live with reduced charset support, you can even remove the rest of the share directory. You are running essentially only with a mysqld binary.

Strip symbol information from binary

All compilations are done with extended diagnosis information in the binary. These symbol data helps if you want to analyze a core dump for example. Symbols are included by default in the MySQL binaries. These take a surprisingly large amount of space. You can remove these symbols from the binary with the tool “strip(1). After stripping the binary size came down to 24MB, which is only 10% of the initial size.

More ideas

There are some more options to use either system libraries or the libraries that come with the source code. Using existing libraries from the system might help save a few bytes.

Summary

It is possible to make MySQL very lean for your (embedded) system. Despite all the functionality that we added to MySQL in the releases since MySQL 5.1 you get a full featured RDBMS with only a handful of MB. Here are my final results:

  • MySQL 5.6, minimal features: 79MB, stripped 13MB
  • MySQL 5.7, default features: 240MB, stripped 24MB
  • MySQL5.7, minimal features: 216MB, stripped 24MB (removing features brings minimal savings only)

Addendum

This is the CMAKE statement I used to compile MySQL 5.7 on Oracle Linux 7:
cmake . -DCMAKE_INSTALL_PREFIX=/home/testy/TQ/dist-mysql-5.7.10/        \
        -DDOWNLOAD_BOOST=1                                              \
        -DWITH_BOOST=/home/testy/TQ/boost/                              \
        -DDISABLE_PSI_COND=1   \
        -DDISABLE_PSI_FILE=1   \
        -DDISABLE_PSI_IDLE=1   \
        -DDISABLE_PSI_MEMORY=1 \
        -DDISABLE_PSI_METADATA=1 \
        -DDISABLE_PSI_MUTEX=1 \
        -DDISABLE_PSI_RWLOCK=1 \
        -DDISABLE_PSI_SOCKET=1 \
        -DDISABLE_PSI_SP=1     \
        -DDISABLE_PSI_STAGE=1  \
        -DDISABLE_PSI_STATEMENT=1  \
        -DDISABLE_PSI_STATEMENT_DIGEST=1    \
        -DDISABLE_PSI_TABLE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=0 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=0 \
-DWITH_EXAMPLE_STORAGE_ENGINE=0 \
-DWITH_FEDERATED_STORAGE_ENGINE=0 \
-DWITH_PARTITION_STORAGE_ENGINE=0 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=0 \
-DENABLED_PROFILING=0 \
-DENABLE_DEBUG_SYNC=0 \
-DENABLE_DTRACE=0 \
-DENABLE_GCOV=0 \
-DENABLE_GPROF=0 \
-DOPTIMIZER_TRACE=0 \
-DWITH_CLIENT_PROTOCOL_TRACING=0 \
-DWITH_DEBUG=0 \
-DWITH_INNODB_EXTRA_DEBUG=0 

4 comments:

  1. Even a Raspberry PI has 1 GB of RAM and at least 4GB hard drive, so even running MySQL Cluster on a Raspberry PI is ok

    ReplyDelete
    Replies
    1. I ran MySQL Cluster on RPi. See here: http://mablomy.blogspot.de/2014/03/mysql-cluster-on-raspberry-pi-sub.html
      This time the target platform is more something like SOC chips, e.g. i.MX6 https://en.wikipedia.org/wiki/I.MX with only 128MB RAM and 4GB flash storage. In these environments every MB counts...

      Delete
  2. Did you see how small it goes after packing with UPX?
    http://upx.sourceforge.net/

    ReplyDelete
    Replies
    1. That was a great idea. Thanks a lot! It wasn't me but Ted who tested it. The MySQL binary is now down to 8.2MB! See details in Ted's blog: http://mysql-nordic.blogspot.se/2016/03/mysql-footprint-less-than-10mb.html

      Delete