Introduction

Getting Started

  • For information on how to install SiriDB, please visit the SiriDB Server github page.

  • For information on how to create a new database or expand an existing database, please visit the SiriDB Admin github page.

Other SiriDB projects:

Query data

select

Syntax:

select <points/functions> from <match_series [<where>]> [<time_range>] [<merge_data>]

Example:

# Select points from "series-001"
select * from "series-001"

functions

It's possible to select multiple aggregate functions in one query. This has some advantages over performing multiple queries since the database in this case only needs to search for the series and points once. To find the requested aggregate in the result we must add a prefix and/or suffix to the series name to make the name unique. Note that a prefix and/or suffix is only required when querying multiple aggregates.

Example:

# Select both the min and max grouped by 5 minutes from "series-001"
select min(5m) prefix "min-", max (5m) prefix "max-" from "series-001"

For more help on aggregate functions see help functions.

Combine functions

The aggregate function can be used together by parsing the result of one function to the next. It's also possible to use the same function twice which can be useful with for example difference.

Example:

# Select the median grouped by 1 minute and return the difference for that result
select median (1m) => difference () from "series-001"

match_series

see help list series on how to match series.

time_range

An optional time range can be given to select only a part of the series data. If no time range is provided SiriDB returns all the data available. As a time range we can use before, after or between .. and ..

When using after you basically set a start time, with before a end time and when using between .. and .. you set both a start and end time. Points having the exact the start time are included in the result, points with the exact end time are excluded from the result.

Note

It's safe to use now multiple times in query. SiriDB only calculates now one time while processing a query. This way you can be sure that now has the same value.

Examples:

# Select all points from "series-001" in the last 24h
select * from "series-001" after now - 1d

# Select all points from "series-001" today
select * from "series-001" between now - (now % 1d) and now - (now % 1d) + 1d

# Select all points from "series-001" before November, 2015
select * from "series-001" before "2015-11"

merge_data

When selecting points from multiple series you can merge the data together in result. Most of the time you also want to provide an aggregate function with the merge so series get really merged into one series. Even with merge it's still possible to use aggregate functions on the series before they are merged.

Note

Sometimes it does not matter for the end result if you use an aggregate function on the series or only while merging. However, if you have multiple pools it can be an advantage to aggregate the series and the merge. This is an advantage because each pool can do some aggregate work and only send the aggregated result to the server processing the query.

For example:

select * from /series.*/ merge as "series" using sum(1h)

will have the exact same result as

select sum(1h) from /series.*/ merge as "series" using sum(1h)

but the last one will be faster, assuming you are using a SiriDB cluster and /series.*/ contains multiple series spread out over multiple pools.

Examples:

Note

In the examples below we assume there are no points in the future. If you have points in the future and only want points from 7 days ago up till now you can use between now - 7d and now. Since we assume our series have no points in the future we use after now - 7d.

# We want the average value per 1 hour over the last 7 days over s01
# and s02. The series should weight equal to each other but s01 has
# a point each 2 seconds while s02 only has a point each 5 seconds.
# We solve this by first getting the mean value for each series
# by 1 hour before merging the series.
#
# Note that we use mean(1) while merging. This means we group by 1 second or
# millisecond depending on the time precision. We can do this because the
# series are already grouped by 1h and therefore have re-indexed timestamps
# at precisely each hour.
select mean(1h) from "s01", "s02" after now - 7d merge as "merged_s" using mean(1)

# We want the number of points s01 and s02 have over the last 7 days.
# Note: when having no timestamps after now this will result in one
#       value with timestamp *now*
select count(now) from "s01", "s02" after now - 7d merge as "merged_s" using sum(1)

# We have s01 and s02 representing counter data. We want to sum the
# values per 4 hours over January, 2015 and show this as one series.
select sum(4h) from "s01", "s01" between "2015-01" and "2015-02" merge as "merged_s" using sum(1)

aggregate functions

SiriDB supports multiple build-in aggregation and filter functions. Using these functions can be useful to reduce network traffic. Note that multiple functions can be combined using the arrow => sign. (see help select for more information on how to use and combine functions)

List of supported aggregation and filter functions:

limit

Syntax:

limit(max_points, aggr_function)

Returns at most max_points and uses a given aggregation function to reduce the number of points if needed.

Example:

# Returns at most 100 points for 'my-series'. The original values are
# returned in case hundred or less points are found. In case more points
# are found a mean aggregation function is used.
select limit(100, mean) from "my-series"

count

Syntax:

count(ts)

Returns an integer value.

Count can be used to calculate points over a period of time.

Example:

# Get the number of points in 'series-001' over the past 24 hours.
select count(now) from "series-001" between now - 1d and now

sum

Syntax:

sum(ts)

Returns an integer or float value depending on the series data type.

Sum can be used when you want to know the sum of the values over a period of time.

Example:

# Get the sum of the values collected over the last 24 hours per hour.
select sum(1h) from "series-001" between now - 1d and now

max

Syntax:

max(ts)

Returns an integer or float value depending on the series data type.

Max can be used to identify the highest value in the selected time window.

Example:

# Get the maximum value in 'series-001' over the last week.
select max(now) from "series-001" between now - 1w and now

min

Syntax:

min(ts)

Returns an integer or float value depending on the series data type.

Min is the opposite of max, you identify the lowest value in the selected time window.

Example:

# Get the minimum value per day from 'series-001' between two dates.
select min(1d) from "series-001" between '2016-11-14' and '2016-11-21'

mean

Syntax:

mean(ts)

Returns a float value.

Mean is used to calculate the average values per selected time window.

Example:

# Get average value of 'series-001' up until now.
select mean(now) from "series-001" before now

median

Syntax:

median(ts)

Returns a float value.

The median is a robust measure of central location, and is less affected by the presence of outliers in your data. When the number of data points is odd, the middle data point is returned as float value. When the number of data points is even, the median is interpolated by taking the average of the two middle values.

median_high

Syntax:

median_high(ts)

Returns an integer or float value depending on the series data type.

The high median is always a member of the data set. When the number of data points is odd, the middle value is returned. When it is even, the larger of the two middle values is returned.

median_low

Syntax:

median_low(ts)

Returns an integer or float value depending on the series data type.

The low median is always a member of the data set. When the number of data points is odd, the middle value is returned. When it is even, the smaller of the two middle values is returned.

variance

Syntax:

variance(ts)

Returns a float value.

Returns the sample variance of data, an iterable of at least two real-valued numbers. Variance, or second moment about the mean, is a measure of the variability (spread or dispersion) of data. A large variance indicates that the data is spread out; a small variance indicates it is clustered closely around the mean.

pvariance

Syntax:

pvariance(ts)

Returns a float value.

Returns the population variance of data, a non-empty iterable of real-valued numbers. Variance, or second moment about the mean, is a measure of the variability (spread or dispersion) of data. A large variance indicates that the data is spread out; a small variance indicates it is clustered closely around the mean.

difference

Syntax:

difference([ts])

Returns an integer or float value depending on the series data type.

Difference without arguments is used to get the difference between values. As an optional argument you can specify a time period. In this case the function returns the difference between the first value and the last value within the time window.

Example:

# Select difference between values in series-001.
select difference() from 'series-001'

derivative

Syntax:

derivative([ts [, ts]])

Returns a float value.

The derivative can be used to get the difference per time unit. When no optional arguments are used we return the difference per one unit. For example, in a database with second precision the return value will be the difference per second. Optionally another time unit can be used. A second argument can be used to set a time period. This time period will be used to get the difference between the first and last value within the time window.

Example:

# Select the difference per second for values in series-001.
select derivative(1s) from 'series-001'

# Select the difference per second between the first and last value
# within each hour for values in 'series-001'
select derivative(1s, 1h) from 'series-001'

filter

Syntax:

filter(<operator> <value>)

Returns an integer or float value depending on the series data type.

Filter is used to filter the result by values.

Example:

# Select all values from 'series-001' except where the value is 0
select filter(!= 0) from 'series-001'

# Select all positive values from 'series-001'
select filter(> 0) from 'series-001'

debug (timeit)

Can be placed in front of any query and will return information about the time it took to process the query.

Syntax:

timeit <any_query>

Example result:

{
    "__timeit__": [
        {
            "time": 0.001156334212755393,
            "server": "server04.siridb.net:9010"
        },
        {
            "time": 0.001481771469116211,
            "server": "server01.siridb.net:9010"
        }
    ]
}

Here __timeit__ is an array containing response data from each server involved in processing the query. The last server in this list is the server who has received the query. Since this server is responsible for sending the response it has to wait for all other servers to complete and therefore the query time for this server will always be the highest value of all servers in the list.

Series

list series

syntax

list series [columns] [match_series] [where ...] [limit ...]

columns

Valid columns are:

  • name: Series name
  • pool: Pool where the series is assigned to
  • start: Time-stamp of the first value in the series
  • end: Time-stamp of the last value in the series
  • length: The number of points in a series
  • type: The series type. ("integer" or "float")

When no columns are provided the default is used. (name)

match series

Series can be matched using different methods. Groups can help to quickly get the required series even in a database with millions of unique series.

syntax:

<series_name | regular_expression | group> [update_function <match_series>]

series name

A series name is a string containing the series name. An error is raised when the given series name is not found in SiriDB. (Note that this exception is not raised when the database is re-indexing. In case of re-indexing we just don't return the series like a search using regular expression)

An advantage of using series names in a SiriDB cluster is that we know in which pool the series exists. The given query will therefore only be send to the applicable pool(s). We don't know which pool has series when using a regular expression or group match so each pool then needs the query.

Example:

list series 'series-001'

regular expression

Regular expressions can be used to select series. Note that each pool in a SiriDB cluster will look for matching series. If you plan to use a regular expression multiple times, you should consider creating a group for the expression.

Example:

# list all series starting with "linux"
list series /linux.*/

# list all series starting with "linux" (case-insensitive)
list series /linux.*/i

# list all series not starting with "linux"
list series /(?!linux).*/

# list all series not containing "linux"
list series /((?!linux).)*/

group

Groups are basically cached regular expression and can be used together with normal regular expressions. When you use a regular expression to match series in a group it's best to first select the group and then the regular expression. This way the regular expression only needs to validate series inside the group.

Examples:

# list all series in group "linux"
list series `linux`

# list all series in group "linux" with "cpu" in the name
# note that we first select the group so the regular expression only
# needs to be validated on series in the group.
list series `linux` & /.*cpu.*/

update functions

When selecting series you can combine series-names, regular-expressions and groups. Update functions tell SiriDB how to combine selection. SiriDB knows four update functions:

  • difference (alias: -)
  • symmentric_difference (alias: ^)
  • union (aliases: , and |)
  • intersection (alias: &)

examples

# list multiple series using union (we actually use the alias here)
list series 'series-001', 'series-002', 'series-003'

# list series in group "linux" except series which are also in group "cpu"
list series `linux` - `cpu`

# list series when member of group "linux" or group "cpu" but not both
list series `linux` ^ `cpu`

# list series that are both members of `linux` and `cpu` except when
# a series name contains "test".
list series `linux` & `cpu` - /.*test.*/

# list series in group `linux` and view their length.
list series name, length `linux`

# list series in group `linux` which have their last data point more
# than 100 days ago
list series `linux` where end < now - 100d


# sample output (list series)
{
    "columns": ["name"],
    "series": [
        ["series-001"],
        ["series-002"]
    ]
}

count series

Syntax:

count series [length] [match_series] [where ...]

Count series in the SiriDB cluster. For more information about how to select series see help list series

Count series length gives the total number of points for the selected series.

Examples:

# Get number of series
count series

# Get the total number of points in the database
count series length

# Get number of series in group `group_server01`
count series `group_server01`

# Get number of points for series in pool 0
count series length where pool == 0

# Get the number of series which started in the last week
count series where start > now - 1w and start <= now

Example output (series):

{"series": 1105946}

Example output (series length):

{"series_length": 77450345251}

drop series

Syntax:

drop series [series_match] [where ...] [set ignore_threshold true/false]

Drops series from SiriDB. Optionally you can use a match and/or where statement to filter the series you want to drop. For more information about how to match series look at help list series.

SiriDB has a mechanism to protect you from accidentally dropping all (or many) series. This is done with a threshold value. If the server receiving your drop request finds more series to drop than the threshold, the request is denied and you receive an error_msg about trying to delete more series than the threshold value. The drop_threshold value will not be checked by other servers in the cluster. You can view the current drop_threshold with show drop_threshold or look up help alter database for how to change this value. If you want to ignore the drop_threshold for one request you can add set ignore_threshold true. The default drop threshold is set to 1 (100%) which means you cannot drop all series but any other amount will pass. Any value between 0 and 1 will work. For example a value of 0.5 means you cannot drop more than 50% of the available series.

Warning

Before dropping series using a regular expression you should check the expression using count series and/or list series and see if your match has the expected result.

Examples:

# Drop series "series-001"
drop series "series-001"

# Drop all series
drop series set ignore_threshold true

Shards

list shards

syntax

list shards [columns] [where ...] [limit ...]

columns

Valid columns are:

  • start: Start timestamp for the shard
  • end: End timestamp for the shard
  • sid: Shard identifier (the same sid usually exist on multiple servers).
  • server: Server name on which the shard exists.
  • pool: Pool where the shard in exists.
  • status: Status flags for the shard.
  • type: Type of the shard (number or log).
  • size: Size of the shard. This is the total shard size over all pools. When a pool has more servers (replicas) the displayed size can vary when running this query multiple times because servers are responsible for optimizing their own shards and this could result in different shard sizes.

When no columns are provided the default is used. (sid, pool, server, start, end)

Example:

# List all shards
list shards

# List shards used for data older then 100 days
list shards where start < now - 100d

# sample output (list shards)
{
    "columns": ["sid", "pool", "server", "start", "end"
    ],
    "shards": [
        [1449705600, 0, "srv01:9010", 1449705600, 1450310400],
        [1449705601, 0, "srv01:9010", 1449705600, 1450310400],
        ...
    ]
}

count shards

Syntax:

count shards [size] [where ...]

Count shards returns the number of shards on all online servers in a SiriDB cluster. This means that offline servers are ignored and replica servers are included in the query. It's also possible to count the shards size in case you want to see the amount of disk-space shards are using.

Example:

# Get number of shards
count shards

# Get number of shards for the current points. (assuming you have
# no shards for points in the future)
count shards where end > now

# Get the amount of disk space (in bytes) which shards are using
# on server01.
count shards size where server == 'server01'

Example output (count shards):

{"shards": 51}

Example output (count shards size):

{"shards_size": 355243846}

drop shards

Syntax:

drop shards [where ...] [set ignore_threshold true/false]

Drops an existing shard using the shard id (sid). Use list shards for an overview of the current shards. This statement requires all pools to have at least one online server. The number of dropped shard in the result message only contains the dropped shards by one member of a pool, not the shards which are dropped by a replica. This is different from the count shards statement which includes shards on replica servers as well.

Note

This statement is protected with a threshold. See help drop series and help alter database for more information about this threshold value.

Example:

# Drop shards for points which are older than one year
drop shards where end < now - 52w

Groups

create group

Syntax:

create group `groupname` for /regular_expression/

Groups should be between backticks to make them different from strings. Since a group is basically a cached regular expression we need to provide the regular expression we want to use for the group.

If you want to drop an existing group see help drop group.

Example:

# Create a group linux
create group `linux` for /linux.*/

count groups

Syntax:

count groups [where ...]

Count groups returns the number of groups defined in the database.

Example:

# Get number of groups
count groups

# Get number of groups with more than 100 series
count groups where series > 100

Example output:

{"groups": 23}

list groups

syntax

list groups [columns] [where ...] [limit ...]

columns

Valid columns are:

  • name: Group name
  • series: Number of series in the group.
  • expression: Show the expression used for this group.

When no columns are provided the default is used. (name)

Examples:

# View all groups
list groups

# View groups and the expression used
list groups name, expression

# sample output (list groups)
{
    "columns": ["name"],
    "groups": [
        ["linux"],
        ["windows"]
    ]
}

alter group

Syntax:

alter group `groupname` set <option>

Valid options are expression and name.

set expression

Change the regular expression for a group.

Example:

# Create group `linux`
create group `linux` for /linux.*/

# Change expression so we will match case insensitive
alter group `linux` set expression /linux.*/i

set name

Change the name for a group.

Note

This statement expects a normal string using single or double quotes. The reason is that 'set name' expects a string and not a group.

Example:

# Rename group `linux` to `ubuntu`
alter group `linux` set name 'ubuntu'

drop groups

Syntax:

drop group `groupname`

Drops an existing group.

Example:

# Drop group `linux`
drop group `linux`

Servers

alter server

Syntax:

alter server <server_uuid / server_name> set <option>

Valid options are address, port, backup_mode and log_level. We can use both, a servers name or uuid to change a server. To view the current servers names and uuids use the command: list servers name, uuid

set address/port

Usually its not required to change the servers address or port using this command but instead you should change the address/port in the configuration file (default /etc/siridb/siridb.conf). When the server gets online it will contact all SiriDB servers and they will automatically update to the new address/port in their local database. However, if all servers in a cluster are updated at once, we need to tell at least one SiriDB server where to find the other server(s). This should be the only situation when this command is required.

Example:

# srv1 and srv2 both have changed to another address so
# they are not able find each other. The command below
# is executed on srv1 and tells where to find srv2.

alter server 'srv2.old.domain:9010' set address 'srv2.new.domain'

# After executing the above command, srv1 will connect to srv2
# using the new domain name and announces its own new address so
# srv2 will update the address automatically and will connect
# to srv1 again.

backup_mode

When a backup_mode is enabled on a SiriDB server, all files in the database directory will be closed (both dbpath and buffer_path). This way you can make a backup of SiriDB without having problems with open files.

log_level

With the argument --log-level it's possible to start with a certain log level. The default log level is info. If you want the log level to change while being online, this command can be used. It will not be saved when the server is restarted.

Valid loglevels are "debug", "info", "warning", "error" and "critical"

Example:

# Change the log-level to "debug"
alter server f851c6a4-820e-11e5-9661-080027f37001 set log_level debug

alter servers

Syntax:

alter servers [where...] set log_level <option>

Valid options are debug, info, warning, error and critical.

This command will change the log level for n servers at once. Changing the log level is explained in more detail at help alter server

count servers

Syntax:

count servers [received_points/selected_points] [where ...]

Count servers returns the number of servers in a SiriDB cluster. Received points are the number of points received by a server since uptime. After a restart the received points counters are reset to zero.

Info

Received points only shows the number of points after uptime. For the total number of points you can use count series length

Examples:

# Get number of servers
count servers

# Get number of servers in pool 0
count servers where pool == 0

# Get total received points since uptime
count servers received_points

# Get total number of selected (queried) points since uptime
count servers selected_points

Example output (count servers):

{"servers": 6}

Example output (count servers received_points):

{"count": 21573435683}

list servers

syntax

list servers [columns] [where ...] [limit ...]

List servers in a SiriDB Cluster. This command can be useful to view status information about a server.

columns

Valid columns are:

  • active_handles: Returns the active handles which can be used as an indicator on how busy a server is.
  • address: Server address.
  • buffer_path: Path where this server keeps the buffer file.
  • buffer_size: Size the server uses for one series in the buffer.
  • dbpath: Path where the server stores the database.
  • fifo_files: Number of fifo files which are used to update the replica server. This value is 0 if the server has no replica. A value greater than 1 could be an indication that replication is not working.
  • ip_support: IP Support setting on the server. (ALL/ IPV4ONLY/ IPV6ONLY)
  • libuv: Version of libuv library.
  • log_level: Current loglevel for the server.
  • max_open_files: Returns the maximum open files value used for sharding on this server. (If this value is lower than expected, please check the log files for SiriDB as startup time)
  • mem_usage: Shows memory usage for the server in MB's.
  • name: Server name.
  • online: True when the server is online.
  • open_files: Number of open files for this database on the server.
  • pool: Returns the pool ID for the server.
  • port: Server port.
  • received_points: Returns the number of received points by the server. On each restart of the SiriDB Server the counter will reset to 0. This value is only incremented when the server is receiving points from a client.
  • reindex_progress: Returns the re-index status. Only available when the database is re-indexing series over pools.
  • selected_points: Returns the selected points on the server. On each restart of the SiriDB Server the counter will reset to 0. This value includes all points which are read from the local shards and the points received from other servers to respond to a select query. The value is only incremented when the server received the select query from a client.
  • startup_time: Time it takes to start the server.
  • status: Current server status.
  • sync_progress: Return synchronization status while creating a new replica server.
  • uptime: Uptime in seconds.
  • uuid: Server UUID (unique ID)
  • version: SiriDB version

When no columns are provided the default is used. (name, pool, version, online, status)

examples

# list all servers in a SiriDB cluster.
list servers

# list all offline servers
list servers where online == false

# view memory usage and open files on all servers.
list servers name, mem_usage, open_files


# sample output (list servers)
{
    "columns": ["name", "pool", "version", "online", "status"],
    "servers": [
        ["siri1:9010", 0, "2.0.10", true, "running"],
        ["siri2:9010", 1, "2.0.10", true, "running"]
    ]
}

drop server

Syntax:

drop server <server_uuid / server_name>

Can be used to remove a server. We only allow dropping a server which has a replica since scaling down in number of pools is currently not supported. A server needs to be turned off before it can be dropped.

Note

When having two servers in a pool, let's call them siri1 and siri2 and for some reason siri2 is broken and does not start. You might be in a situation where siri1 is waiting for siri2 to connect and start to synchronize data. Both servers are not working in this case but when dropping siri2, siri1 removes the 'wait for synchronization' status and starts accepting inserts and queries.

Example:

# Drop server 'siri2:9010'. We first need to turn off
# this server and make sure the server has a replica.
drop server 'siri2:9010'

Access

help access

SiriDB knows the following access rights:

  • select
  • show
  • list
  • count
  • create
  • insert
  • drop
  • alter
  • grant
  • revoke

The most obvious ones are combined into access profiles which can be used to grant or revoke multiple access rights at once.

  • read: (select, show, list and count)
  • write: read + (create and insert)
  • modify: write + (drop and alter)
  • full: modify + (grant, revoke)

Warning

Changes to access rights are active immediately, so be careful when revoking access rights from users.

grant access

Syntax:

grant <access> to user 'username'

Grants access rights to a user. For information about access rights see help access.

Example:

# Grant drop and create to user "iris"
grant drop, create to user "iris"

Output:

{"success_msg": "Successfully granted permissions to user ..."}

revoke access

Syntax:

revoke <access> from 'username'

Revokes access rights from a user. For information about access rights see help access.

Warning

If accidentally all access rights for all users are gone, you need to recover the default user. See help noaccess for how to recover from a situation not having access to SiriDB.

Example:

# Revoke drop and create from user "iris"
revoke drop, create from user "iris"

Output:

{"success_msg": "Successfully revoked permissions from ..."}

restore access

Read/use this section when accidentally all access rights to a database are gone. Follow the steps below to recover the default user. (username: iris, password: siri)

Stop all servers in the SiriDB cluster.

# Assume you use systemd to start SiriDB...
> sudo systemctl stop siridb-server.service

Warning

It's really important to stop all servers in a SiriDB cluster to prevent database inconsistency.

Remove the appropriate file on all servers in the SiriDB cluster.

# Restore the default user
> rm my_database_folder/users.dat

Start all servers in the SiriDB cluster.

# Assume systemd is used to start SiriDB...
> sudo systemctl start siridb-server.service

Users

create user

Syntax:

create user 'my-username' set password 'my-password'

Create a new user. This will create a new user without access to SiriDB. For more information on how to grant access to a user see help grant.

Example:

# This will create a new user 'iris' with password 'siri'
create user 'iris' set password 'siri'

# Grant "read" access to "iris"
grant read to user "iris"

count users

Syntax:

count users [where ...]

Count users returns the number of users.

Examples:

# Get number of users
count users

# Get number of users not equal to 'iris'
count users where name != 'iris'

Example output:

{"users": 6}

list users

syntax

list users [columns] [where ...] [limit ...]

columns

Valid columns are:

  • name: User name.
  • access: Access rights assigned to the user.

When no columns are provided the default is used. (name, access)

Example:

# List all users
list users

# List users with full access
list users where access == full

alter user

Syntax:

alter user 'username' set <option>

Valid options are password and name.

Change a user name or password.

Example:

# Change the password for "iris" to "siri"
alter user 'iris' set password 'siri'

Database

alter database

Syntax:

alter database set <option>

Valid options are drop_threshold, timezone, select_points_limit and list_limit.

drop_threshold

This value is used to protect you from accidentally dropping data from SiriDB. The threshold is a value between 0 and 1 (0/100%). The threshold value is only checked against the pool receiving your query. The default threshold value is 1 (100%) but it might be a good idea to change this to a lower value.

Note

Currently the drop_threshold is only used for dropping series and shards because these are the only queries where we allow to drop multiple entries at once.

Example:

# Do not allow dropping more than 10% series or shards at once
alter database set drop_threshold 0.1

# View the current threshold
show drop_threshold

set timezone

Change the timezone for the database. When using a date/time in a query SiriDB needs to convert the given date to a timestamp. Default NAIVE is used which means SiriDB is naive about the time zone and acts as if it's a local time.

Warning

When using a SiriDB database over multiple time zones it's probably best to set the time zone to anything other than NAIVE since with NAIVE the server receiving the query will convert the date to a local time-stamp. This means that sending the same query to a server in another time zone could respond with a different result.

However, it's always possible in the query to specify a UTC date by adding 'Z' to the date. For example: '2016-01-11 16:00Z' will use UTC as it's time zone, no matter what time zone the database has configured.

For a list of valid time zones see help timezones

Example:

# Set the default time zone to UTC
alter database set timezone 'UTC'

# Set the default time zone to NAIVE
alter database set timezone 'NAIVE'

# Set the default time zone to Europe/Amsterdam
alter database set timezone 'Europe/Amsterdam'

select_points_limit

Change the maximum points which can be returned by a select query. The default and recommended value is set to one million points. This value is chosen to prevent a single query for taking to much memory and ensures SiriDB can respond to almost any query in a reasonable amount of time.

Example:

# Increase the select points limit to 5 million
alter database set select_points_limit 5000000

list_limit

Change the maximum value which can be used as a limit for a list statement. The default and recommended value is set to ten thousand to prevent queries which could take a large amount of memory. The value must be greater than or equal to 1000.

Example:

# Set the list limit to 50 thousand.
alter database set list_limit 50000

timezones

For information on how to change the time zone see help alter database

Note

There is one special time-zone, named NAIVE which is the default time-zone when creating a database. When this time-zone is used, a SiriDB server handles a given date as a local time. All other time zones can be found below.

  • Africa/Abidjan
  • Africa/Accra
  • Africa/Addis_Ababa
  • Africa/Algiers
  • Africa/Asmara
  • Africa/Bamako
  • Africa/Bangui
  • Africa/Banjul
  • Africa/Bissau
  • Africa/Blantyre
  • Africa/Brazzaville
  • Africa/Bujumbura
  • Africa/Cairo
  • Africa/Casablanca
  • Africa/Ceuta
  • Africa/Conakry
  • Africa/Dakar
  • Africa/Dar_es_Salaam
  • Africa/Djibouti
  • Africa/Douala
  • Africa/El_Aaiun
  • Africa/Freetown
  • Africa/Gaborone
  • Africa/Harare
  • Africa/Johannesburg
  • Africa/Juba
  • Africa/Kampala
  • Africa/Khartoum
  • Africa/Kigali
  • Africa/Kinshasa
  • Africa/Lagos
  • Africa/Libreville
  • Africa/Lome
  • Africa/Luanda
  • Africa/Lubumbashi
  • Africa/Lusaka
  • Africa/Malabo
  • Africa/Maputo
  • Africa/Maseru
  • Africa/Mbabane
  • Africa/Mogadishu
  • Africa/Monrovia
  • Africa/Nairobi
  • Africa/Ndjamena
  • Africa/Niamey
  • Africa/Nouakchott
  • Africa/Ouagadougou
  • Africa/Porto-Novo
  • Africa/Sao_Tome
  • Africa/Tripoli
  • Africa/Tunis
  • Africa/Windhoek
  • America/Adak
  • America/Anchorage
  • America/Anguilla
  • America/Antigua
  • America/Araguaina
  • America/Argentina/Buenos_Aires
  • America/Argentina/Catamarca
  • America/Argentina/Cordoba
  • America/Argentina/Jujuy
  • America/Argentina/La_Rioja
  • America/Argentina/Mendoza
  • America/Argentina/Rio_Gallegos
  • America/Argentina/Salta
  • America/Argentina/San_Juan
  • America/Argentina/San_Luis
  • America/Argentina/Tucuman
  • America/Argentina/Ushuaia
  • America/Aruba
  • America/Asuncion
  • America/Atikokan
  • America/Bahia
  • America/Bahia_Banderas
  • America/Barbados
  • America/Belem
  • America/Belize
  • America/Blanc-Sablon
  • America/Boa_Vista
  • America/Bogota
  • America/Boise
  • America/Cambridge_Bay
  • America/Campo_Grande
  • America/Cancun
  • America/Caracas
  • America/Cayenne
  • America/Cayman
  • America/Chicago
  • America/Chihuahua
  • America/Costa_Rica
  • America/Creston
  • America/Cuiaba
  • America/Curacao
  • America/Danmarkshavn
  • America/Dawson
  • America/Dawson_Creek
  • America/Denver
  • America/Detroit
  • America/Dominica
  • America/Edmonton
  • America/Eirunepe
  • America/El_Salvador
  • America/Fort_Nelson
  • America/Fortaleza
  • America/Glace_Bay
  • America/Godthab
  • America/Goose_Bay
  • America/Grand_Turk
  • America/Grenada
  • America/Guadeloupe
  • America/Guatemala
  • America/Guayaquil
  • America/Guyana
  • America/Halifax
  • America/Havana
  • America/Hermosillo
  • America/Indiana/Indianapolis
  • America/Indiana/Knox
  • America/Indiana/Marengo
  • America/Indiana/Petersburg
  • America/Indiana/Tell_City
  • America/Indiana/Vevay
  • America/Indiana/Vincennes
  • America/Indiana/Winamac
  • America/Inuvik
  • America/Iqaluit
  • America/Jamaica
  • America/Juneau
  • America/Kentucky/Louisville
  • America/Kentucky/Monticello
  • America/Kralendijk
  • America/La_Paz
  • America/Lima
  • America/Los_Angeles
  • America/Lower_Princes
  • America/Maceio
  • America/Managua
  • America/Manaus
  • America/Marigot
  • America/Martinique
  • America/Matamoros
  • America/Mazatlan
  • America/Menominee
  • America/Merida
  • America/Metlakatla
  • America/Mexico_City
  • America/Miquelon
  • America/Moncton
  • America/Monterrey
  • America/Montevideo
  • America/Montserrat
  • America/Nassau
  • America/New_York
  • America/Nipigon
  • America/Nome
  • America/Noronha
  • America/North_Dakota/Beulah
  • America/North_Dakota/Center
  • America/North_Dakota/New_Salem
  • America/Ojinaga
  • America/Panama
  • America/Pangnirtung
  • America/Paramaribo
  • America/Phoenix
  • America/Port-au-Prince
  • America/Port_of_Spain
  • America/Porto_Velho
  • America/Puerto_Rico
  • America/Rainy_River
  • America/Rankin_Inlet
  • America/Recife
  • America/Regina
  • America/Resolute
  • America/Rio_Branco
  • America/Santa_Isabel
  • America/Santarem
  • America/Santiago
  • America/Santo_Domingo
  • America/Sao_Paulo
  • America/Scoresbysund
  • America/Sitka
  • America/St_Barthelemy
  • America/St_Johns
  • America/St_Kitts
  • America/St_Lucia
  • America/St_Thomas
  • America/St_Vincent
  • America/Swift_Current
  • America/Tegucigalpa
  • America/Thule
  • America/Thunder_Bay
  • America/Tijuana
  • America/Toronto
  • America/Tortola
  • America/Vancouver
  • America/Whitehorse
  • America/Winnipeg
  • America/Yakutat
  • America/Yellowknife
  • Antarctica/Casey
  • Antarctica/Davis
  • Antarctica/DumontDUrville
  • Antarctica/Macquarie
  • Antarctica/Mawson
  • Antarctica/McMurdo
  • Antarctica/Palmer
  • Antarctica/Rothera
  • Antarctica/Syowa
  • Antarctica/Troll
  • Antarctica/Vostok
  • Arctic/Longyearbyen
  • Asia/Aden
  • Asia/Almaty
  • Asia/Amman
  • Asia/Anadyr
  • Asia/Aqtau
  • Asia/Aqtobe
  • Asia/Ashgabat
  • Asia/Baghdad
  • Asia/Bahrain
  • Asia/Baku
  • Asia/Bangkok
  • Asia/Beirut
  • Asia/Bishkek
  • Asia/Brunei
  • Asia/Chita
  • Asia/Choibalsan
  • Asia/Colombo
  • Asia/Damascus
  • Asia/Dhaka
  • Asia/Dili
  • Asia/Dubai
  • Asia/Dushanbe
  • Asia/Gaza
  • Asia/Hebron
  • Asia/Ho_Chi_Minh
  • Asia/Hong_Kong
  • Asia/Hovd
  • Asia/Irkutsk
  • Asia/Jakarta
  • Asia/Jayapura
  • Asia/Jerusalem
  • Asia/Kabul
  • Asia/Kamchatka
  • Asia/Karachi
  • Asia/Kathmandu
  • Asia/Khandyga
  • Asia/Kolkata
  • Asia/Krasnoyarsk
  • Asia/Kuala_Lumpur
  • Asia/Kuching
  • Asia/Kuwait
  • Asia/Macau
  • Asia/Magadan
  • Asia/Makassar
  • Asia/Manila
  • Asia/Muscat
  • Asia/Nicosia
  • Asia/Novokuznetsk
  • Asia/Novosibirsk
  • Asia/Omsk
  • Asia/Oral
  • Asia/Phnom_Penh
  • Asia/Pontianak
  • Asia/Pyongyang
  • Asia/Qatar
  • Asia/Qyzylorda
  • Asia/Rangoon
  • Asia/Riyadh
  • Asia/Sakhalin
  • Asia/Samarkand
  • Asia/Seoul
  • Asia/Shanghai
  • Asia/Singapore
  • Asia/Srednekolymsk
  • Asia/Taipei
  • Asia/Tashkent
  • Asia/Tbilisi
  • Asia/Tehran
  • Asia/Thimphu
  • Asia/Tokyo
  • Asia/Ulaanbaatar
  • Asia/Urumqi
  • Asia/Ust-Nera
  • Asia/Vientiane
  • Asia/Vladivostok
  • Asia/Yakutsk
  • Asia/Yekaterinburg
  • Asia/Yerevan
  • Atlantic/Azores
  • Atlantic/Bermuda
  • Atlantic/Canary
  • Atlantic/Cape_Verde
  • Atlantic/Faroe
  • Atlantic/Madeira
  • Atlantic/Reykjavik
  • Atlantic/South_Georgia
  • Atlantic/St_Helena
  • Atlantic/Stanley
  • Australia/Adelaide
  • Australia/Brisbane
  • Australia/Broken_Hill
  • Australia/Currie
  • Australia/Darwin
  • Australia/Eucla
  • Australia/Hobart
  • Australia/Lindeman
  • Australia/Lord_Howe
  • Australia/Melbourne
  • Australia/Perth
  • Australia/Sydney
  • Canada/Atlantic
  • Canada/Central
  • Canada/Eastern
  • Canada/Mountain
  • Canada/Newfoundland
  • Canada/Pacific
  • Europe/Amsterdam
  • Europe/Andorra
  • Europe/Athens
  • Europe/Belgrade
  • Europe/Berlin
  • Europe/Bratislava
  • Europe/Brussels
  • Europe/Bucharest
  • Europe/Budapest
  • Europe/Busingen
  • Europe/Chisinau
  • Europe/Copenhagen
  • Europe/Dublin
  • Europe/Gibraltar
  • Europe/Guernsey
  • Europe/Helsinki
  • Europe/Isle_of_Man
  • Europe/Istanbul
  • Europe/Jersey
  • Europe/Kaliningrad
  • Europe/Kiev
  • Europe/Lisbon
  • Europe/Ljubljana
  • Europe/London
  • Europe/Luxembourg
  • Europe/Madrid
  • Europe/Malta
  • Europe/Mariehamn
  • Europe/Minsk
  • Europe/Monaco
  • Europe/Moscow
  • Europe/Oslo
  • Europe/Paris
  • Europe/Podgorica
  • Europe/Prague
  • Europe/Riga
  • Europe/Rome
  • Europe/Samara
  • Europe/San_Marino
  • Europe/Sarajevo
  • Europe/Simferopol
  • Europe/Skopje
  • Europe/Sofia
  • Europe/Stockholm
  • Europe/Tallinn
  • Europe/Tirane
  • Europe/Uzhgorod
  • Europe/Vaduz
  • Europe/Vatican
  • Europe/Vienna
  • Europe/Vilnius
  • Europe/Volgograd
  • Europe/Warsaw
  • Europe/Zagreb
  • Europe/Zaporozhye
  • Europe/Zurich
  • GMT
  • Indian/Antananarivo
  • Indian/Chagos
  • Indian/Christmas
  • Indian/Cocos
  • Indian/Comoro
  • Indian/Kerguelen
  • Indian/Mahe
  • Indian/Maldives
  • Indian/Mauritius
  • Indian/Mayotte
  • Indian/Reunion
  • Pacific/Apia
  • Pacific/Auckland
  • Pacific/Bougainville
  • Pacific/Chatham
  • Pacific/Chuuk
  • Pacific/Easter
  • Pacific/Efate
  • Pacific/Enderbury
  • Pacific/Fakaofo
  • Pacific/Fiji
  • Pacific/Funafuti
  • Pacific/Galapagos
  • Pacific/Gambier
  • Pacific/Guadalcanal
  • Pacific/Guam
  • Pacific/Honolulu
  • Pacific/Johnston
  • Pacific/Kiritimati
  • Pacific/Kosrae
  • Pacific/Kwajalein
  • Pacific/Majuro
  • Pacific/Marquesas
  • Pacific/Midway
  • Pacific/Nauru
  • Pacific/Niue
  • Pacific/Norfolk
  • Pacific/Noumea
  • Pacific/Pago_Pago
  • Pacific/Palau
  • Pacific/Pitcairn
  • Pacific/Pohnpei
  • Pacific/Port_Moresby
  • Pacific/Rarotonga
  • Pacific/Saipan
  • Pacific/Tahiti
  • Pacific/Tarawa
  • Pacific/Tongatapu
  • Pacific/Wake
  • Pacific/Wallis
  • US/Alaska
  • US/Arizona
  • US/Central
  • US/Eastern
  • US/Hawaii
  • US/Mountain
  • US/Pacific
  • UTC

status information

Syntax

show [<option> [,<option> [...]]

See available options for more info on each show command:

  • show active_handles: Returns the active handles which can be used as an indicator for how busy a server is.
  • show buffer_path: Returns the local buffer path on this server.
  • show buffer_size: Returns the buffer size in bytes on this server.
  • show dbname: Returns the database name.
  • show dbpath: Returns the local database path on this server.
  • show drop_threshold: Returns the current drop threshold (value between 0 and 1 representing a percentage).
  • show duration_log: Returns the sharding duration for log data on this database (not supported yet).
  • show duration_num: Returns the sharding duration for num data on this database.
  • show fifo_files: Returns the number of fifo files which are used to update the replica server. This value is 0 if the server has no replica. A value greater than 1 could be an indication that replication is not working.
  • show ip_support: Returns the ip support setting on this server.
  • show libuv: Returns the version of libuv on this server.
  • show list_limit: Returns the maximum value which can be used as limit in a list query.
  • show log_level: Returns the current log level for this server.
  • show max_open_files: Returns the maximum open files value used for sharding on this server (if this value is lower than expected, please check the log files for SiriDB as startup time).
  • show mem_usage: Returns the current memory usage in MB's on this server.
  • show open_files: Returns the number of open files on this server for the selected database (should be 0 when the server is in backup_mode).
  • show pool: Returns the pool ID for this server.
  • show received_points: Returns the number of received points for this server. On each restart of the SiriDB Server the counter will reset to 0. This value is only incremented when this server is receiving points from a client.
  • show reindex_progress: Returns the re-index status on this server. Only available when the database is re-indexing series over pools.
  • show selected_points: Returns the selected points for this server. On each restart of the SiriDB Server the counter will reset to 0. This value includes all points which are read from the local shards and the points received from other servers to respond to a select query. The value is only incremented when this server received the select query from a client.
  • show select_points_limit: Returns the maximum number of points which can be returned with a select query.
  • show server: Returns this server name. The name has format host:port
  • show startup_time: Returns the time in seconds it took to startup the SiriDB database on this server.
  • show status: Returns the current status for this server.
  • show sync_progress: Return synchronization status while creating a new replica server on this server.
  • show time_precision: Returns the time precision for this database.
  • show timezone: Returns the timezone for this database.
  • show uptime: Returns the uptime in seconds this server is running.
  • show uuid: Returns the UUID (unique ID) for this server.
  • show version: Returns the SiriDB version running on this server.
  • show who_am_i: Returns the user who is running this request.

examples

# show the database name and time precision
show dbname, time_precision


# sample output
{
    "data": [
        {
            "name": "dbname",
            "value": "mydb"
        },
        {
            "name": "time_precision",
            "value": "s"
        }
    ]
}