Database Issues and Best Practices

Analytics Database Best Practices

Jive requires that your Analytics database is separate from the core database, ideally on two separate servers. While we support storing the analytics and core databases on one server, you should understand why isolation must be ensured:

Analytics has a very different access pattern to the core database, e.g., many streaming writes, occassional reads, and busy activity once a day when the ETL runs.
The storage requirements for Analytics are very different from the core database requirements because analytics indexes may far exceed the core database if there is heavy activity retained for a long period of time.
The core application database is more critical than the Analytics database. Therefore, the Analytics database should not be in a position to demand resources that the core database may need.
The Analytics database is intended to be accessed by users running ad-hoc queries. Because the application cannot predict which queries users may run, it is possible that a bad query could execute and thus consume all of the server's capacity.

MySQL Character Encoding Issues

MySQL does not have proper Unicode support, which makes supporting postings in non-Western languages difficult. However, the MySQL JDBC driver has a workaround which you can enable by adding <mysql><useUnicode>true</useUnicode></mysql> to the <database> section of your jive_startup.xml file. When using this setting, you must also set the Jive character encoding to UTF-8 in the Admin Console under System > Management > Locale.

MySQL 4.1 introduced better support for character encodings than previous versions. This functionality assigns a default character encoding to the database and its tables and columns. It's best to set the default character encoding for your database before installing the Jive schema so that you can be sure that you will not have encoding problems in the future. After creating your database, execute the following line in the MySQL console:

ALTER DATABASE <database name> DEFAULT CHARACTER SET <character set>;

For example, if you plan on using UTF-8, you should enable the JDBC driver workaround mentioned above and then execute this line in the MySQL console:

ALTER DATABASE <database name> DEFAULT CHARACTER SET utf8;

MySQL Max Attachment Size Issues

You can fix the maximum attachment size problem on a MySQL server by following the directions here: http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html

The easiest way to change the MySQL server setting on Windows is to add a line to your my.ini file (you'll find this file in a location such as C:\database\mysql\5.0.19); on Linux, look for the my.cnf file instead. Add the following line after the [mysqld] section heading:

max_allowed_packet = 500M

After you add the line, restart MySQL.

MySQL Adjust the InnoDB Buffer Pool Size

When you have the database running on a dedicated server, you should increase the InnoDB buffer pool size from the default (8 MB) to up to 80 percent of the computer's available memory. If the computer has 2 GB of RAM or less, you should think about setting the buffer to something less than 80 percent to ensure that the operating system has enough memory to avoid swapping.

See the MySQL documentation for more on configuration.

MySQL Case Sensitivity Issues

To avoid upgrade problems, use lower-case table names in MySQL and set the lower_case_table_names system variable to 1 in the MySQL configuration.

Set up UTF-8 Collation if Supported

If supported, you must use UTF-8 as the database and application encoding, and it must be configured for the Jive Core and the Analytics databases. See your database administrator for more information.

SQL Server Case Sensitivity Issues

SQL Server is case insensitive by default, creating special case sensitivity issues for Jive, which requires a DBMS that matches strings with case sensitivity. The SQL Server issues differ between versions 2005 and 2008.

SQL Server 2005: Set collation to SQL_Latin1_General_CP1_CI_AI, which specifies that table names and indexes are case insensitive. Note that this creates a limitation in which the application does not support multiple usernames in which the only difference is case, such as "Gladys" and "gladys". Therefore, your username values must differ in other ways.

SQL Server 2008: Set collation to Latin1_General_CP1_CI_AS, which specifies case-insensitivity, not distinguishing between upper and lower case. If database collation is not specified, server collation will be used.

SQL Server 2008 R2: Set collation to Latin1_General_CP1_CI_AS, which specifies case-insensitivity, not distinguishing between upper and lower case. If database collation is not specified, server collation will be used.

You'll find more information on adjusting SQL Server settings in the SQL Server documentation:

Setting and Changing the Server Collation

Setting and Changing the Database Collation