We recently upgraded to Debian 9 so I think there are some server configs and tuning yet to be done. Originally draft mailings wouldn't save at all and I recreated the smart group that fed the recipients. I turned off the smart group caching job and created a smart group cache flushing job and changed the caching from opportunistic to deterministic, according to this article: https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement
It's the autosave function which I believe is also loading the 62,000+ recipients. Once they load, clicking "save" goes much faster. However, opening the draft and making an edit takes a long time to save again.
I checked the MySQL process that is running and there are two at play:
| 140355 | db_user | localhost | civicrm | Query | 540 | Sending data | INSERT INTO civicrm_mailing_recipients (mailing_id, contact_id, email_id)
SELECT 953, i.contact_id, | 0.000 |
| 140388 | db_user | localhost | civicrm | Query | 540 | updating | DELETE FROM civicrm_mailing_recipients WHERE mailing_id = 946
I have cleared the templates_c and flushed caches. Let me know if I can provide any more clues or information. I'm worried about a database corruption. Nothing is being reported in the CiviCRM log for this transaction.
At the time of writing, the draft has been "saving" for about 20 minutes now.
Edit: After the save was complete, there was an error and backtrace in the CiviCRM ConfigAndLog log, which I am unsure if is related, so I'm editing the draft to see if the same error comes up which seems to be related to a specific user. The backtrace suggests it's related to a diffent issue I'm having when users get an unknown DB_Error after using a contribution form. I'm putting it here in case it's somehow related.
[debug_info] => INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 139092 , 1 , 'user@example.org' , 1 , 0 , 0 ) [nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]
CiviCRM 5.7.2 on Drupal 7.62.