Looker settings for migrating the backend default HyperSQL in memory Database to MySQL

Looker settings for migrating the back-end default HyperSQL in memory Database to MySQL:

Looker uses by default HyperSQL in memory db to store its configuration, users and other data. Over a period of time this might increase in size and can cause performance issues, Java memory pressures and long startup time etc.

Looker recommends to replace the HyperSQL db to MySQL db when internal HyperSQL db exceeds 600MB.

Verify for looker.script file size:

cd looker/.db

ls -lah looker.script

Note: Looker requires MySQL 5.7.x instance to use as the backend.

Tune MySQL:

>mysqld --user=root max_allowed_packet=1073741824

>mysqld --user=root character_set_client = utf8mb4

>mysqld --user=root character_set_results=utf8mb4

>mysqld --user=root character_set_connection=utf8mb4

>mysqld --user=root character_set_database=utf8mb4

>mysqld --user=root character_set_server=utf8mb4

>mysqld --user=root collation_connection=utf8mb4_general_ci

>mysqld --user=root collation_server=utf8mb4_general_ci

In MySQL shell:

SET GLOBAL binlog_format = 'MIXED';

or

SET GLOBAL binlog_format = 'ROW';

Create a Database Credentials File:

cd looker

Create looker-db.yml file and set file permissions to 600 and owned y looker user running application, so looker knows to which MySQL db to talk.

dialect: mysql

host: <DB_host-name>

username: <DB_user-name>

password: <DB_password>

database: <DB_name>

port: 3306

Create a Database and User:

Replace <DB_charset> and <DB_collation> with the chosen character set and collation that matches the RDS instance param group settings (for true UTF8 support, we recommend utf8mb4 and utf8mb4_general_ci).

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

set password for <DB_username> = password ('<DB_password>');

create database <DB_name> default character set <DB_charset> default collate <DB_collation>;

grant all on <DB_name>.* to '<DB_username>'@'localhost';

grant all on looker_tmp.* to '<DB_username>'@'localhost';

Back Up the .db Directory

cp -r .db .db-backup

tar -zcvf db-backup.tar.gz ./.db-backup

Migrate the Database

i) On looker host:

cd looker

./looker stop

vi looker

ii) Add second line in the file:

exit

iii) Make sure you have atleast 26G of memory for heap allocation or change accordingly.

cd looker

java -Xms26000m -Xmx26000m -jar looker.jar migrate_internal_data  looker-db.yml

iv) When running the migrate_internal_data step, libcrypt may not be found and a stack trace will appear, starting with this:

NotImplementedError: getppid unsupported or native support failed to load

ppid at org/jruby/RubyProcess.java:752

ppid at org/jruby/RubyProcess.java:749

If this happens, set the LD_LIBRARY_PATH manually before executing the Java command:

export LD_LIBRARY_PATH=$HOME/looker/.tmp/:$LD_LIBRARY_PATH

v) reboot system

Starting looker:

i) Edit the Looker startup script and delete the exit line you added earlier.

cd looker

vi looker

Here, remove the line added as "exit"

ii) Ensure that there are no arguments defined in LOOKERARGS in the startup script. Instead, any arguments should move to the lookerstart.cfg file so that they will not be overwritten by new versions of the startup script. Save and exit the startup script.

iii) Edit lookerstart.cfg. It should look similar to the following:

LOOKERARGS="-d looker-db.yml"

If there were any other arguments in the Looker startup script, add them to the lookerstart.cfg file.

iv) Archive the .db directory, if it is not archived already.

mv .db .db-backup

tar -zcvf db-backup.tar.gz ./.db-backup

rm -rf ./.db-backup/

v) Start Looker:

./looker start

Verification:

i) Verifiy if looker is using MySQL as backend

netstat -an | grep 3306

***