Kodi Community Forum
Solved Synology NAS Centralized DB extremely slow performance - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Solved Synology NAS Centralized DB extremely slow performance (/showthread.php?tid=204620)

Pages: 1 2

Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2014-09-18

Hi,
i have the following setup at home. Synology NAS DS411 running DSM 5.0.4493 Update 4 which is the latest release of DSM. I am running XBMC Kodi on a Windows 7 machine with decent specs.
I have a huge movie library 2000 movies+. Each movie stored in a separate folder and has all info inside. (Scraper, NFO, Actors...etc). i had a centrialize MySQL DB running on Synology to serve all other clients (couples of PIs, another Windows machine..etc)
Now the issue is that since i installed Kodi navigating any menu take a huge time. even going from videos to files..etc.
Accordingly i thought may be it is an upgrade issue. so i uninstalled all, deleted the SQL DBs and reinstalled Kodi fresh. Same problem.
So i tried to troubleshoot the issue and added the movies source directly to XBMC without the SQL story. and all works perfectly which shows it is probably a mySQL/KODI issue.
not sure how to trouble shoot further.
If anyone can help i would be glad. i am sure you need more details so just ask me and i will give you all the details.

RE: Synology NAS Centralized DB extremely slow performance - schumi2004 - 2014-09-18

You could try to modify (Maria) database config as described in this Dutch thread http://gathering.tweakers.net/forum/list_message/41727419#41727419
Worked here on my DS211 although my library isn't that big.

RE: Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2014-09-18

Thanks schumi2004. its all in dutch so it is quite difficult for me to follow even with Google translate Smile besides it is a very long thread. can you tell me which part is the one with the configuration tweak and how to edit it?\

RE: Synology NAS Centralized DB extremely slow performance - schumi2004 - 2014-09-18

(2014-09-18, 13:13)aymanwaheed Wrote: Thanks schumi2004. its all in dutch so it is quite difficult for me to follow even with Google translate Smile besides it is a very long thread. can you tell me which part is the one with the configuration tweak and how to edit it?\
Maria DB's default config should look like this:
Note: Compare both files to see difference.

Find location like this via commandline: find / -name my.cnf (or google)
Make backup and create your own and upload with WinSCP for example.
Note: That your custom file could get overwritten when a update for MariaDB is available and installed!

my.cnf
Code:
[client]
port = 3306
socket = /run/mysqld/mysqld.sock

[mysqld]
bind-address = 0.0.0.0
port = 3306
socket = /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 240K
innodb_data_home_dir = /var/services/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/services/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
#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

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

# Please add your custom configuration to here:
!include /usr/syno/etc/packages/MariaDB/my.cnf


Modified code looks like this:
modded.my.cnf
Code:
[client]
port = 3306
socket = /run/mysqld/mysqld.sock

[mysqld]
bind-address = 0.0.0.0
port = 3306
socket = /run/mysqld/mysqld.sock
skip-name-resolve
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 512K
net_buffer_length = 2K
thread_stack = 240K
innodb_data_home_dir = /var/services/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/services/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
#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

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

# Please add your custom configuration to here:
!include /usr/syno/etc/packages/MariaDB/my.cnf

Please notice the file location were to place this modded file when done.
It's also advised to cut modded values in half when your system is low on RAM (512MB or 256MB)
The user from linked thread noticed that his DB was using +/- 64MB after this mod, his system had 1GB RAM

RE: Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2014-09-18

ok. will try that. Thanks Shumi2004.
Question though when i searched for the my.ini using find / -name my.cnf command with Putty i found the only version at /volume1/@appstore/MariaDB/etc/mysql/my.cnf.
When i FTP with WINSCP using admin account the NAS i cannot find this folders. i find only the libraries like video, music...etc. so i believe it might be hidden or something similar. Any idea as i am new to all these customization?

RE: Synology NAS Centralized DB extremely slow performance - schumi2004 - 2014-09-18

(2014-09-18, 15:21)aymanwaheed Wrote: ok. will try that. Thanks Shumi2004.
Question though when i searched for the my.ini using find / -name my.cnf command with Putty i found the only version at /volume1/@appstore/MariaDB/etc/mysql/my.cnf.
When i FTP with WINSCP using admin account the NAS i cannot find this folders. i find only the libraries like video, music...etc. so i believe it might be hidden or something similar. Any idea as i am new to all these customization?

Login as ROOT and use admins password Wink

RE: Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2014-09-18

ok. found it. i need to use SCP. FTP or SFTP opens only shared folders. will try and update

RE: Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2014-09-18

up till now all is perfect. will enable the 2nd client and update.

RE: Synology NAS Centralized DB extremely slow performance - SiO2y - 2015-01-23

Did that work? I'm experiencing exactly the same problem with a huge library and centralised MySQL library on a ReadyNAS but I wouldn't mind someone walking me through editing this .cnf file.

RE: Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2015-01-23

Yes it did. All runs perfect.

RE: Synology NAS Centralized DB extremely slow performance - rlg6767 - 2015-01-23

Can you let me know if this speeds things up? I have a DS411j and converted to Maria DB. Still really slow. Gave up in the end and migrated the SQL server to a Windows machine with more CPU/RAM.

RE: Synology NAS Centralized DB extremely slow performance - aymanwaheed - 2015-01-25

Its a a life changer. Prior to that i was not able to use it at all. now i have 5 machines. 2 Windows, 3 Raspberry PIs accessing the centralized SQL DBs and all is working smooth,I have a library of 2000+ movies& series + personal photos and others.
I

RE: Synology NAS Centralized DB extremely slow performance - LeoS - 2015-05-31

Guys, thanks a lot for the info shared here. Especially to schumi2004.
I'm just at the initial stage of setting up the db and was wondering wtf is taking the import so long...

edit: apparently my issue is not related to db at all. It seems as I was importing the 'single xml export', my client was redownloading the artwork from the internet (??). I'm just gonna start from a fresh db and see if I can export/import watched status separately after. I already have the .nfo artwork on my NAS maintained by Ember Media Manager anyway; so rescraping from the locally available files is much faster (1-2s per title).

RE: Synology NAS Centralized DB extremely slow performance - iHomer - 2015-10-13

Oh man, I'm so glad I found this thread. I noticed this behaviour since I changed my internet provider and was forced to unplug my powerful ADSL router (Asus DSL-N55U) and started using the shitty FTTH router (Sercomm FD1018).

I have around 500 items in my library and it was all working flawlessly in a windows PC and 2 raspberry pies, after the switch it all started going terribly slow, even after the movie/tv show started playing I did not have access to the player controls in yatse. I inmediatly thought it was an issue with the Sercomm router and although I tested/tried several config changes I could not find a way to make the kodi experience flow again.

But just a few minutes ago I found this thread and when I reach the line "skip-name-resolve" on the second conf file I thought that that must be the problem. Effectively, somehow the new router is not letting the name resolve hapen and was slowing down the whole thing. I just added that single parameter to my config (not a single change more, only that one) and now it's all back to normal speed

The way I changed it for others to try:

1.- Log in your synology NAS through SSH using the 'admin' user. Use the admin password.
2.- Impersonate root with "sudo -i". Type the admin password
3.- Naviate to where the 'my.cnf' is. (cd /volume1/\@appstore/MariaDB10/usr/local/mariadb10/etc/mysql)
4.- vi edit the my.cnf file (you may to duplicate the file first with 'cp my.cnf my.cnf.original' for example)
  • press 'i' to insert
  • type 'skip-name-resolve' under the socket line
  • press 'Esc' to exit insert mode
  • type ':wq' to write the file and exit
5.- restart the service or the NAS (I restarted with 'reboot' from the ssh session)
6.- enjoy

Thanks guys for posting this and remember, the problem may be in the router

Edit: when editing the my.cnf file there is a line at the very bottom that points you to a custom my.cnf file. In my case the file didn't exist so I created it and pasted inside the following code:

Code:
[mysqld]
skip-name-resolve

I hope that I can now update MariaDB safely so I don't have to redo all the steps every time it gets updated.

RE: Synology NAS Centralized DB extremely slow performance - sivilya - 2018-10-28

OMG! I've been trying to figure this out for two weeks now.
An update from synology and/or mariadb f**ked something up and this fixed it for me

10x