Repository: scrapy/scrapyd

Issue:
SQLite queue is using all CPU on high frequency poller (<1s)
When running spiders that do nothing at all, the sqlite based poller uses all cpu just reading scheduled tasks. It would be good to have a plug and play alternative queues like redis.

Discussion:
@pspsdev: Related:
https://github.com/scrapy/scrapyd/issues/197@jpmckinney: Why are you running spiders that "do nothing at all"?@pspsdev: @jpmckinney just to rule out that cpu is being used by a spider. This can be replicated when scheduling a lot of jobs and polling rate is below a second e.g 0.1. SQLite queue will use massive ammount of cpu.@pspsdev: There are also some unmaintained repos that tries to solve this:
https://github.com/speakol-ads/scrapyd-redis

Simply the sqlite queue is a really bad option for high frequency queues.@jpmckinney: Hmm, yeah, same with https://github.com/Tiago-Lira/scrapyd-mongodb (from which scrapyd-redis is forked) and https://github.com/balena/python-pqueue (mentioned in #197).

https://github.com/peter-wangxu/persist-queue is still active, though maybe a first attempt is to switch to https://github.com/scrapy/queuelib as mentioned in #197.

Can you share your setup for reproducing the issue?@pspsdev: I will try to create a demo later, but it's pretty much can be empty scrapyd service running with 1 spider that does nothing. Then creating like 50 schedules per second and making polling rate 0.1. It will destroy powerful cpu.@pspsdev: Also, in my personal opinion I would say it would make sense to add interface to add your own queue backend instead of doing hacks like those 2 repos mentioned above.@pspsdev: And then later sqlite can be switch to some other default is needed, but having a simple method to replace the queue on your own would be a very good option to quick solve this problem for those who use high frequency polling@jpmckinney: Do you have your own queue ready to use? You can try it with this PR: https://github.com/scrapy/scrapyd/pull/476@pspsdev: @jpmckinney thanks, give me a few hours I will try it out.@jpmckinney: @pspsdev Now that #476 is merged, do you have suggestions for how to edit the default spider queue, or should there just be a note in the documentation that it doesn't perform well under high frequency polling, and a custom queue would be better?@pspsdev: @jpmckinney I am still doing some tests on my end, give me a few days I will report with more details.@jpmckinney: FWIW, I can't replicate this issue. I set `poll_interval = 0.1` and scheduled 100 jobs in a loop.

Solution patch:
diff --git a/docs/config.rst b/docs/config.rst
index b8173dc..975bf62 100644
--- a/docs/config.rst
+++ b/docs/config.rst
@@ -142,6 +142,16 @@ and services.
 
 For more info see `Twisted Application Framework`_
 
+.. _spiderqueue:
+
+spiderqueue
+-----------
+
+The scheduler enqueues crawls in per-project spider queues, for the poller to pick.
+You can define a custom spider queue class that implements the ISpiderQueue interface.
+
+Defaults to ``scrapyd.spiderqueue.SqliteSpiderQueue``.
+
 .. _webroot:
 
 webroot
diff --git a/docs/news.rst b/docs/news.rst
index c70d724..e478630 100644
--- a/docs/news.rst
+++ b/docs/news.rst
@@ -3,6 +3,21 @@
 Release notes
 =============
 
+Unreleased
+----------
+
+Added
+~~~~~
+
+- Add ``spiderqueue`` configuration option for custom spider queue.
+
+Changed
+~~~~~~~
+
+- ``scrapyd.spiderqueue.SqliteSpiderQueue`` is initialized with a ``scrapyd.config.Config`` object and a project name, rather than a SQLite connection string (i.e. database file path).
+- If ``dbs_dir`` is set to ``:memory`` or to a URL, it is passed through without modification and without creating a directory to ``scrapyd.jobstorage.SqliteJobStorage`` and ``scrapyd.spiderqueue.SqliteSpiderQueue``.
+- ``scrapyd.utils.get_spider_queues`` defers the creation of the ``dbs_dir`` directory to the spider queue implementation.
+
 1.4.1 (2023-02-10)
 ------------------
 
diff --git a/scrapyd/default_scrapyd.conf b/scrapyd/default_scrapyd.conf
index 763ed72..456e363 100644
--- a/scrapyd/default_scrapyd.conf
+++ b/scrapyd/default_scrapyd.conf
@@ -17,6 +17,7 @@ runner      = scrapyd.runner
 jobstorage  = scrapyd.jobstorage.MemoryJobStorage
 application = scrapyd.app.application
 launcher    = scrapyd.launcher.Launcher
+spiderqueue = scrapyd.spiderqueue.SqliteSpiderQueue
 webroot     = scrapyd.website.Root
 eggstorage  = scrapyd.eggstorage.FilesystemEggStorage
 
diff --git a/scrapyd/interfaces.py b/scrapyd/interfaces.py
index f892e37..dff2a75 100644
--- a/scrapyd/interfaces.py
+++ b/scrapyd/interfaces.py
@@ -105,10 +105,16 @@ class ISpiderScheduler(Interface):
 class IEnvironment(Interface):
     """A component to generate the environment of crawler processes"""
 
+    def get_settings(message):
+        """Return the Scrapy settings to use for running the process.
+
+        `message` is the message received from the IPoller.next() method.
+        """
+
     def get_environment(message, slot):
         """Return the environment variables to use for running the process.
 
-        `message` is the message received from the IPoller.next() method
+        `message` is the message received from the IPoller.next() method.
         `slot` is the Launcher slot where the process will be running.
         """
 
diff --git a/scrapyd/jobstorage.py b/scrapyd/jobstorage.py
index ffaa950..7c94177 100644
--- a/scrapyd/jobstorage.py
+++ b/scrapyd/jobstorage.py
@@ -1,10 +1,10 @@
-import os
 from datetime import datetime
 
 from zope.interface import implementer
 
 from scrapyd.interfaces import IJobStorage
 from scrapyd.sqlite import SqliteFinishedJobs
+from scrapyd.utils import sqlite_connection_string
 
 
 def job_log_url(job):
@@ -50,11 +50,7 @@ class MemoryJobStorage(object):
 class SqliteJobStorage(object):
 
     def __init__(self, config):
-        dbsdir = config.get('dbs_dir', 'dbs')
-        if not os.path.exists(dbsdir):
-            os.makedirs(dbsdir)
-        dbpath = os.path.join(dbsdir, 'jobs.db')
-        self.jstorage = SqliteFinishedJobs(dbpath, "finished_jobs")
+        self.jstorage = SqliteFinishedJobs(sqlite_connection_string(config, 'jobs'), "finished_jobs")
         self.finished_to_keep = config.getint('finished_to_keep', 100)
 
     def add(self, job):
diff --git a/scrapyd/spiderqueue.py b/scrapyd/spiderqueue.py
index 1c88943..ef129a6 100644
--- a/scrapyd/spiderqueue.py
+++ b/scrapyd/spiderqueue.py
@@ -2,13 +2,14 @@ from zope.interface import implementer
 
 from scrapyd.interfaces import ISpiderQueue
 from scrapyd.sqlite import JsonSqlitePriorityQueue
+from scrapyd.utils import sqlite_connection_string
 
 
 @implementer(ISpiderQueue)
 class SqliteSpiderQueue(object):
 
-    def __init__(self, database=None, table='spider_queue'):
-        self.q = JsonSqlitePriorityQueue(database, table)
+    def __init__(self, config, project, table='spider_queue'):
+        self.q = JsonSqlitePriorityQueue(sqlite_connection_string(config, project), table)
 
     def add(self, name, priority=0.0, **spider_args):
         d = spider_args.copy()
diff --git a/scrapyd/utils.py b/scrapyd/utils.py
index 16e6800..baf26fc 100644
--- a/scrapyd/utils.py
+++ b/scrapyd/utils.py
@@ -2,13 +2,13 @@ import json
 import os
 import sys
 from subprocess import PIPE, Popen
+from urllib.parse import urlsplit
 
 from packaging.version import InvalidVersion, Version
 from scrapy.utils.misc import load_object
 from twisted.web import resource
 
 from scrapyd.config import Config
-from scrapyd.spiderqueue import SqliteSpiderQueue
 from scrapyd.sqlite import JsonSqliteDict
 
 
@@ -55,14 +55,17 @@ class UtilsCache:
 
 def get_spider_queues(config):
     """Return a dict of Spider Queues keyed by project name"""
-    dbsdir = config.get('dbs_dir', 'dbs')
-    if not os.path.exists(dbsdir):
-        os.makedirs(dbsdir)
-    d = {}
-    for project in get_project_list(config):
-        dbpath = os.path.join(dbsdir, '%s.db' % project)
-        d[project] = SqliteSpiderQueue(dbpath)
-    return d
+    spiderqueue = load_object(config.get('spiderqueue', 'scrapyd.spiderqueue.SqliteSpiderQueue'))
+    return {project: spiderqueue(config, project) for project in get_project_list(config)}
+
+
+def sqlite_connection_string(config, database):
+    dbs_dir = config.get('dbs_dir', 'dbs')
+    if dbs_dir == ':memory:' or urlsplit(dbs_dir).scheme:
+        return dbs_dir
+    if not os.path.exists(dbs_dir):
+        os.makedirs(dbs_dir)
+    return os.path.join(dbs_dir, f'{database}.db')
 
 
 def get_project_list(config):


Test patch:
diff --git a/scrapyd/tests/test_spiderqueue.py b/scrapyd/tests/test_spiderqueue.py
index 69e1905..5f86e5a 100644
--- a/scrapyd/tests/test_spiderqueue.py
+++ b/scrapyd/tests/test_spiderqueue.py
@@ -3,6 +3,7 @@ from twisted.trial import unittest
 from zope.interface.verify import verifyObject
 
 from scrapyd import spiderqueue
+from scrapyd.config import Config
 from scrapyd.interfaces import ISpiderQueue
 
 
@@ -11,7 +12,7 @@ class SpiderQueueTest(unittest.TestCase):
     """
 
     def setUp(self):
-        self.q = spiderqueue.SqliteSpiderQueue(':memory:')
+        self.q = spiderqueue.SqliteSpiderQueue(Config(values={'dbs_dir': ':memory:'}), 'quotesbot')
         self.name = 'spider1'
         self.priority = 5
         self.args = {