Soluzioni per l'architettura ospitata dal cliente: procedure dettagliate per i componenti

Questa pagina fa parte di una serie in più parti che illustra l'hosting di Looker, le metodologie di deployment e le best practice per i componenti coinvolti. Questa pagina esplora le pratiche comuni per i componenti specifici dell'architettura di Looker e descrive come configurarli all'interno di un deployment.

Questa serie si compone di tre parti:

Looker ha una serie di dipendenze per l'hosting del server, la gestione dei carichi di lavoro ad hoc e pianificati, il monitoraggio dello sviluppo iterativo dei modelli e così via. In questa pagina, tali dipendenze sono chiamate componenti e ogni componente è trattato in dettaglio nelle sezioni seguenti:

Configurazione host

Sistema operativo e distribuzione

Looker funziona bene sulle versioni più comuni di Linux: RedHat, SUSE e Debian/Ubuntu. Le derivate di queste distribuzioni progettate e ottimizzate per l'esecuzione in un determinato ambiente sono generalmente consentite. Ad esempio, le distribuzioni Google Cloud o AWS di Linux sono compatibili con Looker. Debian/Ubuntu è la varietà Linux maggiormente utilizzata nella community di Looker e le versioni più familiari dell'assistenza Looker. È più facile utilizzare Debian/Ubuntu o un sistema operativo per un provider cloud specifico derivato da Debian/Ubuntu.

Looker viene eseguito nella Java Virtual Machine (JVM). Quando scegli una distribuzione, valuta se le versioni di OpenJDK 8 sono aggiornate. Le distribuzioni precedenti di Linux potrebbero essere in grado di eseguire Looker, ma la versione e le librerie Java richieste da Looker per funzionalità specifiche devono essere aggiornate. Se la JVM non contiene tutte le librerie e le versioni di Looker consigliate, Looker non funzionerà normalmente. Attualmente Looker richiede l'aggiornamento Java HotSpot 1.8 161 o versioni successive oppure OpenJDK 8 181 o versioni successive.

CPU e memoria

I nodi 4 x 16 (4 CPU e 16 GB di RAM) sono sufficienti per un sistema di sviluppo o per un'istanza Looker di test di base utilizzata da un piccolo team. In genere, tuttavia, per l'uso in produzione non è sufficiente. Secondo la nostra esperienza, i nodi 16x64 (16 CPU e 64 GB di RAM) sono un buon equilibrio tra prezzo e prestazioni. Oltre 64 GB di RAM possono influire sulle prestazioni, poiché gli eventi di garbage collection sono a thread singoli e interrompono l'esecuzione di tutti gli altri thread.

Spazio di archiviazione su disco

In genere, 100 GB di spazio su disco sono più che sufficienti per un sistema di produzione.

Considerazioni sui cluster

Looker viene eseguito su una JVM Java e Java può avere difficoltà a gestire una memoria superiore a 64 GB. Come regola generale, se è necessaria una maggiore capacità, devi aggiungere ulteriori nodi 16 x 64 al cluster anziché aumentare le dimensioni dei nodi oltre 16 x 64. Potremmo anche preferire un'architettura in cluster per una maggiore disponibilità.

In un cluster, i nodi di Looker devono condividere determinate parti del file system. I dati condivisi includono:

  • Modelli LookML
  • I modelli LookML degli sviluppatori
  • Connettività server Git

Poiché il file system è condiviso e ospita una serie di repository Git, la gestione degli accessi simultanei e il blocco dei file è fondamentale. Il file system deve essere conforme a POSIX. Il file system di rete (NFS) è noto per il suo funzionamento ed è immediatamente disponibile con Linux. Devi avviare un'istanza Linux aggiuntiva e configurare NFS per condividere il disco. Il NFS predefinito è potenzialmente un single point of failure, quindi valuta una configurazione di failover o ad alta disponibilità.

Anche i metadati di Looker devono essere centralizzati, quindi è necessario eseguire la migrazione del suo database interno a MySQL. Può trattarsi di un servizio MySQL o di un deployment MySQL dedicato. La sezione Database interno (backend) di questa pagina viene fornita in maggiore dettaglio.

Configurazione JVM

Le impostazioni JVM di Looker sono definite nello script di avvio di Looker. Dopo eventuali aggiornamenti, Looker deve essere riavviato per poter modificare il manifest. Le configurazioni predefinite sono le seguenti:

java \
  -XX:+UseG1GC -XX:MaxGCPauseMillis=2000 \
  -Xms$JAVAMEM -Xmx$JAVAMEM \
  -verbose:gc -XX:+PrintGCDetails -XX:+PrintGCTimeStamps \
  -Xloggc:/tmp/gc.log ${JAVAARGS} \
  -jar looker.jar start ${LOOKERARGS}

Risorse

Le impostazioni delle risorse sono definite nello script di avvio di Looker.

JAVAMEM="2300m"
METAMEM="800m"

L'allocazione della memoria per la JVM deve prendere in considerazione l'overhead del sistema operativo su cui è in esecuzione Looker. La regola generale è che la JVM può essere allocata fino al 60% della memoria totale, ma ci sono delle avvertenze a seconda delle dimensioni della macchina. Per le macchine con il minimo indispensabile di 8 GB di memoria totale, consigliamo un'allocazione di 4-5 GB per Java e 800 MB per Meta. Per macchine più grandi, è possibile allocare una percentuale inferiore di memoria per il sistema operativo. Ad esempio, per le macchine con 60 GB di memoria totale, consigliamo un'allocazione di 36 GB a Java e 1 GB a Meta. È importante notare che l'allocazione della memoria Java in genere dovrebbe scalare con la memoria totale della macchina, ma Meta dovrebbe essere sufficiente a 1 GB.

Inoltre, poiché Looker condivide le risorse di sistema con altri processi come Chromium per il rendering, la quantità di memoria allocata a Java deve essere selezionata in base al carico di rendering previsto e alle dimensioni della macchina. Se il carico di rendering dovrebbe essere basso, a Java può essere allocata una quota maggiore della memoria totale. Ad esempio, su una macchina con 60 GB di memoria totale, Java potrebbe essere allocato in modo sicuro a 46 GB, un valore superiore alla raccomandazione generale del 60%. Le allocazioni esatte delle risorse appropriate per ogni deployment variano, quindi utilizza il 60% come base di riferimento e apporta modifiche in base all'utilizzo.

Garbage collection

Looker preferisce utilizzare il garbage collector più moderno disponibile nella sua versione Java. Per impostazione predefinita, il timeout per la garbage collection è di 2 secondi, ma può essere modificato modificando la seguente opzione nella configurazione di avvio:

-XX:MaxGCPauseMillis=2000

Su una macchina più grande con più core, il timeout GC potrebbe essere ridotto.

Log

Per impostazione predefinita, i log di GC di Looker sono archiviati in /tmp/gc.log. Per cambiare questa impostazione, modifica la seguente opzione nella configurazione di avvio:

-Xloggc:/tmp/gc.log

JMX

La gestione di Looker potrebbe richiedere il monitoraggio per contribuire a perfezionare le risorse. Consigliamo di utilizzare JMX per monitorare l'utilizzo della memoria da parte di JVM.

Opzioni di avvio di Looker

Le opzioni di avvio sono archiviate in un file denominato lookerstart.cfg. Questo file proviene nello script shell che avvia Looker.

Pool di Thread

Essendo un'applicazione multi-thread, Looker ha diversi pool di thread. Questi pool di thread vanno dal server web principale a sottoservizi specializzati come pianificazione, rendering e connessioni ai database esterni. A seconda dei flussi di lavoro aziendali, potrebbe essere necessario modificare questi pool rispetto alla configurazione predefinita. In particolare, esistono considerazioni speciali per le topologie dei cluster menzionate nella pagina Best practice e schemi di architettura dell'infrastruttura ospitata dal cliente.

Opzioni di velocità effettiva di pianificazione elevata

Per tutti i nodi non scheduler, aggiungi --scheduler-threads=0 alla variabile di ambiente LOOKERARGS in lookerstart.cfg. Senza i thread dello scheduler, non verranno eseguiti job pianificati su questi nodi.

Per tutti i nodi dello scheduler dedicati, aggiungi --scheduler-threads=<n> alla variabile di ambiente LOOKERARGS in lookerstart.cfg. Per impostazione predefinita, Looker inizia con 10 thread dello scheduler, ma è possibile aumentarlo fino a <n>. Con <n> thread dello scheduler, ogni nodo sarà in grado di eseguire <n> job di pianificazione simultanei. In genere è consigliabile mantenere <n> meno del doppio del numero di CPU. L'host più grande consigliato è 16 CPU e 64 GB di memoria, quindi il limite superiore dei thread dello scheduler deve essere inferiore a 32.

Opzioni relative a velocità effettiva di rendering elevata

Per tutti i nodi non di rendering, aggiungi --concurrent-render-jobs=0 a environment variable in lookerstart.cfg. Without renderer nodes, no render jobs will run on these nodes.

For all dedicated render nodes, add --concurrent-render-jobs=<n> to the LOOKERARGS environment variable in lookerstart.cfg. Looker starts with two render threads by default, but this can be increased to <n>. With <n> render threads, each node will be capable of executing <n> concurrent render jobs.

Each render job can utilize a significant amount of memory. Budget about 2 GB per render job. For example, if the core Looker process (Java) is allocated 60% of the total memory and 20% of the remaining memory is reserved for the operating system, that leaves the last 20% for render jobs. On a 64 GB machine, that leaves 12 GB, which is enough for 6 concurrent render jobs. If a node is dedicated to rendering and is not included in the load balancer pool that is handling interactive jobs, the core Looker process memory can be reduced to allow for more render jobs. On a 64 GB machine, one might allocate approximately 30% (20 GB) to the Looker core process. Reserving 20% for general OS use, that leaves 50% (32 GB) for rendering, which is enough for 16 concurrent render jobs.

Internal (backend) database

The Looker server maintains information about its own configuration, database connections, users, groups, and roles, folders, user-defined Looks and dashboards, and various other data in an internal database.

For a standalone Looker instance of moderate size, this data is stored within an in-memory HyperSQL database embedded in the Looker process itself. The data for this database is stored in the file <looker install directory>/.db/looker.script. Although convenient and lightweight, this database experiences performance issues with heavy usage. Therefore, we recommend starting with a remote MySQL database. If this isn't feasible, we recommend migration to a remote MySQL database once the ~/looker/.db/looker.script file reaches 600 MB. Clusters must use a MySQL database.

The Looker server makes many small reads and writes to the MySQL database. Every time a user runs a Look or an Explore, Looker will check the database to verify that the user is still logged in, the user has privileges to access the data, the user has privileges to run the Look or Explore, etc. Looker will also write data to the MySQL database, including the actual SQL that was run, the time the request started and ended, etc. A single interaction between a user and the Looker application could result in 15 or 20 small reads and writes to the MySQL database.

MySQL

The MySQL server should be version 8.0.x, and must be configured to use utf8mb4 encoding. The InnoDB storage engine must be used. The setup instructions for MySQL, as well as instructions for how to migrate data from an existing HyperSQL database to MySQL, are available on the Migrating the Looker backend database to MySQL documentation page.

When configuring Looker to use MySQL, a YAML file must be created containing the connection information. Name the YAML file looker-db.yml and add the setting -d looker-db.yml in the LOOKERARGS section of the lookerstart.cfg file.

MariaDB

MySQL is dual-licensed, available both as open source and as a commercial product. Oracle has continued to enhance MySQL, and MySQL is forked as MariaDB. The MariaDB equivalent versions of MySQL are known to work with Looker, but they aren't developed for or tested by Looker's engineering teams; therefore, functionality is not supported or guaranteed.

Cloud versions

If you host Looker in your cloud infrastructure, it is logical to host the MySQL database in the same cloud infrastructure. The three major cloud vendors — Amazon AWS, Microsoft Azure, and Google Cloud. The cloud providers manage much of the maintenance and configuration for the MySQL database and offer services to help manage backups, provide rapid recovery, etc. These products are known to work well with Looker.

System Activity queries

The MySQL database is used to store information about how users are using Looker. Any Looker user who has permission to view the System Activity model has access to a number of prebuilt Looker dashboards to analyze this data. Users can also access Explores of Looker metadata to build additional analysis. The MySQL database is primarily used for small, fast, "operational" queries. The large, slow, "analytic" queries generated by the System Activity model can compete with these operational queries and slow Looker down.

In these cases, the MySQL database can be replicated to another database. Both self-managed and certain cloud-managed systems provide simple configuration of replication to other databases. Configuring replication is outside the scope of this document.

In order to use the replica for the System Activity queries, you will create a copy of the looker-db.yml file, for example named looker-usage-db.yml, modify it to point to the replica, and add the setting --internal-analytics-connection-file looker-usage-db.yml to the LOOKERARGS section of the lookerstart.cfg file.

The System Activity queries can run against a MySQL instance or a Google BigQuery database. They are not tested against other databases.

MySQL performance configuration

In addition to the settings required to migrate the Looker backend database to MySQL, highly active clusters may benefit from additional tuning and configuration. These settings can be made to the /etc/my.cnf file, or through the Cloud Console for cloud-managed instances.

The my.cnf configuration file is divided into several sections. The setting changes discussed below are made in the [mysqld] section.

Set the InnoDB buffer pool size

The InnoDB buffer pool size is the total RAM that is used to store the state of the InnoDB data files in memory. If the server is dedicated to running MySQL, the innodb_buffer_pool_size should be set to 50%-70% of total system memory.

If the total size of the database is small, it is allowable to set the InnoDB buffer pool to the size of the database rather than 50% or more of memory.

For this example, a server has 64 GB of memory; therefore, the InnoDB buffer pool should be between 32 GB and 45 GB. Bigger is typically better.

[mysqld]
...
innodb_buffer_pool_size=45G

Set the InnoDB buffer pool instances

When multiple threads attempt to search a large buffer pool, they could contend. To prevent this, the buffer pool is divided into smaller units that can be accessed by different threads without conflict. By default, the buffer pool is divided into 8 instances. This creates the potential for an 8 thread bottleneck. Increasing the number of buffer pool instances reduces the chance of a bottleneck. The innodb_buffer_pool_instances should be set so that each buffer pool gets at least 1 GB of memory.

[mysqld]
...
innodb_buffer_pool_instances=32

Optimize the InnoDB log file

When a transaction is committed, the database has the option to update the data in the actual file, or it can save details about the transaction in the log. If the database crashes before the data files have been updated, the log file can be "replayed" to apply the changes. Writing to the log file is a simple append operation. It is efficient to append to the log at commit time, then batch up multiple changes to the data files and write them in a single IO operation. When the log file is filled, the database has to pause processing new transactions and write all the changed data back to disk.

As a general rule of thumb, the InnoDB log file should be large enough to contain 1 hour of transactions.

There are typically two InnoDB log files. They should be about 25% of your InnoDB buffer pool. For an example database with a 32 GB buffer pool, the InnoDB log files should total 8 GB, or 4 GB per file.

[mysqld]
...
innodb_log_file_size=8GB

Configure InnoDB IO capacity

MySQL will throttle the speed at which writes are recorded to the disk so as not to overwhelm the server. The default values are conservative for most servers. For best performance use the sysbench utility to measure the random write speed to the data disk, then use that value to configure the IO capacity so that MySQL will write data more quickly.

On a cloud-hosted system, the cloud vendor should be able to report the performance of the disks used for data storage. For a self-hosted MySQL server, measure the speed of random writes to the data disk in IO operations per second (IOPS). The Linux utility sysbench is one way to measure this. Use that value for the innodb_io_capacity_max, and a value one-half to three-quarters of that for innodb_io_capacity. So, in the example below, we would see the values if we measured 800 IOPS.

[mysqld]
...
innodb_io_capacity=500
innodb_io_capacity_max=800

Configure InnoDB threads

MySQL will open at least one thread for each client being served. If many clients are connected simultaneously, that can lead to a huge number of threads being processed. This can cause the system to spend more time swapping than processing.

Benchmarking should be done to determine the ideal number of threads. To test, set the number of threads between the number of CPUs (or CPU cores) on the system and 4x the number of CPUs. For a 16-core system, this value is likely between 16 and 64.

[mysqld]
...
innodb_thread_concurrency=32

Transaction durability

A transaction value of 1 forces MySQL to write to disk for every transaction. If the server crashes, the transaction won't be lost, but database performance will be impacted. Setting this value to 0 or 2 can improve performance, but it will come at the risk of losing a couple of seconds' worth of data transactions.

[mysqld]
...
innodb_flush_log_at_trx_commit=1

Set the flush method

The operating system normally does buffering of writes to the disk. Since MySQL and the OS are both buffering, there is a performance penalty. Reducing the flush method one layer of buffering can improve performance.

[mysqld]
...
innodb_flush_method=O_DIRECT

Enable one file per table

By default, MySQL will use a single data file for all data. The innodb_file_per_table setting will create a separate file for each table, which improves performance and data management.

[mysqld]
...
innodb_file_per_table=ON

Disable stats on metadata

This setting disables the collection of stats on internal metadata tables, improving read performance.

[mysqld]
...
innodb_stats_on_metadata=OFF

Disable the query cache

The query cache is deprecated, so setting the query_cache_size and query_cache_type to 0 disables it.

[mysqld]
...
query_cache_size=0
query_cache_type=0

Enlarge the join buffer

The join_buffer is used to perform joins in memory. Increasing it can improve certain operations.

[mysqld]
...
join_buffer_size=512KB

Enlarge the temporary table and max heap sizes

The tmp_table_size and max_heap_table_size set reasonable defaults for temporary in-memory tables, before they are forced to disk.

[mysqld
...
tmp_table_size=32MB
max_heap_table_size=32MB

Adjust the table open cache

The table_open_cache setting determines the size of the cache that holds the file descriptors for open tables. The table_open_cache_instances setting breaks the cache into a number of smaller parts. There is a potential for thread contention in the table_open_cache, so dividing it into smaller parts helps increase concurrency.

[mysqld]
...
table_open_cache=2048
table_open_cache_instances=16

Git service

Looker is designed to work with a Git service to provide version management of the LookML files. Major Git hosting services are supported, including GitHub, GitLab, Bitbucket, etc. Git service providers offer additional value adds such as a GUI to view code changes and support for workflows like pull requests and change approvals. If required, Git can be run on a plain Linux server.

If a Git hosting service is not appropriate for your deployment because of security rules, many of these service providers offer versions that can be run in your own environment. GitLab, in particular, is commonly self-hosted and can be used as an open source product with no license cost or as a supported licensed product. GitHub Enterprise is available as a self-hosted service and is a supported commercial product.

The following sections list nuances for the most common service providers.

GitHub/GitHub Enterprise

The Setting up and testing a Git connection documentation page uses GitHub as an example.

GitLab/gitlab.com

Refer to the Using GitLab for version control in Looker Looker Community post for detailed setup steps for GitLab. If your repo is contained within subgroups, these can be added to the repo URL using either the HTTPS or SSH format:

https://gitlab.com/accountname/subgroup/reponame

git@gitlab.com:accountname/subgroup/reponame.git

Additionally, there are three different ways you can store Looker-generated SSH keys in GitLab: as a user SSH key, as a repository deploy key, and as a global shared deploy key. A more in-depth explanation can be found in the GitLab documentation.

Google Cloud Source

Refer to the Using Cloud Source Repositories for version control in Looker Community Post for steps to set up Git with Cloud Source Repositories.

Bitbucket Cloud

Refer to the Using Bitbucket for version control in Looker Community Post for steps for setting up Git with Bitbucket Cloud. As of August 2021, Bitbucket Cloud does not support secrets on deploy webhooks.

Bitbucket Server

To use pull requests with Bitbucket Server, you may need to complete the following steps:

  1. When you open a pull request, Looker will automatically use the default port number (7999) in the URL. If you are using a custom port number, you will need to replace the port number in the URL manually.
  2. You will need to hit the project's deploy webhook to sync the production branch in Looker with the repo's master branch.

Phabricator diffusion

Refer to the Setting up Phabricator and Looker for version control Community Post for steps on setting up Git with Phabricator.

Network

Inbound connections

Looker web application

By default, Looker listens for HTTPS requests on port 9999. Looker uses a self-signed certificate with a CN of self-signed.looker.com. The Looker server can alternately be configured to do the following:

  1. Accept HTTP connections from an SSL-termination load balancer/proxy, with the --ssl-provided-externally-by=<s> startup flag. The value should either be set to the IP address of the proxy, or to a host name that can be locally resolved to the IP address of the proxy. Looker will accept HTTP connections only from this IP address.
  2. Use a customer supplied SSL certificate, with the --ssl-keystore=<s> startup flag.

Looker API

The Looker API listens on port 19999. If the installation requires access to the API, then the load balancer should have the requisite forwarding rules. The same SSL considerations apply as with the main web application. We recommend using a distinct port from the web application.

Load balancers

A load balancer is often used to accept an HTTPS request at port 443 using the customer's certificate, then forward the request to the Looker server node at port 9999 using the self-signed certificate or HTTP. If load balancers are using Looker's self-signed certificate, they must be configured to accept that certificate.

Idle connections and timeouts

When a user starts a large request in Looker, that could result in a query that could be expensive to run on the database. If the user abandons that request in any way — by shutting the lid on their laptop, disconnecting from the network, killing that tab in the browser, etc. — Looker wants to know and terminate that database query.

To handle this situation, when the client web application makes a request to run a database query, the browser will open a socket connection via a long-lived HTTP request to the Looker server. This connection will sit open and idle. This socket will get disconnected if the client web application is killed or disconnected in any way. The server will see that disconnect and cancel any related database queries.

Load balancers often notice these open idle connections and kill them. In order to run Looker effectively, the load balancer must be configured to allow this connection to remain open for as long as the longest query a user might run. A timeout of at least 60 minutes is suggested.

Outbound connections

Looker servers can have unrestricted outbound access to all resources, including the public internet. This simplifies many tasks, such as installing Chromium, which requires access to the package repositories for the Linux distribution.

The following are outbound connections that Looker may need to make.

Internal database connection

By default, MySQL listens for connections on port 3306. The Looker nodes must be able to initiate connections to MySQL on this port. Depending on how the repository is hosted, you may need to traverse a firewall.

External services

Looker's telemetry and license servers are available via HTTPS on the public internet. Traffic from a Looker node to ping.looker.com:443 and license.looker.com:443 may need to be added to an allowlist.

Data warehouse connections

Cloud-hosted databases may require a connection via the public internet. For example, if you are using BigQuery, then accounts.google.com:443 and www.googleapis.com:443 may need to be added to an allowlist. If the database is outside of your own infrastructure, consult with your database host for network details.

SMTP services

By default, Looker sends outgoing mail via SendGrid. That may require adding smtp.sendgrid.net:587 to an allowlist. The SMTP settings can be changed in the configuration to use a different mail handler as well.

Action hubs, action servers, and webhooks

Many scheduler destinations, in particular webhooks and the ones that are enabled in the Looker Admin panel, involve sending data via HTTPS requests.

  • For webhooks, these destinations are specified at runtime by users, and may be contrary to the goal of firewalling outbound connections.
  • For an action hub, these requests are sent to actions.looker.com. Details can be found in our Looker Action Hub configuration documentation.
  • For other action servers, these requests are sent to the domains specified in the action server's configuration by administrators in the Looker Admin panel.

Proxy server

If the public internet cannot be reached directly, Looker can be configured to use a proxy server for HTTP(S) requests by adding a line like the following to lookerstart.cfg:

JAVAARGS="-Dhttp.proxyHost=myproxy.example.com
  -Dhttp.proxyPort=8080
  -Dhttp.nonProxyHosts=127.0.0.1|localhost
  -Dhttps.proxyHost=myproxy.example.com
  -Dhttps.proxyPort=8080"

Note that internode communications happen over HTTPS, so if you use a proxy server and your instance is clustered, you will usually want to add the IPs/host names for all the nodes in the cluster to the Dhttp.nonProxyHosts argument.

Internode communications

Internal host identifier

Within a cluster, each node must be able to communicate with the other nodes. To allow this, the host name or IP address of each node is specified in the startup configuration. When the node starts up, this value will be written into the MySQL repository. Other members of the cluster can then refer to those values to communicate with this node. To specify the host name or IP address in the startup configuration, add -H node1.looker.example.com to the LOOKERARGS environment variable in lookerstart.cfg.

Since the host name must be unique per node, the lookerstart.cfg file needs to be unique on each instance. As an alternative to hardcoding the host name or IP address, the command hostname -I or hostname --fqdn can be used to find these at runtime. To implement this, add -H $(hostname -I) or -H $(hostname --fqdn) to the LOOKERARGS environment variable in lookerstart.cfg.

Internal ports

In addition to the ports 9999 and 19999, which are used for the web and API servers, respectively, the cluster nodes will communicate with each other through a message broker service, which uses ports 1551 and 61616. Ports 9999 and 19999 must be open to end-user traffic, but 1551 and 61616 must be open between cluster nodes.