vinit dhull vinit dhull - 2 months ago 7
MySQL Question

Exact Network time from php application Server to mysql database server

I got two servers One is application server on which PHP application is running and another server on which Mysql database is running. I want to measure time taken for the request send from application server to request received on database server for executing any query.

Note: The timestamp when request is just sent to database server and request which is received on database server before executing any query in response to the request. Interested in time required to transmit data between two servers.I know it depends on the size of data. I just want to get that time for different size of data.

kindly suggest If there is any tool for this kind to measurement or any method to do it for this specific request only.(Already know about request send timestamp only need to know how to capture request received timestamp)

Can anyone suggest way to do this from php server on one machine to mysql db server on another machine.
And record that time anywhere on database or file or any tool that can be helpful.
What i am doing till now is uploading any file from php server and recording time before executing mysql query and trying to get timestamp at database level when it receive request to execute the query but problem is that time is taken by now() which i don't think is the time when datsbase is hit first time.

Code is written like this

<?php
include('db_config.php');
if (isset($_FILES['data'])) {

if (!file_exists('files/')) {
mkdir('files/', 0777, true);
}
if (!file_exists('img/')) {
mkdir('img/', 0777, true);
}
$errors = array();
$file_name = $_FILES['data']['name'];
$file_size = $_FILES['data']['size'];
$file_tmp = $_FILES['data']['tmp_name'];
$file_type = $_FILES['data']['type'];
if (move_uploaded_file($file_tmp, "img/" . $file_name)) {
$l = 1;

$t = microtime(true);
$micro = sprintf("%03d", ($t - floor($t)) * 1000);
$d = new DateTime(date('Y-m-d H:i:s.' . $micro, $t));
$dd = 'Receiving Time : ' . $d->format("Y-m-d H:i:s.u");
$fileSize = 'File Size : ' . $file_size;
$res = 'Sending Time : ' . $_REQUEST['hidd'] . ' ' . $dd . ' ' . $fileSize;


$directory = 'files/';

if (glob($directory . "*.txt") != false) {
$filecount = count(glob($directory . "*.txt"));
$filecount ++;
$file = "files/record_" . $filecount . ".txt";
$fd = fopen($file, "a");
if (!$fd) {
die("Could not open $file to write in");
}
fwrite($fd, $res);
} else {
$file = "files/record_" . $l . ".txt";
$fd = fopen($file, "a");
if (!$fd) {
die("Could not open $file to write in");
}
fwrite($fd, $res);
}

fclose($fd);
}

$db = new Database();
$db->connect();

echo $send_time = date("Y-m-d H:i:s");
$db->insert('request_record', array("$file_name", "$file_size", $send_time, "$res"), "file_name,
file_size,
request_generated_time,
file_uploaded
");
$res = $db->getResult();
print_r($res);
echo $end_time = date("Y-m-d H:i:s"); } ?>
<html>
<body>

<form action="" method="POST" enctype="multipart/form-data" id="frm1">
<input type="file" name="data" />
<input type="hidden" name="hidd" id="hd" />
<input type="button" value="Submit" onclick="pdfSubmit()"/>
</form>

</body>

<script type="text/javascript" src="./jquery-1.9.1.min.js"></script>
<script>
function pdfSubmit() {
var currentTime = new Date();
var hours = currentTime.getHours();
var minutes = currentTime.getMinutes();
var seconds = currentTime.getSeconds();
var miliseconds = currentTime.getMilliseconds();

if (minutes < 10) {
minutes = "0" + minutes;
}
if (seconds < 10) {
seconds = "0" + seconds;
}
var year = pad(currentTime.getFullYear());
var month = pad(currentTime.getMonth() + 1);
var day = pad(currentTime.getDate());

var n = year + '-' + month + '-' + day + ' ' + hours + ':' + minutes + ':' + seconds + '.' + miliseconds;
$('#hd').val(n);
$('#frm1').submit();
}
function pad(numb) {
return (numb < 10 ? '0' : '') + numb;
}
</script>
</html>

Answer

PHP is synchronous so this can be pretty easy. Use something very simple that should return right away.

Use microtime(true) to return a timestamp float (before and after).

Then it depends on what SQL API you use, but run something like SELECT 1.

Then compare the timestamps.