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
