Skip to content

cp_fediverse_activities request on database make a very long time

Describe the bug

We're experiencing sql overload problems on our server due to the spark tasks:run task generating those sql query

Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` ="

that won't stop (over 900 seconds).

The problem is that this cron job is launched every minute, so the sql requests accumulate and load the server.

Screenshot or logs

Extract from mytop :

  100525 pod_uzer       localhost  castopod  929.5   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100562 pod_uzer       localhost  castopod  869.3   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100589 pod_uzer       localhost  castopod  809.6   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100630 pod_uzer       localhost  castopod  749.7   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100672 pod_uzer       localhost  castopod  689.9   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100699 pod_uzer       localhost  castopod  629.7   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100737 pod_uzer       localhost  castopod  569.8   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100770 pod_uzer       localhost  castopod  509.4   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100792 pod_uzer       localhost  castopod  449.9   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100825 pod_uzer       localhost  castopod  389.8   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100859 pod_uzer       localhost  castopod  329.7   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100894 pod_uzer       localhost  castopod  269.8   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100920 pod_uzer       localhost  castopod  209.6   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC                        
   100948 pod_uzer       localhost  castopod  149.8   0.0  Query Creating sort i SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC

Explain on on request :

EXPLAIN SELECT * FROM `cp_fediverse_activities` WHERE `scheduled_at` <= UTC_TIMESTAMP() AND `status` = 'queued' ORDER BY `scheduled_at` ASC:

*** row 1 ***
          table:  cp_fediverse_activities
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  366074
          Extra:  Using where; Using filesort

Steps to reproduce

  1. crontab executed all minutes
  2. see request with mytop in mysql
  3. clean all request in mysql to unlead the server

Context

  • Castopod: 1.12.3
  • OS: DEbian 12
  • Browser: all
  • Web server: Apache

Best regards