Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a dedicated Linux Server for our mysql DB's. We have been having terrible speed issues with any scripts we run, it seems to get worse over time, as we run more scripts. I can run these scripts on my local machine and they will run twice as fast.

Now it seems to me that we have a memory problem. As we keep running scripts, memory usage gets higher (This is obviously normal), but when these end they do not release the memory. Note* This is not cached memory.

I can't post any images as I have 0 rep points, but running htop shows me that currently we are using 28245/32150MB, again - this is not cached memory and no scripts are currently running.

Any help will be greatly appreciated.

The my.cnf from the server:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
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
lc-messages-dir = /usr/share/mysql
#skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 41.76.208.223
#bind-address   = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 384M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 384
skip-name-resolve
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
table_cache            = 1500 
table_definition_cache  = 1500
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 4M
query_cache_size        = 128M
join_buffer_size    = 128M
max_heap_table_size = 128M
tmp_table_size      = 128M
read_buffer_size    = 32M 
innodb_buffer_pool_size = 25G
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#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          = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index           = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index         = /var/log/mysql/mysql-relay-bin.index
expire_logs_days        = 10
max_binlog_size         = 100M
log_slave_updates       = 1
auto-increment-increment    = 2
auto-increment-offset       = 1
replicate-ignore-db             = mysql
replicate-ignore-db             = information_schema
replicate-ignore-db             = performance_schema
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * 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!
#
# * 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  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * 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/
share|improve this question
    
You can use htop to get an easier overview over your resources. –  Christopher Perrin Aug 7 '13 at 6:58
    
I did specify that I use htop in the question. That's how I found the memory problem. –  SeanRoutledge Aug 7 '13 at 7:13
add comment

2 Answers

up vote 1 down vote accepted

Looking at the config here, mysql will use up nearly all the memory on the machine - if it's a dedicated MySQL server only running InnoDB, that's exactly how it should be configured.

but when these end they do not release the memory

They won't realease memory form the innodb buffer pool - that's how the system is intended to work.

If your system is using swap then either mysql is leaking somewhere (you forgot to tell us what version you are using). Far more likely is that something else is using up significant amounts of memory and not freeing it up.

share|improve this answer
    
Thanks for the explanation. This is a dedicated mysql server, so I guess that is alright. I always thought that once a mysql process was complete the memory it was using would be cached. I am running mysql 5.5 –  SeanRoutledge Aug 7 '13 at 8:58
add comment

Is it "innodb_buffer_pool_size = 25G"?

Using internal caches of the database make database work faster but at the cost of slowing down all other services as system can't reclaim this memory, can't use it for filesystem cache, can't give it to processes. Try lowering your MySQL appetite.

share|improve this answer
    
So this in turn would also affect any new scripts I run? Causing them to run slower? –  SeanRoutledge Aug 7 '13 at 9:00
    
This makes MySQL store in it's own memory most innodb data it encounters. The more script you run the more data gets touched by database and cached. Try limiting this number to something like 8gb and check again. –  kworr Aug 7 '13 at 9:11
    
Edit* As this is a DB server, running only mysql and apache. What is the point of having the buffer pool size so low? –  SeanRoutledge Aug 7 '13 at 9:52
    
If you don't want to lower the buffer size, start the scripts somewhere else. –  Christopher Perrin Aug 7 '13 at 10:14
    
As this is a DB server, running only mysql and apache - you said before this was a dedicated DBMS server - if it's not a dedicated DBMS server then that's a completely different question / answer. –  symcbean Aug 7 '13 at 10:49
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.