[RETIRED] Migrating Looker Backend Database to MySQL

hypersql
mysql

(david) #1

The content of this article has been updated and moved to Looker’s technical documentation here.


Backend database in AWS MySQL
Looker 4.14 Release Notes
(Ken Yeoh) #2

Hi @david,

We’re looking to host our Looker Backend on MySQL, but on the same host that is running Looker.
Do you think this is a bad idea?


(Ian Ross) #3

Hey Ken,

As a general best practice we recommend hosting the Looker instance and MySQL backend on separate hosts so the two do not need to compete for resources. It is possible to host the two on the same machine but this could reduce the performance increases you might otherwise notice if you make the move to an external MySQL backend. Let us know if you have any further questions here.

Thanks,
Ian


(John Norman) #8

Please update the main instructions with the addition from Carter Moar.


(Dave Hunsinger) #10

Hey John,

Thanks so much for helping us make this article better! We’ve cleaned up a few things and made some additions based on your suggestions, let us know what you think!


(John Norman) #11

It looks much better.

RDS creates a database, so instead of actually creating one, I altered the one created by RDS. [e.g., alter database looker_staging DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;).

Do you happen to know if the max_allowed_packet applies to 5.6.x? I did use that version of MySQL rather than 5.7.x.

In our internal docs, we belabored some of the AWS stuff, e.g.,

  1. Click Launch DB Instance
  2. Select MySQL, and then MySQL in the “Production” block
  3. Take defaults, except
    1. DB Instance Class: db.m3.medium
    2. Multi-AZ Deployment: No
    3. Storage Type: General Purpose (SSD)
    4. Allocated Storage: 150GB
    5. DB Instance Identifier: [Your DB Instance Identifier]
    6. Master Username: [Your Master Username]
    7. Master Password:
    8. Confirm Password:
  4. Advanced Settings: Take defaults except
    1. VPC: [Your VPC ID]
    2. Publicly Accessible: No
    3. Availability Zone: [Your Zone]
    4. VPC Security Group(s): [Your Security Group]
    5. Database Name: [Your Database Name]
    6. Enable Encryption: Yes
    7. Master Key: (default) aws/rds
  5. This will give you an endpoint such as looker-staging-metadata-db.cy3guwt4hv1r.us-east-1.rds.amazonaws.com:3306

And a typical MySQL connection . . .

mysql -p -P 3306 -u looker_staging -h looker-staging-metadata-db.cy3guwt4hv1r.us-east-1.rds.amazonaws.com looker_staging

(host name changed.)


(david) #12

The max_allowed_packet parameter should also be set to the maximum value for MySQL 5.6.x. Both MySQL 5.6.x and 5.7.x have much smaller default settings for that parameter than Looker requires.


(Dave Hunsinger) #13

David beat me to it! The max_allowed_packet default has been 4MB since MySQL 5.6.6, when it was 1MB. MySQL 5.5.x, 5.6.x and 5.7.x all support our suggested value - 1073741824.


(John Norman) #14

Thanks you in at least 10 characters.


(John Norman) #15

Hey, I know this page was revised a bit based on suggestions from me and others – did the instructions regarding touch NORESTART get dropped? I have that in my personal notes.


(Dave Hunsinger) #16

Hi John -

Yes, that was removed. It’s a component of our internal maintenance process and has no effect outside of our hosted infrastructure.


(John Norman) #17

On a C4.2xlarge:

looker@reports:~/looker$ export LD_LIBRARY_PATH=$HOME/looker/.tmp/:$LD_LIBRARY_PATH
looker@reports:~/looker$ java -Xms26000m -Xmx26000m -jar looker.jar migrate_internal_data  looker-db
Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000167000000, 18175492096, 0) failed; error='Cannot allocate memory' (errno=12)

(John Norman) #18

Had to use a c4.4xlarge

We really don’t have much else running on that instance except some monitoring. I will look at the memory again later.


(david) #19

Thanks for the feedback, John. The AWS C-series (c3, c4) instance types are optimized for additional CPU without scaling up the RAM at the same level as the general purpose (m3, m4) instance types, so the c4.2xlarge only has half the RAM of the recommended m3.2xlarge. I’ll edit the doc to make that a bit more explicit.


(srivarun lokku) #20

I am trying to migrate Backend database to AWS aurora. I am getting following issues:

Starting Looker: Version 4.12.15-02323d…
Error starting Looker. See log file for details.


(Aleksandrs Vedernikovs) #21

Hey Srivarun,

We do not support AWS aurora for our backend DB as yet.
By default, Looker uses a HyperSQL in-memory database to store its configuration, users, and other data.

On a busy instance this database can grow to be gigabytes in size, which leads to performance issues, Java memory pressure, and extremely long startup times.

In these cases it’s best to replace HyperSQL with a full MySQL backend. Please refer to steps outlined above.


(srivarun lokku) #22

No man. It worked for me. I have fixed it. We can migrate to Aurora. Aurora is Mysql compatable.


(Aleksandrs Vedernikovs) #23

Wow, that is good to know. Would you like to share some insights for our Customers, you more then welcome to do it here Srivarun.


(Graham Kenville) #24

Important note: please make sure to use INNODB for all tables!

This has been the default since MySQL 5.5.5 but it is possible to set it to MYISAM which can cause problems.


(kenneth.vinson) #25

I retired this article. The content can be found in Looker’s technical documentation here.