Limit connection limit of moved trac instances

[robe]

trac appears to be suffering from some bot attacks of late eating up connections.

I think many of these strk observed were to sites no longer used.

For now, I'm going to limit the number of connections of these defunct site databases to 5 connections max.

[robe]

ran this

ALTER DATABASE trac_csmap WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_fdo WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_gdal WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_mapserver WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_qgis WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_metacrs WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_proj4j WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_proj4js WITH CONNECTION LIMIT = 5;
ALTER DATABASE trac_geos WITH CONNECTION LIMIT = 5;

there are others but will start with these. At the moment there seem to be no active connections and inactive connections are as follows:

SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;
    datname     | count
----------------+-------
                |     5
 gitea          |     2
 trac_qgis      |     5
 trac_mapguide  |     4
 postgres       |     1
 trac_osgeolive |     3
 trac_postgis   |    10
 trac_mapserver |     2
 trac_gdal      |     5
 trac_osgeo     |    13
 trac_grass     |     1
 trac_csmap     |     1
 trac_osgeo4w   |     2
(13 rows)

[Jeff McKenna]

@robe would you mind increasing the connection limit for the 'trac_gdal' instance? (the ​BuildHints wiki pages are still actively maintained, read, and modified, by GDAL's packaging community - really, these old steps are a lifeline for packagers).

Almost every page load gives the error:

Unable to get database connection within 0 seconds. (OperationalError: FATAL: too many connections for database "trac_gdal" )

[strk]

Should we use pgbouncer ? ​https://www.pgbouncer.org/

[strk]

I've run this:

ALTER DATABASE trac_gdal WITH CONNECTION LIMIT = 10;

Is that enough, Jeff ?

[strk]

See #2176 for pooled database connections

[strk]

Assuming raising to 10 was good enough.

[Jeff McKenna]

thanks @strk 10 seems to work well. thanks again.

[jng]

Hi,

I am getting errors like this on the MapGuide and FDO instances (replace trac_mapguide with trac_fdo where applicable)

Trac Error

TracError: Unable to check for upgrade of trac.db.api.DatabaseManager: TimeoutError: Unable to get database connection within 0 seconds. (OperationalError: FATAL:  too many connections for database "trac_mapguide"
)

I am actively maintaining wiki content and tickets on both instances and use the timeline feature heavily to monitor commits from Autodesk for merging and the above error is appearing way too frequently.

Could we get the limits increased on these 2 instances?

[robe]

I've upped the limit of trac_fdo to 30. trac_mapguide I had already upped to 30 as part of #3244

Replying to strk:

Should we use pgbouncer ? ​https://www.pgbouncer.org/

Yah was thinking that and I thought I had added a ticket for this, but can't find it. Anyway I'll add one and detail my findings which sadly is not good.

[Jeff McKenna]

@strk can you also up the limit for "trac_gdal" to 30? (the error is back).

[strk]

Regina the ticket for pgbouncer is #2176 as written in comment:6

[strk]

Jeff, limit raised to 30:

trac_gdal=# ALTER DATABASE trac_gdal WITH CONNECTION LIMIT = 30;
ALTER DATABASE

[Jeff McKenna]

thanks again @strk, smooth, works well.

[strk]

I've also raised trac_mapguide to 30 as the log was full of TimeoutErrors about it

[robe]

Replying to strk:

I've also raised trac_mapguide to 30 as the log was full of TimeoutErrors about it

I had raised it already to 30 per #3244. Maybe we need to raise it more that 30 is not enough.