Infrastructure at your Service

Daniel Westermann

Getting started with Exasol – Sessions and auditing

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:

    [email protected]:~/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:

    [email protected]:~/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:

    [email protected]:~/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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure