System Troubleshooting MariaDB ColumnStore

MariaDB ColumnStore Support Tool

This tool can be executed by users, called "columnstoreSupport", that will generated a report that contains the log files and other system data that is used by MariaDB Personnel to help diagnose system related issues and errors within the MariaDB Columnstore Product.

Here is how to run it:

On a single server:

/usr/local/mariadb/columnstore/bin/columnstoreReport -a

On a multi-node combo server, run on pm1

/usr/local/mariadb/columnstore/bin/columnstoreReport -a -p 'user-password'

On a multi-node separate server, run on um1

/usr/local/mariadb/columnstore/bin/columnstoreReport -a -p 'user-password'

Here is an example of it getting run the the report that is generated:

/usr/local/mariadb/columnstore/bin/columnstoreSupport -a
Get software report data for pm1
Get config report data for pm1
Get log report data for pm1
Get log config data for pm1
Get hardware report data for pm1
Get resource report data for pm1
Get dbms report data for pm1

Columnstore Support Script Successfully completed, files located in columnstoreSupportReport.tar.gz

columnstoreSupportReport.tar.gz is what you would provide to MariaDB personnel or attach to a JIRA.

MariaDB ColumnStore logging

MariaDB ColumnStore utilizes the install system logging tool, whether its syslog, rsyslog, or ng-syslog. The logs are located in /var/log/mariadb/columnstore. There are these 5 logs:

• crit.log
• err.log
• warning.log
• info.log
• debug.log We also utilize the log rotate tool and by default we are configured to keep 7 days of log files. They are stored in /var/log/mariadb/columnstore/archive.

Also in the /var/log/mariadb/columnstore directory, there are a few other logs that are kept:

• activeAlarms – List of active alarms currently set on the system
• alarm.log – list of all the alarms and associated clear alarms
• uiCommands.log – list of the mcsadmin commands entered

The MariaDB ColumnStore Process Corefiles would be stored in /var/log/mariadb/columnstore/corefiles, that is if core file dumping is enabled on the system.

MariaDB ColumnStore log files and what goes in them.

• Crit, err, and warning used to log problems by a MariaDB ColumnStore Process.
• Info will have logs showing high level actions that are going on in the system. During a stop/startsystem, it will show the high level commands of the process/modules being stopped and started. The bulk-load (cpimport) tool logs is high level actions there also.
• Debug will have the lower level actions from the MariaDB ColumnStore Processes, which will include queries. So all the mysql query commands will be logged here.

MariaDB ColumnStore-MySQL logs are stored here:

/usr/local/mariadb/columnstore/mysql/db/’server-name’.err

NOTE: Other log files will be written to the log directory as well as the /tmp directory from MariaDB ColumnStore process during certain operations. So you will see a few other logs show up in the log directory besides these.

MariaDB ColumnStore database files

The MariaDB ColumnStore has 3 sets of database files. These files are also always backed up and restored together as part of the backup and restore process.

• MariaDB ColumnStore-MySQL schemes - /usr/local/mariadb/columnstore/mysql/db/*
• MariaDB ColumnStore Database - /usr/local/mariadb/columnstore/dataX/000.dir
• MariaDB ColumnStore DBRM files - /usr/local/mariadb/columnstore/data1/systemFiles/dbrm/*

MariaDB ColumnStore Database: The X represents the DBroot ID #. DBroot is the file directory containing the meta-data. We generally assign 1 DBroot per Performance Module. This DBroot can be pointing to local disk storage area or mounted to external disk. So for a single-server setup, we would have ‘data1’ as the dbroot. If we had a system will 2 Performance modules, then we would have ‘data1’ on PM1 and ‘data2’ on PM2.

MariaDB ColumnStore DBRM files: This is where the Extent Map and Versioning files are located. These make up the DBRM files. There are 3 copies of the files that are keep, one is the current active set and the other 2 are the backup. The current active copy file name is located in this file:

/usr/local/mariadb/columnstore/data1/systemFiles/dbrm/BRM_saves_current

The Extent Map is loaded into shared memory on each of the nodes during the start-system process time. The version in shared memory on the PM1 node is the main copy. Changes are applied to that version in memory. Then changes are made to the disk version that only exist on PM1 disk storage and a copy of those changes are sent out to the other nodes and their memory copies are updated.

NOTE: The following utility can be used to dump the internal memory copy of the Extent Map /usr/local/mariadb/columnstore/bin/editem : there are a few options with this command, -i dumps a raw copy and -d dumps a formatted copy

MariaDB ColumnStore troubleshooting utilities

Here are a few of the common utilities that are used to view and troubleshoot issues. All of these commands are located in /usr/local/mariadb/columnstore/bin/

• editem – used to view the Extent Map in internal memory, discussed in previous section
• clearShm – used to clear the shared internal memory, used at times after a system-shutdown command is do just to make sure the memory is cleared
• configxml.sh – used to get and set parameters from the system config file, ColumnStore.xml
  • ./configxml.sh getconfig ExeMgr1 Port
  • ./configxml.sh setconfig ExeMgr1 Port 8601
• dbrmctl – used to display are change the DBRM status
  • Example, the dbrm might be in a readonly state, this would unlock
    • ./dbrmctl resume
• viewtablelock – will display what tables are locked. There might be times when a DML command might fail and leave a table in a locked state. You can run this command to find which tables are locked, and then use the ‘cleartablelock’ command
• cleartablelock – use to clear a table lock. As explained above, could be used to clear a lock on a table that was left set on a failed command

MariaDB ColumnStore Administration multi-node commands

This quick start guide shows how to bring up a single-node system. MariaDB ColumnStore also supports multi-node systems. From reading the other documents, you will have seen where we can support different configurations.

Some systems will have the User Module functionality on a separate server than the Performance Module functionality. These types of system will have both User Module and Performance Module servers. User Modules is where the MySQL front-end will reside and where the sql commands are entered. The Performance Modules will have access to the meta-data both stored on disk and cached in its internal memory.

Some systems will have the User Module functionality on the same server as the Performance Module functionality. The single-server node is like this and you can also have multiple-nodes using this configuration. Here are some common mcsadmin commands that are used on multi-node systems:

1. Adding modules
   • Example of adding another Performance Module to the system. This is assuming the server is setup for a MariaDB ColumnStore install, like dependent packages are installed and it will allow a root user login. This command will add 1 performance module to the system and will auto assign its ID. So you might end up with say ‘pm2’ add if you started with only a 1 pm mulit-node-system
     • addmodule pm 1 ‘host-name’
2. Adding DBroots
   • When a new Performance Module is added, you will need to add a new DBroot to the system, which will then be assigned to that newly added module. This command would add 1 DBroot and will auto assign the ID. So if the system had just 1 DBroot configured, DBRoot #2 would be added
     • adddbroot 1
3. Assigning DBroot
   • If you have added a new Performance Module and a new DBroot, then you will need to assign that Dbroot to the module. You can also assign a new dbroot to an existing module.
     • assigndbrootpmconfig 2 pm2
4. Moving DBroot
   • This command might be used if you decided to remove or disable a Performance Module on a system, but you wanted to keep the data in the DBroot that was assigned to the module. You can use this command to move the assignment from one pm to another. This example is moving DBRoot 2 from pm2 to pm1
     • movepmdbrootconfig pm2 2 pm1
   • NOTE: DBroot #1 is always assigned to Performance Module #1, so this DBroot can’t be manually moved to another Performance Module.
5. Enabling Module
   • One a module has been added, it will be in a disabled state. This means its now configured, but not in-use. You would run this command to enable a newly added module. If it's a User Module, then that module will be enabled and started, making it part of the system. If it's a Performance Module, it will only be enabled and set to a Manually Offline state. At this point, you would use the assigndbrootpmconfig or movepmdbrootconfig command to make a DBroot assigned to that module. Then you need to run startSystem to bring online.
     • alterSystem-enableModule um2
6. Disabling Module
   • You can disable a module. This will put a module in a disabled state, meaning the system will not be utilizing it. But it's still configured and could be enabled back at some point
     • alterSystem-disableModule um2
7. Removing Module
   • You can remove a module. This will de-configure a module in the system. A stopSystem is required to be run first before a remove can be removed.
     • removeModule um2

Command sequence to add an User Module using the 'mcsadmin' tool

mcadmin > addModule um2 'host-name'
mcadmin > alterSystem-enableModule um2

Command sequence to add a Performance Module using the 'mcsadmin' tool

mcadmin > addModule pm2 'host-name'
mcadmin > alterSystem-enableModule pm2
mcadmin > adddbroot 1
mcadmin > assigndbrootpmconfig 2 pm2
mcadmin > startSystem

MariaDB ColumnStore Administration Misc commands

1. Get List of active SQL Statements
   • This command will show the active SQL statements in the system
     • getActiveSQLStatements
2. Get Module Configuration
   • This command shows the Module Configuration of the system
     • getModuleConfig
3. Get MariaDB Columnstore software version
   • This command shows the MariaDB Columnstore software version installed
     • getSoftwareInfo
4. Get Storage Configuration
   • This command shows the Storage Configuration of the system
     • getStorageConfig
5. Get System Information
   • This command shows the System, Module, and Process Status's of the system
     • getSystemInfo
6. Get System Network Configuration
   • This command shows the Module Network Configuration of the system
     • getSystemNetworkConfig
7. Get System Network Configuration
   • This command shows the Module Resource usage of the system, which includes system and process CPU usage, Memory and Swap usage, and Disk Usage
     • getSystemResourceUsage