Archive for June, 2011

For version 5.1.6 and above you can enable slow query logging without restarting the MySQL service. For versions before 5.1.6 a service restart will be needed. Note that slow query logging should not remain enabled for a long period of time, especially on production environments. This could result in poor performance and the log files can grow rather quickly.

Version 5.1.6 and above:


1. Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:

     Log details for queries expected to retrieve all rows instead of using an index:
     set global log_queries_not_using_indexes = 'ON'

     Set the path to the slow query log:
     set global slow_query_log_file ='/var/log/mysql/slow-query.log';

     Set the amount of time a query needs to run before being logged:
     set global long_query_time = '20';
     (default is 10 seconds)

3. Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';

Versions below 5.1.6:


1. Edit the /etc/my.cnf file with your favorite text editor
vi /etc/my.cnf

2. Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:
log-slow-queries=/var/log/mysql/slow-query.log

3. Enable additional options as needed. Here are the same commonly used examples from above:

     Set the amount of time a query needs to run before being logged:
     long_query_time=20
     (default is 10 seconds)

     Log details for queries expected to retrieve all rows instead of using an index:
     log-queries-not-using-indexes

4. Restart the MySQL service:
service mysqld restart

5. Confirm the change is active by entering the MySQL shell and running the following:
show variables like '%slow%';

This article will discuss installing and configuring Munin and Munin-node on the same server for server performance monitoring. It makes use of the EPEL repository which might not be needed depending on your system.

1. First see if yum lists any Munin related packages:
yum search munin

If this returns similar results to the image below, move on to step #3.

2. If no Munin packages are found we need to install the EPEL repository:
Install the EPEL Repository: RHEL and CentOS

3. Install the munin package:
yum install munin

4. Install the munin-node package:
yum install munin-node

5. Add both services to chkconfig so they start on system boot:

chkconfig munin on
chkconfig munin-node on

6. Confirm Munin is loading properly by navigating to your IP/munin. For instance:
http://192.168.1.1/munin/

Optional step: Install plugins

For this example we will be installing the Apache plugin which is commonly installed and provides further data on the Apache process.

1. Create a symbolik link for the Apache plugins which are located in /etc/share/munin/plugins:

ln -s /usr/share/munin/plugins/apache_* /etc/munin/plugins/

2. Enable server status reports for Apache by adding the following lines to your Apache configuration file (httpd.conf):

ExtendedStatus On

SetHandler server-status
Order Deny,Allow
Deny from all
Allow from 127.0.0.1

3. Restart the Apache and munin-node services:
service httpd restart && service munin-node restart

4. It may take a few minutes for Apache statistics to start being collected. Check back shortly to confirm Munin is now receiving data for Apache.

Extra Packages for Enterprise Linux (EPEL) is a community based project to provide commonly used Fedora packages to RHEL and CentOS. First find the appropriate rpm for your system. The list can be found on the EPEL section of Fedora’s site.

In the example below we will be installing the 32bit version of release 6-5:

sudo rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm

After that any packages included in the EPEL repository will be available to install through yum.

Note: Since this is a third party repository, it can cause conflicts especially with other third party repositories such as RPMForge. Usually it is best to leave other third party repositories disabled and only use them with the –enablerepo switch in yum. Alternatively the yum-priorities package can be installed to set priorities for installed repositories.

While it’s possible to scan through logs to determine when the SQL service was last restarted, that is not always the quickest option. There is a simple query which will display the date and time of the last restart of the SQL service.

select min(login_time) from master..sysprocesses