Page 1 of 1
Caching every 1 second
Posted: Wed Jan 20, 2010 7:20 am
by sn4k3
hi,
Situation:
I want reduce MySQL querys
Webpage makes a AJAX request every 1s to retrieve updated auctions from MySQL
So if theres 100 users viewing that page it will make 100 mysql querys per second
Im thinking in a cache solution so instead make 100 MySQL querys, do only one
——
If i use cache it will respond at time every 1 second? since cache reads and writes to disk
Maybe the best solution its MemCache?
website type:
NOTE: requests must be every 1s
Thanks
Re: Caching every 1 second
Posted: Wed Jan 20, 2010 8:22 am
by Weirdan
Memcache would work just fine in this situation.
Re: Caching every 1 second
Posted: Wed Jan 20, 2010 8:48 am
by sn4k3
Weirdan wrote:Memcache would work just fine in this situation.
and it will respond at time,
just because i have 0 experience in memcache
any easy sample how to use iT?
Re: Caching every 1 second
Posted: Wed Jan 20, 2010 3:34 pm
by Weirdan
Basically you need memcached daemon (on debian/ubuntu installing is as simple as 'sudo aptitude install memcached') and memcached extension (sudo pecl install memcached). Please note that memcached allows anyone to use it. So you either need to run it on a loopback adapter interface (127.0.0.1) / unix socket or you would need to firewall it to prevent unauthorized access. After you have installed everything using is also quite simple:
Code: Select all
<?php
$m = new Memcached();
$m->addServer('localhost', 11211);
$data = $m->get('data');
switch ($m->getResultCode()) {
case Memcached::RES_NOTFOUND:
$data = fetchDataFromDB();
$m->add('data', $data, 1); // cache data under name 'data' for 1 second
break;
case Memcached::RES_SUCCESS:
// do nothing, fetched from cache successfully
break;
default:
logError('memcache error:' . $m->getResultCode());
$data = fetchDataFromDB();
break;
}
displayData($data);
function displayData($data) {
// display your data here
}
function fetchDataFromDB() {
// fetch your data here
return $data;
}
function logError($error) {
// log error here
}
Re: Caching every 1 second
Posted: Wed Jan 20, 2010 7:04 pm
by sn4k3
thanks, also memcache came with
64MB of maximum memory and
1024 Maximum Connections, what will be the best value for that two fields?
Code: Select all
function GetAllAuctions($active = 1, $closed = 0, $limit = 0)
{
if($limit > 0)
$this->db->limit($limit);
$query = $this->db->get_where('auctions', array('active' => $active, 'closed' => $closed));
$data = NULL;
if ($query->num_rows() > 0)
$data = $query->result_array();
$query->free_result();
return $this->PrepareAuctions($data);
}
Since it is only for active and unclosed auctions, it may not go more than 50 - 100 auctions
table size:
Code: Select all
CREATE TABLE IF NOT EXISTS `auctions` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`active` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`name` varchar(50) NOT NULL,
`description` text NOT NULL,
`img` varchar(255) NOT NULL,
`allowautobid` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
`nosuspension` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`bidtime` tinyint(3) UNSIGNED NOT NULL DEFAULT '15',
`bidprice` decimal(2,2) UNSIGNED NOT NULL DEFAULT '0.01',
`realprice` decimal(5,2) UNSIGNED NOT NULL,
`currenttime` time NOT NULL,
`maxtime` tinyint(3) UNSIGNED NOT NULL DEFAULT '60',
`currentprice` decimal(5,2) UNSIGNED NOT NULL DEFAULT '0.00',
`standby` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`closed` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`shipmenttax` decimal(3,2) UNSIGNED NOT NULL,
`currentuser` varchar(20) DEFAULT NULL,
`seconduser` varchar(20) DEFAULT NULL,
`thirduser` varchar(20) DEFAULT NULL,
`paytype` tinyint(3) UNSIGNED NOT NULL DEFAULT '255',
`closedate` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Store all items for auction' AUTO_INCREMENT=1;
Re: Caching every 1 second
Posted: Thu Jan 21, 2010 2:38 am
by Weirdan
sn4k3 wrote:thanks, also memcache came with 64MB of maximum memory and 1024 Maximum Connections, what will be the best value for that two fields?
Well, that really depends on your server configuration and traffic. If you're using Apache prefork MPM, max connections should be no less than MaxClients specified in httpd.conf. As for cache size - try experimenting with different values. Generally you should be using about 80% of available cache memory. Unfortunately Memcached extension comes without gui frontend (unlike older memcache extension), but gui from memcache extension seems to be extension-agnostic, so you may try to use it (
http://svn.php.net/viewvc/pecl/memcache ... iew=markup)
Re: Caching every 1 second
Posted: Thu Jan 21, 2010 5:59 am
by sn4k3
thanks for that information
Re: Caching every 1 second
Posted: Thu Jan 21, 2010 7:14 am
by onion2k
Why are you issuing an AJAX request every second? It would make a lot more sense to some sort of long polling Comet structure (
http://en.wikipedia.org/wiki/Comet_(programming) ). Essentially you keep the connection between the browser and server open and push the updates to the users when there is one rather than having the user repeatedly ask if there's anything new. It's far more efficient.
Re: Caching every 1 second
Posted: Thu Jan 21, 2010 7:35 am
by sn4k3
onion2k wrote:Why are you issuing an AJAX request every second? It would make a lot more sense to some sort of long polling Comet structure (
http://en.wikipedia.org/wiki/Comet_(programming) ). Essentially you keep the connection between the browser and server open and push the updates to the users when there is one rather than having the user repeatedly ask if there's anything new. It's far more efficient.
comet? i never heard about it, can you giveme a sample how it works?
im currently using ajax request:
Code: Select all
<script type = "text/javascript">
var liveids = <?php echo json_encode($this->Auction_model->AuctionsToIds($auctions)); ?>;
function joinArray(ar)
{
var b;
b = ar.join(",");
return(b);
}
function updateAuctions()
{
jQuery.ajax({
type: "POST",
url: base_url+"Api/GetAuctions",
data: ({ ids : '['+joinArray(liveids)+']' }),
timeout: 1000,
/*contentType: "application/json; charset=utf-8",*/
dataType: "json",
success: function(message) {
for (var i in message)
{
var oldcurrentuser = jQuery("#AICurrentuser"+message[i].id).text();
if(message[i].closed == 1){
jQuery("#AICurrenttime"+message[i].id).text('Terminou');
jQuery("#AICurrenttime"+message[i].id).css('color', 'red');
jQuery("#AIBid"+message[i].id).fadeOut('slow');
jQuery.jGrowl('Vencedor: '+message[i].currentuser+'<br />Preço de mercado: <strong>'+message[i].realprice+' €</strong><br />Preço Final: <strong>'+message[i].currentprice+' €</strong><br />Poupou: <strong>'
+message[i].saveprice+' €</strong>',
{ header: 'Leilão #'+message[i].id+' Terminado: '+message[i].name,
sticky: true, glue: 'before', position: 'top-left' });
liveids.splice(i, 1);
}else{
if(message[i].standby == 1)
jQuery("#AICurrenttime"+message[i].id).text('Suspenso');
else
jQuery("#AICurrenttime"+message[i].id).text(message[i].currenttime);
jQuery("#AICurrentprice"+message[i].id).html(message[i].currentprice+' €');
jQuery("#AICurrentuser"+message[i].id).text(message[i].currentuser);
jQuery("#AISeconduser"+message[i].id).text(message[i].seconduser);
jQuery("#AIThirduser"+message[i].id).text(message[i].thirduser);
if(message[i].currentuser == myusername)
jQuery("#AICurrentuser"+message[i].id).css('color', 'red');
else
jQuery("#AICurrentuser"+message[i].id).css('color', '#666');
if(message[i].seconduser == myusername)
{
jQuery("#AISeconduser"+message[i].id).css('color', 'red');
if(message[i].currentuser != myusername && oldcurrentuser == myusername)
jQuery.jGrowl("Um utilizador licitou por cima da sua licitação!<br />Utilizador: <strong>"+message[i].currentuser+"</strong><br /><a href=\"#\" onclick=\"bidAuction("+message[i].id+");\">Licitar</a>",
{ header: message.name, life: 7500, glue: 'before' });
}
else
jQuery("#AISeconduser"+message[i].id).css('color', '#666');
if(message[i].thirduser == myusername)
jQuery("#AIThirduser"+message[i].id).css('color', 'red');
else
jQuery("#AIThirduser"+message[i].id).css('color', '#666');
}
}
//jQuery("#cDateHour").text(message.d);
}
});
setTimeout('updateAuctions()',1000);
}
Re: Caching every 1 second
Posted: Thu Jan 21, 2010 7:40 am
by onion2k
There's a simple example here:
http://stackoverflow.com/questions/3336 ... ample-code
The thing is though, really, PHP isn't the right language for writing long polling server side code. You need something that sits there running all the time. PHP can do that, but there are more appropriate languages (Python, Java, C++, etc). When Facebook created their chat service they used a Comet approach written in Erlang for the server stuff.
Re: Caching every 1 second
Posted: Thu Jan 21, 2010 8:10 am
by sn4k3
onion2k wrote:There's a simple example here:
http://stackoverflow.com/questions/3336 ... ample-code
The thing is though, really, PHP isn't the right language for writing long polling server side code. You need something that sits there running all the time. PHP can do that, but there are more appropriate languages (Python, Java, C++, etc). When Facebook created their chat service they used a Comet approach written in Erlang for the server stuff.
Thanks, i have a some knowlage in python since i do 'counter strike source' plugins in python
but how can i integrate PHP and Python, im using CodeIgniter Framework
i also search and i found that: (AJAX)
Code: Select all
http://cometdproject.dojotoolkit.org/
and that: (Python)
and others ....
so what you recommend, since that is a new term for me
Thanks and sorry for many questions
