Other MySQL Settings Worth Considering

There are a number of other useful options, especially for performance improvements. Please note, you will have to review them as some of them may impact data reliability and are useful for performance or load tests installations only.

# InnoDB seems to be a better choice
# so lets make it a default DB engine
default-storage-engine = innodb

Some the general MySQL settings which mainly affect performance:

key_buffer = 64M
max_allowed_packet = 32M
sort_buffer_size = 64M
net_buffer_length = 64K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 64M

InnoDB specific settings:

# Keep data in a separate file for each table
innodb_file_per_table = 1
# Allocate memory for data buffers
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 100M
# A location of the MySQL database
innodb_data_home_dir = /home/databases/mysql/
innodb_log_group_home_dir = /home/databases/mysql/
# The main thing here is the 'autoextend' property
# without it your data file may reach maximum size and
# no more records can be added to the table.
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
# Some other performance affecting settings
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16

These settings may not be fully optimized for your system, and have been only tested on our systems. If you have found better settings for your systems, feel free to let us know.

Support for emoji and other icons

Tigase Database Schema can support emojis and other icons, however by using UTF-8 in mysqld settings will not allow this. To employ settings to support emojis and other icons, we recommend you use the following in your MySQL configuration file:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_bin
character-set-client-handshake = FALSE

Doing this, Tigase XMPP Server Database will still use utf8 character set, with utf8_general_ci as collation, and only fields which require support for emojis will be converted to utf8mb4.

Note

If for some reason, with above settings applied to your MySQL instance, you still receive java.sql.SQLException: Incorrect string value: ` you should add to your database URI passed in Tigase XMPP Server following configuration `&useUnicode=true&characterEncoding=UTF-8. If even this fails too, then you may try adding &connectionCollation=utf8mb4_bin as a last resort. This changes situation from previous versions that shipped older MySQL JDBC connector.

Note

Tigase XMPP Server databases should be created with utf8_general_ci collation as it will work properly and is fastest from utf8mb4_general_ci collations supported by MySQL