Monthly Archives: February 2013


Enable slow query log for cpanel whm mysql 5.5 2

*** Note the comments below, this is for MySQL 5.5. MySQL 5.6 uses slow_query_log ***

To enable the slow query log for your whm / cpanel server, do the following:

open /etc/my.cnf with your favorite editor and add the following lines under the [mysqld] section:

log-slow-queries=/var/lib/mysql/slow_queries.log
long_query_time = 3
log_queries_not_using_indexes

The log_queries_not_using_indexes option will also write any queries to the file that are not using indexes. They may take less than your long_query_time threshold, but is good for finding easy targets for indexes and performance gains.

Then create the log file and set the proper permissions:

touch /var/lib/mysql/slow_queries.log
chmod 660 /var/lib/mysql/slow_queries.log
chown mysql:mysql /var/lib/mysql/slow_queries.log

Then restart mysql:

service mysql restart

If you want to view the contents of the file in real time, go to the mysql directory – /var/lib/mysql/ and then run:
tail -f slow_queries.log

Monitor the queries being written to the file and make adjustments as necessary.


MySQL – indexes on unique data can slow down your query

Today I was running a query that joined two tables with one table having 1.9 million records and the other 340K. I had put an index on two columns that contained unique data and the query took 1.37 seconds to complete. By removing these indexes, the completion time was reduced to 0.25 seconds. The indexes did nothing for performance since the all the data was already unique. What surprised me was how much slower it made the query though.

SELECT * FROM geo_city_ip_block b INNER JOIN geo_city_ip_location l USING(location_id) 
WHERE '1142362046' BETWEEN b.start_ip AND b.end_ip
/* without indexes: 0.25 */
/* with indexes: 1.37 */

It’s worth noting that I was able to further improve the performance of this query by not using MySQL’s “between” clause.

SELECT * FROM geo_city_ip_block b INNER JOIN geo_city_ip_location l USING(location_id) 
WHERE b.start_ip < '1142362046' AND b.end_ip > '1142362046';
/* without indexes: 0.16 */

Zend_Cache_Core is missing the setCacheDir method

This seems to be troubling a lot of people, so after debugging Zend’s code, I’ll post the solution on how to change the cache directory of zend_cache.

I’m using the below code in my zend framework bootstrap to setup Zend_Cache for caching to files.

$frontend= array('lifetime' => 7200,
				 'automatic_serialization' => true);
$backend= array('cache_dir' => DIR_CACHE);
$cache = Zend_Cache::factory('core',
								   'File',
								   $frontend,
								   $backend);
//assign to registry                        
$registry     = Zend_Registry::getInstance();
$registry->cache = $cache;

I need to be able to override the cache directory at a later time and when I’m ready to use the cache in the application, I reference this Zend_Cache like below, which returns an object of Zend_Cache_Core, which you cannot call setCacheDir() on. We must first get the backend component, then call the method as below.

$cache = Zend_Registry::get('cache');
 
//set the new cache directory
$cache->getBackend()->setCacheDir($cacheDirectory);