MySQL Bugs: #71763: mysqld keeps deleted AIO resources open

Bug #71763	mysqld keeps deleted AIO resources open
Submitted:	18 Feb 2014 15:56	Modified:	5 Jun 2014 6:23
Reporter:	Laurent Bonnaud	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.37, 5.6.17	OS:	Linux (Debian sid)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I am using mysqld 5.5.35 on a Debian sid system and noticed that the mysqld process keeps deleted resources (probably related to its use of AIO) in its address space.

This is a problem for the "checkrestart" utility that looks for deleted files in the mysqld address space and incorrectly lists mysqld as a process that needs to be restarted.

How to repeat:
1. Start the mysql server.

2. Look for the mysqld PID:

# ps aux | grep mysqld
[...]
mysql    13650  0.0  1.3 339620 53660 pts/2    Sl   15:31   0:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

3. Look inside the mysqld address space:

# grep deleted /proc/13650/maps 
f3ed1000-f3ed6000 rw-s 00000000 00:0a 12681711                           /[aio] (deleted)
f3ed6000-f3edb000 rw-s 00000000 00:0a 12681710                           /[aio] (deleted)
f3edb000-f3ee0000 rw-s 00000000 00:0a 12681709                           /[aio] (deleted)
f6002000-f6004000 rw-s 00000000 00:0a 12681712                           /[aio] (deleted)
f6004000-f6009000 rw-s 00000000 00:0a 12681708                           /[aio] (deleted)
f6009000-f600e000 rw-s 00000000 00:0a 12681707                           /[aio] (deleted)
f6c8c000-f6c91000 rw-s 00000000 00:0a 12681706                           /[aio] (deleted)
f6c91000-f6c96000 rw-s 00000000 00:0a 12681705                           /[aio] (deleted)
f6c96000-f6c9b000 rw-s 00000000 00:0a 12681704                           /[aio] (deleted)
f6c9b000-f6ca0000 rw-s 00000000 00:0a 12681703                           /[aio] (deleted)
f6ca0000-f6ca5000 rw-s 00000000 00:0a 12681702                           /[aio] (deleted)
f6ca5000-f6ca6000 rw-s 00000000 00:0a 12681701                           /[aio] (deleted)

Suggested fix:
mysqld should close any resource that has been deleted.

Hello Laurent,

Thank you for the  bug report.
I could not see this behavior on Debian/Ubuntu/OL etc Linux OS instances despite keeping the instance up for longer/shorter duration with all kind of load. 
Could you please confirm if it is still repeatable on latest MySQL versions 5.5.38/5.6.19?

Thanks,
Umesh

I can only test up to version 5.5.37 which is the most recent version available in Debian:

Package: mysql-server-core-5.5
Version: 5.5.37-1

and the problem is still there.

Thank you for the feedback.
I noticed this on one of the Ubuntu box(tried with other OS instances without any luck from long).  Installed MySQL using APT-repo, removed DB directory for 2-3 times and then left the instance up for next 8-10hrs... 

ushastry@ushastry:~/Downloads$ sudo grep deleted /proc/5885/maps
[sudo] password for ushastry: 
7f5fb3e40000-7f5fb3e45000 rw-s 00000000 00:0b 26104                      /[aio] (deleted)
7f5fb3e45000-7f5fb3e4a000 rw-s 00000000 00:0b 26103                      /[aio] (deleted)
7f5fb3e4a000-7f5fb3e4f000 rw-s 00000000 00:0b 26102                      /[aio] (deleted)
7f5fb3e4f000-7f5fb3e54000 rw-s 00000000 00:0b 26101                      /[aio] (deleted)
7f5fb3e81000-7f5fb3e86000 rw-s 00000000 00:0b 26100                      /[aio] (deleted)
7f5fb3e86000-7f5fb3e8b000 rw-s 00000000 00:0b 26099                      /[aio] (deleted)
7f5fbf3f4000-7f5fbf3f6000 rw-s 00000000 00:0b 26105                      /[aio] (deleted)
7f5fbf3f6000-7f5fbf3fb000 rw-s 00000000 00:0b 26098                      /[aio] (deleted)
7f5fbf5dc000-7f5fbf5e1000 rw-s 00000000 00:0b 26097                      /[aio] (deleted)
7f5fbf5e1000-7f5fbf5e6000 rw-s 00000000 00:0b 26096                      /[aio] (deleted)
7f5fbf5e6000-7f5fbf5eb000 rw-s 00000000 00:0b 26095                      /[aio] (deleted)
7f5fbf5eb000-7f5fbf5ec000 rw-s 00000000 00:0b 26094                      /[aio] (deleted)

ushastry@ushastry:~/Downloads$cat /etc/*release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04 LTS"
NAME="Ubuntu"
VERSION="14.04, Trusty Tahr"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 14.04 LTS"
VERSION_ID="14.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

ushastry@ushastry:~/Downloads$ dpkg -l | grep mysql
ii  libdbd-mysql-perl                                     4.025-1                                             amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient18:amd64                                5.5.37-0ubuntu0.14.04.1                             amd64        MySQL database client library
ii  mysql-apt-config                                      0.1.5-2ubuntu14.04                                  all          Auto Config Pkg for MySQL APT Repo.
ii  mysql-client                                          5.6.17-1ubuntu14.04                                 amd64        MySQL Client meta package depending on latest version
ii  mysql-common                                          5.6.17-1ubuntu14.04                                 amd64        MySQL Common
ii  mysql-community-client                                5.6.17-1ubuntu14.04                                 amd64        MySQL Client
ii  mysql-community-server                                5.6.17-1ubuntu14.04                                 amd64        MySQL Server
ii  mysql-server                                          5.6.17-1ubuntu14.04                                 amd64        MySQL Server meta package depending on latest version
ii  mysql-workbench-community                             6.1.6-1ubu1310                                      amd64        MySQL Workbench
ushastry@ushastry:~/Downloads$