I have a Laravel application that writes and reads from a mysql database setup on innodb sql serve in ubuntu server. The application is a multi-tenant application and has at least 10 tenancies that performs similar functionalities. However, I realized that one tenancy has over 9980 entries in one of the common tenancy tables. When that particular tenancy tries to add another entry to the table, the whole app crashes but other tenancies can add to their copy copy of that table without any issues because they do not have that number of entries. When this happens the sql service runs at around 190% of processing power and the application can not be used by anyone, including those in other tenancies.
This is the create function
public function update(Request $request, $id)
{
$form = GeneralSpecimen::find($id);
$patient = Patient::where('id', $form->patient_id)->first();
$patientName = $patient->first_name . " " . $patient->last_name;
$patientNum = $patient->patient_number;
$patientPhoneNumber = $patient->phone;
$pathologyNumber = $form->pathology_number;
$formName = "GeneralSpecimen Form";
$sms = new SmsHelper();
// Get current Hostname Tenant Facility
$hostname = app(\Hyn\Tenancy\Environment::class)->hostname();
$tenantFacilityName = $hostname->tenant_facility_name;
$form->update( $request->except( 'auth_signature' ) );
$billPat = Bill::where('id', $form->bill_id)->first();
$new_arr = [];
if(!is_null($billPat)){
//* storing the column with clinical module items inside a variable [pathology_number]
$pathology_number = $billPat->clinical_module_items;
//*creating an empty array to store the updated array containing the new pathology number
//*looping through the clinical_module array to extract the pathology number using the key
foreach( $billPat->clinical_module_items as $key =>$value){
//*reassigning the pathology number with the new value
$value['pathology_number'] =request()->pathology_number;
//* Pushing the updated value array inside the new empty array created
array_push($new_arr, $value);
}
}
//*Finally updating the clinical_module column with the new array created
Bill::where('id',$form->bill_id)->update(['clinical_module_items'=>$new_arr]);
if( $request->submission_ready != 1 ) $form->update([ 'case_status' => 'pending']) ;
if( !is_null( $this->getSignature() ) && $request->submission_ready == 1 )
{ # health facility name for address
$healthFacility = DB::table('health_facility_profiles')->first();
$healthFacility == null ? $address = $tenantFacilityName : $address = $healthFacility->name_of_facility;
$form->update([ 'auth_signature' => $this->updateSignature( auth()->guard('api')->user()->id ) ]) ;
# update patient with the new generated password
//$patient->update(['password' => password_hash($this->password, PASSWORD_DEFAULT)]);
if( !is_null($patient->email) )
{ # send email
Mail::to($patient->email)->send(new PatientEmail($patientName, $patient->test_password, $patientNum, $hostname->tenant_facility_name));
}
if($sms->isEnabled == 1 && $sms->bundle > 1)
{ # send sms
$sms->sendSpecimenReadySms($patientPhoneNumber, $patientName, $pathologyNumber, $formName, $address, $patient->test_password, $patientNum);
}
if(!is_null($patientPhoneNumber))
{ #send WhatsAppMessage
$sms->sendSpecimenReadyWhatsAppMessage($patientPhoneNumber, $patientName, $pathologyNumber, $formName, $address, $patient->test_password, $patientNum);
}
$form->update([ 'case_status' => 'ready' ]);
$message = "Specimen updated successfully. An SMS has been sent to Patient";
return response(compact('message'), 200);
}
else if( is_null( $this->getSignature() ))
{
$message = "Kindly Upload Your Digital Signature to Finalize this Report" ;
return response(compact('message'), 200);
}
$message = "Specimen form updated" ;
return response(compact("message"), 200);
}
other tenancies that have not reached similar number of entries use the same functions to create new entries without any problem
I inspected the table information in workbench and realized that the total size estimate in is only 6.5 MiB. On deleting some entries, I could add more forms to the app without any problems but once I reach the initial number, the app breaks.
This is a very broad issue, the few details you have provided do not specifically violate the limitations of your chosen RDBMS, at least by what is documented here: InnoDB Limitations
What you haven't investigated is what is causing the spike in the SQL process. It is not likely to be adding a new record alone, it is more likely to be something related to your indexing strategies or how your client is processing the data.
You might find investigation strategies in this article helpful Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back but you really need to identify if it is your app that is spamming the database and thus causing the performance degradation or if it is the database alone.
10K records is remarkably small for a database, 6MB sounds very small indeed, so I doubt this is a problem with the database itself and likely related to something at the application layer.
When you see spikes in SQL CPU usage, that can indicate that there is at least one long running query, so you need to query the database or the logs to identify which queries are holding everything up. Read through TRACKING MYSQL QUERY HISTORY IN LONG RUNNING TRANSACTIONS for advice on how to do this or search for other techniques to inspect your active queries.
NOTE: If your DB is non-responsive you may need to implement a query logging strategy to diagnose this. Find out the very last line of code in the application before the failure and what is the current and recent SQL queries that have been sent to the database.
Arbitrary item thresholds like this are often artifacts of inefficient looping or rebalancing operations across a dataset, especially when there are triggers or other recursive operations involved.