Bug #80196 MySQL startup takes 30 minutes when OS cache used is high.

Submitted: 29 Jan 2016 6:08 Modified: 11 Feb 2016 16:05
Reporter: Saurabh Bhartia Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Options Severity:S5 (Performance)
Version:mysql Ver 14.14 Distrib 5.6.27, for Lin OS:CentOS (2.6.32)
Assigned to: CPU Architecture:Any

[29 Jan 2016 6:08] Saurabh Bhartia
Description:
top - 21:57:58 up 105 days, 24 min,  1 user,  load average: 0.15, 0.03, 0.01
Tasks: 690 total,   1 running, 689 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.3%us,  0.3%sy,  0.0%ni, 96.2%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  264423436k total, 263814344k used,   609092k free,    19920k buffers
Swap:        0k total,        0k used,        0k free, 257729488k cached

How to repeat:
untar a big file.
make sure most of your memory is used by OS disk cache.
( 257729488k cached in my case ) 

Now try to start MySQL with say 80GB of innodb_buffer_pool

It takes many minutes to start.

Here is a tail from /var/log/mysqld.log showing it took us 30 minutes to start MySQL.

used 07:58:32 to  08:31:59 ( 33 minutes ) to start MySQL. 

( Infact I am seeing the performance going quite bad, once OS cache used is high. ) 
 

=====
160128 07:58:32 mysqld_safe Starting mysqld daemon with databases from /data/mysql
2016-01-28 08:13:47 0 [Warning] Using unique option prefix character-set-client instead of character-set-client-handshake is deprecated and will be removed in a future release. Please use the full name instead.
2016-01-28 08:13:47 0 [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'utf8'
2016-01-28 08:13:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-28 08:13:47 0 [Note] /usr/sbin/mysqld (mysqld 5.6.28-log) starting as process 24268 ...
2016-01-28 08:13:58 24268 [Warning] option 'innodb-rollback-on-timeout': boolean value 'yes' wasn't recognized. Set to OFF.
2016-01-28 08:13:58 24268 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-28 08:13:58 24268 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-28 08:13:58 24268 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-28 08:13:58 24268 [Note] InnoDB: Memory barrier is not used
2016-01-28 08:13:58 24268 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-28 08:13:58 24268 [Note] InnoDB: Using Linux native AIO
2016-01-28 08:13:58 24268 [Note] InnoDB: Using CPU crc32 instructions
2016-01-28 08:13:58 24268 [Note] InnoDB: Initializing buffer pool, size = 210.0G
2016-01-28 08:21:18 24268 [Note] InnoDB: Completed initialization of buffer pool
2016-01-28 08:27:04 24268 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-28 08:31:58 24268 [Warning] InnoDB: Resizing redo log from 2*16384 to 2*32768 pages, LSN=3562783599169
2016-01-28 08:31:58 24268 [Warning] InnoDB: Starting to delete and rewrite log files.
2016-01-28 08:31:58 24268 [Note] InnoDB: Setting log file ./ib_logfile101 size to 512 MB
InnoDB: Progress in MB: 100 200 300 400 500
2016-01-28 08:31:58 24268 [Note] InnoDB: Setting log file ./ib_logfile1 size to 512 MB
InnoDB: Progress in MB: 100 200 300 400 500
2016-01-28 08:31:59 24268 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-01-28 08:31:59 24268 [Warning] InnoDB: New log files created, LSN=3562783599169
2016-01-28 08:31:59 24268 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-28 08:31:59 24268 [Note] InnoDB: Waiting for purge to start
2016-01-28 08:31:59 24268 [Note] InnoDB: 5.6.28 started; log sequence number 3562783599169
2016-01-28 08:31:59 24268 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a7700430-c5dc-11e5-97af-0cc47a4bf79c.
2016-01-28 08:31:59 24268 [Note] Server hostname (bind-address): '*'; port: 3306
2016-01-28 08:31:59 24268 [Note] IPv6 is available.
2016-01-28 08:31:59 24268 [Note]   - '::' resolves to '::';
2016-01-28 08:31:59 24268 [Note] Server socket created on IP: '::'.
2016-01-28 08:31:59 24268 [Note] Event Scheduler: Loaded 2 events
2016-01-28 08:31:59 24268 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.28-log'  socket: '/var/lib/mysql/mysql1.sock'  port: 3306  MySQL Community Server (GPL)

==

Suggested fix:
When I start MySQL after clearing cache, MySQL starts in seconds. 

echo 1 > /proc/sys/vm/drop_caches
[11 Feb 2016 16:05] MySQL Verification Team
Hi!

This is a known issue with Linux and not MySQL. Linux is trying to use all the available memory for disk caching. So, when you come with a demand to allocate a large chunk of memory, then Linux has to free OS cache in order to make the memory available. Freeing OS cache is a complex operation that involves freeing memory that caches file that are not used at that moment.

This is a Linux issue and not MySQL issue.

Not a bug.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.