[MapServer-users] Mapcache - postgresql dimensions causing HUGE number of open db connections

[Tim Kempisty]

Mapcache folks,

This is a technical question about the Mapcache connection pool and how postgreql dimensions are implemented.

I have a big mapcache WMS with several hundred layers defined.  Most of them have a postgresql dimension that reports back a list of available timestamps for these layers.  When we are under load, I end up with a couple thousand database connections all coming from Mapcache.  They're only lightweight dimension queries... it's just a LOT of connections hanging open.  I'm looking for the upper bound on the number of these connections.

Looking at the Mapcache source code...  I see a hard coded max_list_size  = 10 in connection_pool.c.   I assume that means any one mapcache process/thread is allowed to keep up to ten network connections open at one time.

dimension_pg.c  seems to get its connections from this pool. Other connections use this pool though, right?   Like the source URLs?

So if I have Mapcache running as an Apache module, and if Apache can launch up to 256 threads...  I could be on the hook for up to 2560 connections sitting open (until the threads die off / time out.)  Is my understanding correct?

It also looks like dimension_pg uses prepared statements on the db server.  That would probably break a connection pooler like PgBouncer.  So my options for managing the number of postgresql connections are: 

1.  Setting an obscene max_connections limit in postgresql.conf, allowing my mapcache role to consume a lot of them.
2.  Reducing the number of Apache threads in mpm_event   (At the risk of impacting general Apache performance)
3.  Using the fastcgi version of Mapcache, and limiting the number of mapcache fcgi processes
4.  Reducing max_list_size in connection_pool.c, which might impact performance.

Am I on the right track here?

-Tim