{"id":396,"date":"2015-01-05T00:53:17","date_gmt":"2015-01-04T23:53:17","guid":{"rendered":"http:\/\/paguilar.org\/?p=396"},"modified":"2015-01-05T00:58:50","modified_gmt":"2015-01-04T23:58:50","slug":"running-mysql-in-arm-with","status":"publish","type":"post","link":"https:\/\/paguilar.org\/?p=396","title":{"rendered":"Running MySQL with <16MB memory in an ARM processor"},"content":{"rendered":"

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<\/a> that is may be the widest used embedded database.<\/p>\n

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).
\nIn these cases SQLite starts to have issues handling the concurrence even if you configure it for using Write-Ahead-Log (WAL)<\/a> instead of the default rollback journal.<\/p>\n

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.
\nIn fact, MySQL was running exactly in 13.58MB:<\/p>\n

\"htop_mysql\"<\/a><\/p>\n

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

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.<\/p>\n

First, let’s export common environment variables that make our life easier:<\/p>\n

export HOST=arm-linux\r\nexport BUILD=i386-linux\r\nexport ROOTFS=\/home\/projects\/rootfs<\/pre>\n
Requirements<\/strong><\/p>\n
libncurses<\/span>
\nPackage: libncurses 5.7<\/a><\/p>\n
Configure, build and install the only requirement:<\/p>\n
\r\ncd \/home\/nonoo\/mysql\r\ntar xvf ncurses-5.7.tar.gz\r\ncd ncurses-5.7\r\nCC=arm-linux-gcc CXX=arm-linux-g++ .\/configure --host=arm-linux-gnu --prefix=\/usr\r\nmake\r\nmake install DESTDIR=\/home\/projects\/libncurses-compiled\r\n<\/pre>\n
MySQL<\/span>
\nPackage: MySQL 5.5.37<\/a><\/p>\n
MySQL<\/strong><\/p>\n
Download MySQL and copy it these two directories:<\/p>\n
\r\nmkdir mysql-src-x86   mysql-src-arm\r\ntar xvf mysql-5.5.37.tar.gz\r\nmv mysql-5.5.37 mysql-src-x86\r\ntar xvf mysql-5.5.37.tar.gz\r\nmv mysql-5.5.37 mysql-src-arm\r\n<\/pre>\n
Compile the host version. In my case I have an AMD 64 bits processor. Choose the one that fits your host:<\/p>\n
\r\ncd mysql-src-x86\/BUILD\r\n.\/compile-amd64-max\r\n<\/pre>\n
Now we can start to cross-build MySQL:<\/p>\n
Configuring<\/strong><\/p>\n
Create the CMake toolchain file mysql-src-arm\/build\/mysql_at91sam9260.cmake<\/em>:<\/p>\n
\r\n# this one is important\r\nSET(CMAKE_SYSTEM_NAME Linux)\r\n#this one not so much\r\nSET(CMAKE_SYSTEM_VERSION 1)\r\n\r\n# specify the cross compiler\r\nSET(CMAKE_C_COMPILER   \/opt\/buildroot-we500\/usr\/bin\/arm-linux-gcc)\r\nSET(CMAKE_CXX_COMPILER \/opt\/buildroot-we500\/usr\/bin\/arm-linux-g++)\r\n\r\n# where is the target environment \r\nSET(CMAKE_FIND_ROOT_PATH  \/opt\/buildroot\/usr\/arm-linux\/sysroot \/home\/projects\/rootfs)\r\n\r\n# search for programs in the build host directories\r\nSET(CMAKE_FIND_ROOT_PATH_MODE_PROGRAM NEVER)\r\n# for libraries and headers in the target directories\r\nSET(CMAKE_FIND_ROOT_PATH_MODE_LIBRARY ONLY)\r\nSET(CMAKE_FIND_ROOT_PATH_MODE_INCLUDE ONLY)\r\n\r\nSET(CMAKE_LIBRARY_PATH ${CMAKE_LIBRARY_PATH} \/home\/projects\/libncurses-compiled\/usr)\r\n\r\nSET(CMAKE_INSTALL_PREFIX \/home\/projects\/rootfs)\r\n\r\n# MySQL specific\r\nSET(STACK_DIRECTION 1)\r\nSET(WITH_UNIT_TESTS OFF)\r\n<\/pre>\n
Create and run the script mysql-src-arm\/BUILD\/doconfig<\/em>:<\/p>\n
\r\n#!\/bin\/bash\r\nrm CMakeCache.txt\r\n#path=`dirname $0`\r\ncmake -DCMAKE_TOOLCHAIN_FILE=mysql_at91sam9260.cmake ..  \r\n<\/pre>\n
Prepare to build<\/strong><\/p>\n
Create the script mysql-src-arm\/BUILD\/domake<\/em>:<\/p>\n
\r\n#!\/bin\/bash\r\nexport PATH=$PATH:`pwd`\/extra\r\nexport PATH=$PATH:`pwd`\/..\/scripts\r\n\r\n# These scripts are needed to be copied from the x86 dir every time before make\r\n# runs because they will be overwritten with ARM versions after a while.\r\ncp ..\/..\/mysql-5.5.37-x86\/sql\/gen_lex_hash   sql\/gen_lex_hash\r\nexport PATH=$PATH:`pwd`\/sql\r\ncp ..\/..\/mysql-5.5.37-x86\/storage\/perfschema\/gen_pfs_lex_token   storage\/perfschema\/gen_pfs_lex_token\r\nexport PATH=$PATH:`pwd`\/storage\/perfschema\/\r\n\r\nmake\r\n<\/pre>\n
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:<\/p>\n
\r\nGenerating pfs_lex_token.h\r\n\/bin\/sh: 1: gen_pfs_lex_token: not found\r\n<\/pre>\n
\r\nBuilding CXX object client\/CMakeFiles\/mysqladmin.dir\/mysqladmin.cc.o\r\n\/home\/nonoo\/mysql\/mysql-src-arm\/sql\/sql_lex.cc: In function 'void lex_init()':\r\n\/home\/nonoo\/mysql\/mysql-src-arm\/sql\/sql_lex.cc:153:18: error: 'symbols' was not declared in this scope\r\n<\/pre>\n
Cross-building<\/strong><\/p>\n
Copy the following ncurses include files generated when building ncurses inside the client\/<\/em> directory, otherwise the building process won’t find them:<\/p>\n
\r\ncurses.h\r\nncurses_dll.h\r\nunctrl.h\r\n<\/pre>\n
and edit the file mysql.cc<\/em> by modifying how the above files are included:<\/p>\n
\r\n\"curses.h\"\r\n\"unctrl.h\"\r\n<\/pre>\n
Run domake<\/em>. If you’re lucky it will finish successfully at the first try. <\/p>\n
If you have the following error<\/p>\n
  \r\nGenerating ..\/include\/mysqld_error.h, ..\/sql\/share\/english\/errmsg.sys\r\n\/bin\/sh: 1: comp_err: not found\r\n<\/pre>\n
copy all executables from mysql-src-x86\/BUILD\/extra<\/em> to mysql-src-arm\/BUILD\/extra<\/em> and mysql-src-arm\/extra<\/em>.
\nRun domake<\/em> again.<\/p>\n
If you still have problems and get this error:<\/p>\n
\r\nmake[2]: *** No rule to make target `..\/scripts\/comp_sql', needed by `scripts\/mysql_fix_privilege_tables_sql.c'.  Stop.\r\n<\/pre>\n
Copy mysql-src-x86\/BUILD\/scripts\/comp_sql<\/em> to mysql-src-arm\/scripts\/comp_sql<\/em>.
\nRun domake again.<\/p>\n
At this point you should have built it successfully.<\/p>\n
Installing<\/strong><\/p>\n
\r\nmake install DESTDIR=$ROOTFS\r\n<\/pre>\n
It will install in the usr\/local\/<\/em> directory inside the $ROOTFS.<\/p>\n
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:<\/p>\n
Edit \/etc\/passwd<\/em><\/p>\n
\r\nmysql:x:1:1:mysql:\/nonexistent:\/bin\/false\r\n<\/pre>\n
Edit \/etc\/group<\/em><\/p>\n
\r\nmysql:x:1:\r\n<\/pre>\n
and create the required directory<\/p>\n
\r\nmkdir -p \/var\/lib\r\n<\/pre>\n
Now you can run MySQL’s install script:<\/p>\n
\r\ncd \/usr\/local\/\r\n.\/scripts\/mysql_install_db\r\nchown -R mysql:mysql data \/var\/lib\/mysql\r\n<\/pre>\n
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.<\/p>\n
You need to configure it properly (my.conf<\/em>) 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:<\/p>\n
\r\n# MySQL clients\r\n[client]\r\nport = 3306\r\nsocket = \/var\/run\/mysqld\/mysqld.sock\r\n\r\n# MySQL server\r\n[mysqld]\r\nport = 3306\r\nsocket = \/var\/run\/mysqld\/mysqld.sock\r\n\r\n# The maximum permitted number of simultaneous client connections\r\nmax_connections = 10\r\n\r\n# How many threads the server should cache for reuse\r\nthread_cache_size = 0 \r\n\r\n# The maximum size of one packet or any generated\/intermediate string\r\nmax_allowed_packet = 256K\r\n\r\n# Each session that needs to do a sort allocates a buffer of this size\r\nsort_buffer_size = 64K\r\n\r\n# Improve ORDER BY performance\r\nread_rnd_buffer_size = 64K\r\n\r\n# Each client thread is associated with a connection buffer and result buffer \r\n# that begin with a size given by net_buffer_length but are dynamically \r\n# enlarged up to max_allowed_packet bytes as needed. \r\nnet_buffer_length = 1K\r\n\r\n# The stack size for each thread\r\nthread_stack = 128K\r\n\r\n# Do not cache results that are larger than this number of bytes\r\nquery_cache_limit = 16K\r\n\r\n# Don\u2019t listen on a TCP\/IP port at all\r\nskip-networking\r\n\r\n# The common part of the directory path for all InnoDB data files\r\ninnodb_data_home_dir = \/var\/lib\/mysql\/\r\n\r\n# The paths to individual InnoDB data files and their sizes\r\ninnodb_data_file_path = ibdata1:10M:autoextend\r\n\r\n# Stores data and indexes for each newly created table in a separate .ibd file,\r\n# rather than in the system tablespace\r\n#innodb_file_per_table = on\r\n\r\n# The directory path to the InnoDB redo log files\r\ninnodb_log_group_home_dir = \/var\/lib\/mysql\/\r\n\r\n# The size in bytes of the buffer pool, the memory area where InnoDB \r\n# caches table and index data\r\ninnodb_buffer_pool_size = 5M\r\n\r\n# The size in bytes of a memory pool InnoDB uses to store data dictionary \r\n# information and other internal data structures\r\ninnodb_additional_mem_pool_size = 2M\r\n\r\n# The size in bytes of each log file in a log group\r\ninnodb_log_file_size = 1M\r\n\r\n# The size in bytes of the buffer that InnoDB uses to write to the log files \r\n# on disk\r\ninnodb_log_buffer_size = 256K\r\n\r\n# The length of time in seconds an InnoDB transaction waits for a row lock \r\n# before giving up\r\ninnodb_lock_wait_timeout = 50\r\n\r\n# Use InnoDB memory allocator instead of system's malloc\/free\r\ninnodb_use_sys_malloc = 0\r\n\r\ntable_cache = 4\r\n\r\n### MyISAM only???\r\n\r\n# Index blocks for MyISAM tables are buffered and are shared by all threads\r\nkey_buffer_size = 16K\r\nread_buffer_size = 256K\r\n\r\n[mysqldump]\r\nquick\r\nmax_allowed_packet = 4M\r\n\r\n[mysql]\r\nno-auto-rehash\r\n# Remove the next comment character if you are not familiar with SQL\r\n#safe-updates\r\n\r\n[isamchk]\r\nkey_buffer = 1M\r\nsort_buffer_size = 1M\r\n\r\n[myisamchk]\r\nkey_buffer = 1M\r\nsort_buffer_size = 1M\r\n\r\n[mysqlhotcopy]\r\ninteractive-timeout\r\n<\/pre>\n
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.<\/p>\n
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<\/em> option.<\/p>\n
Play around with the different options to fit your needs.<\/p>\n
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.<\/p>\n
Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,44],"tags":[],"class_list":["post-396","post","type-post","status-publish","format-standard","hentry","category-compiling","category-databases"],"_links":{"self":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts\/396","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=396"}],"version-history":[{"count":17,"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts\/396\/revisions"}],"predecessor-version":[{"id":416,"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts\/396\/revisions\/416"}],"wp:attachment":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=396"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=396"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=396"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}