View currently running MySQL queries in realtime

Today I was playing around with Apache Solr. I was really impressed by its text searching capability, especially the MoreLikeThis search handler. I wanted to configure the DataImportHandler to import data directly from my MySQL database. It was really easy to configure, and I was able to perform a full import quickly. But when I tried to do a delta import, I found that it was not working as expected. Even though I was calling the delta import, it was causing a full import.

You might be wondering  why I am saying all these here. Well, I suspected that the problem was actually because of my SQL query for delta load.  But to be sure, I wanted to see the query being executed by Solr DataImportHandler. As always I turned to Google for assistance, and I finally reached the MySQL documentation on the General Query Log. Voila! This was exactly what I wanted. All I had to do was use the –log=[filename] parameter and all my queries will be logged to the specified log file. Nice, isn’t it?

Now I have to stop my running MySQL server and restart it with the –log switch, in addition to the other regular options. But there was a problem, I was not sure of the other required parameters. You can use the ps utility, when the MySQL server is running, to find out the normal parameters.

ps -ax | grep mysql

For me the output was

/usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –user=mysql –pid-file=/usr/local/mysql/data/localhost.pid –port=3306 –socket=/tmp/mysql.sock

Now shutdown the MySQL server.

// On Mac
/Library/StartupItems/MySQLCOM/MySQLCOM stop

// For other Linux/Unix variants try
/etc/init.d/mysqld restart
service mysql restart

Start mysqld with –log option

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/localhost.pid --port=3306 --socket=/tmp/mysql.sock --log=/tmp/query.log

The general query log contains lots of irrelevant information. To view the log after filtering out the unwanted details use tail and grep as given below

tail -f /tmp/query.log | grep -v Connect | grep -v Quit

The amount of information added to the file is quite large. If you are using this on a production server, I recommend turning off the logging once you are done.

Enabling gzip compression if mod_deflate is not enabled

Today I saw a tweet by my friend Niyas that Host Gator does not support mod_gzip. This made me wonder whether it is possible to achieve this using PHP. I contacted HostGator support and confirmed that GZip support for PHP is enabled on both shared and dedicated hosting accounts, whereas mod_deflate Apache module is available only for dedicated hosting customers. Fortunately, we can use PHP to compress files on the fly.

Enabling gzip compression in an existing PHP file is very simple. Add the following line at the beginning of the file (it should appear before any output statement).

<?php
ob_start ("ob_gzhandler");
?>

Very simple, right? But what if we have hundreds of files to edit? Don’t worry, there is solution for that too. We can use the PHP configuration variable auto_prepend_file to automatically include the above code at the beginning of all PHP files. Copy the above code to a file named prepend.php and place it at the root of your website (~/public_html in CPanel, ~/httpdocs in Plesk).

Now we are going to automatically include the above file at the beginning of all PHP files using auto_prepend_file. Depending on how PHP is configured on your server, the steps for modifying the PHP configuration variable is also different.

If PHP is configured in CGI/FastCGI mode (HostGator uses this mode), we will be using php.ini file to make the above change. Create a file named php.ini at the root of your website and copy the following code into it. If the file already exists, append it to the end of the file.

auto_prepend_file=<full-path-to-document-root>/prepend.php

If PHP is loaded as apache module (mod_php), we will have to use .htaccess to achieve the same effect. Create a file named .htaccess at the web root and copy the following code into it. If the file exists, append at the end of the file.

php_value auto_prepend_file <full-path-to-document-root>/prepend.php

In both methods, replace <full-path-to-document-root> with the full path to your website root directory, for example /home/joyce/public_html.

Now the prepend.php file is automatically included by the PHP interpreter, whenever a PHP file is requested. But this method (as of now) does not work for non PHP files like HTML, CSS, JavaScript etc, which should also be compressed. This is because these files are handled directly by Apache and is not passed to the PHP interpreter. To ensure that these files are also compressed, we have to instruct Apache to pass these files to the PHP interpreter before sending them to the browser. For this, lets once again go back to .htaccess. Append the following code at the end of your .htaccess file (create one, if you don’t have  it already).

<FilesMatch "\.(htm|html|css|js)">
	ForceType application/x-httpd-php
</FilesMatch>

The above code instructs Apache that files ending with .js, .htm, .html, .css and .js extensions are also PHP files and should be passed through PHP interpreter. The only problem remaining is that Content-type header for all files have been changed to text/html. To fix this we need to check the requested file-name and set the correct Content-type header depending on the file extension. In order to do this, open prepend.php and replace the current code with

<?php
// Start output buffering
ob_start ("ob_gzhandler");

// Set the correct content type header depending on filename
$arContentType = array('htm' => 'text/html', 'html' => 'text/html', 'css' => 'text/css', 'js' => 'text/javascript');
if(strpos($_SERVER['REQUEST_URI'], '.') !== FALSE){
	$ext = explode('.', $_SERVER['REQUEST_URI']);
	$ext = array_pop($ext);
	if(($pos = strpos($ext, '?')) !== FALSE){
		$ext = substr($ext, 0, $pos);
	}
	if(isset($arContentType[$ext])){
		header ("Content-type: {$arContentType[$ext]}; charset: UTF-8");
	}
}

// You can also set expiration headers in this file
?>

That’s all Folks. You have successfully enabled Gzip compression on your server without using any Apache modules.

This is a working code. But I have tested it only with mod_php configuration. You have to be very careful when enabling PHP code in JS/CSS files. There may be many flaws in this code. If you find any, please let me know. We can further improve this code by adding expiration and cache headers to enable client side caching.