Shutdown a MySQL Instance

How  you can  shutdown you mysql instance? We can use SIGTERM, mysqladmin and SIGKILL . These three methods will end up with a dead mysqld but the one you choose depends on the situation and can even result in lost data. On Linux the difference between SIGTERM and SIGKILL is significant and often times misunderstood.

Before processes start to die it is important to understand the relationship between mysqld_safe and mysqld. mysqld_safe is the watchdog script for mysqld. It is responsible for starting mysqld and keeping an eye on it. It does this by waiting for mysqld to exit then checking the return code. On a safe shutdown such as one done by mysqldadmin or a SIGTERM mysqld will return zero. When mysqld_safe sees a zero return code it will also exit. If the return code is anything else then mysqld_safe assumes mysqld crashed and starts a new instance of mysqld. This difference can help explain why mysqld sometimes just won’t go away.

 It is important to understand the difference between mysqladmin and sigterm.  mysqladmin will create a shutdown thread. Using a TERM signal will also create a shutdown thread. The major difference between the two is that mysqladmin makes a connection to mysql and sends a shutdown packet. This means it can be used from a remote host. It also means that mysqladmin must pass mysql permissions before allowing the shutdown. SIGTERM is delivered through the signal mechanism in linux and must only pass the linux system user rules for delivering a signal. For example if mysqld is running as root then the anil user can’t deliver a signal to it without using sudo command or sudo acess.

SIGTERM is handy for safely shutting down mysqld when the root password is lost or there are too many connections and the reserved super user connection is also taken up. There are several different methods to find the mysqld process and send it a term signal. Here are a few examples where nnnn is the pid of the mysqld process. These also work with SIGKILL. Note that killall defaults to a TERM signal

• kill -TERM nnnn
• killalll mysqld
• kill -TERM `pidof mysqld`
• kill -TERM `cat /var/run/mysqld.pid`

Getting into SIGKILL or kill -KILL is where things get interesting. In Linux SIGKILL isn’t really a signal in that it never actually gets delivered to the process. Since it never gets delivered to the process it can’t be caught or blocked.

SIGKILL means is to remove a process from existence. The process never gets another chance to run to attempt to block the signal. Linux simply cleans it up. What this means for MySQL is that it doesn’t get a chance to perform any shutdown tasks like flushing indexes for myisam tables. To MySQL it is effectively the same as pulling the plug on the server except that the filesystem still has a chance to flush modified data to disk.

SIGKILL should really be a last resort or only used when you know your mysqld is safe to shutdown. There is plenty of discussion on this with respect to making consistent backups. The rules are basically the same. When you take a filesystem snapshot or lvm snapshot the way the snapshot looks is effectively the same as running SIGKILL on mysqld to remove it from existence.

How to Boost MySQL Scalability

We face a lot of scalability challenges with clients again and again. The list of point I cannot limit to numbers. However I am giving some basic ideas to scale MySQL Server.

1. Tune queries which are causing performance degradation

Till now the biggest cause of performance buck is unoptimized query. Queries can be functionally correct and meet business requirements, but if they are used without stress tested for high traffic and high load can cause pain.   This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn’t necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn’t receive real-world traffic. So some amount of reactive tuning is common and appropriate.

Enable the slow query log, watch it, analyze it and try to optimize slow queries. Also make sure the log_queries_not_using_indexes flag is set.  Once you’ve found a heavy resource intensive query, optimize it!  Use the EXPLAIN facility, use a profiler, look at index usage and create missing indexes, and understand how it is joining and/or sorting.

2. Employ Master-Master Replication

Master-master active-passive replication, otherwise known as circular replication, can be a boon for high availability, but also for scalability.  That’s because you immediately have a read-only slave for your application to hit as well.  Many web applications exhibit an 80/20 split, where 80% of activity is read or SELECT and the remainder is INSERT and UPDATE.  Configure your application to send read traffic to the slave or rearchitect so this is possible.  This type of horizontal scalability can then be extended further, adding additional read-only slaves to the infrastructure as necessary.

But use it only Active-Passive mode. If you want to use Active-Active replication, make sure you are not using same database on both server at same time. It may cause data inconsistency as MySQL does not having ability for Remote Locking.

3. Use Your Memory

It sounds very basic and straightforward, yet there are often details overlooked.  At minimum be sure to set these:

• innodb_buffer_pool_size
• key_buffer_size (MyISAM index caching)
• query_cache_size
• thread_cache & table_cache
• innodb_log_file_size & innodb_log_buffer_size
• sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size
• tmp_table_size & max_heap_table_size

4. RAID Your Disk I/O

What is underneath your database?  You don’t know?  Well please find out!  Are you using RAID 5?  This is a big performance hit.  RAID5 is slow for inserts and updates.  It is also almost non-functional during a rebuild if you lose a disk.  Very very slow performance.  What should I use instead?  RAID 10 mirroring and striping, with as many disks as you can fit in your server or raid cabinet.  A database does a lot of disk I/O even if you have enough memory to hold the entire database.  Why?  Sorting requires rearranging rows, as does group by, joins, and so forth.  Plus the transaction log is disk I/O as well!

Are you running on EC2?  In that case EBS is already fault tolerant and redundant.  So give your performance a boost by striping-only across a number of EBS volumes using the Linux md software raid.

5. Tune Key Parameters

These additional parameters can also help a lot with performance.

innodb_flush_log_at_trx_commit=2

This speeds up inserts & updates dramatically by being a little bit lazy about flushing the innodb log buffer.  You can do more research yourself but for most environments this setting is recommended.

innodb_file_per_table

Innodb was developed like Oracle with the tablespace model for storage.  Apparently the kernel developers didn’t do a very good job.  That’s because the default setting to use a single tablespace turns out to be a performance bottleneck.  Contention for file descriptors and so forth.  This setting makes innodb create tablespace and underlying datafile for each table, just like MyISAM does.

MySQL basic utility

Whenever you install MySQL inside the bin folder you can see some file other then mysqld and mysqld. These are utilities which used to different purpose. Here is the list some of the utilities. 

1.    mysqladmin: This utility can do all admin related. The exception is MySQL Service Start.  It can stop MySQL service but will not be able to start it. Below is the list for options available for it.

  create databasename   Create a new database

  debug                 Instruct server to write debug information to log

  drop databasename     Delete a database and all its tables

  extended-status       Gives an extended status message from the server

  flush-hosts           Flush all cached hosts

  flush-logs            Flush all logs

  flush-status          Clear status variables

  flush-tables          Flush all tables

  flush-threads         Flush the thread cache

  flush-privileges      Reload grant tables (same as reload)

  kill id,id,...        Kill mysql threads

  password [new-password] Change old password to new-password in current format

  old-password [new-password] Change old password to new-password in old format

  ping                  Check if mysqld is alive

  processlist           Show list of active threads in server

  reload                Reload grant tables

  refresh               Flush all tables and close and open logfiles

  shutdown              Take server down

  status                Gives a short status message from the server

  start-slave           Start slave

  stop-slave            Stop slave

  variables             Prints variables available

  version               Get version info from server

         

Below is example for same.

[root@cluster1 ~]# mysqladmin ping

mysqld is alive

[root@cluster1 ~]# mysqladmin processlist

+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+

| Id | User | Host      | db | Command | Time | State | Info             | Rows_sent | Rows_examined | Rows_read |

+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+

| 11 | root | localhost |    | Query   | 0    |       | show processlist | 0         | 0             | 1         |

+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+

2.    mysqlcheck: This command will check the status of all MySQL Tables and show you the output. In case of any error you can check/repair that table further. Below is the example for same.

[root@cluster1 ~]# mysqlcheck -B sakila -a

sakila.actor                                       OK

sakila.address                                     OK

sakila.category                                    OK

sakila.city                                        OK

sakila.country                                     OK

sakila.customer                                    OK

sakila.film                                        OK

sakila.film_actor                                  OK

sakila.film_category                               OK

sakila.film_text                                   Table is already up to date

sakila.inventory                                   OK

sakila.language                                    OK

sakila.payment                                     OK

sakila.rental                                      OK

sakila.staff                                       OK

sakila.store                                       OK

3.    mysqlshow: This command will show the list of all created database on MySQL instance. Below is the example.

[root@cluster1 ~]# mysqlshow

+--------------------+

|     Databases      |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sakil              |

| sakila             |

| test               |

+--------------------+