Documentation Performance FAQs

Improve query performance

Background

Chartio runs its queries on the databases it’s connected to, so the server will take the additional load of those queries. Chartio opens a connection to your database for every query that it sends, and it does this concurrently for up to 100 queries by default. Each connection and query takes a certain amount of server resources (RAM, CPU time, Input/Output, etc.) to process the request.

Database servers have a limited amount of resources. As more queries are sent, each individual query has to compete with all of the other running queries for the limited resources. Additionally, complex queries can tie up more server resources for a longer period of time.

The number of concurrent queries can increase when more users are viewing different dashboards at the same time, or with an increase in the number of charts per dashboard.

We do many things to minimize that potential load:

All queries are dirty unlocking reads
Caching of results at your set interval (default five minutes)
Queuing of query results if multiple people are requesting the query at the same time

If you are concerned about an additional load to your database, we recommend adding more memory, creating a replica or running off of a backup database if you are not already. For more information, see our tips for improving query performance below.

What if my queries seem to take longer in Chartio than against my database directly?

If your queries seem to take longer in Chartio than when you run them directly against your database, the cause can generally be attributed to the fact that Chartio is running many queries at once.

To test this, try refreshing one query at a time in Chartio, and check the query log duration. The query log will show you whether there are any other running queries - be sure to cancel any running queries before you begin the test. Compare the query duration in Chartio against the same query when executed from another workbench outside Chartio.

Suggestions for improving query performance

These are merely suggestions and may not all apply to your database. You will need to review the options and determine which suggestions are applicable.

Upgrade your database (recommended)

Upgrading your database is the recommended option. This can involve upgrading your memory and/or CPU. This gives your server more resources to work with and is the fastest, easiest way to improve query performance.

Identify long-running queries

Identify long-running queries and either optimize them or put them into a Data Store to run less often.

To locate long-running queries:

sort your query log results by Duration descending
export your Query Log and upload it as a CSV for analysis
view the Usage tab in your data source settings

Create Data Stores for commonly-queried datasets (recommended)

Chartio allows you to save query results as a new data source, which is updated on a regular basis. If you use the same data across multiple charts, Data Stores can be a huge help in taking the load off your database. For more information, see our Data Store documentation.

Limit number of concurrent queries

If your database is large, and your queries contain complex logic and consistently read large portions of the data, you may want to limit the number of concurrent queries.

Chartio defaults to a maximum of 100 concurrent connections. If you’d like to lower this limit, please send us an email at support@chartio.com and we’d be happy to adjust it for you.

Optimize queries

If you find that certain queries take longer to run, there are steps you can take to limit the load they put on your database.

return a smaller subset of data (setting a limit on the query, aggregating the data)
limit use of WHERE clauses - especially IN filters with a long list of values (this includes Dashboard Controls such as categorical Dropdowns)
if you find yourself writing similar aggregated queries across multiple charts, consider creating one or more summary tables. This way, the query results are already aggregated and/or filtered, and are saved to a table you can query directly.

Lower Maximum Query Duration

In your Data Source settings, there is a Maximum Query Duration setting. Any queries that take longer than the amount of time specified will be canceled. This can prevent long-running queries from tying up resources on your database server and slowing down other queries.

Increase Cache Duration

The Cache Duration is a dashboard setting that specifies how long query data is considered “fresh”. For Dashboards that are currently open, new queries are sent to the database at this frequency. Increase this value to send queries to your database less often.

Create Indexes

Create indexes or clustered indexes in your database. Indexes are like a table of contents for your database rows, which allows for faster data lookup.