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 CentOS 64bit with CPanel installed and I use

service mysql stop

However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? Right now I have to do killall -9 mysql but is there a better way?

The server is also very very active.

Is this a config issue? Do I have memroy settings too high?

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
share|improve this question
    
Well, what is MySQL doing after you tell it to stop? –  EEAA Mar 6 '13 at 5:30
    
It just keeps ticking periods and never seems like it stops. In the logs it just posts a lot of 130303 17:42:38 [Warning] /usr/sbin/mysqld: Forcing close of thread –  Tiffany Walker Mar 6 '13 at 5:32
    
Is this related to your previous question? –  Ladadadada Mar 6 '13 at 6:53
    
what did you see in err.log file before doing killall? –  Mannoj Mar 6 '13 at 6:54
    
[Warning] /usr/sbin/mysqld: Forcing close of thread (lots of these) –  Tiffany Walker Mar 6 '13 at 7:15
add comment

migrated from serverfault.com Mar 6 '13 at 19:54

This question came from our site for professional system and network administrators.

5 Answers

The slickest way to shutdown mysql when it does that is simply to run

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

Here is why:

The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard service mysql stop from working.

It is not enough to say

mysqladmin -uroot -p -h127.0.0.1 shutdown

because mysqld will route a user coming in as root@127.0.0.1 to root@localhost if TCP/IP is not explicitly enabled. By default, mysqld will choose the least path of resistance and connect root@127.0.0.1 to root@localhost via the socket file. Yet, if there is no socket file, root@localhost will never connect.

Even the MySQL Documentation on mysqladmin says this:

If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

That is why it is imperative to enable TCP/IP:

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

Back on Sept 30, 2011, I scripted my own version of mysqld_multi called mysqlservice (See my post : Running multiple instances on the same host). It serves as a virtual engine for connecting to mysqld from different ports. You just have to bring your own my.cnf with customized parameters. In that script, I issue shutdowns like this:

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 0 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQL_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

But what is ${MYSQLD_STOP} ?

MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"

Please notice I use 127.0.0.1 and an explicit port. That way, I am not relying on a socket file.

I have always used mysqladmin --protocol=tcp shtudown as the proper alternative to shutdowns of mysql if service mysql stop hangs. Doing kill -9 on mysqld and mysqld_safe should the last of the last of the last resorts. (Yes, I said last three times).

Many times, mysqld has deleted mysql.sock without warning. Other people have had this issue as well over the years:

EPILOGUE

The secret is just as I stated: Connect to mysql using mysqladmin via TCP/IP (--protocol=tcp) and issue shutdown. This has to work because the shutdown privilege is in mysql.user for the exclusive purpose of authenticated shutdowns. This has saved my workday a few times when I was able to issue a remote shutdown from my Windows machine when shutting down mysqld on a Linux server.

UPDATE 2013-03-06 22:48 EST

If you are worried about what's going on during the shutdown, there is a way to manipulate the shutdown time and the way the data is flushed to disk, especially if you have lots of InnoDB data in the Buffer Pool

SUGGESTION #1

If you have a lot of dirty pages, you can lower the innodb_max_dirty_pages_pct to 0:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Set this about 15-30 minutes before shutdown. This will give mysqld the least possible amount of dirty pages to write to disk.

SUGGESTION #2

By default, innodb_fast_shutdown is 1. There are three values for this option

  • 0 : InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down.
  • 1 : InnoDB skips these operations at shutdown, a process known as a fast shutdown.
  • 2 : InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

Documentation further says this :

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

Defaults for innodb_max_dirty_pages_pct and innodb_fast_shutdown should be just fine in most cases.

share|improve this answer
    
"Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably" Citation? I've never had a socket file just "disappear inexplicably". Even if there was some 4.0 bug that caused that to happen it seems dangerous to propagate the thinking that things on your filesystem mysteriously disappearing are a fact of life you should just live with. –  atxdba Mar 6 '13 at 16:13
    
I've seen the socket file vanish at times. So moral of the story is use "mysqladmin --protocol=tcp shtudown" over "service mysql stop/restart" –  Tiffany Walker Mar 6 '13 at 17:23
    
Exactly !!! I am not the only person that feels this way. I commented on someone's answer in ServerFault along these same lines: serverfault.com/a/478424/69271 –  RolandoMySQLDBA Mar 6 '13 at 17:24
1  
The socket file disappears on Red Hat derivatives because tmpwatch deletes it, along with everything else in /tmp that has an atime older than its configured threshold. –  Michael - sqlbot Mar 6 '13 at 19:32
    
Thank you, @Michael-sqlbot I needed to hear that from someone, finally. I guess that's why someone at MySQL AB (pre-Oracle, pre-Sun) decided to add SHUTDOWN privilege to mysql.user to get around headaches like this. –  RolandoMySQLDBA Mar 6 '13 at 19:38
add comment

It sounds as if your question is less about "how to" shut down MySQL and more about why yours is shutting down so slowly.

In my answer to a similar question, I offered some suggestions for a smooth restart, which help by reducing the amount of activity that has to happen after you request that MySQL begin the shutdown process.

If you are not a frequent user of SHOW FULL PROCESSLIST; that's item #1, because you need to have a sense of what's going on in your server that's making the shutdown so slow. If there are long-running queries that are safe to interrupt, you can kill them with KILL <thread-id>.

Recapping the other suggestions:

Setting the global variable innodb_fast_shutdown = 1 (the default) will speed up InnoDB's portion of the shutdown. This is only safe, however, if you are shutting down the server for reasons unrelated to performing an upgrade. If you're shutting down for an upgrade, this must be set to 0.

Using FLUSH TABLES; gracefully closes all open tables. They will be reopened if referenced by subsequent queries, but this action should still ultimately reduce the time that elapses between the time you request the shutdown and the time it the shutdown is complete, because it does some early housekeeping and more importantly it sets the stage for the final step...

FLUSH TABLES WITH READ LOCK; closes all open tables and obtains an exclusive lock owned by your current client connection that prevents any other connection from writing to any table on the entire server. You won't get your mysql> prompt back until you own this lock, at which point you can issue the shutdown request -- but do not disconnect from this session.

On a busy server, these steps should reduce the level of activity on the server, should make things much quieter, and should help make a shutdown or restart go more smoothly.

share|improve this answer
add comment

I am sorry to hear about your experience and do hope that my experience with silly MySQL scenarios, similar to this, can help you out.

Instead of trying to figure out how to kill off the MySQL service from the server, in some cases, you will need to check through your system's health via the following to determine if there is a service abuse (assumption is based on the notion for a CentOS/RHEL environment):

 /usr/bin/iostat
 /usr/bin/uptime
 top -c

Use the following to identify the average system load and the top-consuming resources within the system.

You will also need to install mytop to get an overall view on the SQL statements that are being processed by the system.

To install mytop, simply perform the following:

 cd /root
 wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
 tar -zxvf /root/mytop-1.6.tar.gz
 cd /root/mytop-1.6
 perl Makefile.pl
 make
 make install
 chmod 644 /usr/local/bin/mytop
 vi /usr/local/bin/mytop 

(use any text editor that you prefer)

Search for "long|!" => \$config{long_nums},

Comment it out as #"long|!" => \$config{long_nums},

chmod 555 /usr/local/bin/mytop

And you are good to go with mytop. Use it to check through on the MySQL statements that are hogging your MySQL service and stop them.

The execution of the above instructions will provide you with the following ability:

  • Diagnosis of your server's status/health
  • Diagnosis of your the cause of bottleneck

Once you have eliminated the cause of the bottleneck, you should have an easier day when you attempt to restart the MySQL service. I do hope that you will find the above information useful.

share|improve this answer
4  
mytop is ancient and unmaintained. You should probably use innotop on a modern system. –  Michael Hampton Mar 6 '13 at 6:49
    
that's a great suggestion! +1 –  Michael Feng Mar 6 '13 at 7:41
add comment

The standard implementation of MySQL's init-script signals MySQL with SIGTERM and waits for a certain amount of time for MySQL to shut down.

MySQL, after receiving a SIGTERM, will first stop receiving new connections, then complete executing whatever queries are still pending (this can take a while, depending on your workload and number of concurrent clients), then start flushing data to disk (this can take a long time, again depending on your workload, configurations, available memory, and storage engine choices). After all the data-flushing is done, MySQL will then deallocate whatever memory it allocated during the initialization stage (this can also take a while, depending on the amount of memory allocated by MySQL), close all file handles still open, and then call "exit(0)".

If after your request for shutdown MySQL is taking a long time to shut down, it's quite possible that one or more of this stages is taking a long time to complete. If you have the time, I strongly recommend waiting for the process to complete (this will avoid data-loss or long recovery processes when you start your database instance again).

Unfortunately there is not enough information in your question to allow me to suggest a proper solution for your problem. I hope this helps understanding the issue.

share|improve this answer
add comment

It's probable MySQL is not purely locked up, but is doing cleanup activities (rollbacks, etc.) when shutting down. If you don't let it do all that when shutting down, often you'll have to wait when starting up.

Here's something to look at: Shutdown mysql in one terminal window while watching the error log (tail -f [yourerror.log]) in another terminal window. The error log will show you what MySQL is doing.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.