[ForgotMyUsername]

Hello everybody,

first of all I hope this is the correct forum. If not, please move this to the correct place.

I've used Kodi on four Windows-Machines for several years and after getting into more and more trouble with watched-states, I decided to create a centralised database on my QNAP-NAS.

This went well without any problems and two of the Windows-Machines are now set up to use the central database, which works fine with one exception, unfortunately.

Browsing TV-Shows is dead slow on both machines. Switching to Season View takes 20-30 seconds. After that, it will be cached, but only until I start a video, then it's the same thing again - it's virtually unusable. I've read some older posts here on the forum and tried messing around with the my.cnf - to no avail so far. In these posts nobody came up with a solution (except switiching to emby, which is not what I really want to do).

Here is the log:

Debug Log (Database Details enabled)

Some specs:
Server: QNAP TS-853A / 16 GB / Celeron N3150 @ 1.60 GHz / QTS 4.5.1.1456
Client: PC / 32 GB / i9-9900K @ 3.60 GHz / Windows 10 64 Pro (latest)
Kodi ver. 18.8 / Aeon Madnox

Hope this is enough information and somebody can help me.
Thanks!

[Klojum]

Please remove the banned repository from your Kodi setup first.
We have no support for Kodi setups with video piracy installs.

[ForgotMyUsername]

Thanks for your reply. Can you tell me which repository you're referring to? Must be some leftover from long ago. I installed most of the repositories to try skins.

And BTW how do I remove repositories? Context menu only gives me "deactivate" für repositories, no remove or uninstall...

[Klojum]

Look at your log, banned items are colored orange.
Go to the Information page of the repository. It should have an Uninstall option.

About your database server, it's a pretty old version. Any chance you can upgrade it?

[ForgotMyUsername]

So, I uninstalled the Repo, but it doesn't make any difference, still the same behaviour.

As with the database, the problem is, it's embedded and part of the QNAP OS. So, in the end I'm quite dependent on QNAP concerning this part of the setup (which is a real pain, wouldn't recommend this to anybody!). As soon as I changed something that is part of the official firmware in the past, I lost all changes with the next firmware update, so that's not a good option. Besides, I'm not really familiar with Unix, so I might cause damage to the NAS which would be awful.

However, I found this post on the qnap-forums, it might be an option as a last resort as well as this might be possible, but I doubt whether I manage to set up the whole thing correctly.

Moreover, it doesn't really seem to be a performance issue with the database. Browing to album view in music database (4777 albums) causes a delay of 1 second. Also, browsing to the list of movies (3553 movies) causes virtually no delay. When in TV-Shows, it makes no difference how many seasons / episodes the series has. I tried "Cranford" (7 episodes) = 34,82 seconds to enter Season View and "The Simpsons" (663 episodes) = 34,80 seconds to enter Season View. These times were measured by hand, so I'd guess they're identical in reality.

I'm sure the issue must be related to Season View itself or flattening or whatever. On the other hand, the same database doesn't cause any browsing issue on the two machines which still use their dedicated local databases.

Update: Browsing "Genres" in Music Database also causes the 35 seconds delay!

[ForgotMyUsername]

Over the weekend, I have set up a Windows VM with the latest MySQL-Version on my QNAP and completely rebuilt the database.

Unfortunately, this doesn't make any change. After the first 10 Series were in the database, it looked good, browsing speed was okay. But now that I've put all the series in the database, it's ~35 seconds to switch to Season View again.

So I guess, it's not a problem with MySQL or the SQL-Version, as the problem is identical with an older Linux-version and a new Windows-version of the server.

Looks as this is a problem with Kodi. Hope somebody has an idea, otherwise it might really be time to look for an alternative, as it is virtually unusable this way.

[Klojum]

Looks as this is a problem with Kodi. Hope somebody has an idea, otherwise it might really be time to look for an alternative, as it is virtually unusable this way.

Any chance you can double all cache-related settings in the my.cnf or my.ini file from MySQL? Just for testing purposes? I've used MySQL for years now on Linux, and no real problems emerged. Browsing episodes from a TV show with many episodes can take slightly longer (1-2 seconds) due to the more complex query at hand, but a query taking more than 30 seconds shouldn't happen.

[ForgotMyUsername]

I'm a real newbie to SQL, so I don't really know what exactly to do. This is the my.cnf, perhaps you can tell me which values to change?

# Example MariaDB config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MariaDB plays
# an important part, or systems up to 128M where MariaDB is used together with
# other programs (such as a web server)
#
# MariaDB programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, do:
# 'my_print_defaults --help' and see what is printed under
# Default options are read from the following files in the given order:
# More information at: http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MariaDB clients
[client]
#password    = your_password
port = 3306
socket        = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MariaDB server
[mysqld]
port = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer = 32M
key_buffer_size = 384M
max_allowed_packet = 64M
max_connections = 151
max_user_connections = 90
table_cache = 2048
sort_buffer_size = 3M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 100
connect_timeout = 10
query_cache_limit = 1M
query_cache_size = 128M
query_cache_type = 1
tmp_table_size = 256M
max_heap_table_size = 256M Add
max_connect_errors = 10


#skip-innodb

# Point the following paths to different dedicated disks
#tmpdir        = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[ForgotMyUsername]

Good News!

After installing this MariaDB-Server on my QNAP, it finally works like a charm. It's really fast and the delay is gone. Still wondering what might be wrong with the older version or MySQL 8 on the Windows-VM, but I'll leave those problems to someone else and hope nobody ever runs into them. Hope it will still be so fast after feeding all my media into the database, but judging from the first test results, everything looks fine.

Greetz,
Mark

[Klojum]

Thread marked solved.

[ForgotMyUsername]

Indeed!

Thanks for your support!