Listing my configurations
50-server.cnf configurations BD
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql
# this is read by the standalone daemon and embedded servers
[server]
skip_name_resolve = 1
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_max_dirty_pages_pct = 90
query_cache_type = 1
query_cache_limit = 2M
query_cache_min_res_unit = 2k
query_cache_size = 64M
tmp_table_size= 64M
max_heap_table_size= 64M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
# this is only for the mysqld standalone daemon
[client]
default-character-set = utf8mb4
[client-server]
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow2.log
long_query_time = 1
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=1
#
# * Basic Settings
#
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
#port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
#key_buffer_size = 16M
#max_allowed_packet = 16M
#thread_stack = 192K
#thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
#query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit = 1000
#log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
#max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = exclude_database_name
#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
#ssl = on
#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]
config.php - Configuration NEXTCLOUD
<?php
$CONFIG = array (
'instanceid' => '***',
'passwordsalt' => '***',
'secret' => '***',
'trusted_domains' =>
array (
0 => '192.168.23.10',
),
'datadirectory' => '/media/datanc/nexcloud-data/',
'dbtype' => 'mysql',
'version' => '20.0.9.1',
'overwrite.cli.url' => 'https://192.168.23.10',
'dbname' => 'nextcloud',
'dbhost' => 'localhost',
'dbport' => '',
'dbtableprefix' => 'oc_',
'mysql.utf8mb4' => true,
'dbuser' => 'nextclouduser',
'dbpassword' => '******',
'installed' => true,
'memcache.local' => '\OC\Memcache\Redis',
'memcache.distributed' => '\OC\Memcache\Redis',
'memcache.locking' => '\OC\Memcache\Redis',
'redis' => [
'host' => '127.0.0.1',
'port' => 6379,
],
);
nexctloud.conf - Configuration Nginx
upstream php-handler {
server 127.0.0.1:9000;
server unix:/var/run/php/php7.4-fpm.sock;
}
server {
listen 80;
# listen [::]:80;
server_name 192.168.23.10;
# Enforce HTTPS
return 301 https://$server_name$request_uri;
}
server {
listen 443 ssl http2;
# listen [::]:443 ssl http2;
server_name 192.168.23.10;
# Use Mozilla's guidelines for SSL/TLS settings
# https://mozilla.github.io/server-side-tls/ssl-config-generator/
ssl_certificate /etc/nginx/cert/server.crt;
ssl_certificate_key /etc/nginx/cert/server.key;
# HSTS settings
# WARNING: Only add the preload option once you read about
# the consequences in https://hstspreload.org/. This option
# will add the domain to a hardcoded list that is shipped
# in all major browsers and getting removed from this list
# could take several months.
#add_header Strict-Transport-Security "max-age=15768000; includeSubDomains; preload;" always;
# set max upload size
client_max_body_size 2048M;
fastcgi_buffers 64 4K;
# Enable gzip but do not remove ETag headers
gzip on;
gzip_vary on;
gzip_comp_level 4;
gzip_min_length 256;
gzip_proxied expired no-cache no-store private no_last_modified no_etag auth;
gzip_types application/atom+xml application/javascript application/json application/ld+json application/manifest+json application/rss+xml application/vnd.geo+json application/vnd.ms-fontobject application/x-font-ttf application/x-web-app-manifest+json application/xhtml+xml application/xml font/opentype image/bmp image/svg+xml image/x-icon text/cache-manifest text/css text/plain text/vcard text/vnd.rim.location.xloc text/vtt text/x-component text/x-cross-domain-policy;
# Pagespeed is not supported by Nextcloud, so if your server is built
# with the `ngx_pagespeed` module, uncomment this line to disable it.
#pagespeed off;
# HTTP response headers borrowed from Nextcloud `.htaccess`
add_header Referrer-Policy "no-referrer" always;
add_header X-Content-Type-Options "nosniff" always;
add_header X-Download-Options "noopen" always;
add_header X-Frame-Options "SAMEORIGIN" always;
add_header X-Permitted-Cross-Domain-Policies "none" always;
add_header X-Robots-Tag "none" always;
add_header X-XSS-Protection "1; mode=block" always;
# Remove X-Powered-By, which is an information leak
fastcgi_hide_header X-Powered-By;
# Path to the root of your installation
root /usr/share/nginx/nextcloud;
# Specify how to handle directories -- specifying `/index.php$request_uri`
# here as the fallback means that Nginx always exhibits the desired behaviour
# when a client requests a path that corresponds to a directory that exists
# on the server. In particular, if that directory contains an index.php file,
# that file is correctly served; if it doesn't, then the request is passed to
# the front-end controller. This consistent behaviour means that we don't need
# to specify custom rules for certain paths (e.g. images and other assets,
# `/updater`, `/ocm-provider`, `/ocs-provider`), and thus
# `try_files $uri $uri/ /index.php$request_uri`
# always provides the desired behaviour.
index index.php index.html /index.php$request_uri;
# Rule borrowed from `.htaccess` to handle Microsoft DAV clients
location = / {
if ( $http_user_agent ~ ^DavClnt ) {
return 302 /remote.php/webdav/$is_args$args;
}
}
location = /robots.txt {
allow all;
log_not_found off;
access_log off;
}
# Make a regex exception for `/.well-known` so that clients can still
# access it despite the existence of the regex rule
# `location ~ /(\.|autotest|...)` which would otherwise handle requests
# for `/.well-known`.
location ^~ /.well-known {
# The following 6 rules are borrowed from `.htaccess`
rewrite ^/\.well-known/host-meta\.json /public.php?service=host-meta-json last;
rewrite ^/\.well-known/host-meta /public.php?service=host-meta last;
rewrite ^/\.well-known/webfinger /public.php?service=webfinger last;
rewrite ^/\.well-known/nodeinfo /public.php?service=nodeinfo last;
location = /.well-known/carddav { return 301 /remote.php/dav/; }
location = /.well-known/caldav { return 301 /remote.php/dav/; }
try_files $uri $uri/ =404;
}
# Rules borrowed from `.htaccess` to hide certain paths from clients
location ~ ^/(?:build|tests|config|lib|3rdparty|templates|data)(?:$|/) { return 404; }
location ~ ^/(?:\.|autotest|occ|issue|indie|db_|console) { return 404; }
# Ensure this block, which passes PHP files to the PHP process, is above the blocks
# which handle static assets (as seen below). If this block is not declared first,
# then Nginx will encounter an infinite rewriting loop when it prepends `/index.php`
# to the URI, resulting in a HTTP 500 error response.
location ~ \.php(?:$|/) {
fastcgi_split_path_info ^(.+?\.php)(/.*)$;
set $path_info $fastcgi_path_info;
try_files $fastcgi_script_name =404;
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_param PATH_INFO $path_info;
fastcgi_param HTTPS on;
fastcgi_param modHeadersAvailable true; # Avoid sending the security headers twice
fastcgi_param front_controller_active true; # Enable pretty urls
fastcgi_pass php-handler;
fastcgi_intercept_errors on;
fastcgi_request_buffering off;
}
location ~ \.(?:css|js|svg|gif)$ {
try_files $uri /index.php$request_uri;
expires 6M; # Cache-Control policy borrowed from `.htaccess`
access_log off; # Optional: Don't log access to assets
}
location ~ \.woff2?$ {
try_files $uri /index.php$request_uri;
expires 7d; # Cache-Control policy borrowed from `.htaccess`
access_log off; # Optional: Don't log access to assets
}
location / {
try_files $uri $uri/ /index.php$request_uri;
}
}
There is a system
I3 4210
4Gb RAM DDR3
Storage 1HDD 4Tb FS EXT4
ssd system 480Gb Adata - FS EXT4
NextCloud 20.09
OS ubuntu0.20.04.1
Database 10.3.25-MariaDB
PHP 7.4.3
Nginx 1.18
sda1 - for data storage
sdb2 - partition with system and database
The issue you are facing:
The system, like the database, is installed on ssd. There are about 50 active users in the NextCloud system, users periodically drop folders with photos of 20-30 photos with a volume of 150-300 mgb. After that, the user threw the folder into the repository for copying, the entire system interface begins to freeze (slowdown in the responsiveness of the interface up to 40 seconds). Sometimes a 502 bad gateway error pops up in the browser.
In disk busy processes through the iotop program, you can see that during the copy process, the jbd2 / sdb2-8 process takes up to 10% of the busyness, and php-fpm: pool www in which a small write load is visible.
If no one tries to write files to the system, then the web interface works well, and if someone downloads files, the interface does not slow down. Problems begin apparently when changes are made to the database, that is, when writing
What has been done
I tried disabling EXT4 logging - it didn’t help
I finished all applications that can be disabled in order to eliminate the likelihood of problems from applications, there is no result
I installed a clean installation on another computer with a similar configuration where there are only 2 users, if one user downloads photo files (100 photos of 500-600 kb are enough), then the web interface for everyone else is very slow, so it is impossible to work.
I tried to install the snap version of NextCloud, and everything works just fine there, 4 different users added 1000 files of 1 GB each, and the interface is faster than ever!
Enabling logging of slow queries “slow_query_log_file” does not give any sensible reports, queries are processed in 1.8 - 2 seconds.
I have cleared all system errors and have the status All checks passed.
Questions and Answers
What mistakes and mistakes do I make?
Perhaps I was mistaken somewhere in the settings of the caching servers? REdis APCu Memcached
Is PHP configuration error possible?
Is MariaDB configuration error possible?
Is it possible to configure Nginx configuration error?
The following was done from the database settings
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_max_dirty_pages_pct = 90
query_cache_type = 1
query_cache_limit = 2M
query_cache_min_res_unit = 2k
query_cache_size = 64M
tmp_table_size= 64M
max_heap_table_size= 64M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 5
The following was done from NGINX settings
client_max_body_size 2048M;
fastcgi_buffers 64 4K;
The following was done from PHP.ini settings
apc.enable_cli=1
memory_limit = 1500M
The following was done from PHP-Nexcloud settings
<?php
$CONFIG = array (
'instanceid' => '***',
'passwordsalt' => '***',
'secret' => '***',
'trusted_domains' =>
array (
0 => '192.168.23.10',
),
'datadirectory' => '/media/datanc/nexcloud-data/',
'dbtype' => 'mysql',
'version' => '20.0.9.1',
'overwrite.cli.url' => 'https://192.168.23.10',
'dbname' => 'nextcloud',
'dbhost' => 'localhost',
'dbport' => '',
'dbtableprefix' => 'oc_',
'mysql.utf8mb4' => true,
'dbuser' => 'nextclouduser',
'dbpassword' => '******',
'installed' => true,
'memcache.local' => '\OC\Memcache\Redis',
'memcache.distributed' => '\OC\Memcache\Redis',
'memcache.locking' => '\OC\Memcache\Redis',
'redis' => [
'host' => '127.0.0.1',
'port' => 6379,
],
);