Ubuntu 16.10 Server MariaDB (MySql) open_file_limit won’t go higher than 16364

In Ubuntu 16.10 and other systemd enabled OS like Debian, CentOs and LinuxMint with MariaDB Server version 10.x+ open_file_limit won’t go higher than 16364. according to MariaDB it’s simply because the SystemD daemon prefer new ways to set limit and a few other vital modifications

systemd is an init replacement that MariaDB uses on releases since 10.1.8. Distribution packages before this version may use a different configuration so consult their documentation if required.

systemd services are packaged on RPM and Debian based Linux distributions. When systemd is used, mysqld_safe is not used and settings in the mysqld_safe section of configuration files will not be read or applied.

systemd overview

systemd service files are included in the MariaDB-server package. The service definition is installed in /usr/lib/systemd/system/mariadb.service. The service name is mariadb.service; however aliases to mysql.service and mysqld.service are included for convenience.

Unlike previous init scripts, the mysqld process is executed directly from the init script running as the mysql user. This places a couple of limitations on situations that were previously possible:

  • open-files-limit cannot be raised beyond the operating system limit (usually 1K) and hence the systemd configuration for mariadb has LimitNOFILE set to 16K by default;
  • memlock can be used from version 10.1.10; and
  • The start timeout from init scripts was quite large and special configuration may be required if there is a slow startup time (MDEV-9202).
  • A mapping of common mysqld_safe options to systemd options is provided below.

    mysqld_safe option systemd option Comments
    no option ProtectHome=false If any MariaDB files are in /home/
    no option PrivateDevices=false If any MariaDB storage references raw block devices
    no option ProtectSystem= If any MariaDB write any files to anywhere under /boot, /usr or /etc
    no option TimeoutStartSec={time} Set if the systemd reports failure to start because of timeout. 0 disables any timeout
    no option (ref MDEV-9264) OOMScoreAdjust={priority} e.g. -600 to lower priority of OOM killer for mysqld
    open_files LimitNOFILE={limit}  
    core_file_size LimitCORE={size}  
      LimitMEMLOCK={size} or unlimited When large-pages or memlock is used
    nice Nice={nice value}  
    syslog StandardOutput=syslog  
    syslog-tag SyslogIdentifier  
    flush-caches ExecStartPre=/usr/bin/sync  
      ExecStartPre=/usr/sbin/sysctl -q -w vm.drop_caches=3  
    numa-interleave ExecStart=/usr/bin/numactl –interleave=all /usr/sbin/mysqld ${MYSQLD_OPTS} ${_WSREP_NEW_CLUSTER}  

    Note: systemd.service contains the official meanings for these systemd settings.

    There are other options and the mariadb-service-convert script will attempt to convert these as accurately as possible.

    In addition to the set of options previously provided by mysqld_safe, systemd.service has considerably more options.

When all or some of these settings have been altered don’t forget to:

systemctl daemon-reload
systemctl restart mysql.service

To make the new values active

To verify your changes open a mysql instance in terminal or your preferred Mysql GUI and type

show global variables like 'open%';

If you can see the changes, your done. As exsamle I’ve set my LimitNOFILE=1024000 in /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf and the output looks like

MariaDB [(none)]> show global variables like 'open%';
| Variable_name    | Value   |
| open_files_limit | 1024000 |
1 row in set (0.00 sec)

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.