MYSQL

[claushviid]

Steps to reproduce

1. Upload multiple smal files and folder via web interface > 1000

Expected behaviour

All files and folder should be uploaded

Actual behaviour

Uploads stops after some time. When using REDIS locked files is one of the problems
Using Memcache instead is a lot better but problem stil exist

I think it has something to with the way nextcloud is handling MYSQL
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.

The number of times a row lock had to be waited for is high

The number of tables that have been opened. If opened tables is big,

The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Server configuration detail

Operating system: Linux 4.4.0-89-generic #112-Ubuntu SMP Mon Jul 31 19:38:41 UTC 2017 x86_64

Webserver: Apache/2.4.18 (Ubuntu) (apache2handler)

Database: mysql 10.2.16

PHP version: 7.0.30-0ubuntu0.16.04.1
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, redis, apcu, mysqlnd, PDO, xml, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, iconv, igbinary, imap, intl, json, ldap, exif, mcrypt, mysqli, pdo_mysql, pdo_pgsql, pdo_sqlite, pgsql, Phar, posix, readline, shmop, SimpleXML, smbclient, sockets, sqlite3, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, libsmbclient, Zend OPcache

Nextcloud version: 13.0.5 - 13.0.5.2

**Updated from an older Nextcloud/ownCloud or fresh install:
Updated from 13.0.4

**Where did you install Nextcloud from:
Tech and me VM

Signing status

Array

List of activated apps

Enabled:
 - activity: 2.6.1
 - announcementcenter: 3.2.1
 - apporder: 0.4.1
 - audioplayer: 2.3.1
 - bookmarks: 0.11.0
 - calendar: 1.6.1
 - camerarawpreviews: 0.5.5
 - comments: 1.3.0
 - contacts: 2.1.5
 - dashboard: 5.0.0
 - dav: 1.4.7
 - deck: 0.4.1
 - drawio: 0.9.0
 - external: 3.0.3
 - federatedfilesharing: 1.3.1
 - federation: 1.3.0
 - files: 1.8.0
 - files_external: 1.4.1
 - files_frommail: 0.1.0
 - files_markdown: 2.0.4
 - files_pdfviewer: 1.2.1
 - files_rightclick: 0.8.4
 - files_sharing: 1.5.0
 - files_texteditor: 2.5.1
 - files_trashbin: 1.3.0
 - files_versions: 1.6.0
 - files_videoplayer: 1.2.0
 - firstrunwizard: 2.2.1
 - flowupload: 0.0.7
 - gallery: 18.0.0
 - groupfolders: 1.2.2
 - issuetemplate: 0.3.0
 - logreader: 2.0.0
 - lookup_server_connector: 1.1.0
 - mail: 0.8.3
 - metadata: 0.6.0
 - mindmaps: 0.1.0
 - news: 12.0.4
 - nextcloud_announcements: 1.2.0
 - notes: 2.3.2
 - notifications: 2.1.2
 - oauth2: 1.1.1
 - onlyoffice: 1.3.0
 - password_policy: 1.3.0
 - previewgenerator: 1.1.0
 - provisioning_api: 1.3.0
 - quota_warning: 1.2.0
 - radio: 0.6.1
 - serverinfo: 1.3.0
 - sharebymail: 1.3.0
 - socialsharing_facebook: 1.0.2
 - spreed: 3.2.5
 - survey_client: 1.1.0
 - systemtags: 1.3.0
 - tasks: 0.9.6
 - theming: 1.4.5
 - twofactor_backupcodes: 1.2.3
 - updatenotification: 1.3.0
 - weather: 1.5.1
 - workflowengine: 1.3.0
Disabled:
 - admin_audit
 - encryption
 - user_external
 - user_ldap

Configuration (config/config.php)

{
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "localhost",
        "192.168.0.104",
        "nextcloud1.claushviid.dk",
        "onlyoffice.claushviid.dk",
        "claushviid.dk"
    ],
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "overwrite.cli.url": "https:\/\/nextcloud1.claushviid.dk\/",
    "dbtype": "mysql",
    "version": "13.0.5.2",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbport": "",
    "dbtableprefix": "oc_",
    "mysql.utf8mb4": true,
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "installed": true,
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "maintenance": false,
    "mail_smtpmode": "smtp",
    "memcache.local": "\\OC\\Memcache\\APCu",
    "filelocking.enabled": true,
    "memcache.locking": "\\OC\\Memcache\\Memcached",
    "redis": {
        "host": "***REMOVED SENSITIVE VALUE***",
        "port": 0,
        "timeout": 0,
        "dbindex": 0,
        "password": "***REMOVED SENSITIVE VALUE***"
    },
    "htaccess.RewriteBase": "\/",
    "filelocking.ttl": 25,
    "filesystem_check_changes": 0,
    "loglevel": 0,
    "log_type": "file",
    "logfile": "\/var\/ncdata\/nextcloud.log",
    "logtimezone": "Europe\/Copenhagen",
    "mail_smtpsecure": "tls",
    "mail_smtpauthtype": "LOGIN",
    "mail_from_address": "***REMOVED SENSITIVE VALUE***",
    "mail_domain": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpauth": 1,
    "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpport": "587",
    "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
    "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
    "mail_smtptimeout": 30,
    "app.mail.imap.timeout": 20,
    "app.mail.smtp.timeout": 2,
    "app.mail.imaplog.enabled": true,
    "app.mail.smtplog.enabled": true,
    "updater.release.channel": "stable",
    "enable_previews": true,
    "enabledPreviewProviders": [
        "OC\\Preview\\PNG",
        "OC\\Preview\\JPEG",
        "OC\\Preview\\GIF",
        "OC\\Preview\\BMP",
        "OC\\Preview\\XBitmap",
        "OC\\Preview\\Movie",
        "OC\\Preview\\PDF",
        "OC\\Preview\\MP3",
        "OC\\Preview\\TXT",
        "OC\\Preview\\MarkDown"
    ],
    "preview_max_x": 1024,
    "preview_max_y": 768,
    "preview_max_scale_factor": 1,
    "theme": ""
}

Are you using external storage, if yes which one: local/smb/sftp/...
NFS v3

Are you using encryption: no

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

With access to your command line run e.g.:
sudo -u www-data php occ ldap:show-config
from within your Nextcloud installation folder

Without access to your command line download the data/owncloud.db to your local
computer or access your SQL server remotely and run the select query:
SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap';


Eventually replace sensitive data as the name/IP-address of your LDAP server or groups.

Client configuration

Browser: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36

Operating system:

Logs

Browser log

Insert your webserver log here 

Nextcloud log

Insert your Nextcloud log here

Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

[nextcloud-bot]

GitMate.io thinks possibly related issues are #6745 (MySQL Database rights), #8863 (MySQL server has gone away during upgrade), #3349 (oc_calendarchanges in MySQL is very big), #5912 (Recommend PostgreSQL instead of MariaDB/MySQL), and #4914 (occ db:convert-type mysql -> UniqueConstraintViolationException).

[rullzer]

I just tried and it works just fine here. But maybe my setup is different.

Could you enable the slow query log on your server to see mysql warning of uninexed queries that might be running?

[claushviid]

After several days of investigating and monitoring mysql i came up with a working result. No more filelocks and freezing while uploading files. But i am sure the code for accesing the DB could be optimized a lot. And maybe more relevant indexes.

I had to go away from using REDIS and are now using Memcached and APCu Config.php attached
I have altered an tuned my.cnf

MY.CNF

---

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking

max_connections = 200
connect_timeout = 50
wait_timeout = 1000
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 15M
bulk_insert_buffer_size = 16M
tmp_table_size = 1024M
max_heap_table_size = 1024M
open_files_limit = 10000

myisam_recover_options = BACKUP
key_buffer_size = 1M

table_open_cache = 8000
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M

query_cache_type = 1
query_cache_limit = 100M
query_cache_min_res_unit = 2k
query_cache_size = 128M

log_warnings = 2
slow-query-log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_slow_verbosity = query_plan
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
expire_logs_days = 10
max_binlog_size = 100M

default_storage_engine = InnoDB

innodb_buffer_pool_size = 256M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 64M
innodb_max_dirty_pages_pct = 90
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_adaptive_flushing = 1

innodb_fast_shutdown = 0
innodb_large_prefix=on
innodb_file_format = barracuda
innodb_doublewrite = 0
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server = utf8mb4
skip-character-set-client-handshake
innodb_use_native_aio = 1
innodb_flush_log_at_trx_commit = 2
[galera]

innodb_flush_log_at_trx_commit=1

[mysqldump]
quick
quote-names
max_allowed_packet = 520M

[mysql]
default-character-set = utf8mb4

[mariadb]
innodb_use_fallocate = 1
innodb_use_atomic_writes = 1
innodb_use_trim = 1

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

CONFIG.PHP

---

'XXXXXXXXXXXXXXXXXXXXXXXXX', 'secret' => 'XXXXXXXXXXXXXXXXXXXXXXX', 'trusted_domains' => array ( 0 => 'localhost', 1 => '192.168.0.104', 2 => 'Changed For security reasons ', 3 => 'Changed For security reasonsk', 4 => 'Changed For security reasons', ), 'datadirectory' => '/mnt/data', 'overwrite.cli.url' => 'https://Changed For security reasons/', 'dbtype' => 'mysql', 'version' => '13.0.5.2', 'dbname' => 'nextcloud_db', 'dbhost' => 'localhost', 'dbport' => '', 'dbtableprefix' => 'oc_', 'mysql.utf8mb4' => true, 'dbuser' => 'oc_ncadmin', 'dbpassword' => 'XXXXXXXXXXXXXXXX, 'installed' => true, 'instanceid' => 'XXXXXXXXXXXXXX', 'maintenance' => false, 'mail_smtpmode' => 'smtp', 'memcache.locking' => '\\OC\\Memcache\\Memcached', 'memcache.local' => '\\OC\\Memcache\\APCu', 'filelocking.enabled' => true, 'filesystem_check_changes' => 0, 'redis' => array ( 'host' => '/var/run/redis/redis.sock', 'port' => 0, 'timeout' => 0.0, 'dbindex' => 0, 'password' => 'XXXXXXXXXXXXXXXXXXXXXXX', ), 'filelocking.ttl' => 5*5, 'htaccess.RewriteBase' => '/', 'loglevel' => 2, 'log_type' => 'file', 'logfile' => '/var/ncdata/nextcloud.log', 'logtimezone' => 'Europe/Copenhagen', 'mail_smtpsecure' => 'tls', 'mail_smtpauthtype' => 'LOGIN', 'mail_from_address' => 'nextcloud1', 'mail_domain' => 'XXXXXXXXXXX', 'mail_smtpauth' => 1, 'mail_smtphost' => 'smtp.gmail.com', 'mail_smtpport' => '587', 'mail_smtpname' => 'XXXXXXXXXX', 'mail_smtppassword' => 'XXXXXXXXXX', 'mail_smtptimeout' => 30, 'app.mail.imap.timeout' => 20, 'app.mail.smtp.timeout' => 2, 'app.mail.imaplog.enabled' => true, 'app.mail.smtplog.enabled' => true, 'updater.release.channel' => 'stable', 'enable_previews' => true, 'enabledPreviewProviders' => array ( 0 => 'OC\\Preview\\PNG', 1 => 'OC\\Preview\\JPEG', 2 => 'OC\\Preview\\GIF', 3 => 'OC\\Preview\\BMP', 4 => 'OC\\Preview\\XBitmap', 5 => 'OC\\Preview\\Movie', 6 => 'OC\\Preview\\PDF', 7 => 'OC\\Preview\\MP3', 8 => 'OC\\Preview\\TXT', 9 => 'OC\\Preview\\MarkDown', ), 'preview_max_x' => 1024, 'preview_max_y' => 768, 'preview_max_scale_factor' => 1, 'theme' => '', 'data-fingerprint' => '4a07029789ca0c347cd21d1bcaacf91d', );

[claushviid]

In addition, I've tried via the web interface to upload a library of> 1000 files and 1000 libraries. a total of about 200 Mb. Unfortunately, the process stops at a random time. The same exercise via a webdav app goes well.
Uoloading the same amount of MB with only a few files, goes OK. without any problems

Therefore, I can conclude that I have only been optimized up to a certain point (amount of files/(directories). Maybe there is a BUG somewhere else

[claushviid]

Hi I'm almost 100% sure that I've found the cause of the problem. It's a bug in nextcloud 13.0.5.

by creating five different libraries and for example copying 10 of the same files to each library. Then they are uploaded to nextcloud via the web interface. Upload stops almost instantly.

Doing the same with unike files causes no problems.

Therefore, I can conclude that nothing has to do with REDIS or incorrect configuration of mysql.

with best regards
Claus hviid

[claushviid]

same bug as "Upload via web interface of file with same name in different subfolders" #10527

Please close this one