I had a Drupal website on a test box and I am the only visitor.
When I do nothing. Mysql is taking like 7% mem. When I click homepage. I can literally see that mem ramps up to 15% of the mem by using top command.
Then 15% it is and it sits there. So previously, I have 200M about free memory, now only 10M free memory left.
No kidding that everything is so slow. The memory just not enough.
Memory in all is 1.5GB. It might not be big but it's a good Dell server with good CPU.
Any mysql variable to look into? Or any tools to help me make mysql more realistic .
Attached is the my.cnf
[client]
default-character-set=utf8
port = 3306
socket = /tmp/mysql.sock
[mysqld]
character-set-server = utf8
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
user = mysql
port = 3306
socket = /data0/mysql/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data0/mysql/data
log-error = /data0/mysql/mysql_error.log
pid-file = /data0/mysql/mysql.pid
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
table_cache = 614
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 300
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = MyISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 512M
max_heap_table_size = 246M
long_query_time = 3
log-slave-updates
log-bin = /data0/mysql/binlog/binlog
binlog_cache_size = 4M
binlog_format = MIXED
max_binlog_cache_size = 64M
max_binlog_size = 1G
relay-log-index = /data0/mysql/relaylog/relaylog
relay-log-info-file = /data0/mysql/relaylog/relaylog
relay-log = /data0/mysql/relaylog/relaylog
expire_logs_days = 30
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
interactive_timeout = 120
wait_timeout = 120
skip-name-resolve
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 256M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
log-slow-queries = /data0/mysql/slow.log
long_query_time = 10
log-queries-not-using-indexes
[mysqldump]
quick
max_allowed_packet = 32M
Your MySQL config isn't scaled for a setup where you want to use only a small amount of memory for your database server. In your MySQL distribution, there should be a set of pre-written
my.cnf
files optimised for different levels of memory usage. I'd recommend starting with themy-small.cnf
version, and seeing how well that meets your performance/memory usage requirements.