Scanner uses too many connections

[wiz78]

It seems like it exceeds the connection limit and dies. There should be a configurable limit to the number of connections it's allowed to create, and it should close them as soon as they're not needed anymore. Here's an example stacktrace (db is postgresql) after scanning 239 files:

Traceback (most recent call last):
  File "/usr/local/bin/supysonic-cli", line 11, in <module>
    load_entry_point('supysonic==0.4.1', 'console_scripts', 'supysonic-cli')()
  File "/usr/local/lib/python3.6/dist-packages/supysonic/cli.py", line 408, in main
    cli = SupysonicCLI(config)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/cli.py", line 91, in __init__
    self.__daemon = DaemonClient(config.DAEMON["socket"])
  File "/usr/local/lib/python3.6/dist-packages/supysonic/daemon/client.py", line 76, in __init__
    self.__key = get_secret_key("daemon_key")
  File "<string>", line 2, in get_secret_key
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 528, in new_func
    result = func(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/utils.py", line 23, in get_secret_key
    key = b64decode(Meta[keyname].value)
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 3939, in __getitem__
    return entity._find_one_(kwargs)
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 4058, in _find_one_
    if obj is None: obj = entity._find_in_db_(avdict, unique, for_update, nowait, skip_locked)
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 4117, in _find_in_db_
    cursor = database._exec_sql(sql, arguments)
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 938, in _exec_sql
    connection = cache.prepare_connection_for_query_execution()
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 1797, in prepare_connection_for_query_execution
    if connection is None: connection = cache.connect()
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 1761, in connect
    connection, is_new_connection = provider.connect()
  File "<string>", line 2, in connect
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/dbapiprovider.py", line 61, in wrap_dbapi_exceptions
    raise OperationalError(e)
pony.orm.dbapiprovider.OperationalError: FATALE:  i rimanenti slot di connessione sono riservati a connessioni di superutenti non di replica

[spl0k]

The error you posted happens after the scan? By the look of the stack trace it seems to be raised as soon as you invoke the CLI.
Are you running the daemon? Did you start the scan from the CLI or from the web interface?
I'll also be interested to know on which system is running your Postgres cluster(s) and if you set any specific configuration on it.

The connections are handled by PonyORM and unfortunately there is no way to directly control how many connections it can create.

[wiz78]

it appeared after scanning 239 files: at that point it crashed and stopped.

I was using the CLI, without the daemon.

Postgresql is running on Ubuntu 18.04 LTS, like supysonic. I have max_connections = 16 in my postgresql.conf.

I'm not sure how many files it would be able to scan without exhausting more connections. If I set max_connections to 256, scan dies after 251 files due to:

UnicodeEncodeError: 'utf-8' codec can't encode character '\udce0' in position 23: surrogates not allowed

Traceback (most recent call last):
  File "/usr/local/bin/supysonic-cli", line 11, in <module>
    load_entry_point('supysonic==0.4.1', 'console_scripts', 'supysonic-cli')()
  File "/usr/local/lib/python3.6/dist-packages/supysonic/cli.py", line 410, in main
    cli.onecmd(" ".join(pipes.quote(arg) for arg in sys.argv[1:]))
  File "/usr/lib/python3.6/cmd.py", line 217, in onecmd
    return func(arg)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/cli.py", line 71, in method
    **{key: vars(args)[key] for key in vars(args) if key != "action"}
  File "/usr/local/lib/python3.6/dist-packages/supysonic/cli.py", line 224, in folder_scan
    self.__folder_scan_foreground(folders, force)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/cli.py", line 278, in __folder_scan_foreground
    scanner.run()
  File "/usr/local/lib/python3.6/dist-packages/supysonic/scanner.py", line 113, in run
    self.__scan_folder(folder)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/scanner.py", line 142, in __scan_folder
    self.scan_file(entry)
  File "<string>", line 2, in scan_file
  File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 528, in new_func
    result = func(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/scanner.py", line 213, in scan_file
    tr = Track.get(path=path)
  File "/usr/local/lib/python3.6/dist-packages/supysonic/db.py", line 63, in get
    kwargs["_path_hash"] = sha1(path.encode("utf-8")).digest()
UnicodeEncodeError: 'utf-8' codec can't encode character '\udce0' in position 23: surrogates not allowed

[spl0k]

Hi!
I took another look at this too-many-connections problem, and up until now the CLI didn't use more than 2 connections. This has been recently (acb6b77) reduced to only one connection.
This has been tested by setting max_connections on Postgres to no more than one plus the number of superuser-reserved connections.

The scanning itself only uses one connection (or reuse an unused one since they are pooled). Running the daemon might use, under certain circumstances, a few connections (since it's threaded) but it shouldn't spawn an indecent number. And finally, a new connection might be opened by the webserver each time a new request arrives (and will be closed thereafter).