#3185: Limit connection limit of moved trac instances
----------------------+--------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: new
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin | Keywords:
----------------------+--------------------------------------
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.
--
Ticket URL: <https://trac.osgeo.org/osgeo/ticket/3185>
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
----------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin | Resolution: fixed
Keywords: |
----------------------+---------------------------------------
Changes (by robe):
* status: new => closed
* resolution: => fixed
Comment:
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)
}}}
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
----------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin | Resolution:
Keywords: |
----------------------+---------------------------------------
Changes (by Jeff McKenna):
* status: closed => reopened
* resolution: fixed =>
Comment:
@robe would you mind increasing the connection limit for the 'trac_gdal'
instance? (the [BuildHints – GDAL 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" )
}}}
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
----------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin | Resolution:
Keywords: |
----------------------+---------------------------------------
Comment (by strk):
Should we use pgbouncer ? https://www.pgbouncer.org/
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
----------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin | Resolution:
Keywords: |
----------------------+---------------------------------------
Comment (by strk):
I've run this:
> ALTER DATABASE trac_gdal WITH CONNECTION LIMIT = 10;
Is that enough, Jeff ?
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution:
Keywords: |
---------------------------+---------------------------------------
Changes (by strk):
* component: SysAdmin => SysAdmin/Trac
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution:
Keywords: |
---------------------------+---------------------------------------
Comment (by strk):
See #2176 for pooled database connections
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution:
Keywords: |
---------------------------+---------------------------------------
Comment (by strk):
Assuming raising to 10 was good enough.
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Changes (by strk):
* status: reopened => closed
* resolution: => fixed
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Comment (by Jeff McKenna):
thanks @strk 10 seems to work well. thanks again.
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution:
Keywords: |
---------------------------+---------------------------------------
Changes (by jng):
* status: closed => reopened
* resolution: fixed =>
Comment:
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?
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Changes (by robe):
* status: reopened => closed
* resolution: => fixed
Comment:
I've upped the limit of trac_fdo to 30. trac_mapguide I had already upped
to 30 as part of #3244
Replying to [comment:3 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.
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution:
Keywords: |
---------------------------+---------------------------------------
Changes (by Jeff McKenna):
* status: closed => reopened
* resolution: fixed =>
Comment:
@strk can you also up the limit for "trac_gdal" to 30? (the error is
back).
--
Ticket URL: <https://trac.osgeo.org/osgeo/ticket/3185#comment:12>
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: reopened
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution:
Keywords: |
---------------------------+---------------------------------------
Comment (by strk):
Regina the ticket for pgbouncer is #2176 as written in comment:6
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Changes (by strk):
* status: reopened => closed
* resolution: => fixed
Comment:
Jeff, limit raised to 30:
{{{
trac_gdal=# ALTER DATABASE trac_gdal WITH CONNECTION LIMIT = 30;
ALTER DATABASE
}}}
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Comment (by Jeff McKenna):
thanks again @strk, smooth, works well.
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Comment (by strk):
I've also raised trac_mapguide to 30 as the log was full of TimeoutErrors
about it
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Comment (by robe):
Replying to [comment:16 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.
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.
#3185: Limit connection limit of moved trac instances
---------------------------+---------------------------------------
Reporter: robe | Owner: sac-tickets@…
Type: task | Status: closed
Priority: normal | Milestone: Sysadmin Contract 2024-I
Component: SysAdmin/Trac | Resolution: fixed
Keywords: |
---------------------------+---------------------------------------
Comment (by strk):
For the record, this is the query you can use to check current limit for
databases:
> select datname,datconnlimit from pg_database;
--
Ticket URL: <#3185 (Limit connection limit of moved trac instances) – OSGeo;
OSGeo <Gter - OSGeo;
OSGeo committee and general foundation issue tracker.