This is the fourth post in the series about Exasol and this time it is about sessions and auditing. If you are interested in the previous posts you can find them here:
- Getting started with Exasol – Setting up an environment
- Getting started with Exasol – Loading data from PostgreSQL
- Getting started with Exasol – Some words about indexes and transactions
For looking at the current sessions in Exasol there is exa_dba_sessions:
SQL_EXA> col column_name for a30; COLUMN column_name ON FORMAT a30 SQL_EXA> col sql_type for a20; COLUMN sql_type ON FORMAT a20 SQL_EXA> desc exa_dba_sessions; EXA: desc exa_dba_sessions; COLUMN_NAME SQL_TYPE NULLABLE DISTRIBUTION_KEY PARTITION_KEY ------------------------------ -------------------- -------- ---------------- ---------------- SESSION_ID DECIMAL(20,0) USER_NAME VARCHAR(128) UTF8 EFFECTIVE_USER VARCHAR(128) UTF8 STATUS VARCHAR(30) UTF8 COMMAND_NAME VARCHAR(40) UTF8 STMT_ID DECIMAL(9,0) DURATION VARCHAR(20) UTF8 QUERY_TIMEOUT DECIMAL(6,0) ACTIVITY VARCHAR(100) UTF8 TEMP_DB_RAM DECIMAL(10,1) PERSISTENT_DB_RAM DECIMAL(10,1) LOGIN_TIME TIMESTAMP CLIENT VARCHAR(100) UTF8 DRIVER VARCHAR(100) UTF8 ENCRYPTED BOOLEAN HOST VARCHAR(100) UTF8 OS_USER VARCHAR(40) UTF8 OS_NAME VARCHAR(100) UTF8 SCOPE_SCHEMA VARCHAR(128) UTF8 CONSUMER_GROUP VARCHAR(128) UTF8 NICE BOOLEAN RESOURCES DECIMAL(3,0) SQL_TEXT VARCHAR(2000000) UTF8
As you might remember from the previous posts there is no parameter for controlling the amount of concurrent sessions in Exasol:
SQL_EXA> col parameter_name for a30; COLUMN parameter_name ON FORMAT a30 SQL_EXA> col session_value for a30; COLUMN session_value ON FORMAT a30 SQL_EXA> col system_value for a30; COLUMN system_value ON FORMAT a30 SQL_EXA> select * from exa_parameters where parameter_name like '%session%' or parameter_name like '%connection%'; EXA: select * from exa_parameters where parameter_name like '%session%' or ... PARAMETER_NAME SESSION_VALUE SYSTEM_VALUE ------------------------------ ------------------------------ ------------------------------ 0 rows in resultset.
There is a limit of 100 concurrent active sessions, but that does not mean that there cannot be more sessions if they are idle. Looking at the current sessions, only my own session is doing something (or just did something):
SQL_EXA> select user_name, effective_user,status,sql_text from exa_dba_sessions; EXA: select user_name, effective_user,status,sql_text from exa_dba_sessions... USER_NAME EFFECTIVE_USER STATUS SQL_TEXT ------------------------------ ------------------------------ ------------------------------ ------------------------------ SYS SYS IDLE SYS SYS EXECUTE SQL select user_name, effective_us er,status,sql_text from exa_db a_sessions; SYS SYS IDLE SYS SYS IDLE 4 rows in resultset.
Once the 100 active session limit is hit there is a queue of another hundreds slots for sessions (active but blocked session slots) that need to wait for other sessions to commit or rollback. Using the example from the previous post we should be able to simulate what happens, when we reach one of those limits. In my session I am creating a table, insert some data, commit and then do an update without committing. That will block other sessions if they try to do something like this:
-- first session SQL_EXA> set autocommit off; SQL_EXA> create table tt ( a int ); EXA: create table tt ( a int ); Rows affected: 0 SQL_EXA> insert into tt values (1); EXA: insert into tt values (1); Rows affected: 1 SQL_EXA> commit; EXA: commit; Rows affected: 0 SQL_EXA> update tt set a = 2 where a = 1; EXA: update tt set a = 2 where a = 1; Rows affected: 1 SQL_EXA> -- every other session doing this will be blocked: SQL_EXA> set autocommit off; SQL_EXA> insert into tt values(2); EXA: insert into tt values(2);
Here is a simple Perl script that opens 110 connections in parallel trying to do the blocking part from above (sorry, needs to be a screenshot, otherwise the output is somehow wrong):
The script which is executed is this one:
dwe@dwe:~/Downloads$ cat script.sql open schema demo; set autocommit off; insert into tt values(2);
Executing that against the Exasol community version with the default configuration (4GB of memory) leads to this:
dwe@dwe:~/Downloads$ perl connections.pl Error: [R0002] Query terminated because system running out of memory. (Session: 1679720214738370560) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720211706806272) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720211993788416) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720214120169472) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720214828679168) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720212758528000) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720207574892544) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720214671982592) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720213252800512) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720212555038720) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720215199809536) Error: [R0002] Query terminated because system running out of memory. (Session: 1679720212048510976)
Doubling that to 8GB of memory and doing the test again results in 111 active transactions, what, according to the documentation should not be possible:
SQL_EXA> select user_name, effective_user,status,sql_text from exa_dba_sessions where status != 'IDLE'; EXA: select user_name, effective_user,status,sql_text from exa_dba_sessions... USER_NAME EFFECTIVE_USER STATUS SQL_TEXT ------------------------------ ------------------------------ --------------- ------------------------------ SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); ... SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); SYS SYS EXECUTE SQL insert into tt values(2); 111 rows in resultset. SQL_EXA>
There is also no warning in EXAOperations:
Either the documentation is not entirely correct in this case, or I misunderstood something. Of course you can kill sessions:
SQL_EXA> kill session 1679721952866271232; EXA: kill session 1679721952866271232; Rows affected: 0 SQL_EXA>
… and you can also kill a statement inside a session without killing the session itself:
SQL_EXA> kill statement in session 1679721957455953920; EXA: kill statement in session 1679721957455953920; Rows affected: 0
The messages the client receives are these:
dwe@dwe:~/Downloads$ perl connections.pl Error: [R0004] Connection was killed. (Session: 1679721952866271232) Error: [R0003] Client requested execution abort. (Session: 1679721957455953920)
What is nice is, that you can send a message to the client when you are terminating a statement:
SQL_EXA> kill statement in session 1679723381396471808 with message 'You are using too many resources'; EXA: kill statement in session 1679723381396471808 with message 'You are us... Rows affected: 0
Doing it that way gives some information to the user why you killed his statement:
SQL_EXA> insert into tt values (3); EXA: insert into tt values (3); Error: [R0003] Client requested execution abort. Message: You are using too many resources (Session: 1679723381396471808)
If you do not want to affect other sessions as much as possible you can “NICE” your own session (which is a concept I really like):
SQL_EXA> alter session set nice = 'on'; EXA: alter session set nice = 'on'; Rows affected: 0 SQL_EXA> select session_id ,user_name, effective_user,status,sql_text,nice from exa_dba_sessions; EXA: select session_id ,user_name, effective_user,status,sql_text,nice from... SESSION_ID USER_NAME EFFECTIVE_USER STATUS SQL_TEXT NICE --------------------- ------------------------------ ------------------------------ --------------- ------------------------------ ----- 1679723273468968960 SYS SYS EXECUTE SQL select session_id ,user_name, FALSE effective_user,status,sql_text ,nice from exa_dba_sessions; 4 SYS SYS IDLE FALSE 1679723381396471808 SYS SYS IDLE TRUE
If you want more control over how the system is assigning resource you can do that by using consumer groups, managed by the Resource Manager. There are some pre-defined resource groups in the system already:
SQL_EXA> select CONSUMER_GROUP_NAME,PRECEDENCE,CPU_WEIGHT from EXA_CONSUMER_GROUPS; EXA: select CONSUMER_GROUP_NAME,PRECEDENCE,CPU_WEIGHT from EXA_CONSUMER_GRO... CONSUMER_GROUP_NAME PRECEDENCE CPU_WEIGHT ------------------------------ ------------ ------------ SYS_CONSUMER_GROUP 1000 1000 HIGH 900 900 MEDIUM 300 300 LOW 100 100 4 rows in resultset.
Either use one of those or create your own consumer groups, if you want to limit resources.
If you want to audit the sessions in the system you need to enable auditing. For being able to do that you need to shutdown the database:
Once it is down you can enable auditing by clicking on the database name:
As soon as the database is started up again you can access the auditing information in the catalog, e.g.:
SQL_EXA> select user_name,os_user,os_name,login_time, logout_time, success from exa_dba_audit_sessions; EXA: select user_name,os_user,os_name,login_time, logout_time, success from... USER_NAME OS_USER OS_NAME LOGIN_TIME LOGOUT_TIME SUCCE ------------------------------ ---------- ----------------------------------- -------------------------- -------------------------- ----- SYS dwe Linux - 5.4.0-48-generic - amd64 2020-10-05 15:25:49.694000 TRUE SYS dwe Linux - 5.4.0-48-generic - amd64 2020-10-05 15:25:55.212000 TRUE
But auditing is not limited to sessions, there is much information about historical SQL statements as well:
SQL_EXA> desc EXA_DBA_AUDIT_SQL; EXA: desc EXA_DBA_AUDIT_SQL; COLUMN_NAME SQL_TYPE NULLABLE DISTRIBUTION_KEY PARTITION_KEY ------------------------------ ---------------------------------------- -------- ---------------- ---------------- SESSION_ID DECIMAL(20,0) STMT_ID DECIMAL(9,0) COMMAND_NAME VARCHAR(40) UTF8 COMMAND_CLASS VARCHAR(20) UTF8 DURATION DECIMAL(9,3) START_TIME TIMESTAMP STOP_TIME TIMESTAMP CPU DECIMAL(4,1) TEMP_DB_RAM_PEAK DECIMAL(10,1) PERSISTENT_DB_RAM_PEAK DECIMAL(10,1) HDD_READ DECIMAL(7,1) HDD_WRITE DECIMAL(7,1) LOCAL_READ_SIZE DECIMAL(10,1) LOCAL_READ_DURATION DECIMAL(9,3) LOCAL_WRITE_SIZE DECIMAL(10,1) LOCAL_WRITE_DURATION DECIMAL(9,3) CACHE_READ_SIZE DECIMAL(10,1) CACHE_READ_DURATION DECIMAL(9,3) CACHE_WRITE_SIZE DECIMAL(10,1) CACHE_WRITE_DURATION DECIMAL(9,3) REMOTE_READ_SIZE DECIMAL(10,1) REMOTE_READ_DURATION DECIMAL(9,3) REMOTE_WRITE_SIZE DECIMAL(10,1) REMOTE_WRITE_DURATION DECIMAL(9,3) NET DECIMAL(7,1) SUCCESS BOOLEAN ERROR_CODE VARCHAR(10) UTF8 ERROR_TEXT VARCHAR(1000) UTF8 SCOPE_SCHEMA VARCHAR(128) UTF8 CONSUMER_GROUP VARCHAR(128) UTF8 NICE BOOLEAN RESOURCES DECIMAL(3,0) ROW_COUNT DECIMAL(18,0) EXECUTION_MODE VARCHAR(20) UTF8 CLUSTER_NAME VARCHAR(128) UTF8 SQL_TEXT VARCHAR(2000000) UTF8 36 rows in resultset. SQL_EXA>
This gives a nice overview about how the system behaved in the past. If you decide to go with auditing you should implement a procedure that purges the logs you do not need anymore, e.g.:
SQL_EXA> truncate audit logs keep from '2020-10-01'; EXA: truncate audit logs keep from '2020-10-01'; Rows affected: 0 SQL_EXA>
That’s it for the introduction of sessions and auditing. In the next post we’ll look at backup and restore.