CODEWITHSUNDEEP
phpmysqldatabase-performance
user2721564
user2721564

Reputation: 43

Mysql using memory limit

I have database (30mb only) and this database using 4gb ram! Max 10 people online in my site. I think that is bad cnf.my.

mysql> show variables like '%size%';


+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 32768                |
| bulk_insert_buffer_size         | 8388608              |
| delayed_queue_size              | 1000                 |
| innodb_additional_mem_pool_size | 1048576              |
| innodb_buffer_pool_size         | 8388608              |
| innodb_log_buffer_size          | 1048576              |
| innodb_log_file_size            | 5242880              |
| join_buffer_size                | 4194304              |
| key_buffer_size                 | 268435456            |
| key_cache_block_size            | 1024                 |
| large_page_size                 | 0                    |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_size                 | 1073741824           |
| max_heap_table_size             | 268435456            |
| max_join_size                   | 18446744073709551615 |
| max_long_data_size              | 1048576              |
| max_relay_log_size              | 0                    |
| myisam_data_pointer_size        | 6                    |
| myisam_max_sort_file_size       | 9223372036853727232  |
| myisam_mmap_size                | 18446744073709551615 |
| myisam_sort_buffer_size         | 8388608              |
| preload_buffer_size             | 32768                |
| profiling_history_size          | 15                   |
| query_alloc_block_size          | 8192                 |
| query_cache_size                | 268435456            |
| query_prealloc_size             | 8192                 |
| range_alloc_block_size          | 4096                 |
| read_buffer_size                | 131072               |
| read_rnd_buffer_size            | 262144               |
| sort_buffer_size                | 20971520             |
| sql_max_join_size               | 18446744073709551615 |
| thread_cache_size               | 4                    |
| tmp_table_size                  | 41943040             |
| transaction_alloc_block_size    | 8192                 |
| transaction_prealloc_size       | 4096                 |
+---------------------------------+----------------------+
35 rows in set (0.00 sec)

And my.cnf:

 [mysqld]

    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    long_query_time=1
    log-slow-queries=/var/log/mysql/log-slow-queries.log
    interactive_timeout=180
    wait_timeout=180
    max_connections=100
    innodb_buffer_pool_size=256M
    query_cache_size=256M
    key_buffer_size=256M
    sort_buffer_size=20M
    innodb_flush_log_at_trx_commit=0
    innodb_flush_method=O_DIRECT
    query_cache_type=1
    query_cache_limit=2M
    table_cache=1024
    join_buffer_size=4M
    thread_cache_size=4
    tmp_table_size=40M
    max_heap_table_size=256MB

    user=mysql
    symbolic-links=0
    #bind-address=127.0.0.1

    [mysqld_safe]

    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

In my vps I have 5.7 gb ram

how to reduce memory usage?

Upvotes: 1

Views: 3443

Answers (2)

Wilson Hauck
Wilson Hauck

Reputation: 2343

my.cnf is asking for 

innodb_buffer_pool_size=256M

but size list indicated you have only 8M for ibps. Need to find out why you do not have 256M, check logs.

and

max_heap_table_size=256MB    needs to be
max_heap_table_size=40M # from 256MB to correct MB and match tmp_table_size

Upvotes: 0

Michael - sqlbot
Michael - sqlbot

Reputation: 179004

I suspect sort_buffer_size and join_buffer_size are the big contributors to excessive memory usage here. Start by commenting those out, in configuration, so that you use the default values.

Those values can have an impact on memory usage that is workload-related.

Knowing very little about your system, it still seems like your key_buffer_size and innodb_buffer_pool_size could be twice as large as needed (and one or the other may be entirely unnecessary, depending on which storage engine you are using), and your query_cache_size is probably anywhere from 2 to 16 times as large as necessary. Unlike the settings mentioned above, that can vary memory implications by workload, these settings will reduce the baseline amount of memory used by the server, regardless of workload.

The next time your system's memory usage gets higher than you expect, it might also be interesting to SET GLOBAL THREAD_CACHE_SIZE = 0; and then watch whether the server's memory allocation changes as clients disconnect and reconnect.

Upvotes: 0

Related Questions