Repository: sqlfluff/sqlfluff

Issue:
Number of processes configurable in .sqlfluff
Being able to set the number of processes to run with in .sqlfluff might be useful to avoid having to pass it in the CLI every time.

Discussion:
NiallRees: What would you think about setting this value to 0 meaning 'use all available CPUs'?
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#3662](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (b227382) into [main](https://codecov.io/gh/sqlfluff/sqlfluff/commit/1977bc938bc3da18f606abeb0a4ef3dc786952bc?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (1977bc9) will **decrease** coverage by `0.01%`.
> The diff coverage is `85.71%`.

```diff
@@             Coverage Diff             @@
##              main    #3662      +/-   ##
===========================================
- Coverage   100.00%   99.98%   -0.02%     
===========================================
  Files          176      176              
  Lines        13348    13360      +12     
===========================================
+ Hits         13348    13358      +10     
- Misses           0        2       +2     
```


| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/cli/commands.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NsaS9jb21tYW5kcy5weQ==) | `100.00% <ø> (ø)` | |
| [src/sqlfluff/cli/formatters.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NsaS9mb3JtYXR0ZXJzLnB5) | `99.57% <66.66%> (-0.43%)` | :arrow_down: |
| [src/sqlfluff/core/linter/runner.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvbGludGVyL3J1bm5lci5weQ==) | `98.94% <83.33%> (-1.06%)` | :arrow_down: |
| [src/sqlfluff/core/linter/linter.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvbGludGVyL2xpbnRlci5weQ==) | `100.00% <100.00%> (ø)` | |
| [src/sqlfluff/core/parser/segments/base.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvcGFyc2VyL3NlZ21lbnRzL2Jhc2UucHk=) | `100.00% <0.00%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [1977bc9...b227382](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3662?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).

tunetheweb: > What would you think about setting this value to 0 meaning 'use all available CPUs'?

I’d prefer defaulting to -1 so we don’t freeze machines.

Btw, on a related question is the minimum set to 1 so 1-core machines still actually run 😄
alanmcruickshank: > What would you think about setting this value to 0 meaning 'use all available CPUs'?

> Btw, on a related question is the minimum set to 1 so 1-core machines still actually run 😄

Good point - that was my intent - but I totally didn't do that on first pass. Both should be done now, also some test cases to make sure.

> I’d prefer defaulting to -1 so we don’t freeze machines.

I'm less sure about defaulting to multi-processing. I reckon for now I'm going to get this merged without changing the default behaviour for existing users. That would be an easy change to make after the fact, but I feel like it also complicates the rest of the test suite too [on top of the behaviour change for users].
tunetheweb: > > I’d prefer defaulting to -1 so we don’t freeze machines.
> 
> I'm less sure about defaulting to multi-processing. I reckon for now I'm going to get this merged without changing the default behaviour for existing users. That would be an easy change to make after the fact, but I feel like it also complicates the rest of the test suite too [on top of the behaviour change for users].

Ah think I misunderstood @NiallRees 's request "What would you think about setting this value to 0 meaning 'use all available CPUs'?" to mean setting this as the default. Didn't realise he was just asking to support `0` with that definition.

So all cool to leave default as is.

Patch:
diff --git a/src/sqlfluff/cli/commands.py b/src/sqlfluff/cli/commands.py
index f87fe47df..63c07d5a4 100644
--- a/src/sqlfluff/cli/commands.py
+++ b/src/sqlfluff/cli/commands.py
@@ -486,8 +486,12 @@ def dump_file_payload(filename: Optional[str], payload: str):
     "-p",
     "--processes",
     type=int,
-    default=1,
-    help="The number of parallel processes to run.",
+    default=None,
+    help=(
+        "The number of parallel processes to run. Positive numbers work as "
+        "expected. Zero and negative numbers will work as number_of_cpus - "
+        "number. e.g  -1 means all cpus except one. 0 means all cpus."
+    ),
 )
 @click.option(
     "--disable_progress_bar",
@@ -497,7 +501,6 @@ def dump_file_payload(filename: Optional[str], payload: str):
 @click.argument("paths", nargs=-1, type=click.Path(allow_dash=True))
 def lint(
     paths: Tuple[str],
-    processes: int,
     format: str,
     write_output: Optional[str],
     annotation_level: str,
@@ -505,6 +508,7 @@ def lint(
     disregard_sqlfluffignores: bool,
     logger: Optional[logging.Logger] = None,
     bench: bool = False,
+    processes: Optional[int] = None,
     disable_progress_bar: Optional[bool] = False,
     extra_config_path: Optional[str] = None,
     ignore_local_config: bool = False,
@@ -675,8 +679,12 @@ def do_fixes(lnt, result, formatter=None, **kwargs):
     "-p",
     "--processes",
     type=int,
-    default=1,
-    help="The number of parallel processes to run.",
+    default=None,
+    help=(
+        "The number of parallel processes to run. Positive numbers work as "
+        "expected. Zero and negative numbers will work as number_of_cpus - "
+        "number. e.g  -1 means all cpus except one. 0 means all cpus."
+    ),
 )
 @click.option(
     "--disable_progress_bar",
@@ -705,10 +713,10 @@ def do_fixes(lnt, result, formatter=None, **kwargs):
 def fix(
     force: bool,
     paths: Tuple[str],
-    processes: int,
     bench: bool = False,
     fixed_suffix: str = "",
     logger: Optional[logging.Logger] = None,
+    processes: Optional[int] = None,
     disable_progress_bar: Optional[bool] = False,
     extra_config_path: Optional[str] = None,
     ignore_local_config: bool = False,
diff --git a/src/sqlfluff/cli/formatters.py b/src/sqlfluff/cli/formatters.py
index 45eae7e04..ed76126ad 100644
--- a/src/sqlfluff/cli/formatters.py
+++ b/src/sqlfluff/cli/formatters.py
@@ -181,6 +181,14 @@ class OutputStreamFormatter:
             f"=== [{self.colorize(templater, Color.lightgrey)}] {message}"
         )  # pragma: no cover
 
+    def dispatch_processing_header(self, processes: int) -> None:
+        """Dispatch the header displayed before linting."""
+        if self._verbosity > 0:
+            self._dispatch(  # pragma: no cover
+                f"{self.colorize('effective configured processes: ', Color.lightgrey)} "
+                f"{processes}"
+            )
+
     def dispatch_dialect_warning(self, dialect) -> None:
         """Dispatch a warning for dialects."""
         self._dispatch(self.format_dialect_warning(dialect))  # pragma: no cover
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index ae2bbbbd7..72cfe79ca 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -39,6 +39,11 @@ fix_even_unparsable = False
 # and warns the user what has happened.
 # Set this to 0 to disable.
 large_file_skip_char_limit = 20000
+# CPU processes to use while linting.
+# If positive, just implies number of processes.
+# If negative or zero, implies number_of_cpus - specifed_number.
+# e.g. -1 means use all processors but one. 0  means all cpus.
+processes = 1
 
 [sqlfluff:indentation]
 # See https://docs.sqlfluff.com/en/stable/indentation.html
diff --git a/src/sqlfluff/core/linter/linter.py b/src/sqlfluff/core/linter/linter.py
index f55f0e8f4..2d8a8f7c7 100644
--- a/src/sqlfluff/core/linter/linter.py
+++ b/src/sqlfluff/core/linter/linter.py
@@ -1066,7 +1066,7 @@ class Linter:
         fix: bool = False,
         ignore_non_existent_files: bool = False,
         ignore_files: bool = True,
-        processes: int = 1,
+        processes: Optional[int] = None,
     ) -> LintedDir:
         """Lint a path."""
         linted_path = LintedDir(path)
@@ -1080,16 +1080,22 @@ class Linter:
             )
         )
 
+        if processes is None:
+            processes = self.config.get("processes", default=1)
+
         # to avoid circular import
         from sqlfluff.core.linter.runner import get_runner
 
-        runner = get_runner(
+        runner, effective_processes = get_runner(
             self,
             self.config,
             processes=processes,
             allow_process_parallelism=self.allow_process_parallelism,
         )
 
+        if self.formatter and effective_processes != 1:
+            self.formatter.dispatch_processing_header(effective_processes)
+
         # Show files progress bar only when there is more than one.
         files_count = len(fnames)
         progress_bar_files = tqdm(
@@ -1124,7 +1130,7 @@ class Linter:
         fix: bool = False,
         ignore_non_existent_files: bool = False,
         ignore_files: bool = True,
-        processes: int = 1,
+        processes: Optional[int] = None,
     ) -> LintingResult:
         """Lint an iterable of paths."""
         paths_count = len(paths)
diff --git a/src/sqlfluff/core/linter/runner.py b/src/sqlfluff/core/linter/runner.py
index cef7daba2..d397c7faa 100644
--- a/src/sqlfluff/core/linter/runner.py
+++ b/src/sqlfluff/core/linter/runner.py
@@ -10,6 +10,7 @@ from abc import ABC
 import bdb
 import functools
 import logging
+import multiprocessing
 import multiprocessing.dummy
 import signal
 import sys
@@ -227,15 +228,29 @@ def get_runner(
     config: FluffConfig,
     processes: int,
     allow_process_parallelism: bool = True,
-) -> BaseRunner:
-    """Generate a runner instance based on parallel and system configuration."""
+) -> Tuple[BaseRunner, int]:
+    """Generate a runner instance based on parallel and system configuration.
+
+    The processes argument can be positive or negative.
+    - If positive, the integer is interpreted as the number of processes.
+    - If negative or zero, the integer is interpreted as number_of_cpus - processes.
+
+    e.g.
+    -1 = all cpus but one.
+    0 = all cpus
+    1 = 1 cpu
+
+    """
+    if processes <= 0:
+        processes = max(multiprocessing.cpu_count() + processes, 1)
+
     if processes > 1:
         # Process parallelism isn't really supported during testing
         # so this flag allows us to fall back to a threaded runner
         # in those cases.
         if allow_process_parallelism:
-            return MultiProcessRunner(linter, config, processes=processes)
+            return MultiProcessRunner(linter, config, processes=processes), processes
         else:
-            return MultiThreadRunner(linter, config, processes=processes)
+            return MultiThreadRunner(linter, config, processes=processes), processes
     else:
-        return SequentialRunner(linter, config)
+        return SequentialRunner(linter, config), processes


Test patch:
diff --git a/test/core/linter_test.py b/test/core/linter_test.py
index bd1a667d1..5831bfcb4 100644
--- a/test/core/linter_test.py
+++ b/test/core/linter_test.py
@@ -14,6 +14,7 @@ from sqlfluff.core.errors import SQLLexError, SQLBaseError, SQLLintError, SQLPar
 from sqlfluff.cli.formatters import OutputStreamFormatter
 from sqlfluff.cli.outputstream import make_output_stream
 from sqlfluff.core.linter import LintingResult, NoQaDirective
+from sqlfluff.core.linter.runner import get_runner
 import sqlfluff.core.linter as linter
 from sqlfluff.core.parser import GreedyUntil, Ref
 from sqlfluff.core.templaters import TemplatedFile
@@ -289,6 +290,36 @@ def test_lint_path_parallel_wrapper_exception(patched_lint):
             result.reraise()
 
 
+@pytest.mark.parametrize(
+    "mock_cpu,in_processes,exp_processes",
+    [
+        # Make the mocked cpu count a really high value which is
+        # unlikely to collide with the real value. We can then
+        # test all the different combos.
+        (512, 1, 1),
+        (512, 0, 512),
+        (512, -12, 500),
+        (512, 5, 5),
+        # Check that we can't go lower than 1 in a 1 cpu case
+        (1, -1, 1),
+    ],
+)
+@patch("multiprocessing.cpu_count")
+def test__linter__get_runner_processes(
+    patched_cpu_count, mock_cpu, in_processes, exp_processes
+):
+    """Test that get_runner handles processes correctly."""
+    # Make the mocked cpu count a really high value which is
+    # unlikely to collide with the real value.
+    patched_cpu_count.return_value = mock_cpu
+    _, return_processes = get_runner(
+        linter=Linter(),
+        config=FluffConfig(overrides={"dialect": "ansi"}),
+        processes=in_processes,
+    )
+    assert return_processes == exp_processes
+
+
 @patch("sqlfluff.core.linter.runner.linter_logger")
 @patch("sqlfluff.core.linter.Linter.lint_rendered")
 def test__linter__linting_unexpected_error_handled_gracefully(