The Database Server

 

 

The Mimer SQL database server is a single, multi-threaded process with SMP scalability. Clients using TCP/IP can access the server. For clients running on the same platform, a shared-memory based communication method is used.

The standard Mimer SQL database server program is named mimexper (there is also an in-memory database server available named miminm). It is controlled using the mimdbserver command, or mimcontrol command. When running the dbinstall command, the database server is automatically created and started for operation. To manually start the database server use the command as follows:

# mimdbserver -s <database name >

 

To stop the database server program, use the following command:

# mimdbserver -t <database name>

 

The target database name can be assigned to the environment variable MIMER_DATABASE. If defined that way the database name can be omitted from the command line.

Database home directory

The database home directory is the catalog where the SYSDB system databank file resides. This path is registered in the sqlhosts file, usually located as /etc/sqlhosts. By using the environment variable MIMER_SQLHOSTS, another file can be pointed out as being the sqlhosts file.

The database home directory can be located using the following command:

# mimpath <database name>

 

The Mimer SQL system databank SYSDB file will be located in the database home directory and other databanks will typically be located relative to it, see Locating Databank Files in System Management Handbook.

Logging database events

Database events are written to the mimer.log file, located in the database home directory.

The following command can be used to list the log-file:

# mimdbfiles -L <database name>

Configuring a database server

The configuration file for an installed Mimer SQL database server is named multidefs and is located in the database home directory.

The content of the configuration file can be seen by using the command:

# mimdbfiles -C <database name>

The multidefs parameter file

The multidefs file holds the parameters adjustable for a database server. It is automatically created when creating the database using the dbinstall command. A default setup is made, but further configurations can be made manually if needed. Refer to the Mimer SQL System Management Handbook or open a discussion with Mimer SQL support representative.

If the multidefs file is not found when starting a database server, a new file will be created using the default values for all parameters. The actual default values used may vary and may depend on factors like machine type and the amount of physical memory available on the machine.

The multidefs settings can be modified after the database is created, and will be taken into account at the next server startup.

The following is an example of a default multidefs parameter file:

-- Mimer SQL version 11.0.6C Beta Test parameters generated 2022-03-29 10:07

Databanks          100         # Max # of databanks (20-1000)

Tables             4000        # Max # of tables (500-1000000)

ActTrans           20000       # Max # of active trans (500-1000000)

SQLPool            1000        # Initial SQLPool (400-8388607 kb)

RequestThreads     8           # # of request threads (1-100)

BackgroundThreads  3           # # of background threads (1-100)

TcFlushThreads     1           # # of t-cache flush threads (0-20)

Users              100         # Max # of logged in users (1-5000)

DBCheck            1           # DB check, 0=index, 1=all, 2=immediate,

                                 3=im. index, 4=im. all (0-4)

Pages4K            206768      # # of 4K bufferpool pages (11-2147480000)

Pages32K           18775       # # of 32K bufferpool pages (7-2147480000)

Pages128K          2186        # # of 128K bufferpool pages (0-2147480000)

DelayedCommit      0           # Delayed commit, 0=Off 1=On 2=Disabled (0-2)

DelayedCommitTimeout 100       # Delayed commit timeout in milliseconds

                                 (0-60000)

GroupCommitTimeout 2           # Group commit timeout in milliseconds (0-20)

Oper                           # Receivers for messages

DumpPath           .           # Path for dump directory

TCPPort            inetd       # TCP/IP port

MaxSQLPool         216000      # SQLPool max size (2400-16777215 kb)

NetworkEncryption  1           # Client/server encryption, 0=None

                                 1=Optional, 2=Required (0-2)

MemLock            0           # Lock bpool in memory, 0=No 1=Yes (0-1)

MiniDump           1           # Small bufferpool dump (no page content),

                                 0=No 1=Yes (0-1)

BackgroundPriority 0           # Thread priority, 0=Default, 1=Highest,

                                 40=Lowest (0-40)

AutoStart          1           # Autostart, 0=No, 1=Yes (0-1)

DumpScript         ./.dumper.sh %p   # Dump Script

HugePages          0           # HugePages, 0=No, 1=2MB, 2=1GB (0-2)

IOQueue            1024        # Max # of concurrent I/O requests (0-65535)

ServerType         3           # Server type: 3=mimexper, 7=miminm (3-9)

 

Comments in the file are introduced by the character sequence --, or by the character ! or #.

A new multidefs file can also be generated manually. If no multidefs file is located in the database home directory, the following command will generate a new one, having the default values:

# mimdbserver -g <database name>

The parameters in multidefs

Automatic database start and stop

When Mimer SQL is installed, autostart is automatically enabled. The mimservers program will start or stop all local Mimer SQL v11.0 servers defined in the sqlhosts file. The setup is done by using the mimautoset command, invoked during installation. For details, see the man-page for mimautostart. To exclude a server from the automatic start/stop procedure, set the AutoStart parameter in the multidefs file for that server to 0. To see the autostart installation made, the following command can be used:

# mimautoset -lv

 

The mimservers command is used to manage all database servers installed. The following command will list the state for all database servers:

# mimservers -b

HugePages

HugePages is a Linux kernel feature to enable use of larger memory pages. Normal pages are usually 4KB, and large pages can vary between 2MB to 1GB. By having the Mimer Bufferpool using HugePages, the operating systems use of page table entries and page state maintenance is reduced. It also increases the hit ratio in the CPU’s Translation Lookaside Buffers (TLB).

Using HugePages

To enable HugePages in Linux, specify the kernel parameter vm.nr_hugepages in /etc/sysctl.conf file. (/etc/sysctl.conf is read during Linux boot.)

For test purposes, you can also use specify the number of hugepages after boot with sysctl -w vm.nr_hugepages=value. (This command is not writing to /etc/sysctl.conf, which means it will be reset at system reboot.)

Determine the default HugePage size by running the following command:

$ grep Hugepagesize /proc/meminfo

Hugepagesize:       2048 kB

 

Run mimcontrol -c to get the size of the bufferpool:

$ mimcontrol -c

...

Buffer pool size: 4096 MiB

 

Divide the size of the bufferpool with the HugePage size, to get the number of large pages. In this case, 4096 / 2 = 2048. Run sysctl with this value:

$ sudo sysctl -w vm.nr_hugepages=2048

vm.nr_hugepages = 2048

 

Run the following command to the see allocated HugePages:

$ grep Huge /proc/meminfo

AnonHugePages:         0 kB

ShmemHugePages:        0 kB

HugePages_Total:    2048

HugePages_Free:     2048

HugePages_Rsvd:        0

HugePages_Surp:        0

Hugepagesize:       2048 kB

 

Edit /etc/sysctl.conf and add the following line to ensure HugePages are allocated after system restarts:

vm.nr_hugepages=2048

 

Change the HugePages parameter in the multidefs file to 1 or 2, depending on the default HugePage size.

Start the Mimer database server:

$ mimcontrol -s

2022-04-05 16:48:45.08   <Information>

======================================

Mimer SQL 11.0.6C Beta Test Apr  4 2022 Rev a37704m

Mimer SQL Experience server for database MIMERDB STARTED at /mimerdb

 

Verify the number of reserved pages with the following command:

$ grep Huge /proc/meminfo

AnonHugePages:         0 kB

ShmemHugePages:        0 kB

HugePages_Total:    2048

HugePages_Free:     2004

HugePages_Rsvd:     2004

HugePages_Surp:        0

Hugepagesize:       2048 kB

 

Background Thread Priority

In the multidefs configuration file there is a parameter called BackgroundPriority that can be used to raise the priority for the Mimer SQL database server background threads. If enabled, the following warning message can be obtained in the database server log file, mimer.log, as a notification on that the intention to increase the priority failed:

2022-06-22 09:18:07.48   <Warning>

Could not set priority for background thread

setpriority: [EACCES] Permission denied

 

To allow this setting to take place, the following command can be used:

sudo setcap CAP_SYS_NICE+iep mimexper

 

...or, if the Mimer SQL In-memory database server is used:

sudo setcap CAP_SYS_NICE+iep miminm

 

The database server then needs to be restarted.

Remote database access

Database TCP/IP connect dispatcher

When a Mimer SQL database is created using the dbinstall command the definitions needed for remote access to the database is installed automatically. Depending on what support the host machine can offer, one or several configurations may be installed on the host system. Locations are as follows per feature provided and available:

In all these cases the mimtcp command is invoked by the operating system when an incoming Mimer SQL database connection request is identified on the target TCP/IP port. It finds out the database name in the handshake message and redirects the connection to the target database using the registered information in /etc/sqlhosts.

To see the setup made, the following command can be used:

# miminetd -l

 

Note:It is possible to let a Mimer SQL database server listen directly to a TCP/IP port, i.e. not using the mimtcp redirecting function. This is achieved by changing the TCPPort parameter in the multidefs file from the default inetd value to the actual port number used, usually 1360.

The mimtcp command

The mimtcp command is used to handle the handshake between a remote client and a database server. It should be used with, and be invoked by, an Internet Service Daemon - see Networking Setup.

Syntax

The overall syntax for MIMTCP is:

mimtcp [-l [-f filename]

 

mimtcp [--log [--file filename]

 

mimtcp [-v|--version] | [-?|--help]

Command-line Arguments

You can use the following arguments with MIMLOAD.

If mimtcp is used without any option, no logging is performed by the program. If a string value is given in addition to the -l option, that value will be used as the log file. If the -l option is used without a value, the filename mimtcp.log will be used that will end up in the root home folder under a sub directory called .mimer_log.

The following example will start mimtcp with logging using the default log file, located in ~/.mimer_log/mimtcp.log:

# mimtcp -l

Services setup

The /etc/services file holds the Internet network services list. The list is a mapping between names for internet services, and their underlying assigned port numbers and protocol types. The following excerpts from the file shows the header for the list and the mimer entries:

# Port Assignments:

#

# Keyword Decimal Description References

# ------- ------- ----------- ----------

mimer     1360/tcp # MIMER

mimer     1360/udp # MIMER

 

Having this definition done, the name MIMER can be used instead of 1360 when dealing with services.

Networking Setup

There are different system features available to administer this depending on platform and operating system versions. Currently inetd, xinetd and systemd are supported, where the first two are described more in detail below.

The miminetd command is used to handle the networking setup. This is done automatically during the installation.

inetd setup

The Linux command inetd is the Internet services daemon, the server process for the Internet standard services. It is usually started up at system boot time. The configuration file /etc/inetd.conf lists the services that inetd should handle. An excerpt from the file shows the syntax used in the file:

#

# Syntax for socket-based Internet services:

#  <service_name> <socket_type> <proto> <flags> <user> <server_pathname> <args>

#

 

When dbinstall is executed, and the inetd.conf file is found, the following line is added to the configuration file:

mimer stream tcp nowait root /usr/bin/mimtcp mimtcp –l

 

This indicates that mimtcp should be started for the mimer service. The –l option is used standalone which implies that the default log file should be used.

When the inetd configuration is changed, for example if mimer is added like described above, the inetd daemon must reread it. This is triggered by sending the HUP signal to the inetd process (located using the ps –ef command):

# ps -ef | grep inetd

root      8796     1  0  2006 ?        00:00:12 inetd

# kill -HUP 8796

#

xinetd setup

The Linux command xinetd stands for “the extended Internet services daemon”. It is the successor to inetd and works in a slightly different way. Instead of having tasks started at system initialization time, and be dormant until a connection request arrives, xinetd is the only daemon process started and it listens on all service ports for the services listed in its configuration file. When a request comes in, xinetd starts the appropriate server.

The default xinetd definitions for Mimer SQL can be found in the file mimersql.xinetd in the installation directory called misc:

$ cat /opt/MimerSQL-11.0.5A/misc/mimersql.xinetd

# default: on

# description: The MIMER service allows remote users to access the

#              Mimer SQL database servers on this node.

service mimer

{

       port            = 1360

       socket_type     = stream

       wait            = no

       user            = root

       server          = /usr/bin/mimtcp

       server_args     = -l

       log_on_failure  += USERID

       disable         = no

       protocol        = tcp

}

$

 

If the /etc/xinetd.d directory is found when dbinstall is executed, the mimersql.xinetd file is copied there and is given the name mimer.

If the /etc/xinetd.d is not found, but /etc/xinetd.conf is found, the mimersql.xinetd contents is added at the end of the /etc/xinetd.conf file.

When the xinetd configuration is changed, for example if mimer is added like described above, the xinetd daemon must reread it. This is triggered by sending the HUP signal to the xinetd process (located using the ps –ef command):

# ps -ef | grep xinetd

root      8796     1  0  2006 ?        00:00:12 xinetd

# kill -HUP 8796

Using odbc.ini data sources

The standard ODBC odbc.ini file and the Mimer SQL sqlhosts file are related to each other in both being repositories for databases, or data sources. When using ODBC to connect to a Mimer SQL database, data source names (DSN) defined in the odbc.ini file can be used. In this case the odbc.ini file is accessed first, and only if needed the ordinary database lookup is done in the /etc/sqlhosts file.

When a Mimer SQL database is created using the dbinstall command, it gets defined in the sqlhosts file in the LOCAL section. For example, if creating the database named my_db with the home directory /usr/local/MimerSQL/my_db, it will end up in /etc/sqlhosts like this:

LOCAL:

  my_db          /usr/local/MimerSQL/my_db

 

If an ODBC Driver Manager is installed, there will also be an option to automatically define it in the global odbc.ini file, usually located as /etc/odbc.ini. Such a definition will look like the following:

[my_db]

Driver    = /usr/lib/libmimodbc.so

Database  = my_db

Host      = localhost

Port      = 1360

Trace     = No

TraceFile = /tmp/mimersql.log

 

We can now look at a simple example where the Perl DBI/DBC-ODBC interface is used to connect to a Mimer SQL database:

#!/usr/bin/perl -w

use DBI;

 

$data_source="dbi:ODBC:my_db";

$username="sysadm";

$auth="sysadm_password";

$dbh = DBI->connect($data_source, $username, $auth) or die $DBI::errstr;

print "Connected! ($dbh)\n";

 

In this case the my_db definition in the odbc.ini file will be used, more precisely the attributes Driver, Database, Host and Port are used:

Assuming a Mimer SQL database on a remote computer is defined in the REMOTE section of the sqlhosts file as follows:

REMOTE:

   prod_db     typhon.mimer.se    tcp   '' 1360

 

Also, assuming we have the following DSN defined in the odbc.ini file:

[remote_prod]

Driver = /usr/lib/libmimodbc.so

Database = prod_db

 

To connect to the prod_db database on the typhon.mimer.se node using the program example above, we can simply change the data source definition in the program above to:

$data_source="dbi:ODBC:remote_prod";

 

The data source remote_prod will be looked up in odbc.ini. The database name prod_db will be encountered, but there is no host defined so an attempt will be made to find appropriate connection information for the given database in the sqlhosts file. When the node typhon.mimer.se and the port 1360 are identified for the database name, the connection will be completed.

The ODBCINI environment variable can be used to point out the odbc.ini file to be used.

Note:Tabs are not allowed in the odbc.ini file.