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

Ok so if you are running TS using mysql just do a query to count the users connected, if not then your best bet would be tsvirewer.

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

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.

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?

If you check VA Central you will see they are there. Nothing to worry about.

This does work for xacars but for displaying distance remaining in kACARS it removes the variable and you get a blank distance remaining for other clients.

You will soon find most of the file share sites will be taken down, unfortunately. This was taken down last week.

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

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.

Yeh sorry about that i stripped down those templates of all the other code and css

Sorry should have put. In the results.tpl get rid of the Util::FormatDuration Remove them from lines 60, 65, 70 and 75. That works now for me.

Ok i found the problem and removed some code, works well now

Where does it get the time division from? Let me take another look and see if i can get to the bottom of it.

Great add on, im getting a problem with the time function, if i comment the time calculations out then i get a good output but uncommented i get an error where the page stops, nothing in the logs either. Any ideas?

Great addition well done.

Not with the current version and the results are not stored in the database rather created on the fly.

Speak with Jeff for a custom version he can do that no problem.

In your installed root directory where your files are there will be a folder called install, just delete that.

Did you use the report to moderator? If you did then all the mods would see it, maybe i missed it. We all have other projects as well as babysitting here, im sorry but that's the way it seems at times. We do our best, like i said if something offends or you dont think it should be then use the report to moderator links.

Hi, im not sure i follow, I have seen a number of threads locked and posts about keep the topics on track. This forum is not a sounding board or an ego boosting system its a help and support board for admins of virtual airlines that run phpvms. I have seen posts about what do you think of my site etc well thats just wide open for abuse so really whats the point in them, my suggestion would be to use this board for what its designed for help and support not for ego boosting or offending other members.

If you are going to implement this then you should consider moving from a free domain. Good luck.

ha seems we keep posting together lol

Keep it in your skins folder then any update will not overwright your custom settings of individual files. The system will look first for any files in your active skin directory and if they are not there then are displayed from the core templates directory.

Go to the core>Templates and copy news_newsitem.tpl to your active skin folder then you will see the <hr> to remove