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?
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.