Kubuntu distributive 16.04 with the latest updates. The situation is as follows: apache and mysql are running on the desktop for development. Periodically, a C ++ program accesses mysql data. Basically select queries and them a little. Apache is kept for testing web parts and phpmyadmin. Worth 4GB of RAM, x84. 4 main databases with minimal fields and tables. However, mysql eats from 500mb to 1GB of RAM during idle time. How can I reduce this consumption? my.conf is standard, I quote it:

# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/

Conclusion:

 $ sudo ls /etc/mysql/conf.d mysql.cnf mysqldump.cnf $ sudo ls /etc/mysql/mysql.conf.d mysqld.cnf mysqld_safe_syslog.cnf

To write everything else is frankly lazy, if necessary - write.

  • All you need is content /etc/mysql/conf.d/mysqld.cnf - cheops
  • your file reference simply does not exist. This is what you may need. pastebin.com/HskJM1Zq - Gravit
  • What type of tables do you mostly use? MyISAM, InnoDB? - cheops
  • And those. other. On the table in 3 thousand records (the largest table in all databases) - InnoDB - Gravit
  • How do you determine the size of the occupied - this is exactly physical memory, not virtual? - cheops

2 answers 2

It is not visible in the configuration files that too much memory is allocated to persistent buffers or caches.

128Mb must consume InnoDB buffer, the size of which is governed by the innodb_buffer_pool_size directive in the [mysqld] section. This volume is used to cache InnoDB data and keys, you can try to reduce it, but too small a volume can slow down InnoDB.

16MB goes to the MyISAM key_buffer_size key key_buffer_size , you should not make it smaller - the value is not great either.

16MB goes to the query cache query_cache_size . In principle, you can disable this type of cache, locally it is unlikely that you can benefit from it, especially if MySQL is often idle.

Actually everything, some amount of memory is occupied by other caches, but they are so small that they hardly add up to a few megabytes.

Theoretically, the rest of the memory should consume connections (up to several megabytes can be allocated to each connection). See if the connections are hanging with the command

 SHOW PROCESSLIST;

If there are too many connections, remove them using the KILL operator.

  • 2 active connections 1 from phpmyadmin other from root (i). Initially, the process consumes about 150MB of memory. Some time after the launch, as if on a timer, from 3 to 20mb is added every 3 seconds and so on until it reaches large values. - Gravit
  • @Gravit please execute the query show variables like 'innodb_buffer_pool_size'; what result comes back? - cheops
  • 150MB you just give those buffers that I gave in the answer, it is strange that the size grows further. - cheops
  • innodb_buffer_pool_size 134217728 - Gravit
  • At the moment, the mysqld process consumes 694 732kb of memory. Until he began to kill him, suddenly you need to fulfill some kind of requests. - Gravit

This was a https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1576930 bug. The following solution helped: Write these lines to the config

 innodb-read-io-threads=1 innodb-write-io-threads=1