CODEWITHSUNDEEP
mysqldrupal
Kamran
Kamran

Reputation: 49

Restarting MySQL makes application faster

I have a Drupal 7 application running a large Mysql database. It's built on AWS instance (30GB RAM, 8 CPU). Below is the my.cnf copy.

Issue is that my application gets noticeably fast when I restart the mysql which makes me think that current configuration isn't optimal and once the caches get filled, they slow down the server speed.

Any experts who could point out the issue here?

[mysqld]
# moving datadir to /media/db ebs storage
datadir=/media/db/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/dev/shm/
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


local-infile=0
log-warnings=2

skip-external-locking
bind-address        = 127.0.0.1
expire_logs_days    = 1
max_binlog_size         = 100M
log-bin=bin.log
log-bin-index=bin-log.index
binlog_format=row
key_buffer_size                = 512M
max_allowed_packet  = 32M
thread_stack        = 292K
myisam_sort_buffer_size = 64M
thread_cache_size = 50
myisam-recover         = BACKUP
max_connections        = 100
max_user_connections = 150
table_cache            = 16384
table_open_cache       = 10240
table_definition_cache = 4096
thread_concurrency     = 8
join_buffer_size = 4M
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 200M
innodb_buffer_pool_size= 8G
tmp_table_size=64M
max_heap_table_size=64M
wait_timeout=50
interactive_timeout=50
connect_timeout=10
open-files-limit               = 65535
innodb_flush_log_at_trx_commit = 0
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
table_open_cache = 5000
innodb_log_buffer_size= 32M
innodb_log_file_size = 512m
read_rnd_buffer_size = 8M
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout = 25
innodb_write_io_threads = 16
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 24
innodb_read_io_threads = 16
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_autoinc_lock_mode = 2

[mysqldump]
max_allowed_packet  = 32M

[isamchk]
key_buffer  = 32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
set-variable=long_query_time=1
long_query_time=10
slow_query_log_file =/var/log/mysql/mysql-slow.log

Upvotes: 0

Views: 99

Answers (1)

Wilson Hauck
Wilson Hauck

Reputation: 2343

Rate Per Second=RPS - Suggestions for your my.cnf [mysqld] section

read_rnd_buffer_size=256K  # from 8M to reduce handler_read_rnd_next RPS
innodb_lru_scan_depth=100  # from 1024 to reduce CPU cycles used every SECOND
query_cache_min_res_unit=512  # from 2K to store more RESULTS in same query_cache_size

The BEST to you.

Upvotes: 1

Related Questions