Basic user management

MariaDB ColumnStore allows permissions to be set for user accounts. The syntax of these grants follows the standard MariaDB syntax (see GRANT).

For the root user, ColumnStore comes with full privileges. In order to set/restrict user accounts, privileges must be given/restricted. ColumnStore uses a dedicated schema called infinidb_vtable for creation of all temporary tables used for ColumnStore query processing. The root user account has been given permission to this account by default, but full permission MUST be given for all user accounts to this schema:

grant ALL on infinidb_vtable.* to user_account; where user_account = user login, server and password characteristics

Further permissions/restrictions can now be placed on any existing objects (tables, functions, procedures, views) for any access/limitations wanting to be placed on users: Example to give a user that has a password full access to all tables for a database (after the above grant has been given):

use mysql;
grant ALL on my_schema.* to ‘someuser’@’somehost’
identified by ‘somepassword’;
flush privileges;

Example to give a user that has a password read-only access to only 1 table (after the above grant has been given):

use mysql;
grant SELECT on my_schema.table1 to ‘someuser’@’somehost’
identified by ‘somepassword’;
flush privileges;

PAM authentication

Starting with ColumnStore 1.0.8, ColumnStore includes the necessary authentication plugin for PAM support. For general details see pam-authentication-plugin but here we will outline the steps necessary to configure this for os authentication specific to a ColumnStore installation.

First ensure that the mysql user has read access to the /etc/shadow file, in this example a group is used to facilitate this:

$ sudo groupadd shadow 
$ sudo usermod -a -G shadow mysql 
$ sudo chown root:shadow /etc/shadow 
$ sudo chmod g+r /etc/shadow

Create a pam.d entry to configure unix password authentication:

$ vi /etc/pam.d/mysql
auth required pam_unix.so
account required pam_unix.so

Load the auth_pam.so plugin and create a user:

$ mcsmysql
> INSTALL SONAME 'auth_pam';
> GRANT SELECT ON test.* TO david IDENTIFIED VIA pam;
> GRANT ALL ON infinidb_vtable.* TO david;

Restart ColumnStore so that the mariadb server process picks up the auth plugin and group changes:

$ sudo su - 
$ mcsadmin restartSystem

Now attempt to login to verify correct setup, entering the unix password for the account david when prompted:

$ mcsmysql -u david -p

If this still fails, try restartSystem once more and try logging in again as this seems to resolve the issue.

User Resource Allocation

MariaDB ColumnStore supports the ability to give priority to resources allocated (CPU) based on a user. Users are allocated at least the % of CPU that they are assigned to by priority setting. Effectively a particular user or a set of users can be guaranteed a set amount of resources. E.g:

• User 1 gets a minimum of 40% CPU Resources
• User 2 gets a minimum of 30% CPU Resources
• If any user logs in for a query while User 1 and User 2 are running queries, these new users (i.e., User 3,4 and 5) get only the remaining 30% of the CPU Resources."

User Priority Management

Three stored procedures were created in the infinidb_querystats schema for the user to set, remove and view user priorities. The priority table associates a user with a priority level. A user that does not have an entry is given the low priority level by default.

CalSetUserPriority (host varchar, user varchar, priority varchar)

• Assigns a priority level to user@host.
• Priority is case insensitive 'high', 'medium' or 'low'.
• Host and user will be validated to exist in MariaDB

CalRemoveUserPriority(host varchar, user varchar)

• Removes the user entry, effectively restoring the default of 'low'.
• User existence will not be validated.

CalShowProcessList()

• Prints a combination of mariadb 'show processlist' and user priority

The MariaDB user needs to be granted the execute privileges for these procedures and the select privileges for the tables in the infinidb_querystats schema. Or, chances are, the following should just work for a super user:

GRANT ALL ON infinidb_querystats.* TO 'user'@'host'; // user will now have the privilege to use the priority procedures and view query stats.

Enabling User Priority

To enable this feature, the <UserPriority><Enabled> element in the MariaDB ColumnStore configuration file should be set to Y (default is N).

<UserPriority>
     <Enabled>Y</Enabled>
</UserPriority>

Cross Engine Support must also be enabled. See the ”Cross-Engine Table Access” section in this guide.

User Priority Processing

The PrimProc process has one job queue for each priority level and thread assigned to each queue. The number of threads assigned to each queue is configurable using the following elements in the configuration file:

<PrimitiveServer><HighPriorityPercentage>
<PrimitiveServer><MediumPriorityPercentage>
<PrimitiveServer><LowPriorityPercentage>

The defaults are 60, 30, and 10 respectively. Each queue is given at least 1 thread so there is neither 'idle' priority configuration possible nor starvation. The number of threads started is normalized such that 100% = 2 * (the number of cores on the machine). The user can overbook or underbook their CPUs however they want. This is an example of how threads are assigned on an 8-core system using the defaults.

• 10% of 16 = 1.6, rounds down to 1 thread for the low priority queue.
• 30% of 16 = 4.8, rounds down to 4 threads for the medium priority queue.
• The high priority queue gets the remaining 11 threads.

Each thread is given a preferred queue to get work from. If a thread's preferred queue is empty, it will choose jobs from the high, then medium, then low priority queues. If there are only low priority jobs running, on an 8-core system all 16 threads will process jobs from the low priority queue. If a medium priority query starts, using the defaults, the 15 threads assigned to the high and medium queues will process the medium queue, leaving the 1 assigned to the low queue to process the low priority jobs. Then, if a high priority query starts, the 11 threads assigned to the high priority queue will begin processing the high priority jobs, the 4 assigned to the medium queue will process those jobs, and the 1 assigned to the low queue will process those jobs.
Given this algorithm, the configuration parameters could be thought of as minimum levels for each priority.
Note that this implementation only affects the processing done by PrimProc. Depending on the work distribution of a given query, a user may or may not observe overall performance proportional to their priority level.