View Issue Details

IDProjectCategoryView StatusLast Update
12990Bug reports[All Projects] Survey participants (Tokens)public2017-12-05 02:53
ReporterDeveloperChrisAssigned To 
PrioritynoneSeverityblock 
Status newResolutionopen 
Product Version2.64.x 
Target VersionFixed in Version 
Summary12990: Queries on large participants table uses 100% CPU and causes LS to become non responsive
DescriptionWhen running a "not completed" export on the participants table that has a moderate number of recipients the mysql server spikes to 100% on a single CPU and the application fails to respond to further requests.

I am not sure why limesurvey stops responding until the report is complete. It should only prevent access to the effected survey but even the front page does not render! I have not resolved why that is.

The participant table we are querying has 81K records of which 53K have responses in the survey table.

The query created by LS is as shown in the slow query log which shows the query took ~8 Minutes to process

# Time: 171205 8:46:46
# User@Host: limesurvey[limesurvey] @ [10.190.2.200]
# Query_time: 472.000994 Lock_time: 0.000173 Rows_sent: 25882 Rows_examined: 1491192966
SET timestamp=1512425806;
SELECT *
FROM `lime_tokens_982413`
WHERE ((1=1) AND (completed='N')) AND (token not in (select token from lime_survey_982413 group by token))
ORDER BY `tid`;


I have created a work around that avoids the use of mysql's IN statement a notoriously slow statement...

SELECT * FROM `lime_tokens_982413` `lt` LEFT JOIN `lime_survey_982413` `ls` ON lt.token=ls.token WHERE ((1=1) AND (lt.completed='N')) AND (ls.id IS NULL) ORDER BY `lt`.`tid`
Additional InformationI have attached a diff file for the changes I made to resolve this issue. Please do not use it verbatim but instead verify it for use in all DB scenario's it seems to work fine with MYSQL/MariaDB

Please also avoid using the "IN" statement Joins are far more efficient. creating a join on a temporary table can also help avoid the IN statement.
TagsNo tags attached.
Complete LimeSurvey version number (& build)Version 2.64.7+170404
I will donate to the project if issue is resolvedNo
BrowserNA
Database & DB-VersionServer version: 5.5.58-0+deb8u1 (Debian)
Operating System (Server)NA
Webserver software & versionNA
PHP VersionNA

Activities

DeveloperChris

DeveloperChris

2017-12-05 02:45

reporter  

export_helper.php.diff.txt (3,639 bytes)
diff --git a/public_html/application/helpers/export_helper.php b/public_html/application/helpers/export_helper.php
index ae83a08..7483ecc 100644
--- a/public_html/application/helpers/export_helper.php
+++ b/public_html/application/helpers/export_helper.php
@@ -1712,60 +1712,65 @@ function tokensExport($iSurveyID)
     $oSurvey=Survey::model()->findByPk($iSurveyID);
     $bIsNotAnonymous= ($oSurvey->anonymized=='N' && $oSurvey->active=='Y');// db table exist (survey_$iSurveyID) ?

-    $oRecordSet = Yii::app()->db->createCommand()->from("{{tokens_$iSurveyID}}");
+    $oRecordSet = Yii::app()->db->createCommand()->from("{{tokens_$iSurveyID}} lt");
     $databasetype = Yii::app()->db->getDriverName();
     $oRecordSet->where("1=1");
     if ($sEmailFiter!='')
     {
         if (in_array($databasetype, array('mssql', 'sqlsrv', 'dblib')))
         {
-            $oRecordSet->andWhere("CAST(email as varchar) like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
+            $oRecordSet->andWhere("CAST(lt.email as varchar) like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
         }
         else
         {
-            $oRecordSet->andWhere("email like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
+            $oRecordSet->andWhere("lt.email like ".dbQuoteAll('%'.$sEmailFiter.'%', true));
         }
     }
     if ($iTokenStatus==1)
     {
-        $oRecordSet->andWhere("completed<>'N'");
+        $oRecordSet->andWhere("lt.completed<>'N'");
     }
     elseif ($iTokenStatus==2)
     {
-        $oRecordSet->andWhere("completed='N'");
+        $oRecordSet->andWhere("lt.completed='N'");
         if ($bIsNotAnonymous)
         {
-            $oRecordSet->andWhere("token not in (select token from {{survey_$iSurveyID}} group by token)");
+            // $oRecordSet->andWhere("token not in (select token from {{survey_$iSurveyID}} group by token)");
+          $oRecordSet->leftJoin("{{survey_$iSurveyID}} ls", 'lt.token=ls.token');
+          $oRecordSet->andWhere("ls.id IS NULL");
         }
     }
     if ($iTokenStatus==3 && $bIsNotAnonymous)
     {
-        $oRecordSet->andWhere("completed='N' and token in (select token from {{survey_$iSurveyID}} group by token)");
+         // $oRecordSet->andWhere("lt.completed='N' and token in (select token from {{survey_$iSurveyID}} group by token)");
+      $oRecordSet->leftJoin("{{survey_$iSurveyID}} ls", 'lt.token=ls.token');
+      $oRecordSet->andWhere("lt.completed='N'");
+      $oRecordSet->andWhere("ls.id IS NULL");
     }

     if ($iInvitationStatus==1)
     {
-        $oRecordSet->andWhere("sent<>'N'");
+        $oRecordSet->andWhere("lt.sent<>'N'");
     }
     if ($iInvitationStatus==2)
     {
-        $oRecordSet->andWhere("sent='N'");
+        $oRecordSet->andWhere("lt.sent='N'");
     }

     if ($iReminderStatus==1)
     {
-        $oRecordSet->andWhere("remindersent<>'N'");
+        $oRecordSet->andWhere("lt.remindersent<>'N'");
     }
     if ($iReminderStatus==2)
     {
-        $oRecordSet->andWhere("remindersent='N'");
+        $oRecordSet->andWhere("lt.remindersent='N'");
     }

     if ($sTokenLanguage!='')
     {
-        $oRecordSet->andWhere("language=".dbQuoteAll($sTokenLanguage));
+        $oRecordSet->andWhere("lt.language=".dbQuoteAll($sTokenLanguage));
     }
-    $oRecordSet->order("tid");
+    $oRecordSet->order("lt.tid");
     $bresult = $oRecordSet->query();
     //HEADERS should be after the above query else timeout errors in case there are lots of tokens!
     header("Content-Disposition: attachment; filename=tokens_".$iSurveyID.".csv");
DeveloperChris

DeveloperChris

2017-12-05 02:53

reporter   ~45248

Just as an extra note the reworked SQL Query using joins went from 472 seconds to 0.76 seconds

CREATE TEMPORARY TABLE table5 as (SELECT lt.* FROM `lime_tokens_982413` lt LEFT JOIN `lime_survey_982413` ls ON (lt.token=ls.token) WHERE lt.completed='N' AND ls.id IS NULL GROUP BY lt.token order by lt.tid);
Query OK, 25908 rows affected (0.76 sec)
Records: 25908 Duplicates: 0 Warnings: 0

Creating the temporary table has a small amount of overhead so the actual query. The purpose of creating the temporary table is to prevent echoing the results of the query to the screen.

Issue History

Date Modified Username Field Change
2017-12-05 02:45 DeveloperChris New Issue
2017-12-05 02:45 DeveloperChris File Added: export_helper.php.diff.txt
2017-12-05 02:53 DeveloperChris Note Added: 45248