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

Parameter

Definition

Databanks

Specifies the maximum number of databank files that the database server can have open at any one time.

Tables

Specifies the maximum number of tables that can be accessed simultaneously by the database server.

ActTrans

Specifies the maximum number of transactions that can be active in the database server

SQLPool

Initial size of the SQLPool area in K bytes. This area contains information about each session, i.e. opened tables and databanks, compiled SQL programs, etc. The SQLPool area will expand automatically if it is too small, but it will not be larger than MaxSQLPool.

RequestThreads

The number of threads in the database server that can serve client requests.

BackgroundThreads

The number of background threads in the database server.

TcFlushThreads

Extra threads that run in the background to help clear the transaction cache. This is beneficial for systems with long-running transactions. The thread keeps the size of the transaction cache down by deleting records that are no longer used.

When there are no long running transactions the cache can be cleared efficiently without scanning the cache so in this case the thread is not needed. Default is 1 thread.

To get the same behavior as in version 10.0, specify 0 threads for this parameter. For very large databases with long-running transactions more than 1 thread can be used.

Users

The maximum number of users that are allowed to connect to the database server. This parameter should not exceed the number of users specified in the Mimer SQL license key. This number is also used to calculate the size of the shared memory region used for local database server communication. About 70 Kbytes of shared memory will be allocated for each user.

DBCheck

A number which specifies what kind of check that should be performed when a databank is opened which previously was not closed properly.

0 - check index pages

Index pages only are checked in the foreground while applications that access the databank waits for the operation to complete.

1 - check data pages

A full databank check (involving index and data pages) provides for more secure operations, but may take much longer to execute than an index page check. When a full check is done, the index pages are checked in the foreground and the data pages are checked in the background so there is a smaller effect on performance.

2 - Immediate restart, no check

This option performs no checking when the file is opened. The system still verifies the integrity of each page through a checksum. A few pages may have been pre-allocated and these are not reclaimed when this option is used. If the option is subsequently changed these pages will be reclaimed the next time the databank is opened.

3 - Immediate restart, check index pages

This option performs a check of all index pages in the databank in the background. This is done concurrently with other operations on the system.

4 - Immediate restart, check all pages

This option performs a check of all pages in the databank in the background. This is done concurrently with other operations on the system.

The Immediate restart options require a license key module called ‘Imm Restart’. Databank checks can be avoided by always shutting down the database server properly with the mimcontrol/mimdbserver command, especially prior to shutting down the machine.

Pages4K

The number of 4 Kbytes pages in the bufferpool area containing pages from the databank files. The default value of this parameter is 12.5% of the total RAM memory in the machine.

Pages32K

The number of 32 Kbytes pages in the bufferpool area containing pages from the databank files. The default value of this parameter is 8.33% of the total RAM memory in the machine.

Pages128K

The number of 128 Kbytes pages in the bufferpool area containing pages from the databank files. The default value of this parameter is 5% of the total RAM memory in the machine.

DelayedCommit

This option controls how quickly a transaction commit is secured on disk. It greatly affects the performance of the database server. For example, if a single user commits two transactions in quick sequence the database server may use a single I/O to secure both transactions when delayed commit is on. Transactions are never reordered by using the delayed commit option. I.e. it is not possible for a later transaction to be secured on disk before an earlier one. The database is thus always returned to a consistent state after a machine crash. However, if a transaction has been committed but not yet written to disk it will be lost if the database server or machine goes down in an uncontrolled fashion. Transactions that use the XA transaction protocol are automatically committed with delay commit disabled. The delayed commit option can be set to one of the following:

0 - Default off

In this mode delayed commit is not used unless a transaction is set to use delayed commit by the application. This is the default.

1 - Default on

In this mode all transactions where the delay mode has not been explicitly set are delayed. The transaction will be secured within the time-out period specified. If other transactions are committed before the time-out occurs the transactions may be combined into a single I/O to boost performance.

2 - Disabled

In this mode all transactions are secured to disk immediately and the application will not regain control after a commit until the transaction has been secured. This option overrides any application settings for delay commit.

DelayedCommitTimeout

This specifies the number of milliseconds to wait before the transaction is written to disk. If a value of zero is specified transactions are not flushed until the server determines that the commit set page is full. In general, this is not recommended as transactions are likely to be lost if there is an uncontrolled machine stop. Default is 100 milliseconds.

GroupCommitTimeout

How many milliseconds to wait for other transactions to commit before proceeding with first transaction. If another transaction arrives within the timeout period if will be grouped with existing transactions before they are committed together with a single I/O rather. This improves overall performance but the delay prolongs commits time on a system with low load. Default is one millisecond.

Oper

This parameter gives a list of host system users, i.e. operators, or e-mail addresses that should receive e-mail notification of serious problems with the database server.

DumpPath

This parameter may specify an alternate path for the dump directories. The default is to create dump directories under the database home directory.

TCPPort

Specifies how the database server should handle incoming TCP/IP connection requests. If this parameter is set to - (a single dash), the TCP/IP capability will be disabled for the database server. The TCPPort parameter is, by default, set to inetd - which means that the TCP/IP port server program, mimtcp, will be used for establishing a connection to any Mimer SQL database server (of version 8 and later). In this case clients may connect to the port to which mimtcp listens, usually 1360, and the handshake will be passed over to the requested Mimer SQL database server. If a TCP/IP port number is specified, the database server will listen directly to that port.

MaxSQLPool

The maximum size (in kilobytes) of the SQLPool. The SQLPool memory area grows dynamically, but the size will never exceed this parameter. Use this parameter to control the maximum virtual size (maximum page file usage) for the database server process.

NetworkEncryption

Controls the use of encryption of network communication over TCP/IP between server and clients.

0 = Network encryption disabled

Network encryption is not supported or not used.

1 = Network encryption preferred

Network encryption is enabled for version 11 clients. Older clients use unencrypted network communication. When this setting is used, older clients without support for network encryption are allowed to communicate with the database server over TCP/IP.

Use this option when there is a mix of older and newer clients that communicate with the database server over TCP/IP.

This is the default value.

2 = Network encryption required

The database server requires all clients to use encrypted communication when communicating over TCP/IP.

Clients that do not support encryption are rejected at login with error code -18531.

Named Pipes via OS-user login is not allowed.

This option is recommended over option 1 when possible (i.e. when there are no older clients that need to be supported.)

MemLock

A number which specifies whether the bufferpool and communication buffers should be locked in memory (1) or not locked in memory (0).

Minidump

Small bufferpool dump (no page content).

0 = No

1 = Yes (default)

BackgroundPriority

Specifies if the background threads should run at a higher priority than other server threads. During certain circumstances like in situations where the background threads cannot manage to shorten a transaction queue this can be an alternative.

AutoStart

By default, this parameter is set to 1 which indicates that the database should be started automatically when the operating system goes into multi-user mode.

If the parameter is set to 0 the database will not be started automatically.

DumpScript

If the database server goes into an erroneous and unrecoverable state, it will produce dumps of the current internal database structures before it goes down. If this situation occurs, it is of great importance for the error detection process to get a Linux kernel stack trace from the location where the error was located.

By defining this parameter to a command that can produce a kernel trace, such as pstack, stack information will be automatically generated to mimer.log.

The %p option used in the example setting in the beginning of this section, is used to get the current process ID as a parameter to the command given.

HugePages

Enable use of larger memory pages:

0 = No

1 = 2MB

2 = 1GB

See HugePages for more information.

IOQueue

Specifies the maximum number of concurrent IO requests queued to the operating system. Default is 128, but more advanced disk systems such as SAN’s, battery backed caching IO controllers, PCI Express connected SSD’s and NVMe SSD’s can make use of larger queues. This can give a significantly higher database performance, but specifying a too large queue can overload the IO subsystems. Maximum queue length is 65535.

IOThreads

The number of threads in the database server that can serve I/O requests. This parameter is only present and used for some Linux implementations (if it is not present in a default generated multidefs file, the most common reason is that the native Asynchronous I/O is used.)

ServerType

This option decides which Mimer SQL database server program that should be started to operate the database files for the database:

3 - mimexper

The Mimer SQL Experience database server. This is the standard database server.

7- miminm

The Mimer SQL In-memory database server.

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

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:

inetd

The internet services daemon. Here the /etc/inetd.conf file is used for the Mimer SQL configuration.

xinetd

The extended internet services daemon. Here the /etc/xinetd.conf file or the /etc/xinetd.d directory is used for the Mimer SQL configuration.

systemd

The system and service manager. Here the /etc/systemd/system directory is used for the Mimer SQL configuration.

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.

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.1A/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:

Driver

The ODBC driver to be used, specific to each database kind. For Mimer this is the libmimodbc.so shared library.

Database

The name of the database to be accessed, as defined in the sqlhosts file on the node where the database resides.

Host

The name of the computer node where the database resides. If this attribute is left out, the value of the Database attribute will be looked up in the /etc/sqlhosts file for further information about the connection setup.

Port

The port number to used for the database communication. If this attribute is left out, the default '1360' will be assumed.

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.