Neu PHP - MySQL Konfiguration am Server für JTL Shop 5

[karabey]

Hallo,

Seit kurzem haben wir unser eigenen Server mit ein wenig Kopfschmerzen. Davor war es Managed und haben sich dafür entschieden selbst managen.

Irgendwie mag aber die PHP + MySQL Konfiguration aneinander nicht gut zusammen passen.
Latenzen beim Aufruf der Seiten, Ausfall vom PHP oder MySQL obwohl "children" auf entsprechend optimiert.
Betrieben wird JTL Shop 5.2.5 (wird bald 5.3).

Wir haben nutzen Cloudpanel mit:

• Nginx/1.21.4 (ich weis das JTL nicht supported)
• Redis
• Opcache
• Varnish
• MariaDB 10.11

Würde mich über eure Empfehlungen für PHP + MySQL freuen um die Leistung entsprechend zu optimieren.

Server:

OS: Ubuntu 22.04.5 LTS x86_64
Kernel: 5.15.0-124-generic
--------------------
CPU: AMD EPYC 9634 (8) @ 2.246GHz
CPU Usage: 3%
GPU: 00:02.0 Vendor 1234 Device 1111
Disk (/): 60G / 251G (25%)
Memory: 5133MiB / 15987MiB (32%)
Swap usage: 12%
System load: 0.32

PHP 8.3 Konfiguration:

[domain.com]
listen = 127.0.0.1:18013
user = shop5
group = shop5
listen.allowed_clients = 127.0.0.1
pm = ondemand
pm.max_children = 250
pm.process_idle_timeout = 10s
pm.max_requests = 100
listen.backlog = 65535
pm.status_path = /status
request_terminate_timeout = 7200s
rlimit_files = 131072
rlimit_core = unlimited
catch_workers_output = yes
memory_limit = 768MB
max_execution_time = 60s
max_input_time = 60s
post_max_size = 64MB
max_input_vars = 10000
upload_max_filesize = 64MB

MariaDB 10.11:

[mysqld]

### 1. Allgemeine Einstellungen
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
datadir = /home/mysql/
disable_log_bin
explicit_defaults_for_timestamp = 1
init-connect = 'SET NAMES utf8mb4'
interactive_timeout = 60
log-bin-trust-function-creators = 1
log-error = /var/log/mysql/error.log
log_queries_not_using_indexes = 1
long_query_time = 5
max_allowed_packet = 128M
max_connections = 150
max_heap_table_size = 512M
open_files_limit = 65535
pid-file = /var/run/mysqld/mysqld.pid
query_cache_size = 0
query_cache_type = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
socket = /var/run/mysqld/mysqld.sock
table_definition_cache = 2048
table_open_cache = 2048
thread_cache_size = 150
wait_timeout = 60

### 2. InnoDB Einstellungen
innodb = force
#innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 8G
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 256M
innodb_log_file_size = 2GB
innodb_stats_on_metadata = OFF

### 3. MyISAM Einstellungen
bulk_insert_buffer_size = 64M
join_buffer_size = 16M
key_buffer_size = 8M
myisam_sort_buffer_size = 128M

### 4. Temporäre und Sortierungseinstellungen
read_buffer_size = 4M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
tmp_table_size = 512M

### 5. I/O Einstellungen
innodb_io_capacity = 3000
innodb_read_io_threads = 8
#innodb_thread_concurrency = 32
innodb_write_io_threads = 8

### 6. Performance Schema Einstellungen
performance_schema = ON
performance_schema_events_statements_history_long_size = 1024
performance_schema_events_statements_history_size = 1024

### 7. Sonstige Einstellungen
skip-name-resolve = ON

Nginx Conf:

server {
  listen 80;
  listen [::]:80;
  server_name domain.com;
  return 301 https://$server_name$request_uri;
}

server {
  listen 443 ssl http2;
  listen [::]:443 ssl http2;
  server_name domain.com;
  {{root}}
  index index.php index.html;

  ############# TLS-Certificate #############
 
  {{ssl_certificate_key}}
  {{ssl_certificate}}
  # Set the SSL buffer size
  ssl_buffer_size 8k;
 
  ssl_session_cache shared:le_nginx_SSL:1m;
  ssl_session_timeout 1440m;

  ssl_protocols TLSv1.2 TLSv1.3;
  ssl_ciphers EECDH+AESGCM:EDH+AESGCM;
  ssl_ecdh_curve secp384r1;
  ssl_prefer_server_ciphers off;
 
  ssl_stapling on;
  ssl_stapling_verify on;
  resolver 1.1.1.1 1.0.0.1 valid=300s;
  resolver_timeout 5s;

  ############# LOG #############
 
  #rewrite_log on;
  #error_log /var/log/nginx/error.log error;
  {{nginx_error_log}}
  {{nginx_access_log}}
 
  ############# AUTH #############
 
  location ~ /.well-known {
      auth_basic off;
      allow all;
  }

 
  ############# Enable Brotli compression #############
 
  brotli on;
  brotli_comp_level 6;
  brotli_types text/plain text/css application/json application/javascript application/x-javascript text/xml application/xml application/xml+rss text/javascript;

  ############# GZIP #############
 
  gzip on;
  gzip_min_length 1000;
  gzip_proxied expired private auth;

  gzip_types
      application/atom+xml
      application/ld+json
      application/manifest+json
      application/rdf+xml
      application/rss+xml
      application/schema+json
      application/vnd.geo+json
      application/vnd.ms-fontobject
      application/x-font-ttf
      application/x-web-app-manifest+json
      application/xhtml+xml
      font/eot
      font/opentype
      font/truetype
      image/svg+xml
      image/gif
      image/jpeg
      image/bmp
      image/vnd.microsoft.icon
      image/x-icon
      text/cache-manifest
      #text/html
      text/vcard
      text/vnd.rim.location.xloc
      text/vtt
      text/x-component
      text/x-cross-domain-policy
      text/xml;
  gzip_disable "msie6";
  gzip_vary on;


  ############# SECURITY HEADERS ###########
 
  add_header Strict-Transport-Security "max-age=31536000; includeSubDomains; preload";
  add_header X-Content-Type-Options "nosniff";
  #add_header X-Frame-Options "SAMEORIGIN";
  #add_header X-XSS-Protection "1; mode=block";
  add_header Permissions-Policy "geolocation=(),midi=(),sync-xhr=(),microphone=(),camera=(),magnetometer=(),gyroscope=(),fullscreen=(self),payment=()";
  add_header Referrer-Policy "no-referrer-when-downgrade";
 
  # CORS Header für Cross-Origin-Resource-Policy
  add_header Cross-Origin-Resource-Policy "cross-origin";
  add_header 'Access-Control-Allow-Origin' '*';
  add_header 'Access-Control-Allow-Methods' 'GET, POST, OPTIONS';
  add_header 'Access-Control-Allow-Headers' 'DNT,User-Agent,X-Requested-With,If-Modified-Since,Cache-Control,Content-Type,Range';
 
  add_header X-Fastcgi-Cache $upstream_cache_status;
  add_header Link "<https://domain.com>; rel=preconnect";

  # Setze SameSite-Attribut für JTLSHOP-Cookie
  proxy_cookie_path / "/; SameSite=None; Secure; HttpOnly";
 
  {{settings}}
 
  ############# BLOCK BOTS #########
 
  if ($http_user_agent ~* "SemrushBot") {
      return 403;
  }

  ############# JTL SHOP 5 #############

  ##### Rewrite for admin pages (Shop >= 5.2) #####
 
  location /admin/ {
      try_files $uri $uri/ /admin/index.php?$args;
 
      # OPcache für den Admin-Bereich deaktivieren
      location ~ \.php$ {
          include fastcgi_params;
          fastcgi_pass 127.0.0.1:{{php_fpm_port}};
          fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
 
          # OPcache deaktivieren
          fastcgi_param PHP_VALUE "opcache.enable=0";
      }
  }
 
  location /plugins/ {
      try_files $uri /index.php?$args;
 
      # OPcache für den Plugins deaktivieren
      location ~ \.php$ {
          include fastcgi_params;
          fastcgi_pass 127.0.0.1:{{php_fpm_port}};
          fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
 
          # OPcache deaktivieren
          fastcgi_param PHP_VALUE "opcache.enable=0";
      }
  }

  # Rewrite JTL-Wawi/Worker access - Shop5
  rewrite ^/dbeS/(.*)\.php /dbeS/index.php?id=$1&$args last;

  # Rewrite assets to minify
  rewrite ^/asset/(.*)$ /includes/libs/minify/?g=$1 last;
  rewrite ^/static/(.*)$ /templates_c/min/$1 last;

  # Allow access - Shop5
  location ~* ^/templates_c/min/ { allow all; }

  # Allow access - Sitemap & Search
  location ~* ^/export/.*\.(xml|txt|csv|gz)$ { allow all; }

  # Allow access - Includes
  location ~* ^/includes/(sitemap.php|preisverlaufgraph(.*)\.php|cron_inc.php|newslettertracker.php|libs/minify/index.php(.*)|modules/notify.php)$ {
      allow all;
  }

  # Block access
  location ~* ^/(classes|jtllogs|update|uploads|export|backup|templates_c)/(.*) { deny all; return 404; }
  location ~* ^/includes/(.*)\.(php|phtml|phar|sql|xml|json|log|tpl)$ { deny all; return 404; }
  location ~* ^/templates/\.(php|tpl)$ { deny all; return 404; }
  location ~* ^/(.*)\.md$ { deny all; return 404; }
  location ~* ^/cli { deny all; return 404; }
  location @img_proxy { rewrite ^(.*)$ /index.php; }
  location ~ \.(gif|jpg|jpeg|png|webp)$ { try_files $uri @img_proxy; }
  location @rewrite { rewrite / /index.php?$args; }
 
  # New Block Access Rule for templates_c/filecache

  location ~ ^/templates_c/filecache/ {
      return 403;
  }
 
  # Rewrite Rule for sitemap and urllist
  rewrite ^/(sitemap_index\.xml|sitemap_0\.xml\.gz)$ /export/$1 last;

  # Separate block to handle PHP files within includes directory
  location ~ ^/includes/.*\.php$ {
      include fastcgi_params;
      fastcgi_intercept_errors on;
      fastcgi_index index.php;
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
      fastcgi_read_timeout 3600;
      fastcgi_send_timeout 3600;
      fastcgi_param HTTPS "on";
      fastcgi_param SERVER_PORT 443;
      fastcgi_pass 127.0.0.1:{{php_fpm_port}};
      fastcgi_param PHP_VALUE "{{php_settings}}";
  }
 

  ############# FastCGI #############

  location ~ \.php$ {
      include fastcgi_params;
      fastcgi_intercept_errors on;
      fastcgi_index index.php;
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
  
      ### Expires Header deaktivieren
      expires off;
 
      try_files $uri =404;
 
      fastcgi_read_timeout 1800;  # Timeout auf 5 Minuten reduzieren
      fastcgi_send_timeout 1800;
      fastcgi_connect_timeout 300;
      fastcgi_param HTTPS "on";
      fastcgi_param SERVER_PORT 443;
      #fastcgi_param DOCUMENT_ROOT $document_root;
      fastcgi_pass 127.0.0.1:{{php_fpm_port}};
      fastcgi_param PHP_VALUE "{{php_settings}}";
  
  }
 
  location / {
      {{varnish_proxy_pass}}
      proxy_set_header Host $http_host;
      proxy_set_header X-Forwarded-Host $http_host;
      proxy_set_header X-Real-IP $remote_addr;
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      proxy_hide_header X-Varnish;
      proxy_redirect off;
      proxy_max_temp_file_size 0;
      proxy_connect_timeout 60;
      proxy_send_timeout 300;
      proxy_read_timeout 300;
      proxy_buffer_size 128k;
      proxy_buffers 4 256k;
      proxy_busy_buffers_size 256k;
      proxy_temp_file_write_size 256k;
      index index.php index.html index.htm;
      try_files $uri $uri/ /index.php?$args;
      proxy_cookie_path / "/; SameSite=None; HTTPOnly; Secure";
  }

  location ~* \.(css|js|jpg|jpeg|gif|png|ico|gz|svg|svgz|ttf|otf|woff|woff2|eot|mp4|ogg|ogv|webm|webp|zip|swf|map)$ {
      add_header Access-Control-Allow-Origin '*';
      add_header 'Access-Control-Allow-Methods' 'GET, POST, OPTIONS';
      add_header 'Access-Control-Allow-Headers' 'DNT,User-Agent,X-Requested-With,If-Modified-Since,Cache-Control,Content-Type,Range';
      add_header Vary Accept;
 
      # Last-Modified für die Cache-Validierung beibehalten
      if_modified_since exact;
 
      # Expires und Cache-Control entfernt, da diese von Varnish gesetzt werden sollen
      etag on;
 
      # Loggen der Zugriffe deaktivieren
      access_log off;
 
      # Ressourcen auf Dateiebene prüfen
      try_files $uri @varnish_fallback;
  }

 
  # Fallback auf Varnish, falls die statische Ressource in Nginx nicht gefunden wird
  location @varnish_fallback {
      {{varnish_proxy_pass}}  # Stelle sicher, dass der richtige Port von Varnish verwendet wird
      proxy_set_header Host $host;
      proxy_set_header X-Real-IP $remote_addr;
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      error_page 502 /custom_502.html;
  }

  if (-f $request_filename) {
      break;
  }
 
}

server {
  listen 8080;
  listen [::]:8080;
  server_name domain.com;
  {{root}}

  try_files $uri $uri/ /index.php?$args;
  index index.php index.html;

  location ~ \.php$ {
      include fastcgi_params;
      fastcgi_intercept_errors on;
      fastcgi_index index.php;
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
      try_files $uri =404;
      fastcgi_read_timeout 1800;
      fastcgi_send_timeout 1800;
      fastcgi_connect_timeout 300;
      fastcgi_param HTTPS "on";
      fastcgi_param SERVER_PORT 443;
      fastcgi_pass 127.0.0.1:{{php_fpm_port}};
      fastcgi_param PHP_VALUE "{{php_settings}}";
  }
}

 

[mh1]

Würde mich über eure Empfehlungen für PHP + MySQL freuen um die Leistung entsprechend zu optimieren.

Es ist immer schwer, auf solche Fragen mit direkten Tipps und Einstellungen zu antworten.

Vielleicht so:
Deine nginx.conf erscheint mir doch sehr(!) umständlich . Hast du mal beispielhaft einen Request verfolgt ( log, tcpdump o.ä.).
Was waren eigentlich die Gründe für nginx?

In Anbetracht der Einstellungen für den Prozessmanager in der php-fpm.conf und der Tatsache, dass du diese ja bereits auf deine Anforderungen optimiert hast, denke ich mal, dass es sich um eine "low traffic site" handelt.
Die angesprochenen Latenzen könnten aber darauf hindeuten, dass die Einstellungen doch noch nicht so wirklich optimiert sind .
Dennoch: wenn der PHP Prozess auf derselben Maschine läuft wie nginx, solltest du Sockets benutzen (unabhängig von positiveren Sicherheitsaspekten sparst du dir hier auch den ganzen Overhead für die - unnötige - Netzwerkschicht).

Eine weitere Überlegung ist ja auch, welche Aufgaben das System sonst noch hat. Also konkret, ob du Speicher sparen musst u.s.w.