[eluser]sn4k3[/eluser]
hi,
Situation:
I want reduce MySQL querys
Webpage makes a 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
Also can anyone point me for a good cache library
Thanks
[eluser]richthegeek[/eluser]
sorta defeats the point of caching doing it this regularly - Is your data ever important enough that it can't be out of date for maybe 5 minutes?
[eluser]sn4k3[/eluser]
[quote author="richthegeek" date="1263704182"]sorta defeats the point of caching doing it this regularly - Is your data ever important enough that it can't be out of date for maybe 5 minutes?[/quote]
no, since i need decrease time every second, and check for ended auctions
its a auction website like:
Code: www . bidrivals . com
I have a Cron every second
Code: function CloseAuction($id = 0)
{
if($id > 0)
{
$this->db->where('id', $id);
$this->db->limit(1);
$this->db->set('closedate', 'NOW()', false);
$this->db->update('auctions', array('closed' => 1, 'standyby' => 1));
$this->db->delete('autobid', array('auctionid' => $id)); // Delete all autobids for that auction
return;
}
/*
* Find and close 00:00:00 auctions
* Inform users
*/
$this->db->select('id, currentuser');
$query = $this->db->get_where('auctions', "active AND NOT closed AND NOT standby AND currenttime = '00:00:00'");
if ($query->num_rows() > 0)
foreach ($query->result() as $row)
CloseAuction($row->id); // Close Auction (Declare a winner)
$query->free_result();
}
private function RunAutoBids()
{
/*
* Find and bid auctions with AutoBid
*
*/
$this->db->select('id, currenttime, currentprice, currentuser');
$this->db->where_in('currenttime', array('00:00:05', '00:00:10', '00:00:15', '00:00:20'));
$query = $this->db->get_where('auctions', "active AND NOT closed AND NOT standby AND allowautobid");
if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
//SELECT autobid.id, autobid.userid, users.email, users.bids FROM `autobid` JOIN users ON users.userid = autobid.userid WHERE autobid.auctionid = 1 AND users.username != 'sn4k3' AND users.bids AND autobid.time = 15 AND IF(autobid.maxprice = 0,999999999,autobid.maxprice) > 25.32 AND IF(autobid.maxbids = 0,999999999, autobid.maxbids) > autobid.totalbidded LIMIT 1
$this->db->select('autobid.code, autobid.userid, users.username, users.email, users.bids');
$this->db->join('users', 'users.userid = autobid.userid');
$this->db->where(array('autobid.auctionid' => $row->id, 'users.username !=' => $row->currentuser));
$this->db->where('autobid.time', "TIME_TO_SEC('{$row->currenttime}')", false);
$this->db->where('users.bids >', 0);
$this->db->where('IF(autobid.maxprice = 0,999999999,autobid.maxprice) >', $row->currentprice, false);
$this->db->where('IF(autobid.maxbids = 0,999999999,autobid.maxbids) >', 'autobid.totalbidded', false);
$this->db->limit(1);
$autoBidQuery = $this->db->get('autobid');
if ($autoBidQuery->num_rows() > 0)
{
$autoBidRow = $autoBidQuery->row();
// Bid Auction
$this->_BidAuction($row->id, $row->currentprice, $autoBidRow->userid, $autoBidRow->username, 1);
}
$autoBidQuery->free_result();
}
}
$query->free_result();
}
function DoCron($action = 0) // 0 = Normal | 1 = Suspense | 2 = UnSuspense
{
switch ($action) {
case 0:
CloseAuction();
RunAutoBids();
/*
* Decrease auction time in 1 second
*
*/
$this->db->set('currenttime', 'TIMEDIFF(currenttime, 1)', false);
$this->db->update('auctions', NULL, 'active AND NOT closed AND NOT standby');
break;
case 1: // Suspense
SuspendAuction();
break;
case 2: // Unsuspense
SuspendAuction(0);
break;
}
}
the code above is tigger by a Cron every second
Objective: try close, try auto bid, decrease time on a auction
now for each client on webpage it keep retrieving updated data every second
Code: function GetAuction($id, $joinAutoBid = false, $joinhistory = true, $historylimit = 20)
{
$this->db->limit(1);
$query = $this->db->get_where('auctions', array('id' => $id));
$data = NULL;
if ($query->num_rows() > 0)
{
$data = $query->result_array();
$data = $this->PrepareAuctions($data);
if($joinAutoBid)
$data[0]['autobid'] = $data[0]['allowautobid'] ? $this->GetAutoBid($id) : NULL;
if($joinhistory)
$data[0]['history'] = $this->GetAuctionBidHistory($id, $historylimit);
}
$query->free_result();
return $data;
}
What can i do for improve the situation and use less server resources?
Thanks
[eluser]richthegeek[/eluser]
Actually yes, with regards to your caching.
Caching is only worth it at a large number of hits, cos of maths...
uncached = time to execute * number of hits
cached = (time to execute * time to write to file) + (time to read cache * number of hits)
time to execute = SQL exec time + PHP exec time
In the uncached one if your SQL exec time is low compared to the overall exec time, then it's worth caching the output.
In the cached one, you will be dealing with the relative slowness of your disk - it can take half a second to write a file on a filesystem under strain, and about 1/100th to read, especially repeatedly due to the file being memcached.
Basically, doing it every second means you are spending more time arranging the cache than you are saving from caching.
You can do your cron job every second to automatically end auctions, and stop users from placing bids on auctions that have just ended, but the actual output can be cached every 30 sections, minute, whatever.
All the same, you'd probably get more benefit out of using memcached (stores the compiled PHP for execution, cutting per-page load times) or other exec-time reducing technologies.
[eluser]sn4k3[/eluser]
[quote author="richthegeek" date="1263706778"]Actually yes, with regards to your caching.
Caching is only worth it at a large number of hits, cos of maths...
uncached = time to execute * number of hits
cached = (time to execute * time to write to file) + (time to read cache * number of hits)
time to execute = SQL exec time + PHP exec time
In the uncached one if your SQL exec time is low compared to the overall exec time, then it's worth caching the output.
In the cached one, you will be dealing with the relative slowness of your disk - it can take half a second to write a file on a filesystem under strain, and about 1/100th to read, especially repeatedly due to the file being memcached.
Basically, doing it every second means you are spending more time arranging the cache than you are saving from caching.
You can do your cron job every second to automatically end auctions, and stop users from placing bids on auctions that have just ended, but the actual output can be cached every 30 sections, minute, whatever.
All the same, you'd probably get more benefit out of using memcached (stores the compiled PHP for execution, cutting per-page load times) or other exec-time reducing technologies.[/quote]
i see, so what you recommend for such thing, it must do that jobs every second, i have no other way
also can you recommend me an cache library for i use with other stuff
Thanks
[eluser]kevinprince[/eluser]
Better solution is:
Cache the returned db object in memcache etc
When the object is returned include the expiry time
Use this expiry time to build the view and use frontend code along with validation of the bid method to make sure you cant bid if the expiry based.
If you want you can cache the view and use javascript to evaluate if the auction has closed.
[eluser]sn4k3[/eluser]
[quote author="kevinprince" date="1263714952"]Better solution is:
Cache the returned db object in memcache etc
When the object is returned include the expiry time
Use this expiry time to build the view and use frontend code along with validation of the bid method to make sure you cant bid if the expiry based.
If you want you can cache the view and use javascript to evaluate if the auction has closed.[/quote]
Im not understanding very well what that mean
in that part:
Quote:Use this expiry time to build the view and use frontend code along with validation of the bid method to make sure you cant bid if the expiry based.
If you want you can cache the view and use javascript to evaluate if the auction has closed.
i current have that in my view: (Fragment only)
Code: < 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 >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);
}
function bidAuction(itemid)
{
jQuery.ajax({
type: "POST",
url: base_url+"Api/BidAuction",
data: ({ id : itemid }),
dataType: "json",
success: function(message) {
if(message == -2) // Error in make changes
jQuery.jGrowl("Erro ao tentar proceder a sua licitação!<br />Se este problema continuar faça refresh à página.", { header: 'Erro', life: 10000, glue: 'before' });
if(message == -1) // Logout
document.location=base_url+'Utilizador/Entrar';
else if(message == 0) // No Bids
document.location=base_url+'Utilizador/Comprar';
else // OK
{
jQuery("#AIBid"+itemid).show('pulsate');
jQuery.jGrowl("A sua licitação foi aceite, fique atento.", { header: message.name, life: 7500, glue: 'before' });
}
}
});
}
jQuery(document).ready(function() {
setTimeout('updateAuctions()',1000);
});
</ script>
|
