lamp: adjust mariadb parametersv1.7.1

3 files changed, 62 insertions, 8 deletions

diff --git a/changelog.md b/changelog.md
index bdc674ca..51dfadf8 100644
--- a/changelog.md
+++ b/changelog.md
@@ -1,7 +1,9 @@
 
-[v1.7.0](https://github.com/nextcloud/nextcloudpi/commit/b0a83b0) (2019-02-17) add nc-restore-snapshot
+[v1.7.1](https://github.com/nextcloud/nextcloudpi/commit/a077130) (2019-02-22) lamp: adjust mariadb parameters
 
-[v1.6.7](https://github.com/nextcloud/nextcloudpi/commit/41a48c9) (2019-02-17) nc-backup-auto: notify failures
+[v1.7.0](https://github.com/nextcloud/nextcloudpi/commit/5e1ea77) (2019-02-17) add nc-restore-snapshot
+
+[v1.6.7  ](https://github.com/nextcloud/nextcloudpi/commit/41a48c9) (2019-02-17) nc-backup-auto: notify failures
 
 [v1.6.6  ](https://github.com/nextcloud/nextcloudpi/commit/743cb24) (2019-02-15) nc-automount: fix NFS delay
 
@@ -9,7 +11,7 @@
 
 [v1.6.4  ](https://github.com/nextcloud/nextcloudpi/commit/4e04339) (2019-02-14) nc-nextcloud: update description
 
-[v1.6.3](https://github.com/nextcloud/nextcloudpi/commit/af5e35d) (2019-02-13) ncp-update-nc: BTRFS basedir not supported
+[v1.6.3  ](https://github.com/nextcloud/nextcloudpi/commit/af5e35d) (2019-02-13) ncp-update-nc: BTRFS basedir not supported
 
 [v1.6.2  ](https://github.com/nextcloud/nextcloudpi/commit/b070387) (2019-02-13) nc-datadir: lift mountpoint restriction
 
diff --git a/lamp.sh b/lamp.sh
index 7ec49c8e..ad422bd7 100644
--- a/lamp.sh
+++ b/lamp.sh
@@ -126,11 +126,33 @@ EOF
 
     $APTINSTALL ssl-cert # self signed snakeoil certs
 
-    # configure MariaDB ( UTF8 4 byte support )
-    cp /etc/mysql/mariadb.conf.d/50-server.cnf         /etc/mysql/mariadb.conf.d/90-ncp.cnf
-    sed -i '/\[mysqld\]/ainnodb_large_prefix=true'     /etc/mysql/mariadb.conf.d/90-ncp.cnf
-    sed -i '/\[mysqld\]/ainnodb_file_per_table=1'      /etc/mysql/mariadb.conf.d/90-ncp.cnf
-    sed -i '/\[mysqld\]/ainnodb_file_format=barracuda' /etc/mysql/mariadb.conf.d/90-ncp.cnf
+    # configure MariaDB (UTF8 4 byte support)
+    cat > /etc/mysql/mariadb.conf.d/90-ncp.cnf <<EOF
+[mysqld]
+transaction_isolation = READ-COMMITTED
+innodb_large_prefix=true
+innodb_file_per_table=1
+innodb_file_format=barracuda
+
+[server]
+# innodb settings
+skip-name-resolve
+innodb_buffer_pool_size = 256M
+innodb_buffer_pool_instances = 1
+innodb_flush_log_at_trx_commit = 2
+innodb_log_buffer_size = 32M
+innodb_max_dirty_pages_pct = 90
+innodb_log_file_size = 32M
+
+# disable query cache
+query_cache_type = 0
+query_cache_size = 0
+
+# other
+tmp_table_size= 64M
+max_heap_table_size= 64M
+EOF
+
 
   # launch mariadb if not already running
   if ! pgrep -c mysqld &>/dev/null; then
diff --git a/update.sh b/update.sh
index 90546278..de7847b2 100755
--- a/update.sh
+++ b/update.sh
@@ -309,6 +309,36 @@ EOF
     bash -c "sleep 2 && service apache2 reload" &>/dev/null &
   fi
 
+  # configure MariaDB (UTF8 4 byte support)
+  grep -q max_heap_table_size /etc/mysql/mariadb.conf.d/90-ncp.cnf || {
+    cat > /etc/mysql/mariadb.conf.d/90-ncp.cnf <<EOF
+[mysqld]
+transaction_isolation = READ-COMMITTED
+innodb_large_prefix=true
+innodb_file_per_table=1
+innodb_file_format=barracuda
+
+[server]
+# innodb settings
+skip-name-resolve
+innodb_buffer_pool_size = 256M
+innodb_buffer_pool_instances = 1
+innodb_flush_log_at_trx_commit = 2
+innodb_log_buffer_size = 32M
+innodb_max_dirty_pages_pct = 90
+innodb_log_file_size = 32M
+
+# disable query cache
+query_cache_type = 0
+query_cache_size = 0
+
+# other
+tmp_table_size= 64M
+max_heap_table_size= 64M
+EOF
+    service mysqld restart
+  }
+
   # remove redundant opcache configuration. Leave until update bug is fixed -> https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=815968
   # Bug #416 reappeared after we moved to php7.2 and debian buster packages. (keep last)
   [[ "$( ls -l /etc/php/7.2/fpm/conf.d/*-opcache.ini |  wc -l )" -gt 1 ]] && rm "$( ls /etc/php/7.2/fpm/conf.d/*-opcache.ini | tail -1 )"