Now I've received my morning coffee, here is some further unrequested experience from both non-geoserver and geoserver tomcat instances:
I'd propose to minimize modifying the webapps/geoserver folder: It makes upgrades hard. tomcat copies webapps/whatever/META-INF/context.xml to conf/catalina/localhost/whatever.xml . I'd advise to: * not have a context.xml file: so the file under conf is never overwritten * place the actual context file under conf/catalina/localhost/geoserver.xml future upgrades of geoserver can then simply replace/unpack the war and add extensions. There is an alternative where you integrate the context.xml content directly in server.xml, but again, I'd advise against it. There is a third alternative where you define a global resource in server.xml, but you'll still need a resourcelink in the context.xml from the webapp or host. --- There are many conflicting advices on how to set up a connection pool. Over the years, I've settled on: *initialSize: At least 1, so application startup logs database failures. On my server, I can open ~5000 additional connections in a second, so I saw no point in having more. *minIdle: Can sometimes be 0, you'd be surprised how many tiny connection pools are around, especially at night. *maxActive: Be sure not to overload your server. *defaultAutoCommit/defaultReadOnly/defaultTransactionIsolation; Be aware of the defaults of your driver, or define them explicitly. They can vary even between driver versions for some DB vendors. Postgres generally behaves nicely *test*: I tend to set these to true, validation is low overhead and fixes a lot of trouble, e.g. firewalls disconnectiong the DB without telling anyone . *validationQuery: For postgres, I use SELECT now(); *initSQL: You can set NLS settings here, and maybe add some info telling the DBA this connection comes from geoserver. For postgres, both can also be done in the URL, of course. -- I have an NFS share with my data_dir , shared between multiple production nodes. This works fine. If you want to copy data_dir between prod and dev, there is: * security/masterpw/default/passwd where you'll have to choose: Either keep them identical and have a risk of dev accidentally connecting to or leaking credentials for prod, or have them different, after which every credential has to be updated. * the gwc folder that can grow huge and be irrelevant. * some things in global.xml, like proxyBaseUrl In the end, I decided to script copying only specific folders between dev and prod data dirs, where the script also swaps out URLs and passwords where needed. Then curl a reload REST request to all relevant nodes. Have fun, Hans Hans Yperman Department IT [cid:41345a34-c392-4ca8-a0a8-2e742ead50da] Vlaams Instituut voor de Zee vzw InnovOcean Campus, Jacobsenstraat 1 8400 Oostende, België ☎+32 (0) 59 33 61 13 📧 hans.yper...@vliz.be<mailto:hans.yper...@vliz.be> www.vliz.be<http://www.vliz.be> ________________________________ Van: VANDENSCHRICK, Grégoire <gvandenschrick@urban.brussels> Verzonden: woensdag 11 januari 2023 8:05 Aan: Hans Yperman <hans.yper...@vliz.be> CC: geoserver-users@lists.sourceforge.net <geoserver-users@lists.sourceforge.net> Onderwerp: RE: replacing postgis stores by postgis JNDI stores, bottleneck effect? Hi, thanks for your input. What I'll test is to put the context.xml in the webapps/geoserver/META-INF/ so I don't have to mess with the postgresql jar place (inside geoserver WEB-INF/lib, or inside Tomcat directly). It seems also that I so keep the possibility to have Postgis stores and postgis JNDI stores at the same time, letting me pass some stores to JDNI, and assess the impact of it on the accessibility of these datasets. My aim is to have the possibility to simply copy-paste the data_dir between environments without having to change anything inside it (datastore.xml mainly in this context (no pun intended)). I have check how many connections my different DBs supported in each environement and tailored the context.xml taking that into account. This showed me also that the potential maximum number of connections all the stores could open is way above what the DB can handle, so definitely, this total max number is never reached 🙂. Anyway, I'll report here if I encounter difficulties. Thanks again, Greg ________________________________ De : Hans Yperman <hans.yper...@vliz.be> Envoyé : lundi 9 janvier 2023 09:21 À : VANDENSCHRICK, Grégoire <gvandenschrick@urban.brussels>; geoserver-users@lists.sourceforge.net <geoserver-users@lists.sourceforge.net> Objet : Re: replacing postgis stores by postgis JNDI stores, bottleneck effect? Hi, I wouldn't worry about the JNDI datasource being a bottleneck. Contention happens only when requesting or returning connections, the real action happens inside a connection object and thus uncontended. I've seen tomcats (non-geoserver) having 1000 or more connections in a pool, without sweating. In theory, you should indeed sum all individual maxActives to 1 big maxActive, but you'd be really unlucky if all connection pools are at full capacity at the same time. Hence, I'd expect you'd be using less connections in total. Be sure not to use more connections than your DB can deliver. For postgres, you can check the maximum with e.g.: SELECT * FROM pg_settings WHERE name ILIKE 'max_connections'; A downside with JNDI is that you can't change anything or clean up connections without restarting the whole geoserver instance. That's why I personally decided against it. I recently migrated a geoserver on linux and had to rename all connections. I had a migration script that contained things like this: find YOUR_GEOSERVER_DATA_DIR/workspaces -name 'datastore.xml'|while read ds; do echo "Processing $ds"; #Always validate connections xmlstarlet ed --inplace -d '//*[@key="validate connections"]' -a '//entry[last()]' -t elem -n entry -v true -a '//entry[last()]' -t attr -n key -v 'validate connections' $ds; sed -ri -e 's/>olddbname.domain.be</>newdbname.domain.be</' -e 's/>olddbname2.domain.be</>newdbname2.domain.be</' -e 's/>olddbname3.domain.be</>newdbname3.domain.be</' $ds done; Of course, this needs severe adaptation for your situation, only run when your geoserver is not running, and make sure xmlstarlet is installed Hans Yperman Department IT [cid:bec99dce-4562-4a3c-ade3-0a8114954651] Vlaams Instituut voor de Zee vzw InnovOcean Campus, Jacobsenstraat 1 8400 Oostende, België ☎+32 (0) 59 33 61 13 📧 hans.yper...@vliz.be<mailto:hans.yper...@vliz.be> www.vliz.be<http://www.vliz.be> ________________________________ Van: VANDENSCHRICK, Grégoire <gvandenschrick@urban.brussels> Verzonden: woensdag 4 januari 2023 13:12 Aan: geoserver-users@lists.sourceforge.net <geoserver-users@lists.sourceforge.net> Onderwerp: [Geoserver-users] replacing postgis stores by postgis JNDI stores, bottleneck effect? Hi, We have 50 postgis stores in our geoserver, all set with the default parameters. Now, to avoid having to change IP address of postgresql databases in all the datastore.xml files (currently done with python, when deploying the data directory from dev to staging or production (different environments, different databases), we think to adopt a different strategy: To define once and for all a postgresql resource (named jdbc/databrugis) in a context.xml file, and pass all the 50 stores from postgis type to postgis (JNDI) type using that resource (java:comp/env/jdbc/databrugis). My question is this: Does this will provoke a bottleneck? Having 50 stores using the same resource? Should I change some resource parameters, for instance maxActive, from 20 to let's say maybe not 20*50 but a bigger number? And will this be without effect on the DB slots usage, to have it connected from one resource only instead of 50 different stores? Thank you already for your expert insight on this 🙂. Greg
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
