Chapter 6. Shared Disk Secondary servers
6.2 Configuration and setup
6.2.2 Secondary server
“Troubleshooting for the setup phase” on page 128 for some troubleshooting examples.
6.1.6 Page files
As already mentioned in 6.1.3, “Communication and message flow” on page 113, the secondary SDS node will apply all changes extracted from the logical log files until the current LSN is moved from the primary server to the buffer pool. Any dirty buffers will not be written to disk, since this can only be done by the primary server. In case of a parallel workload on the secondary server with connected user sessions, it is possible that a dirtied page has to be flushed out by a LRU_MAX_DIRTY event or by a foreground write. In such a case, the buffer will be written to a file referred to as a page file. Two page files have to be specified for secondary SDS servers in your file system with the ONCONFIG parameter SDS_PAGING. At startup time on the SDS secondary, the database server will check to see if the files exist. If they do not, they will be automatically created. The intention of the page files is to keep the flushed dirty buffers for the case that they need to be used again during the actual checkpoint period, either from the application of the logs or from read requests for existing user sessions. Two files also must be specified to support the non-blocking checkpoints feature. At the beginning of the checkpoint, the current page file will be swapped with the non-current file. After the swap, all flushed new dirty files will be written to the new page file. The content of the old file will not be reset until the checkpoint is finished. This is to make sure that in the non-blocking phase of the checkpoint where the primary SDS database server writes out all the pages to disk, the secondary node is able to provide the latest version to potential readers. After the checkpoint is finished on the primary server, the secondary will receive a special log entry and remove all the dirty buffer flags in the buffer pool that were dirtied in the last checkpoint interval. After that, the write position in the old page file will be reset to position 0. The size of the page file will not shrink.
While setting up the location of the page file, the DBA has to keep in mind that the page file size will most likely grow over the secondary SDS servers’ uptime. The reset of a swapped page file as described above does not really mean that the file size will be reset to 0, but only that the current offset will be reset to 0. That means that depending on the workload and the buffer pool settings, the page file sizes can be larger.
Be aware that having a large page file does not need to be considered as an I/O bottleneck in case of heavy page in and page out activities of dirty buffers. The server does not sequentially read the page file to determine the requested page. Instead, each page written to the page file will be stored in a server maintained hash table with the additional information about the offset. In the case of a read, the hash table will read and the offset is determined. After that, the read will be requested.
If the optimization goal is to minimize the page file utilization to achieve disk space use reduction, you should configure the buffer pool size appropriately. Look at peak use times for the particular server and try to find the optimal value for the amount of buffer.
You can keep track utilization and size of the page files by running onstat -g sds on a SDS secondary. To give you an idea of how the output looks, refer to Example 6-4.
Example 6-4 onstat -g sds verbose
informix@Linux:/vobs/IDS11/sds11_prim> onstat -g sds verbose
IBM Informix Dynamic Server Version 11.10.UC1 -- Read-Only (SDS) -- Up 01:00:25 -- 42140 Kbytes
SDS server control block: 0x10a5f588 Local server type: SDS
Server Status : Active
Source server name: sds_prim_11 Connection status: Connected
Last log page received(log id,page): 9,298 Next log page to read(log id,page):9,299 Last LSN acked (log id,pos):9,1220864 Sequence number of last buffer received: 0 Sequence number of last buffer acked: 0 Current paging file:/files/sds11_sec1/pagefile Current paging file size:6144
Old paging file:/files/sds11_sec1/pagefile1 Old paging file size:0
Another way to monitor the utilization based on the I/O for the particular page files is to run onstat -g iof. Example 6-5 shows a sample output. Please look at the statistics for the files named pagefile and pagefile1.
Example 6-5 onstat -g iof on a secondary SDS server
informix@Linux:/vobs/IDS11/sds11_prim> onstat -g iof
IBM Informix Dynamic Server Version 11.10.UC1 -- Read-Only (SDS) -- Up 01:06:11 -- 42140 Kbytes
AIO global files:
gfd pathname bytes read page reads bytes write page writes io/s 3 ./chunk_prim 165888 81 0 0 68435.3
op type count avg. time seeks 0 N/A
writes 0 N/A kaio_reads 0 N/A kaio_writes 0 N/A
4 pagefile 2048 1 1058816 517 3465.1
op type count avg. time seeks 0 N/A reads 1 0.0000 writes 21 0.0003 kaio_reads 0 N/A kaio_writes 0 N/A 5 pagefile1 0 0 1050624 513 3756.1
op type count avg. time seeks 0 N/A reads 0 N/A writes 17 0.0003 kaio_reads 0 N/A kaio_writes 0 N/A
6.1.7 The sysha database
Once the SDS primary is defined, the IDS 11 server will create, similar to an RSS environment, a new database in the rootdbs that is named sysha. The primary SDS server will keep track of information about all the involved SDS nodes that are attached to the system in this database.
The primary table for the SDS infrastructure is:
sds_tab
It is used to keep track of all the attached SDS secondary nodes and their connection time.
Example 6-6 shows a possible output for a system with two defined SDS secondary servers.
Example 6-6 Sample output for a select * from sysha:sds_tab
sds_servername sds_sec_11_0 state C when_created 1184865129 sds_servername sds_sec_11_1 state C when_created 1184865132
6.2 Configuration and setup
The switch of a stand-alone IDS 11 database server into an SDS primary can be performed very quickly. This is done by specifying, with the onmode command, that the current database server will be now acting as a SDS primary. All other necessary prerequisites, such as building the sysha database and setting the SDS_ENABLE ONCONFIG parameter, will be done automatically by the server. A few SDS related ONCONFIG parameters must be manually specified for the SDS secondary server. After these changes, simply bring up the server and the database server will automatically determine that this new server will be
dedicated to an SDS environment and attach the server to this environment by starting the communication with the defined primary server followed by the synchronization of the actual log position.
6.2.1 Primary server
In a shared disk environment, the primary server is the owner of a disk and has read/write access, while all SDS servers have read-only access to a disk. To prepare the server to be an SDS primary, there are changes that have to be made. Those are discussed in the following sections.
sqlhosts file
Specify in the sqlhosts file at least one network based connection protocol for the primary SDS server, such as sockets or tli. During the setup and configuration of the secondary, more entries can be added to this file for the new secondary server to enable the communication between the primary and each secondary.
ONCONFIG parameter
Besides setting an SDS_TIMEOUT parameter, there are no requirements for additional manual SDS related parameters. The server itself automatically adds the settings of an SDS_ENABLE parameter to the ONCONFIG file during SDS enablement for the primary with onmode.
SDS_TIMEOUT specifies the amount of time in seconds that the primary server will wait for a log position acknowledgement to be sent from the SD secondary server. If there is no log position acknowledgement received from the SD secondary server in the specified amount of time, then the primary server will be disconnected from the SD secondary server and continue.
Define the primary server
To define the SDS primary server, IDS 11 has a new onmode statement. Specify a network based DBSERVERNAME or DBSERVERALIASES for the definition of the SDS primary, as follows:
$onmode -d set SDS primary <dbservername of the primary>
Verification
If the onmode statement returns without an error, you can verify the success of the switch to an SDS primary by looking in the log file. In a successful transition case you can view the log file, which should contain onstat outputs, as shown in Example 6-7.
Example 6-7 Verification of a transition to a SDS primary 18:59:50 Building 'sysha' database ...
18:59:51 'sysha' database built successfully.
18:59:51 DR: Reservation of the last logical log for log backup turned on
18:59:51 SDS Source Node Alias Changed from (<null>) to (sds_prim_11) 18:59:51 Value of SDS_ENABLE has been changed to 1.
Be aware, if you run an “onstat -” after the transition, the output will not show a difference in a HDR environment that this server is acting now as a primary, as shown in Example 6-8. A better verification is here to run an onstat -g sds verbose.
Example 6-8 Verification for a successful transition into a SDS primary $onstat -
IBM Informix Dynamic Server Version 11.10.UC1 -- On-Line -- Up 00:34:18 -- 2460096 Kbytes
$#before onmode -d set SDS primary $ onstat -g sds verbose
IBM Informix Dynamic Server Version 11.10.UC1 -- On-Line -- Up 00:00:23 -- 31904 Kbytes
Number of SDS servers:0 Updater node alias name :
$#after onmode -d set SDS primary $ onstat -g sds verbose
IBM Informix Dynamic Server Version 11.10.UC1 -- On-Line -- Up 00:00:55 -- 31904 Kbytes
Number of SDS servers:0
Updater node alias name :sds_prim_11
6.2.2 Secondary server
The SDS server will utilize the definitions of the dbspaces from the primary, so there is no need to create cooked files or configure raw devices for normal dbspaces, blob-, or sbspaces.
Attention: Do not manually change the ONCONFIG parameter
SDS_ENABLE, which was set by the onmode statement to 1 during the lifetime of this server as a SDS primary.
Perform the configuration steps shown below to set up the secondary SDS server
Necessary files
As already discussed in 6.1, “Basic concepts” on page 112, there are some special requirements for the existence of two defined page files needed by each secondary server. Be aware that these files, depending on the workload and configuration of the buffer pool, can be large, so it might be best not to specify them in the /tmp directory.
To support the creation of temporary tables on the actual SDS node, there is a need for a separate temporary dbspace that is only visible in the context of the current SDS secondary. In the test scenarios for this book, it is implied that the file specified for the temporary dbspace in the ONCONFIG does not need to exist. The server will create the file at startup time and give the file the appropriate permissions.
sqlhosts file
You have to create an sqlhosts file that contains the entries for the secondary and the primary SDS servers. Both server definitions have to be based on a network protocol, such as sockets or tli.
If the secondary is defined on the same server as the primary, you could share the sqlhosts file of the primary SDS server and add the new SDS node there. If there is a separate machine for both servers, you also have to add the newly defined SDS secondary into the sqlhosts file on the SDS primary.
Defining the SDS server in a server group is also supported and should be considered for application failover in case the primary connection server of a particular application environment becomes temporarily unavailable.
Example 6-9 shows both types of sqlhost files.
Example 6-9 sqlhost file settings for group and stand-alone definitions define standalone servers
sds_prim_11 onsoctcp Localhost 23497 sds_sec_11_0 onsoctcp Localhost 23495 sds_sec_11_1 onsoctcp Localhost 23499 define servers in servergroups
primm group - - i=2930
sds_prim_11 onsoctcp Localhost 23497 g=primm sds_sec_11_0 onsoctcp Localhost 23495 g=primm sds_sec_11_1 onsoctcp Localhost 23499 g=primm
ONCONFIG parameters
First, take over the ONCONFIG file from the SDS primary server. After that, change the following general parameters:
DBSERVERNAME
DBSERVERALIASES
DBSERVERNUM (if the secondary is on the same machine as the primary)
MSGPATH
To enable the SDS, specify the following values in the ONCONFIG:
SDS_ENABLE=1
SDS_PAGING=<path>,<path1>
SDS_TEMPDBS==<name>,<path>,<pagesize in k>,<offset>,<size>
Example 6-10 shows how these parameters could be set with specific values. Be aware that SDS_PAGING and SDS_TEMPDBS are comma separated and do not allow blank characters. SDS_TEMPDBS will only allow and parse the specification of one temporary dbspace.
Example 6-10 Possible ONCONFIG settings for SDS related parameters on a secondary
SDS_ENABLE 1 # SDS Activation 0 - no, 1 - yes
SDS_TIMEOUT 10 # Time Delay that Primary will wait for any ACK SDS_PAGING /dbspaces/sds11_sec/pagefile,/dbspaces/sds11_sec/pagefile1 SDS_TEMPDBS tempspace,/dbspaces/sds11_sec/sds_sec_tempspace,2,0,100000
Start up the secondary server
After applying the changes to the ONCONFIG file, start up the SDS secondary simply with the following command:
$oninit
Verification
Once the SDS server becomes operational, there will be a message in the SDS servers message log, as depicted in Example 6-11.
Example 6-11 SDS secondary server message log 19:00:00 Setting checkpoint 9:102018 intvl:5
19:00:00 IBM Informix Dynamic Server Initialized -- Shared Memory Initialized.
19:00:00 Requesting logs from 9:102
19:00:00 Initializing SDS Temp dbspace holgerk1 19:00:01 Space 'holgerk1' added.
19:00:01 Onconfig parameter MSGPATH modified from /vobs/IDS11/sds11_prim/sds_prim11.log to
/vobs/IDS11/sds11_sec/sds_sec11.log.
19:00:01 Onconfig parameter DBSERVERNAME modified from sds_prim_11 to sds_sec_11_0.
19:00:01 Onconfig parameter SERVERNUM modified from 120 to 112. 19:00:01 Dataskip is now OFF for all dbspaces
19:00:01 Restartable Restore has been ENABLED 19:00:01 Recovery Mode
19:00:03 Logical Recovery Started.
19:00:03 10 recovery worker threads will be started. 19:00:03 Start Logical Recovery - Start Log 9, End Log ? 19:00:03 Starting Log Position - 9 0x102018
19:00:03 Btree scanners disabled.
19:00:04 DR: SDS secondary server operational
The Primary Server message log will also indicate that the SDS server is connected, as shown in Example 6-12.
Example 6-12 Primary Server message log
In contrast to an SDS primary server, the secondary will also report that it is now acting as a secondary server, in the onstat - output string. This is shown in Example 6-13.
Example 6-13 Verification of the transition to a secondary SDS server
informix@Linux:/vobs/IDS11/sds11_prim> onstat -
IBM Informix Dynamic Server Version 11.10.UC1 -- Read-Only (SDS) -- Up 00:56:21 -- 62140 Kbytes