High Load mysql on Debian server
- by Oleg Abrazhaev
I have Debian server with 32 gb memory. And there is apache2, memcached and nginx on this server.
Memory load always on maximum. Only 500m free.
Most memory leak do MySql. Apache only 70 clients configured, other services small memory usage.
When mysql use all memory it stops. And nothing works, need mysql reboot.
Mysql configured use maximum 24 gb memory.
I have hight weight InnoDB bases. (400000 rows, 30 gb). And on server multithread daemon, that makes many inserts in this tables, thats why InnoDB.
There is my mysql config.
[mysqld]
#
# * Basic Settings
#
default-time-zone = "+04:00"
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
default-time-zone='Europe/Moscow'
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#
# * Fine Tuning
#
#low_priority_updates = 1
concurrent_insert = ALWAYS
wait_timeout = 600
interactive_timeout = 600
#normal
key_buffer_size = 2024M
#key_buffer_size = 1512M
#70% hot cache
key_cache_division_limit= 70
#16-32
max_allowed_packet = 32M
#1-16M
thread_stack = 8M
#40-50
thread_cache_size = 50
#orderby groupby sort
sort_buffer_size = 64M
#same
myisam_sort_buffer_size = 400M
#temp table creates when group_by
tmp_table_size = 3000M
#tables in memory
max_heap_table_size = 3000M
#on disk
open_files_limit = 10000
table_cache = 10000
join_buffer_size = 5M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#myisam_use_mmap = 1
max_connections = 200
thread_concurrency = 8
#
# * Query Cache Configuration
#
#more ignored
query_cache_limit = 50M
query_cache_size = 210M
#on query cache
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
#replicate-do-db = gate
log-bin-index = /var/lib/mysql/mysql-bin.index
log-error = /var/lib/mysql/mysql-bin.err
relay-log = /var/lib/mysql/relay-bin
relay-log-info-file = /var/lib/mysql/relay-bin.info
relay-log-index = /var/lib/mysql/relay-bin.index
binlog_do_db = 24avia
expire_logs_days = 10
max_binlog_size = 100M
read_buffer_size = 4024288
innodb_buffer_pool_size = 5000M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
table_definition_cache = 2000
group_concat_max_len = 16M
#binlog_do_db = gate
#binlog_ignore_db = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
#skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 500M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 32M
key_buffer_size = 512M
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
Please, help me make it stable.
Memory used
/etc/mysql # free
total used free shared buffers cached
Mem: 32930800 32766424 164376 0 139208 23829196
-/+ buffers/cache: 8798020 24132780
Swap: 33553328 44660 33508668
Maybe my problem not in memory, but MySQL stops every day.
As you can see, cache memory free 24 gb.
Thank to Michael Hampton? for correction.
Load overage on server 3.5. Maybe hdd or another problem? Maybe my config not optimal for 30gb InnoDB ?
