Moderators mark1million Posted January 23, 2012 Moderators Report Share Posted January 23, 2012 Nabeel I am starting to get problems while pilots are filing pireps, to file a manual pirep it is taking over one minute from pressing the file button, this is also causing problems when pilots are using kACARS with the site delay. If there any way of moving the back end functions to a cron as the current performance can not continue. I have been speaking with Jeff and we have tried various things but its looking like its the back end functions that are causing the delays and problems as described above i tried a manual pirep which took over a minute to file. Is there a way to debug this step by step and find the problem. Just to add my other VA is fine and its running the exact same files just a different skin, the only difference is the size of the pireps and pilots in the database and of course the length of time open. Any help with this would be greatly appreciated as its now starting to effect pilot numbers and the complaints are rising. Thanks. Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted January 23, 2012 Author Moderators Report Share Posted January 23, 2012 Update, i think i know what the delay is "Finance Calculations", im going to look at commenting that part out to see what happens. Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted January 30, 2012 Author Moderators Report Share Posted January 30, 2012 Nabeel, I have traced down the problem to this code below in the PIREPData.class.php, /** * Populate PIREPS which have 0 values for the load/price, etc * */ public static function populateEmptyPIREPS() { $sql = 'SELECT * FROM ' . TABLE_PREFIX . 'pireps '; $results = DB::get_results($sql); if (!$results) { return true; } foreach ($results as $row) { self::PopulatePIREPFinance($row, true); } return true; } /** * Populate the PIREP with the fianancial info needed * * @param mixed $pirep Either a PIREP ID or the row * @param bool $reset_fuel Reset the fuel costs or not? * @return */ public static function populatePIREPFinance($pirep, $reset_fuel = false) { if (!is_object($pirep) && is_numeric($pirep)) { $pirep = PIREPData::getReportDetails($pirep); if (!$pirep) { self::$lasterror = 'PIREP does not exist'; return false; } } # Set the PIREP ID $pirepid = $pirep->pirepid; $sched = SchedulesData::getScheduleByFlight($pirep->code, $pirep->flightnum, ''); if (!$sched) { self::$lasterror = 'Schedule does not exist. Please update this manually.'; return false; } $pilot = PilotData::getPilotData($pirep->pilotid); # Get the load factor for this flight if ($pirep->load == '' || $pirep->load == 0) { $pirep->load = FinanceData::getLoadCount($pirep->aircraft, $sched->flighttype); } // Fix for bug #62, check the airport fuel price as 0 for live //$depapt = OperationsData::getAirportInfo($pirep->depicao); if ($pirep->fuelunitcost == '' || $pirep->fuelunitcost == 0 || $reset_fuel == true) { $pirep->fuelunitcost = FuelData::getFuelPrice($pirep->depicao); } # Check the fuel if ($pirep->fuelprice != '' || $reset_fuel == true) { $pirep->fuelprice = FinanceData::getFuelPrice($pirep->fuelused, $pirep->depicao); } # Get the expenses for a flight $total_ex = 0; $expense_list = ''; /* Account for any fixed-cost percentages */ $allexpenses = FinanceData::getFlightExpenses(); if (is_array($allexpenses)) { foreach ($allexpenses as $ex) { $total_ex += $ex->cost; } } /* Account for any per-flight %age expenses */ $all_percent_expenses = FinanceData::getFlightPercentExpenses(); $gross = floatval($sched->price) * floatval($pirep->load); if (is_array($all_percent_expenses)) { foreach ($all_percent_expenses as $ex) { $cost = str_replace('%', '', $ex->cost); $percent = $cost / 100; $total = ($gross * $percent); $total_ex += $total; } } /* Set the pilotpay here - if it was a per-schedule payment, then set the pilot pay to that, otherwise, set it to the total amount paid... */ # Handle pilot pay if(!empty($sched->payforflight)) { $pilot->payrate = $sched->payforflight; $payment_type = PILOT_PAY_SCHEDULE; } else { $payment_type = PILOT_PAY_HOURLY; } $data = array( 'price' => $sched->price, 'load' => $pirep->load, 'fuelprice' => $pirep->fuelprice, 'expenses' => $total_ex, 'pilotpay' => $pilot->payrate, 'flighttime' =>$pirep->flighttime, ); $revenue = self::getPIREPRevenue($data, $payment_type); /* Now update the PIREP */ $fields = array( 'price' => $sched->price, 'load' => $pirep->load, 'gross' => $gross, 'fuelprice' => $pirep->fuelprice, 'fuelunitcost' => $pirep->fuelunitcost, 'expenses' => $total_ex, 'pilotpay' => $pilot->payrate, 'paytype' => $payment_type, 'revenue' => $revenue ); if (isset($data['load']) && $data['load'] != '') { $fields['load'] = $data['load']; } return self::editPIREPFields($pirepid, $fields); } /** * Calculate the gross revenue of a PIREP * * @param array $data Associative array with price, load, pilotpay, flighttime * @param int $payment_type 1 for hourly payment, 2 for per-schedule payment * @return */ public static function getPIREPRevenue($data, $payment_type = PILOT_PAY_HOURLY) { $gross = $data['price'] * $data['load']; if($payment_type == PILOT_PAY_HOURLY) { $pilotpay = $data['pilotpay'] * $data['flighttime']; } else { $pilotpay = $data['pilotpay']; } if ($data['expenses'] == '') { $data['expenses'] = 0; } $revenue = $gross - $data['expenses'] - $data['fuelprice'] - $pilotpay; return $revenue; } When you have a massive pireps table something in here is causing the delay when filing pireps. Can this stuff be moved to a cron somehow? Quote Link to comment Share on other sites More sharing options...
piuozorio Posted January 30, 2012 Report Share Posted January 30, 2012 Hello, if i understood i think have the same problem. At the beginning was very fast, click, filed. We only send pireps from kACARS(custom) and when we click to send it takes almost a minute to receive the ok filed. I can see when i click send the pirep is instantaneous on site/database, but the ok result from kACARS is too long. I thought it was my problem(?) because i added some code there, tested with the old files and is the same. Reading your post makes me believe that maybe is a php/mysql problem. I'm going to try make some tests. Sorry my english Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted January 30, 2012 Author Moderators Report Share Posted January 30, 2012 Correct yes. I have increased the available memory to the mysql server and increased php memory limit. I have been running benchmarking tests plus i have switched on the log slow queries. When filing a pirep manual or kACARS it takes ages for the server to process all the functions, at first this is not a problem because the table pireps is small but as it increases the delays become longer, like you said the initial pirep data is sent to the server and its populated instantaneously in to the database, It is the other back end functions that are causing problems and the delays. I am still working on it but in reality the only person that could sort this is Nabeel i think. Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted January 30, 2012 Author Moderators Report Share Posted January 30, 2012 This is my slow query log when a pirep is filed, # Time: 120130 13:47:48 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.378479 Lock_time: 0.000076 Rows_sent: 546 Rows_examined: 18383 SET timestamp=1327931268; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '7' AND YEAR(submitdate) = '2010'; # Time: 120130 13:47:51 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.446819 Lock_time: 0.000116 Rows_sent: 612 Rows_examined: 18383 SET timestamp=1327931271; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '8' AND YEAR(submitdate) = '2010'; # Time: 120130 13:47:54 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.460371 Lock_time: 0.000109 Rows_sent: 649 Rows_examined: 18383 SET timestamp=1327931274; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '9' AND YEAR(submitdate) = '2010'; # Time: 120130 13:47:57 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.266678 Lock_time: 0.000107 Rows_sent: 804 Rows_examined: 18383 SET timestamp=1327931277; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '10' AND YEAR(submitdate) = '2010'; # Time: 120130 13:48:00 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.474292 Lock_time: 0.000087 Rows_sent: 934 Rows_examined: 18383 SET timestamp=1327931280; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '11' AND YEAR(submitdate) = '2010'; # Time: 120130 13:48:04 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 3.230826 Lock_time: 0.000105 Rows_sent: 778 Rows_examined: 18383 SET timestamp=1327931284; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '12' AND YEAR(submitdate) = '2010'; # Time: 120130 13:48:07 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.548711 Lock_time: 0.000079 Rows_sent: 880 Rows_examined: 18383 SET timestamp=1327931287; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '1' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:10 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.303175 Lock_time: 0.000083 Rows_sent: 924 Rows_examined: 18383 SET timestamp=1327931290; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '2' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:13 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.399013 Lock_time: 0.000153 Rows_sent: 1067 Rows_examined: 18383 SET timestamp=1327931293; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '3' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:17 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.721266 Lock_time: 0.000079 Rows_sent: 1111 Rows_examined: 18383 SET timestamp=1327931297; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '4' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:20 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.777314 Lock_time: 0.000091 Rows_sent: 1114 Rows_examined: 18383 SET timestamp=1327931300; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '5' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:25 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 3.575208 Lock_time: 0.000127 Rows_sent: 1500 Rows_examined: 18383 SET timestamp=1327931305; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '6' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:30 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 3.168859 Lock_time: 0.000106 Rows_sent: 1092 Rows_examined: 18383 SET timestamp=1327931310; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '7' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:34 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.469610 Lock_time: 0.000113 Rows_sent: 1034 Rows_examined: 18383 SET timestamp=1327931314; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '8' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:37 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.290348 Lock_time: 0.000098 Rows_sent: 880 Rows_examined: 18383 SET timestamp=1327931317; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '9' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:40 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.598176 Lock_time: 0.000083 Rows_sent: 973 Rows_examined: 18383 SET timestamp=1327931320; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '10' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:43 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.390675 Lock_time: 0.000083 Rows_sent: 898 Rows_examined: 18383 SET timestamp=1327931323; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '11' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:46 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.355913 Lock_time: 0.000111 Rows_sent: 717 Rows_examined: 18383 SET timestamp=1327931326; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '12' AND YEAR(submitdate) = '2011'; # Time: 120130 13:48:49 # User@Host: REMOVED[REMOVED] @ localhost [] # Query_time: 2.434966 Lock_time: 0.000104 Rows_sent: 764 Rows_examined: 18383 SET timestamp=1327931329; SELECT * FROM phpvms_pireps WHERE MONTH(submitdate) = '1' AND YEAR(submitdate) = '2012'; Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted January 31, 2012 Author Moderators Report Share Posted January 31, 2012 Does anybody know what's happening here as that query is taking over 45 seconds to run each time a pirep is filed. Why is it scanning the complete pireps table? How do i make it stop Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted January 31, 2012 Author Moderators Report Share Posted January 31, 2012 Right, Found the culprit. For anyone running Top Pilot from Simpilot there is a pirep file listener in there which is firing every time a pirep is filed to populate the top pilot stats. When your pirep table is small its not got a lot to calculate but when it gets bigger that's where the delay comes in. Simply removing the listener sorted it out, so im going to add this to a cron that runs a few times a day. File location is >core>modules>TopPilot>TopPilot.php <?php //by:simpilot //www.simpilotgroup.com class TopPilot extends CodonModule { public $title = 'Top Pilots'; public function __construct() { //CodonEvent::addListener('TopPilot', array('pirep_filed')); } public function EventListener($eventinfo) { if($eventinfo[0] == 'pirep_filed') { self::refresh_pilot_stats(); } } Quote Link to comment Share on other sites More sharing options...
piuozorio Posted February 1, 2012 Report Share Posted February 1, 2012 Hello, i have tested your fix and it works now The top pilot was installed but never used so i deleted for now. How can a extra module affect the submit pirep!? Thank you very much for the solution Quote Link to comment Share on other sites More sharing options...
RogerB Posted July 21, 2012 Report Share Posted July 21, 2012 Is this what you cron? public function __construct() { CodonEvent::addListener('TopPilot', array('pirep_filed')); } public function EventListener($eventinfo) { if($eventinfo[0] == 'pirep_filed') { self::refresh_pilot_stats(); } } Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted July 24, 2012 Report Share Posted July 24, 2012 I think the cron file should have this in it <?php define('ADMIN_PANEL', true); error_reporting(0); ini_set('display_errors', 0); include dirname(dirname(__FILE__)).'/core/codon.config.php'; TopPilot::refresh_pilot_stats(); Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted July 24, 2012 Author Moderators Report Share Posted July 24, 2012 This is what i use, <?php define('ADMIN_PANEL', true); include dirname(dirname(__FILE__)).'/core/codon.config.php'; Auth::$userinfo->pilotid = 0; /* error_reporting(E_ALL); ini_set('display_errors', 'on'); */ set_time_limit(0); ini_set('memory_limit', '-1'); TopPilot::refresh_pilot_stats(); echo 'Top Pilot Stats Updated'; ?> Quote Link to comment Share on other sites More sharing options...
RogerB Posted July 24, 2012 Report Share Posted July 24, 2012 Ok, I have this on two websites, one is just for testing, should I include the path of Kesuk? Quote Link to comment Share on other sites More sharing options...
RogerB Posted July 25, 2012 Report Share Posted July 25, 2012 getting this error. syntax error near unexpected token Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.