Filing pireps delays [SOLVED]

[mark1million]

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.

[mark1million]

Update, i think i know what the delay is "Finance Calculations", im going to look at commenting that part out to see what happens.

[mark1million]

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?

[piuozorio]

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

[mark1million]

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.

[mark1million]

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';

[mark1million]

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

[mark1million]

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();
       }
   }

[piuozorio]

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

[RogerB]

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();
    }
   }

[Guest lorathon]

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();

[mark1million]

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';

?>

[RogerB]

Ok, I have this on two websites, one is just for testing, should I include the path of Kesuk?

[RogerB]

getting this error. syntax error near unexpected token