I have a site which uses PHP's PDO library to access a mysql database. The mysql database is highly optimised and has all the suitable indexes to make the queries fast and so on. I am encountering some strange behaviour though in relation to the first query to run for a particular web service.
This particular web-service runs a query against the database and returns a json response which is then fed to a jquery auto-complete.
The query upon first run in a client takes approx 2s to run, after which it drops to hundredths of a second, presumably due to innodb caching.
If I type in an entry in the auto-complete box during a new session then the first query response can take upwards of 5 seconds after which it becomes blisteringly fast to return responses. If I then leave the site for a good period i.e. perhaps an hour(not an exact measure but for the sake of argument, a relatively long period of time) and come back to it the same slow first query behaviour is observed again.
I am using a persistent connection out of necessity and owing to a finite number of connections on the server in connection.
I was wondering if any of you had any ideas which might allow me to mitigate the initial delay a bit more.
$DBH = null;
$host = "127.0.0.1";
$db_name = "my_db";
$user_name = "me";
$pass_word = "something";
try {
# MySQL with PDO_MYSQL
$DBH = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $user_name, $pass_word, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(PDOException $e) {
error_log( $e->getMessage(), 0 );
}
Updated with answer
Right guys after much testing and after thoroughly checking that it was not a dns issue, I went checking the innodb buffer pool route. Anyway I wrote a stored procedure which uses a query to generate a a query for each table in the database which would thus cause them to be cached in the innodb_buffer_pool. The query to generate the sql queries for each table is from the following SO question. I made only one edit to that query and put in the database() function so that it would work from whichever database it was called from.
I also set it up so that it can be called via PHP without waiting for the script to complete so your normal application continues on.
I hope this helps someone out. As an aside to be even more efficient you cold wrap the exec in a small function to only run it at certain times and so on.
MySQL stored procedure SQL
DELIMITER $$
USE `your_db_name`$$
DROP PROCEDURE IF EXISTS `innodb_buffer_pool_warm_up`$$
CREATE DEFINER=`user_name`@`localhost` PROCEDURE `innodb_buffer_pool_warm_up`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sql_query VARCHAR(1000) DEFAULT NULL;
DECLARE sql_cursor CURSOR FOR
SELECT
CONCAT('SELECT `',MIN(c.COLUMN_NAME),'` FROM `',c.TABLE_NAME,'` WHERE `',MIN(c.COLUMN_NAME),'` IS NOT NULL')
FROM
information_schema.COLUMNS AS c
LEFT JOIN (
SELECT DISTINCT
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
) AS k
USING
(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
WHERE
c.TABLE_SCHEMA = DATABASE()
AND k.COLUMN_NAME IS NULL
GROUP BY
c.TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN sql_cursor;
read_loop: LOOP
FETCH sql_cursor INTO sql_query;
IF done THEN
LEAVE read_loop;
END IF;
SET @stmt_sql = sql_query;
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
END LOOP;
CLOSE sql_cursor;
END$$
DELIMITER ;
PHP to call the stored procedure
innodb_warm_up_proc_call.php
<?php
$DBH = null;
$host = "localhost";
$db_name = "your_db_name";
$user_name = "user_name";
$pass_word = "password";
try {
# MySQL with PDO_MYSQL
$DBH = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $user_name, $pass_word, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sql = "CALL innodb_buffer_pool_warm_up()";
$STH = $DBH->prepare( $sql );
$STH->execute();
}catch( PDOException $e ) {
error_log( $e->getMessage() . ' in ' .$e->getFile(). ' on line ' .$e->getLine(), 0 );
}
?>
PHP to run the above script silently and without waiting for it to complete
innodb_warm_up.php
<?php
$file_to_execute = dirname(__FILE__) . "/innodb_warm_up_proc_call.php";
//Run the stored procedure but don't wait around for a chat
exec("php -f {$file_to_execute} >/dev/null 2>&1 &");
?>
