Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.31
-
None
-
None
-
Debian 7.1
Description
I think I found a memory leak in MariabDB 5.5.31 built for Debian Wheezy. I have been using MariaDB 5.3 for a few months (I do not remember the exact build) without any problems. But while upgrading the machine to Wheezy I decided to upgrade to MariaDB 5.5.31. Since the upgrade the server is slowing down after a few days because there is no memory left for the filesystem cache. It even starts to swap out memory to disk. When I let it get this far it takes almost an hour to do a /etc/init.d/mysql restart but then I have an immediate 28GB of free memory back. This has happened three times in the last month.
MariaDB 5.3 didn't ever use more than 10GB of memory using the exact same configuration file.
The database is doing mostly processing of data, with 4 to 20 connections. On average the database performs 4500 qps. 79% selects, 12% inserts, 9% updates. It has a mix of MyISAM, InnoDB and Merge tables. Doing a 'FLUSH TABLES' does not help to lower the memory.
I do not know what information I can give to find out what the problem is. I just restarted MariaDB again so in a few days I should have the same situation again. Here is some information about the server:
- 1 Intel quad core Xeon processor
- 32GB memory
- 2TB of filesystem space
$ uname -a
|
Linux travis 3.2.0-4-amd64 #1 SMP Debian 3.2.41-2+deb7u2 x86_64 GNU/Linux
|
|
|
$ mysql -V
|
mysql Ver 15.1 Distrib 5.5.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1
|
my.cnf file:
[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
|
language = /usr/share/mysql/english
|
skip-external-locking
|
bind-address = 127.0.0.1
|
|
|
#
|
# * Fine Tuning
|
#
|
key_buffer = 2G
|
max_allowed_packet = 64M
|
thread_stack = 192K
|
thread_cache_size = 64
|
sort_buffer_size = 1M
|
read_buffer_size = 1M
|
read_rnd_buffer_size = 8M
|
join_buffer_size = 1M
|
back_log = 100
|
max_connect_errors = 10000
|
open-files = 20000
|
interactive_timeout = 600
|
wait_timeout = 600
|
max_connections = 100
|
myisam_sort_buffer_size = 64M
|
concurrent_insert = 1
|
|
|
innodb_file_per_table = 1
|
innodb_buffer_pool_size = 6G
|
innodb_log_file_size = 2000M
|
innodb_log_buffer_size = 4M
|
innodb_flush_log_at_trx_commit = 2
|
innodb_thread_concurrency = 8
|
innodb_flush_method = O_DIRECT
|
|
|
# This replaces the startup script and checks MyISAM tables if needed
|
# the first time they are touched
|
myisam-recover = BACKUP
|
table_cache = 16K
|
thread_concurrency = 16
|
|
|
max_allowed_packet = 16M
|
tmp_table_size = 64M
|
max_heap_table_size = 64M
|
|
|
#
|
# * Query Cache Configuration
|
#
|
query_cache_limit = 1M
|
query_cache_size = 16M
|
|
|
# _____________________________
|
#< Replication/Backup Settings >
|
# -----------------------------
|
log_bin = mysql-bin
|
server_id = 1001
|
expire_logs_days = 10
|
max_binlog_size = 1G
|
|
|
# Enable this to make replication more resilient against server
|
# crashes and restarts, but can cause higher I/O on the server.
|
sync_binlog=1
|
|
|
[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/
|
Attachments
Issue Links
Activity
Hi Ilan,
I'm glad it helped. The bug seems to have been fixed in XtraDB already, so the fix should be merged into the next MariaDB release (5.5.32).
I can't take credit for the workaround, readdressing your thanks to Patryk Pomykalski who posted the link to the XtraDB bug in a similar recently filed JIRA issue:
Just wanted to drop a note to let you know that without restarting the service it is still running with 23.5% of memory usage. So even after 8 days of running under high load the memory usage is stable. I'm now 100% sure the query cache is the cause of my memory problems.
The fix should be in 5.5.32 release. The bug was fixed in XtraDB 5.5.32 (at least it is marked as such in https://bugs.launchpad.net/percona-server/+bug/1170103), and MariaDB 5.5.32 includes XtraDB 5.5.32.
We didn't have a reproducible test case to verify it, so please re-open the issue (or comment on it to get it re-opened) if you still have the problem with MariaDB 5.5.32,
Disabling the query cache seemed to have fixed the problem. With an uptime of 3 days mysqld is only using 23.1% of memory as is configured. Most of the rest of the servers memory is now in use by the filesystem cache which dramatically improved the query times on large MyISAM tables. Thank you for suggesting this fix Elena.