Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.3  |  CUBRID 9.0.0 | 

CUBRID SHARD Utility

By using the CUBRID SHARD utility, CUBRID SHARD can be started or stopped and various status information can be retrieved.

Starting CUBRID SHARD

To start the CUBRID SHARD, enter the following:

% cubrid shard start

@ cubrid shard start

++ cubrid shard start: success

If the CUBRID SHARD has already been started, the following message will appear:

% cubrid shard start

@ cubrid shard start

++ cubrid shard is running.

While executing cubrid shard start, the information of the CUBRID SHARD configuration file (shard.conf) are read to start all components of the configuration. All metadata DBs and shard DBs should be started before starting the CUBRID SHARD because it accesses them.

Stopping CUBRID SHARD

Enter the following to stop the CUBRID SHARD.

% cubrid shard stop

@ cubrid shard stop

++ cubrid shard stop: success

If the CUBRID SHARD has already been stopped, the following message will appear:

$ cubrid shard stop

@ cubrid shard stop

++ cubrid shard is not running.

Dynamic change of CUBRID SHARD parameters
Description

You can configure the parameters related to running CUBRID SHARD in the environment configuration file (shard.conf). Additionally, you can some CUBRID SHARD parameters while it is running by using the shard_broker_changer utility. For details about configuration of CUBRID SHARD parameters and dynamically changeable parameters see CUBRID SHARD > Configuration.

Syntax

The shard_broker_changer syntax used to change parameter while CUBRID SHARD is running is as follows: Enter the name of CUBRID SHARD running in shard-name and enter dynamically changeable parameters in parameter. value must be specified based on the parameter to be modified. You can apply changes in a specific CUBRID SHARD by specifying an identifier of CUBRID SHARD. proxy-number represents PROXY-ID displayed in the cubrid shard status command.

shard_broker_changer shard-name [proxy-number] parameter value

Example

Even though SQL logs are recorded in CUBRID SHARD which is running, you need to enter as follows to configure the SQL_LOG parameter to ON so that SQL logs are recorded in CUBRID SHARD running. Such dynamic parameter change is effective only while CUBRID SHARD is running.

% shard_broker_changer shard1 sql_log on

OK

Checking CUBRID SHARD status Information

cubrid shard status provides a variety of options to check the status information of each shard broker, shard proxy, and shard cas. In addition, it is possible to check the metadata information and the information on the client who has accessed the shard proxy.

Syntax

<When expr> is given, the status monitoring is performed for the corresponding CUBRID SHARD. When it is omitted, status monitoring is performed for all CUBRID SHARDs registered to the CUBRID SHARD configuration file (shard.conf).

cubrid shard status options [<expr>]

options : [-b | -f [-l sec] | -t | -c | -m | -s <sec>]

Options

The following table shows options that can be used together with cubrid broker status.

Option

Description

<expr>

Displays the status information for the CUBRID SHARD whose name includes <expr>. If the name is not specified, displays the status information for all CUBRID SHARDs.

-b

Displays the status information for the CUBRID broker excluding the information on the CUBRID proxy or the CUBRID CAS.

-c

Displays the information on the client which has accessed the CUBRID proxy.

-m

Displays the metadata information.

-t

Displays in tty mode. The output content can be redirected to a file.

-f [-l secs]

Displays more detailed information on the CUBRID SHARD.

-s secs

Periodically displays the status information for the CUBRID SHARD at a specified time. Returns to the command prompt if q is entered.
Example

If no options or parameters are given to check the status of all CUBRID SHARDs, the following will be displayed as a result:

@ cubrid shard status

% test_shard  - shard_cas [2576,45000] /home/CUBRID/log/broker/test_shard.err

 JOB QUEUE:0, AUTO_ADD_APPL_SERVER:ON, SQL_LOG_MODE:ALL:100000

 LONG_TRANSACTION_TIME:60.00, LONG_QUERY_TIME:60.00, SESSION_TIMEOUT:10

 KEEP_CONNECTION:AUTO, ACCESS_MODE:RW

----------------------------------------------------------------

PROXY_ID SHARD_ID   CAS_ID   PID   QPS   LQS PSIZE STATUS

----------------------------------------------------------------

       1        1        1  2580     100     3 55968 IDLE

       1        2        1  2581     200     4 55968 IDLE

  • % test_shard: The proxy name
  • shard_cas: The application server format [shard_cas | shard_cas_myqsl]
  • [2576, 45000]: The proxy process ID and the proxy access port number
  • /home/CUBRID/log/broker/test_shard.err: The error log file of test_shard
  • JOB QUEUE: The number of standing by jobs in the job queue
  • SQL_LOG_MODE: The SQL_LOG parameter value of the shard.conf file has been set to ALL in order to log in all SQL.
  • SLOW_LOG: The SLOW_LOG parameter value of the shard.conf file has been set to ON in order to log the query where any long-duration execution query or any error has occurred to the SLOW SQL LOG file.
  • LONG_TRANSACTION_TIME: The execution time of a transaction to be considered as a long-duration transaction. When the execution time of a transaction exceeds 60 seconds, it is considered as a long-duration transaction.
  • LONG_QUERY_TIME: The execution time of a query to be considered as a long-duration query. When the execution time of a query exceeds 60 seconds, it is considered as a long-duration query.
  • SESSION_TIMEOUT: The timeout value to terminate a CAS session that has made no requests without any commit or rollback after starting the transaction. When this time is expired in this status, the connection between the application client and the application server (CAS) is terminated. The SESSION_TIMEOUT parameter value of the shard.conf is 300 (secs).
  • ACCESS_MODE: The shard broker operation mode. The RW mode allows modification of the database as well as retrieval.
  • PROXY_ID: The serial number of a proxy which has been sequentially given in the shard broker
  • SHARD_ID: The serial number of a shard DB set in the proxy
  • CAS_ID: The serial number of an application server (CAS) which accesses the shard DB
  • PID: The ID of an application server (CAS) process which accesses the shard DB
  • QPS: The number of queries processed per second
  • LQS: The number of long-duration queries processed per second
  • PSIZE: The size of the application server process
  • STATUS: The current status of the application server, such as BUSY/IDLE/CLIENT_WAIT/CLOSE_WAIT/CON_WAIT.

To check the status of the shard broker, enter the following:

$ cubrid shard status -b

@ cubrid shard status

  NAME           PID  PORT  Active-P  Active-C      REQ  TPS  QPS  K-QPS NK-QPS    LONG-T    LONG-Q  ERR-Q

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

* test_shard    3548 45000         1         2        0    0    0      0      0    0/60.0    0/60.0      0

  • NAME: The proxy name
  • PID: The process ID of the proxy
  • PORT: The proxy port number
  • Active-P: The number of proxy
  • Active-C: The number of application servers (CASs)
  • REQ: The number of client requests processed by the proxy
  • TPS: The number of transactions processed per second (calculated only when the option is -b -s <sec>)
  • QPS: The number of queries processed per second (calculated only when the option is -b -s <sec>)
  • K-QPS: QPS for the queries which include a shard key
  • NK-QPS: QPS for the queries which do not include a shard key
  • LONG-T: The number of transactions that exceed the LONG_TRANSACTION_TIME time/LONG_TRANSACTION_TIME parameter value
  • LONG-Q: The number of queries that exceeds the LONG_QUERY_TIME time/LONG_QUERY_TIME parameter value
  • ERR-Q: The number of queries where errors have occurred

To check details on the status of the shard broker, enter as follows:

$ cubrid shard status -b -f

@ cubrid shard status

NAME           PID  PSIZE  PORT  Active-P  Active-C      REQ  TPS  QPS  K-QPS (H-KEY   H-ID H-ALL) NK-QPS    LONG-T    LONG-Q  ERR-Q  CANCELED  ACCESS_MODE  SQL_LOG

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

* test_shard 3548 100644 45000         1         2        0    0    0      0      0      0      0      0    0/60.0    0/60.0      0         0           RW      ALL

  • NAME: The proxy name
  • PID: The process ID of the proxy
  • PSIZE: The process size of the proxy
  • PORT: The proxy port number
  • Active-P: The number of proxies
  • Active-C: The number of application servers (CASs)
  • REQ: The number of client requests processed by the proxy
  • TPS: The number of transactions processed per second (calculated only when the option is -b -s <sec>)
  • QPS: The number of queries processed per second (calculated only when the option is -b -s <sec>)
  • K-QPS: QPS for the queries which include a shard key
  • H-KEY: QPS for the queries which include the shard_key hint
  • H-ID: QPS for the queries which include the shard_id hint
  • H-ALL: QPS for the queries which include the shard_all hint
  • NK-QPS: QPS for the queries which do not include a shard key
  • LONG-T: The number of transactions that exceeds the LONG_TRANSACTION_TIME time/LONG_TRANSACTION_TIME parameter value
  • LONG-Q: The number of queries that exceeds the LONG_QUERY_TIME time/LONG_QUERY_TIME parameter value
  • ERR-Q: The number of queries where errors have occurred
  • CANCELED: The number of queries which have been canceled due to user interruption after the shard broker had been started (the number accumulations for N seconds in case of using with the -l N option)
  • ACCESS_MODE: The shard broker operation mode. The RW mode allows modification of the database as well as retrieval.
  • SQL_LOG: The SQL_LOG parameter value of the shard.conf file is ALL in order to leave the SQL log.

By using the -s option, enter the monitoring interval of the shard broker which includes test_shard, and then enter the following to monitor the shard broker status periodically. If test_shard is not entered as a parameter,the status monitoring is periodically made for all shard brokers. If q is entered, the monitoring screen returns to the command prompt.

$ cubrid shard status -b test_shard -s 1 -t

@ cubrid shard status

  NAME           PID  PORT  Active-P  Active-C      REQ  TPS  QPS  K-QPS NK-QPS    LONG-T    LONG-Q  ERR-Q

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

* test_shard    3548 45000         1         2        0    0    0      0      0    0/60.0    0/60.0      0

Output TPS and QPS information to a file by using the -t option. To stop the output as a file, press <Ctrl+C> to stop the program.

% cubrid shard status -b -s 1 -t  > log_file

Output the metadata information by using the -m option. For details on the parameter of shard.conf, see Default Configuration File shard.conf.

$ cubrid shard status -m

@ cubrid shard status

% test_shard [299009]

MODULAR : 256, LIBRARY_NAME : NOT DEFINED, FUNCTION_NAME : NOT DEFINED

SHARD STATISTICS

           ID  NUM-KEY-Q  NUM-ID-Q   NUM-NO-HINT-Q       SUM

        -----------------------------------------------------

            0          0         0               0         0

            1          0         0               0         0

            2          0         0               0         0

            3          0         0               0         0

  • test_shard: The proxy name
  • [299009]: The decimal value of the METADATA_SHM_ID parameter of shard.conf
  • MODULAR: The SHARD_KEY_MODULR parameter value of shard.conf
  • LIBRARY_NAME: The SHARD_KEY_LIBRARY_NAME parameter value of shard.conf
  • FUNCTION_NAME: The SHARD_KEY_FUNCTION_NAME parameter value of shard.conf
  • SHARD STATISTICS: The shard ID query information
    • ID: The shard DB serial number (shard ID)
    • NUM-KEY-Q: The number of query requests which include the shard key
    • NUM-ID-Q: The number of query requests which include the shard ID
    • NUM-NO-HINT-Q: The number of requests handled by load balancing without hint when IGNORE_SHARD_HINT is configured
    • SUM: NUM-KEY-Q + NUM-ID-Q

Use the -m -f option to display more detailed metadata information. For details on the parameter of shard.conf, see Default Configuration File shard.conf.

$ cubrid shard status –m -f

@ cubrid shard status

% test_shard [299009]

MODULAR : 256, LIBRARY_NAME : NOT DEFINED, FUNCTION_NAME : NOT DEFINED

SHARD : 0 [HostA] [shard1], 1 [HostB] [shard1], 2 [HostC] [shard1], 3 [HostD] [shard1]

SHARD STATISTICS

           ID  NUM-KEY-Q  NUM-ID-Q   NUM-NO-HINT-Q       SUM

        -----------------------------------------------------

            0          0         0               0         0

            1          0         0               0         0

            2          0         0               0         0

            3          0         0               0         0

 

RANGE STATISTICS : user_no

          MIN ~   MAX :      SHARD     NUM-Q

        ------------------------------------

            0 ~    31 :          0         0

           32 ~    63 :          1         0

           64 ~    95 :          2         0

           96 ~   127 :          3         0

          128 ~   159 :          0         0

          160 ~   191 :          1         0

          192 ~   223 :          2         0

          224 ~   255 :          3         0

DB Alias : shard1 [USER : shard, PASSWD : shard123]

  • test_shard: The proxy name
  • [299009]: The decimal value of the METADATA_SHM_ID parameter of shard.conf
  • MODULAR: The SHARD_KEY_MODULR parameter value of shard.conf
  • LIBRARY_NAME: The SHARD_KEY_LIBRARY_NAME parameter value of shard.conf
  • FUNCTION_NAME: The SHARD_KEY_FUNCTION_NAME parameter value of shard.conf
  • SHARD: The shard DB information in the proxy
    • 0: The shard DB serial number (shard ID)
    • [HostA]: The shard access information
    • [shard1]: The actual DB name
  • ID: The shard DB serial number (shard ID)
  • NUM-KEY-Q: The number of query requests which include a shard key
  • NUM-ID-Q: The number of query requests which include a shard ID
  • SUM: NUM-KEY-Q + NUM-ID-Q
  • RANGE STATISTICS: The shard key query information
    • user_no: The shard key name
    • MIN: The minimum range of a shard key
    • MAX: The maximum range of a shard key
    • SHARD: The shard DB serial number (shard ID)
    • NUM-Q: The number of query requests which include the shard key

Displays the information on the client that has accessed the shard proxy by using the -c option.

$ cubrid shard status -c

@ cubrid shard status

% test_shard(0), MAX-CLIENT : 10000

------------------------------------------------------------------------------------------------

 CLIENT-ID           CLIENT-IP             CONN-TIME            L-REQ-TIME            L-RES-TIME

------------------------------------------------------------------------------------------------

         0         10.24.18.68   2011/12/15 16:33:31   2011/12/15 16:33:31   2011/12/15 16:33:31

  • CLIENT-ID: The client serial number sequentially given in the proxy
  • CLIENT-IP: The client IP address
  • CONN-TIME: The time that the proxy has been accessed
  • L-REQ-TIME: The time at which the last request had been made to the proxy
  • L-RES-TIME: The time at which the last response has been received from the proxy
Limit shard proxy access
Description

To limit the applications to access shard proxy, the ACCESS_CONTROL of the cubrid_shard.conf should set to ON and enter a file name where the list of users, databases, and IPs of which access is access is permitted ot the ACCESS_CONTROL_FILE parameter is stored. The default value of ACCESS_CONTROL parameter is OFF.

The ACCESS_CONTROL and ACCESS_CONTROL_FILE parameters should be written under [shard] which are located in common parameters.

The format of ACCESS_CONTROL_FILE is as follows:

[%<shard_name>]

<db_name>:<db_user>:<ip_list_file>

…

  • <shard_name>: Shard proxy name. It is one of shared proxies specified by cubrid_broker.conf.
  • <db_name>: Database name. If it is specified as *, every database can be permitted.
  • <db_user>: The user ID of the database. If it is specified as *, the user ID of every database is permitted.
  • <ip_list_file>: The file name where the list of IPs accessable is stored. You can use a comman to separate each file such as ip_list_file1, ip_list_file2, ….

You can additionally specify [%<broker_name>] and <db_name>:<db_user>:<ip_list_file> for each shard proxy and separate line can be added for the same <db_name> and <db_user>.

The format of writing ip_list_file is as follows:

<ip_addr>

…

  • <ip_addr>: The name of IP of which access is permitted. If * is enterd in back part, it means every IP is permitted.

While the value of ACCESS_CONTROL is ON and ACCESS_CONTROL_FILE is not specified, shard proxy allows access request from localhost. When running shard proxy and if it analysis of ACCESS_CONTROL_FILE and ip_list_file is faled, shard proxy allows access request only from localhost.

When running shard proxy and if it analysis of ACCESS_CONTROL_FILE and ip_list_file is faled, shard proxy does not run.

# cubrid_broker.conf

[broker]

MASTER_SHM_ID           =30001

ADMIN_LOG_FILE          =log/broker/cubrid_broker.log

ACCESS_CONTROL   =ON

ACCESS_CONTROL_FILE     =/home1/cubrid/access_file.txt

[%QUERY_EDITOR]

SERVICE                 =ON

BROKER_PORT             =30000

......

The following is an example of ACCESS_CONTROL_FILE. * means everything; it can be used when you specifying the database name, database user ID, and the list of IP list file.

[%QUERY_EDITOR]

dbname1:dbuser1:READIP.txt

dbname1:dbuser2:WRITEIP1.txt,WRITEIP2.txt

*:dba:READIP.txt

*:dba:WRITEIP1.txt

*:dba:WRITEIP2.txt

 

[%SHARD2]

dbname:dbuser:iplist2.txt

 

[%SHARD3]

dbname:dbuser:iplist2.txt

 

[%SHARD4]

dbname:dbuser:iplist2.txt

The shard proxy specified above is QUERY_EDITOR, SHARD2, and SHARD3, SHARD4.

The QUERY_EDITOR shard proxy allows only access of the same applications.

  • A user logging in with dbuser1 to dbname1 accesses IP registered in READIP.txt
  • A user logging in with dbuser1 to dbname1 accesses IP registered in WRITEIP1.txt or WRITEIP2.txt
  • A user logging in with DBA to every database accesses IP registered in READIP.txt, WRITEIP1.txt, or WRITEIP2.txt

The following shows how to configure IPs accessible in ip_list_file.

192.168.1.25

192.168.*

10.*

*

The IPs specified above are as follows:

  • The configuration of the first line allows 192.168.1.25.
  • The configuration of the second line allows every IP starting with 192.168.
  • The configuration of the third line allows every IP starting with 10.
  • The configuration of the fourth line allows every IP.

For shard proxy which has been running, you can re-apply configuration by using the following command or check the current status.

Syntax

To apply changes to server after database, database user ID, and IP allowed in shard proxy is configured, use the following command.

cubrid shard acl reload [<SP_NAME>]

  • SP_NAME: shard proxy name. If this value is specified, changes are applied to specific shard proxy; if it is omitted, changes are applied to every shard proxy.

To output IP configuration of which database, database user ID, and IP allowed in shard proxy to screen, use the following command.

cubrid shard acl status [<SP_NAME>]

  • SP_NAME: shard proxy name. If this value is specified, changes are applied to specific shard proxy; if it is omitted, changes are applied to every shard proxy.

Note For details, see Limiting Database Server Access.

Managing specific shard

Enter the following to run shard1.

$ cubrid shard on shard1

If shard1 is not configured in shared memory, the following message will output.

% cubrid shard on shard1

Cannot open shared memory

To exit shard1, enter the following.

$ cubrid shard off shard1

To restart shard1, enter the following.

$ cubrhd shard restart shard1

The shard proxy reset feature disconnects exiting connection and makes new connection when shard proxy is connected unwanted database server due to failover in HA. If SHARD_DB_NAME, SHARD_DB_USER, SHARD_DB_PASSWORD is changed dynamically, it will try to connect with the changed value.

% cubrid shard reset shard1