MySql ignores memory settings

Question

I'm using MySql community server 5.6 on Ubuntu 15.04, I have /etc/mysql/my.cnf (no other config files). I've made changes to it and restarted MySql. When I use such query

SELECT @@key_buffer_size

(or some other config from my.cnf file) it returns the same value as in my.cnf. I'm trying to minimize memory consumption, but after all efforts to do that MySql still starts ~20 threads and consumes ~370MB.

Could you please help me to find why MySql doesn't use (but sees) configs?

my.cnf (complete file content) is:

[mysql]
    port                           = 3306
    socket                         = /run/mysqld/mysqld.sock

[mysqld]
    user                           = mysql
    default_storage_engine         = InnoDB
    socket                         = /run/mysqld/mysqld.sock
    pid_file                       = /var/lib/mysql/mysql.pid

    myisam_recover                 = FORCE,BACKUP

    max_allowed_packet             = 16M
    max_connect_errors             = 10

    datadir                        = /var/lib/mysql/

    log_bin                        = /var/lib/mysql/mysql_bin
    expire_logs_days               = 5

    sync_binlog                    = 1
    binlog_cache_size              = 4K
    binlog_stmt_cache_size         = 4K
    log_error                      = /var/lib/mysql/mysql_error.log
    log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql_slow.log

    tmp_table_size                 = 1K
    max_heap_table_size            = 16K
    query_cache_type               = 0
    query_cache_size               = 4M
    query_cache_limit              = 256K
    thread_stack                   = 128K
    open_files_limit               = 65535
    table_definition_cache         = 1K
    table_open_cache               = 2K
    max_connections                = 30
    key_buffer_size                = 8M
    thread_cache_size              = 0
    host_cache_size                = 0
    sort_buffer_size               = 32K
    read_buffer_size               = 8200
    read_rnd_buffer_size           = 8200
    bulk_insert_buffer_size        = 0
    join_buffer_size               = 128

    net_buffer_length              = 1K
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 5M
    innodb_log_buffer_size         = 256K
    innodb_sort_buffer_size        = 64K
    innodb_ft_cache_size           = 1600K
    innodb_ft_total_cache_size     = 32M

I've tried also to change thread_cache_size = 1 and max_connections = 5 And still there 21 Mysql's processes and 74% of RAM used by it(out of 512MB). — heroin, Jan 14 '16 at 20:16

score 0 · Accepted Answer · answered Jan 15 '16 at 06:30

0

Finally, I've managed to resolve it. In bug of 5.6.10 version

I've found a solution: to comment line

table_definition_cache         = 1K

Although I have version 5.6.27 it works greatly. Now MySql consumes less than 70MB.

answered Jan 15 '16 at 06:30

heroin

101
3

MySql ignores memory settings

1 Answers1