+- 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: Using and tuning MariaDB as your central database (/showthread.php?tid=329046)
RE: Using and tuning MariaDB as your central database - sialivi - 2020-08-19
(2018-02-28, 21:01)HeresJohnny Wrote: innodb_adaptive_hash_index=OFF;For Kodi no benefit is gained from additional indexes. This should only be used with large databases.
What's considered a large database?
RE: Using and tuning MariaDB as your central database - Klojum - 2020-08-19
(2020-08-19, 17:56)sialivi Wrote: What's considered a large database?
IMO, a database with at least a couple of million records in a table. Kodi is unlikely to have such large tables. But it all comes back to the hardware running the database.
More cpu grunt and faster disk speeds, more internal memory for cache, and things will go smoother.
Databases running on the SD card of a Raspberry Pi is considered a kid's toy to me. SDcards have a p!ss poor quality track record in my book.
RE: Using and tuning MariaDB as your central database - Zoltrix - 2021-04-08
Anyone have these settings working on Linux?
I've placed the settings into /etc/mysql/my.cnf
But after a restart, mariadb doesn't load and shows errors such as:
mariadb: unknown variable 'innodb_buffer_pool_instances=2'
my.cnf looks as follows:
Code:
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_file_format = Barracuda
innodb_log_block_size = 4096
innodb_adaptive_hash_index = OFF
character_set_server = utf8
key_buffer_size = 16k
skip_name_resolve = 1
optimizer_search_depth = 0
aria_pagecache_buffer_size = 512M
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_doublewrite = 0RE: Using and tuning MariaDB as your central database - Klojum - 2021-04-08
Which MariaDB server version are you running?
innodb_buffer_pool_instances was deprecated in v10.5.
See https://mariadb.com/docs/reference/mdb/system-variables/innodb_buffer_pool_instances/
RE: Using and tuning MariaDB as your central database - HeresJohnny - 2021-04-08
Try commenting out the line with utf8.
RE: Using and tuning MariaDB as your central database - Zoltrix - 2021-04-09
Running version 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
If I comment out every line except the first one, I still get an unknown variable error:
Code:
zoltrix@kodidb:~$ sudo mariadb
mariadb: unknown variable 'innodb_buffer_pool_size=2G'RE: Using and tuning MariaDB as your central database - HeresJohnny - 2021-04-11
Try swapping out [client-server] with [mysqld]
RE: Using and tuning MariaDB as your central database - Zoltrix - 2021-04-12
(2021-04-11, 02:31)HeresJohnny Wrote: Try swapping out [client-server] with [mysqld]
That did the trick!
The two settings I had to leave commented out though were:
#innodb_log_block_size = 4096
#character_set_server = utf8
Not sure if there are newer variables that replace those?
RE: Using and tuning MariaDB as your central database - teriyaki - 2021-04-18
(2021-04-12, 13:23)Zoltrix Wrote:innodb_log_block_size was removed in 10.3 and to use utf8, see: https://mariadb.com/kb/en/setting-character-sets-and-collations/#example-changing-the-default-character-set-to-utf-8(2021-04-11, 02:31)HeresJohnny Wrote: Try swapping out [client-server] with [mysqld]
That did the trick!
The two settings I had to leave commented out though were:
#innodb_log_block_size = 4096
#character_set_server = utf8
Not sure if there are newer variables that replace those?
RE: Using and tuning MariaDB as your central database - vinoo - 2023-01-30
I have try the query cache, that is suitable to kodi for me.
Add this parameters:
- query_cache_type=ON
- query_cache_size=32M
RE: Using and tuning MariaDB as your central database - HeresJohnny - 2023-01-31
Have you seen this https://shatteredsilicon.net/blog/2022/08/08/mysql-waiting-for-query-cache-lock/ and tested performance with and without query cache?
RE: Using and tuning MariaDB as your central database - vinoo - 2023-02-03
Yes i have seen this problem, but with kodi there is not a lot of update on the tables. So if its a dedicated mariadb engine for kodi it's not a problem.
In my context i fell faster, but it's not a rigorous benchmark.
RE: Using and tuning MariaDB as your central database - Jeff721 - 2023-03-11
Good day all,
I'm really sorry for the newbie question but here it is: I'm using the MariaDB 10.3.32-1040 on my Synology NAS and would REALLY like to change the value of optimizer_search_depth to 0 or 1 to speed up the queries... But how do I do this on my Synology NAS 1821+
Thanks for the help.
RE: Using and tuning MariaDB as your central database - jznine - 2023-03-11
Do you have phpmyadmin installed from package centre? Under that you can adjust those under the 'Variables' tab.
-- I was wrong, ignore my comments
RE: Using and tuning MariaDB as your central database - Klojum - 2023-03-11
Otherwise, you'll have to SSH into your NAS, find MariaDB's config file and edit it manually via the command line.
Which is something you only should do when you know what you're doing.