Running MySQL with <16MB memory in an ARM processor

By | January 5, 2015

In many small embedded systems it doesn’t make much sense to have a powerful Relational Data Base Management System (RDBMS) such as MySQL because there is only one application that access the database or one that writes and one/many that reads. In these cases, the best option is SQLite that is may be the widest used embedded database.

However, small systems are getting more complex and even when there are only few MB of memory and few MHz (in my case 64MB and 210MHz respectively), there could be several applications accessing the database simultaneously. For example, several C programs writting and several PHP script reading and writting (through a built-in web server such as Busybox’s httpd or lighttpd).
In these cases SQLite starts to have issues handling the concurrence even if you configure it for using Write-Ahead-Log (WAL) instead of the default rollback journal.

I was curious about the minimum requirements I would need to run MySQL in an small embedded system just to see if it was technically feasible. I gave it a try and I managed to run it in an ARM Atmel AT91SAM9260 with only 64MB.
In fact, MySQL was running exactly in 13.58MB:

htop_mysql

The first thing was obviously building MySQL for my ARM processor. I didn’t find any easy method, but this procedure worked for me. It’s based on this guide written by nonoo.

The most imporant thing when cross-building MySQL is that you have to build first a native MySQL and then cross-build MySQL for your target. This is necessary because MySQL, for building some pieces of itself, it uses binaries generated on-the-fly.

First, let’s export common environment variables that make our life easier:

export HOST=arm-linux
export BUILD=i386-linux
export ROOTFS=/home/projects/rootfs

Requirements

libncurses
Package: libncurses 5.7

Configure, build and install the only requirement:

cd /home/nonoo/mysql
tar xvf ncurses-5.7.tar.gz
cd ncurses-5.7
CC=arm-linux-gcc CXX=arm-linux-g++ ./configure --host=arm-linux-gnu --prefix=/usr
make
make install DESTDIR=/home/projects/libncurses-compiled

MySQL
Package: MySQL 5.5.37

MySQL

Download MySQL and copy it these two directories:

mkdir mysql-src-x86   mysql-src-arm
tar xvf mysql-5.5.37.tar.gz
mv mysql-5.5.37 mysql-src-x86
tar xvf mysql-5.5.37.tar.gz
mv mysql-5.5.37 mysql-src-arm

Compile the host version. In my case I have an AMD 64 bits processor. Choose the one that fits your host:

cd mysql-src-x86/BUILD
./compile-amd64-max

Now we can start to cross-build MySQL:

Configuring

Create the CMake toolchain file mysql-src-arm/build/mysql_at91sam9260.cmake:

# this one is important
SET(CMAKE_SYSTEM_NAME Linux)
#this one not so much
SET(CMAKE_SYSTEM_VERSION 1)

# specify the cross compiler
SET(CMAKE_C_COMPILER   /opt/buildroot-we500/usr/bin/arm-linux-gcc)
SET(CMAKE_CXX_COMPILER /opt/buildroot-we500/usr/bin/arm-linux-g++)

# where is the target environment 
SET(CMAKE_FIND_ROOT_PATH  /opt/buildroot/usr/arm-linux/sysroot /home/projects/rootfs)

# search for programs in the build host directories
SET(CMAKE_FIND_ROOT_PATH_MODE_PROGRAM NEVER)
# for libraries and headers in the target directories
SET(CMAKE_FIND_ROOT_PATH_MODE_LIBRARY ONLY)
SET(CMAKE_FIND_ROOT_PATH_MODE_INCLUDE ONLY)

SET(CMAKE_LIBRARY_PATH ${CMAKE_LIBRARY_PATH} /home/projects/libncurses-compiled/usr)

SET(CMAKE_INSTALL_PREFIX /home/projects/rootfs)

# MySQL specific
SET(STACK_DIRECTION 1)
SET(WITH_UNIT_TESTS OFF)

Create and run the script mysql-src-arm/BUILD/doconfig:

#!/bin/bash
rm CMakeCache.txt
#path=`dirname $0`
cmake -DCMAKE_TOOLCHAIN_FILE=mysql_at91sam9260.cmake ..  

Prepare to build

Create the script mysql-src-arm/BUILD/domake:

#!/bin/bash
export PATH=$PATH:`pwd`/extra
export PATH=$PATH:`pwd`/../scripts

# These scripts are needed to be copied from the x86 dir every time before make
# runs because they will be overwritten with ARM versions after a while.
cp ../../mysql-5.5.37-x86/sql/gen_lex_hash   sql/gen_lex_hash
export PATH=$PATH:`pwd`/sql
cp ../../mysql-5.5.37-x86/storage/perfschema/gen_pfs_lex_token   storage/perfschema/gen_pfs_lex_token
export PATH=$PATH:`pwd`/storage/perfschema/

make

The above script copies a couple of files before building. THis is needed everytime because MySQL will try to run the auto-generated binaries for ARM, but we need to execute them in our host (x86). Not doing this gives these errors:

Generating pfs_lex_token.h
/bin/sh: 1: gen_pfs_lex_token: not found
Building CXX object client/CMakeFiles/mysqladmin.dir/mysqladmin.cc.o
/home/nonoo/mysql/mysql-src-arm/sql/sql_lex.cc: In function 'void lex_init()':
/home/nonoo/mysql/mysql-src-arm/sql/sql_lex.cc:153:18: error: 'symbols' was not declared in this scope

Cross-building

Copy the following ncurses include files generated when building ncurses inside the client/ directory, otherwise the building process won’t find them:

curses.h
ncurses_dll.h
unctrl.h

and edit the file mysql.cc by modifying how the above files are included:

"curses.h"
"unctrl.h"

Run domake. If you’re lucky it will finish successfully at the first try.

If you have the following error

  
Generating ../include/mysqld_error.h, ../sql/share/english/errmsg.sys
/bin/sh: 1: comp_err: not found

copy all executables from mysql-src-x86/BUILD/extra to mysql-src-arm/BUILD/extra and mysql-src-arm/extra.
Run domake again.

If you still have problems and get this error:

make[2]: *** No rule to make target `../scripts/comp_sql', needed by `scripts/mysql_fix_privilege_tables_sql.c'.  Stop.

Copy mysql-src-x86/BUILD/scripts/comp_sql to mysql-src-arm/scripts/comp_sql.
Run domake again.

At this point you should have built it successfully.

Installing

make install DESTDIR=$ROOTFS

It will install in the usr/local/ directory inside the $ROOTFS.

Add the mysql user and group in your target board. If you have more users than root, you have to adapt it by changing the user and group id:

Edit /etc/passwd

mysql:x:1:1:mysql:/nonexistent:/bin/false

Edit /etc/group

mysql:x:1:

and create the required directory

mkdir -p /var/lib

Now you can run MySQL’s install script:

cd /usr/local/
./scripts/mysql_install_db
chown -R mysql:mysql data /var/lib/mysql

Now that you have the MySQL server binary in your rootfs you can execute it, but if you have less than 128MB, it will refuse to start.

You need to configure it properly (my.conf) to use as less memory as possible. I made several tests and the best configuration in which I managed to make it work is the following:

# MySQL clients
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# MySQL server
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# The maximum permitted number of simultaneous client connections
max_connections = 10

# How many threads the server should cache for reuse
thread_cache_size = 0 

# The maximum size of one packet or any generated/intermediate string
max_allowed_packet = 256K

# Each session that needs to do a sort allocates a buffer of this size
sort_buffer_size = 64K

# Improve ORDER BY performance
read_rnd_buffer_size = 64K

# Each client thread is associated with a connection buffer and result buffer 
# that begin with a size given by net_buffer_length but are dynamically 
# enlarged up to max_allowed_packet bytes as needed. 
net_buffer_length = 1K

# The stack size for each thread
thread_stack = 128K

# Do not cache results that are larger than this number of bytes
query_cache_limit = 16K

# Don’t listen on a TCP/IP port at all
skip-networking

# The common part of the directory path for all InnoDB data files
innodb_data_home_dir = /var/lib/mysql/

# The paths to individual InnoDB data files and their sizes
innodb_data_file_path = ibdata1:10M:autoextend

# Stores data and indexes for each newly created table in a separate .ibd file,
# rather than in the system tablespace
#innodb_file_per_table = on

# The directory path to the InnoDB redo log files
innodb_log_group_home_dir = /var/lib/mysql/

# The size in bytes of the buffer pool, the memory area where InnoDB 
# caches table and index data
innodb_buffer_pool_size = 5M

# The size in bytes of a memory pool InnoDB uses to store data dictionary 
# information and other internal data structures
innodb_additional_mem_pool_size = 2M

# The size in bytes of each log file in a log group
innodb_log_file_size = 1M

# The size in bytes of the buffer that InnoDB uses to write to the log files 
# on disk
innodb_log_buffer_size = 256K

# The length of time in seconds an InnoDB transaction waits for a row lock 
# before giving up
innodb_lock_wait_timeout = 50

# Use InnoDB memory allocator instead of system's malloc/free
innodb_use_sys_malloc = 0

table_cache = 4

### MyISAM only???

# Index blocks for MyISAM tables are buffered and are shared by all threads
key_buffer_size = 16K
read_buffer_size = 256K

[mysqldump]
quick
max_allowed_packet = 4M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 1M
sort_buffer_size = 1M

[myisamchk]
key_buffer = 1M
sort_buffer_size = 1M

[mysqlhotcopy]
interactive-timeout

Some of the parameters have hard-coded minimums and MySQL will refuse to start if you set a smaller number or simply ignore it and start with it’s default.

Notice that some of these options reduced significantly its functionality. For example, I was not interested in a TCP/IP server, everything runs inside the embedded device, so I set the skip-networking option.

Play around with the different options to fit your needs.

With this configuration I was able to run MySQL and to have several clients connected writing and reading simultaneously without any memory issues. Even the processor behave nicely when there were only few simultaneous writes and reads.

Have fun!

2 thoughts on “Running MySQL with <16MB memory in an ARM processor

  1. ramya

    hi,
    i’m building mysql5.7.24-27 using yocto. while building i’m getting error like | /bin/sh: comp_sql: command not found
    | make[2]: *** [scripts/sql_commands_sys_schema.h] Error 127
    | make[1]: *** [scripts/CMakeFiles/GenSysSchema.dir/all] Error 2
    | make[1]: *** Waiting for unfinished jobs….
    after copying comp_sql also. Please help me to resolve this issue.

    Regards,
    Ramya

    Reply
    1. paguilar Post author

      Hi ramya,
      This seems more an issue of building MySQL inside Yocto than a fine-tuning of the MySQL performance.
      comp_sql is one of then commands that are built by the native MySQL and used by the MySQL that will be executed in the target.
      Since you’re using Yocto, I suggest to build MySQL using a standard recipe. May be something like this:

      # In your local.conf file:
      IMAGE_INSTALL_append += " mysql"
      # If you're using a Yocto from NXP
      IMAGE_INSTALL_append += " mysql-python"

      Once you’ve successfully built MySQL, you can start the general configuration and fine-tuning in the my.conf file.

      Hope this helps.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *