Actual configuration in préprod

<resource type="javax.sql.DataSource"  

          name="jdbc/customer02"

          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

          driverClassName="org.postgresql.Driver"

          url="jdbc:postgresql://RTDKZ1PQL40.PREPROD.ORG:60902/customer02"

          username="customer02_web"

          password="xxxxx"

          initialSize="2"

          maxActive="20"

          maxIdle="10"

          minIdle="2"     

          maxWait="30000"

          validationQuery="select 1"

          minEvictableIdleTimeMillis="3600000"

          timeBetweenEvictionRunsMillis="1800000"

          numTestsPerEvictionRun="10"

    initSQL="SET application_name TO 'TC_customer02'"

/>

Actual configuration in production

<resource auth="Container" name="jdbc/customer01"  

    type="javax.sql.DataSource"

    driverClassName="org.postgresql.Driver"

    maxActive="20" maxIdle="4" maxWait="30000" password="xxxxx"

    url="jdbc:postgresql://rtdka1pql51:60901/customer01" username="customer01_web"/>

Eléments à ajouter / modifier, à valider par un test de charge

validationQuery="select 1"

validationInterval="30000"  → validationQuery execute time 30

maxAge="10800000" -- 3hours

testWhileIdle="true"

testOnBorrow="false"

rollbackOnReturn="true"

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"  → DBCP upgrade to DataSourceFactory → DB pool monitoring in Zabbix (Pool Size & Pool size busy)

timeBetweenEvictionRunsMillis="180000"

minEvictableIdleTimeMillis="600000" ## Timeout sur la session si Idle → 10mins idel then tomcat will stop it

numTestsPerEvictionRun="20" ## Number of pool size

jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementCache(prepared=true,max=50)"

jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementCache(prepared=true,max=50);org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=30)" ## A tuner, timeout sur internet uniquement

#Modify configuration files #

sed -i 's/type="javax.sql.DataSource"/type="javax.sql.DataSource"\n\t\t\tfactory="org.apache.tomcat.jdbc.pool.DataSourceFactory"/g' */conf/datasource.xml

sed -i 's/minEvictableIdleTimeMillis="3600000"/minEvictableIdleTimeMillis="600000"/g' */conf/datasource.xml

sed -i 's/timeBetweenEvictionRunsMillis="1800000"/timeBetweenEvictionRunsMillis="180000"/g' */conf/datasource.xml

sed -i 's/numTestsPerEvictionRun="10"/numTestsPerEvictionRun="20"/g' */conf/datasource.xml

sed -i 's/validationQuery="select 1"$/validationQuery="select 1" validationInterval="30000"/g' */conf/datasource.xml

sed -i 's/validationQuery="select 1 from dual"$/validationQuery="select 1 from dual" validationInterval="30000"/g' */conf/datasource.xml

grep 'maxThreads="400" ' /opt/tomcat-servers/MYDKT03/conf/server.xml

sed -i 's/maxThreads="400"/maxThreads="400" enableLookups="false" connectionTimeout="600000 " /g' /opt/tomcat-servers/*/conf/server.xml

vim /opt/tomcat-servers/MYDKT02/conf/server.xml

/etc/init.d/TC_MYDKT02 restart

tail -n 30 -f /opt/tomcat-servers/MYDKT02/logs/catalina.out

Possibilité d'écrire en JMX les requêtes longues

Ajouter le connectionState

https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#org.apache.tomcat.jdbc.pool.interceptor.ConnectionState

Mise en place d'un killer de transaction

Mettre en place dans le gestionnaire de pool un temps maxi

Puko - Apache

####Modification des httpd.conf

Sur le www50

Ajout du paramétre TraceEnable Off

Ajout du Mod:  #Compression by Apache

SetOutputFilter DEFLATE

DeflateCompressionLevel 9

AddOutputFilterByType DEFLATE text/plain

AddOutputFilterByType DEFLATE text/xml

AddOutputFilterByType DEFLATE text/html

AddOutputFilterByType DEFLATE text/css

AddOutputFilterByType DEFLATE text/javascript

AddOutputFilterByType DEFLATE application/xhtml+xml

AddOutputFilterByType DEFLATE application/xml

AddOutputFilterByType DEFLATE application/x-javascript

AddOutputFilterByType DEFLATE application/json

 # Don't compress images

  SetEnvIfNoCase Request_URI .(?:gif|jpe?g|jpg|ico|png)  no-gzip dont-vary

# Don't compress PDFs

  SetEnvIfNoCase Request_URI .pdf no-gzip dont-vary

# Pour les proxies

Header append Vary User-Agent env=!dont-vary

Sur le www51

Ajout du paramétre TraceEnable Off

Sur le www53     ---> Redémarrage à 15h05 de l'apache

Modification du KeepAlive timeout de 15 à la valeur 5

Modification du paramétre RewriteRule .* - [F] (où il manquait le -)

Ajout du Mod:   (la meme que sur le 50)

####Modification des sysctl.conf

Sur les serveur PQL59, TOM42, TOM43

rajout des paramètres :

net.core.netdev_max_backlog=2500

net.ipv4.tcp_max_syn_backlog=4096

net.core.somaxconn=2500

net.core.rmem_max=8388608

net.core.wmem_max=8388608

net.ipv4.tcp_no_metrics_save=1

net.ipv4.tcp_keepalive_probes=2

net.ipv4.tcp_keepalive_intvl=20

net.ipv4.tcp_fin_timeout=30

net.ipv4.tcp_keepalive_time=300

net.ipv4.tcp_tw_recycle=1

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_sack=1

net.ipv4.tcp_dsack=0

#Ephemeral ports

net.ipv4.ip_local_port_range=10000 65000

### Discourage Linux from swapping idle processes to disk (default = 60)

vm.swappiness = 10 → SWAP of Memory

### Disable TCP slow start on idle connections

net.ipv4.tcp_slow_start_after_idle = 0

Tomcat

Vérification des pools (timeout)

Audit postgres -- Vincent MOREAU

Ajout de la contribution pg_stat_statements

# postgresql.conf

shared_preload_libraries = 'pg_stat_statements'  → Lib need to be enable like this way

track_activity_query_size = 10240

pg_stat_statements.max = 1000

pg_stat_statements.track = all

create extension pg_stat_statements;

dans les bases customer01, customer02 et custEx

pgBadger

query running longer than 500seconds