In one of the previous blog posts "link", we saw the existence of a system table containing statistics about database tables:
SELECT * FROM sys.statistics WHERE minval = 'A';
We saw that this table can be updated partially with these commands. Now, we will see that for each of these commands we can use stored procedure, as an alternative.
Update schema statistics:
Update table statistics:
Update column statistics:
Command
ANALYZE sys;
ANALYZE sys.auths;
ANALYZE sys.auths ( name );
Procedure
CALL sys.analyze('sys');
CALL sys.analyze('sys', 'auths');
CALL sys.analyze( 'sys', 'auths', 'name' );
There is also a procedure that will update statistics for all the schemas:
CALL sys.analyze();
Table Data Dump
I will read from one system table. We'll see that this table is empty. SELECT * FROM sys.dump_statements;
I will now call this procedure: CALL sys.dump_table_data( 'sys', 'auths' );
We will read again from the "sys.dumpstatements". SELECT * FROM sys.dump_statements;
Now we have a row with a statement that can be used to fill "auths" table with the data.
We can empty this table normally: DELETE FROM sys.dump_statements;
If we call the same procedure, but without arguments, all the tables from the database will be dumped ( image is from "DBfarmDB" database ): CALL sys.dump_table_data(); SELECT * FROM sys.dump_statements;
Eval Procedure
I want to find the table in my database that has the biggest ID. I want to read the content of that table. It is easy to find the name of this table. The problem is how to read its content. SELECT name FROM sys.tables WHERE id = (SELECT MAX( id ) FROM sys.tables);
CREATE OR REPLACE PROCEDURE TableWithBiggestID() BEGIN DECLARE TableName VARCHAR(50); SET TableName = ( SELECT name FROM sys.tables WHERE id = (SELECT MAX( id ) FROM sys.tables)); CALL sys.eval( 'SELECT * FROM ' || TableName || ';' ); END;
For that task, I will use stored procedure "sys.eval". It will transform text into statement.
In our procedure, we will find the name of the table with the biggest ID ( table "tabula" ). We will create string "SELECT * FROM tabula;". To execute string, we will transform it into statement with "sys.eval". If we call our procedure "TableWithBiggestID", we will see the content of the table "tabula". CALL TableWithBiggestID();
This function is only available to administrator.
Vacuum
Vacuuming is a maintenance process where we clean databases of obsolete data and defragment columns. After deleting or updating many rows, old records will not be deleted but will only be marked as deleted. This can lead to excessive disk usage and prevent sequential reading of column values.
We can manually vacuum one table or one column.
CALL sys.vacuum('voc', 'voyages');
CALL sys.vacuum('voc', 'voyages', 'letter');
It is possible to schedule tables and columns vacuuming at regular intervals. The intervals are expressed in seconds.
CALL sys.vacuum('voc', 'voyages', 3600); This is how we stop the schedule.
CALL sys.stop_vacuum( 'voc', 'voyages' );
CALL sys.vacuum('voc', 'voyages', 'letter', 3600); This is how we stop the schedule.
CALL sys.stop_vacuum('voc', 'voyages','letter');
Session Procedures
Session Procedures for Users
User can read his own session ID. SELECT sys.current_sessionid();
User can limit memory consumption for his queries. This is measured in MB.
CALL sys.setmemorylimit(500);
User can disable his memory limit if he call this procedure with the argument equal to zero.
CALL sys.setmemorylimit(0);
Users can set query timeout to 1 second.
CALL sys.setquerytimeout(1);
If our query is longer than 1 second, error message will appear.
Zero will reset query timeout. CALL sys.setquerytimeout(0);
We can limit session to 3 seconds. CALL sys.setsessiontimeout(3);
This will happen if we run a query after 3 seconds.
Zero will reset session timeout.
CALL sys.setsessiontimeout(0);
Users can choose their optimizer. CALL sys.setoptimizer('minimal_pipe');
We can find a list of optimizers in the system table "sys.optimizers".
Default optimizer is "default_pipe".
We can limit the number of threads that user queries can use. CALL sys.setworkerlimit(8);
Zero argument will remove this limit. CALL sys.setworkerlimit(0);
Client Info Procedure for Users
We can declare some properties for the database client we are using. If we read from this system table, we will find a list of the properties that we can declare.
SELECT * FROM sys.clientinfo_properties;
ClientHostname – the name of our computer. ApplicationName – Mclient, DBeaver, Tableau. ClientLibrary – libmapi 11.53.3 ClientPid – number of the client process. ClientRemark – A comment.
I will change one of these properties. CALL sys.setclientinfo('ClientHostname', 'OldServer' );
We can read current values of these properties in the "sys.sessions". SELECT * FROM sys.sessions;
The purpose is to write client data to the "sys.session" table, so we can better understand server usage.
Session Procedures for Admins
I will open another tab in the terminal, and there I will login as a user "voc".
mclient -u voc -d voc --password is voc
If the user reads from "sys.session", he will only get his own session.
SELECT * FROM sys.sessions;
When the admin reads from the "sys.sessions", he will see sessions with all the users. Admin can use IDs of those sessions to manage those sessions. Below we can see how admin can manage user voc session ( ID 1 ). SELECT * FROM sys.sessions;
CALL sys.setmemorylimit( 1, 100 );
Admin can limit memory usage to 100 MB.
We disable this limit by setting it to zero.
CALL sys.setoptimizer( 1, 'sequential_pipe');
Admin can set optimizer.
Default optimizer is "default_pipe".
CALL sys.setquerytimeout( 1, 8 );
User's queries will time out after 8 sec.
Zero will disable query time out.
CALL sys.setsessiontimeout( 1, 30 );
Session will time out after 30 seconds.
Zero will disable session time out.
CALL sys.setworkerlimit( 1, 8 );
User's queries are limited to 8 threads.
Zero will disable this limit.
CALL sys.stopsession( 1);
We can stop user's session.
Queries Que
The Size of Queries Que
I will quit the session, and I will stop the "voc" database. quit monetdb stop voc
After that I will start the database, and I will login to it. monetdb start voc mclient -u monetdb -d voc --password monetdb
I will read from the system view: select * from sys.queue; It is also possible to use function: select * from sys.queue();
The result is a table that has a history of the last run queries. This table was emptied when we restarted the database, so now it only has 2 queries.
I will create a list of 70 queries, and I will paste all of them in the mclient.
After all of them run, I will again read from the "sys.queue" table.
SELECT COUNT(*) FROM sys.queue;
The result will be 64. MonetDB is keeping the last 64 queries.
If I login again as a "voc" user in the other tab of the terminal, I can run a query as a "voc" user. mclient -u voc -d voc --password voc SELECT 'New';
I will read "sys.queue" table as a "voc" user. SELECT * FROM sys.queue; He has 3 queries.
I will go back being admin, and I will again run the statement.
SELECT COUNT( * ) FROM sys.queue();
The result will be 61. MonetDB is keeping only the last 64 queries ( 61 + 3 ), from the all of the sessions.
Admin has ability to read the whole que: select COUNT( * ) FROM sys.queue( 'ALL' );
With "ALL", admin can read from all of the sessions. SELECT DISTINCT sessionid FROM sys.queue( 'ALL' );
Changing The Que Size
The number of queries saved is the same as the maximal number of clients that can connect to database. I will quit my session. I will stop my database and then I will change "nclients" property of the database.
quit monetdb stop voc monetdb set nclients=10 voc
I will log in into database. mclient -u monetdb -d voc
I will again paste 70 queries into mclient.
If we now read from the "sys.queue", we will only find 10 queries. This is the proof that "nclients" property will limit number of queries kept inside of this table.
We can read the current value of "nclients" property with this query. select * from environment where name = 'max_clients';
Management of Queries
We can provide the name of a user, and that will return only the queries for that user. Currently, all the saved queries belong to admin. select COUNT( * ) from queue('monetdb');
"queue" view will also include queries that are currently running. Such queries will be labeled with "running" in the status column.
There are five possible statuses.
Query is executing. running
Query is halted. paused
Query is stopping. stopping
Query is stopped. aborted
Query is executed. finished
I will open one more tab in the terminal, where I will log in as a admin.
mclient -u monetdb -d voc --password monetdb
I will use this query on the right. This is recursive CTE that uses LEFT OUTER JOIN. Such query will never end. It is eternal.
I will run this query in the first terminal tab, and I will check the result in the second terminal table.
WITH recursive numbers AS ( SELECT 1 UNION ALL SELECT 1 FROM numbers LEFT OUTER JOIN sys.auths ON 1 = 1 ) SELECT * FROM numbers;
The last query in my "sys.queue" is number 93 ( column "tag" ). I know that when I run my eternal query, it will get number 94. I will run eternal query.
I will run this statement in the terminal second tab. My query will have status "running".
SELECT status FROM sys.queue WHERE tag = 94;
I can pause my query from the terminal second tab. Status will change.
CALL sys.pause( 94 ); SELECT status FROM sys.queue WHERE tag = 94;
I can resume my query. Status will change again.
CALL sys.resume( 94 ); SELECT status FROM sys.queue WHERE tag = 94;
We can stop the query.
CALL sys.stop( 94 ); SELECT status FROM sys.queue WHERE tag = 94;
It seems that my query can be only stopped with "Ctrl+C". This is because I used eternal query.
Even when we use "Ctrl+C" to stop query, its status will become "aborted".
SELECT status FROM sys.queue WHERE tag = 94;
If we are admin we can manage queries of other users. For that we must provide the name of a user and his query number ( from column "tag" ).
