Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to the problem I have found is to run optimize on every table. Afterwards everything performs normal again. However, rebuilding the whole database after every OS reboot is obviously extremely painful and not a viable long term solution.

I reboot the OS by

  1. stopping all programs querying the database
  2. waiting for queries to finish executing
  3. running stop mysql
  4. making sure that the server stopped with ps -C mysql and ps -C mysqld
  5. running reboot

If I leave out the OS reboot and just start up MySQL again, everything is fine.

Additional information:

  • The error log doesn't contain anything pointing to the issue, nor do I get any errors. Everything is just a magnitude or two slower.
  • All tables are affected. I am using InnoDB, but the very same problem existed when I was using MyISAM.
  • Usually MySQL uses 80% of the 8GB RAM, but after the OS reboot only about 1GB is used. CPU usage goes down from 30-80% of one core to about 1%.
  • The optimize query makes normal use of the full 80% RAM and CPU goes up to normal values, too.

Specs:

MySQL 5.5.35-0ubuntu0.12.04.2

Ubuntu 12.04.4 LTS

8GB RAM, 4 CPU cores

my.cnf:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

local-infile=0

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

innodb_file_per_table

innodb_autoinc_lock_mode = 0

innodb_thread_concurrency=8
innodb_buffer_pool_size=6G
innodb_log_file_size=1600M
innodb_additional_mem_pool_size=1M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0

innodb_read_io_threads=3000
innodb_write_io_threads=7000
innodb_io_capacity=10000

key_buffer_size     = 1024M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]


[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

key_buffer_size is 1GB, that is about as much RAM being used after the reboot. Could that be connected?

Edit: When logging the query execution times per table, I have noticed that after repairing three of the four tables, sometimes the query times of the three repaired tables still go very high. So I am not completely sure that all tables break. Maybe it's just one table that is pulling down the performance of the whole server? However that doesn't really match the low % of used resources.

Edit: Solution by Phil is in the comments.

share|improve this question
1  
It's called caching. When you reboot the OS you remove all of the disk reads that have previously happened and put into cache (RAM). Once you've rebooted, the operating system will have to read the mysql data from disk, which is several orders of magnitude slower than reading from cache (RAM). Optimise "fixes" this as it causes mysql to read all of the table data, enabling the OS to cache the data. Solution: don't reboot. –  Phil May 11 at 13:38
    
Wouldn't that happen upon MySQL restart, too? Also, if that was the case, the queries should quickly go up in speed again, right? (They don't) Does this also impact INSERT/UPDATE speed? My queries are mainly bulk INSERTs. –  Max May 11 at 13:42
    
Restarting mysql does not affect the operating system disk cache. Linux will use all free RAM as disk cache - you can see this using the free command. –  Phil May 11 at 13:43
    
Ok, assuming what you say is the reason (sounds plausible enough to me!) - how do I force MySQL to load the corresponding data into the RAM? –  Max May 11 at 13:46
    
Do a select * on every table, basically. Or you can do it on the operating system side by reading all of the table data from disk using dd. For example, dd if=/var/mysql/data/mytable.idb of=/dev/null –  Phil May 11 at 13:50

1 Answer 1

up vote 3 down vote accepted

When you reboot the OS you remove all of the disk reads that have previously put into operating system disk cache (RAM). Once you've rebooted, the operating system will have to read the MySQL data from disk, which is several orders of magnitude slower than reading from cache (RAM).

Optimise "fixes" this as it causes MySQL to read all of the table data from disk, enabling the OS to cache the data.

Linux uses most free RAM as disk cache. You can see this using the free command:

[oracle@ora12c1 ~]$ free
             total       used       free     shared    buffers     cached
Mem:       4050844     840072    3210772          0      97268     349716
-/+ buffers/cache:     393088    3657756
Swap:      4063228          0    4063228
[oracle@ora12c1 ~]$ 

Restarting MySQL has no affect on this cache, as it is the operating system that has cached the data, not MySQL.

To "fix" your problem you'll have to force the OS to read the data from disk so that it will cache it. You can use the Unix dd tool to do this. For example:

dd if=/var/mysql/data/mytable.idb of=/dev/null
share|improve this answer
3  
Tip: you can verify how much of a given file is in the disk cache with the vmtouch command. That command can also purge the file out of the filesystem cache, or force loading the file into the filesystem cache. See hoytech.com/vmtouch –  Bill Karwin May 11 at 16:23

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.