Troubleshooting

# Check if port is in use
netstat -an | grep 5656
lsof -i :5656

# Check the PID and process name from lsof, then stop that service with its own management command
# To stop an existing Machbase process
machadmin -s

# Check database directory
ls -la $MACHBASE_HOME/dbs/

# Review logs
tail -50 $MACHBASE_HOME/trc/machbase.trc

# Try starting again
machadmin -u

# Check logs
tail -100 $MACHBASE_HOME/trc/machbase.trc

# Check system resources
free -h
df -h

# Reduce memory usage (in machbase.conf)
# PROCESS_MAX_SIZE = 1073741824 # 1GB

# Check for disk space
du -sh $MACHBASE_HOME/dbs/

# Verify server is running
machadmin -e

# Check network connectivity
ping server-address
telnet server-address 5656

# Check firewall
sudo iptables -L | grep 5656

# Verify credentials
machsql -s localhost -u SYS -p MANAGER

-- Check the number of connections
SELECT COUNT(*) FROM V$SESSION;

-- Increase MAX_SESSION_COUNT in machbase.conf
-- MAX_SESSION_COUNT = 8192

-- Kill idle connections (if necessary)

-- Add time filter
SELECT * FROM table DURATION 1 HOUR;  -- Add this!

-- Use LIMIT
SELECT * FROM table LIMIT 1000;

-- Query rollup instead of raw data
SELECT ROLLUP('hour', 1, time) AS rtime, AVG(value)
FROM sensors
WHERE name = 'sensor-1'
GROUP BY rtime;

-- Create index
CREATE INDEX idx_column ON table(column);

-- Check query plan
EXPLAIN SELECT ...;

-- Reduce result set
SELECT * FROM table DURATION 1 HOUR LIMIT 1000;

-- Select fewer columns
SELECT col1, col2 FROM table;  -- Not SELECT *

-- Increase MAX_QPX_MEM in machbase.conf
-- MAX_QPX_MEM = 1G

# Check CSV format
head -10 data.csv

# Verify table schema
machsql -f - <<EOF
SHOW TABLE tablename;
EOF

# Check error log
cat $MACHBASE_HOME/trc/machloader.trc

# Validate data types
# Ensure CSV columns match table schema

# Try small batch first
head -100 data.csv > test.csv
machloader -i -t table -d test.csv -l /tmp/machloader.log

# Check the execution log specified with machloader -l
cat /tmp/machloader.log

-- Check time range
SELECT MIN(_arrival_time), MAX(_arrival_time) FROM table;

-- Check total count
SELECT COUNT(*) FROM table;

-- Check for NULLs
SELECT COUNT(*) FROM table WHERE column IS NULL;

-- Verify import completed
-- Check machloader logs

-- Bad
SELECT * FROM sensors WHERE sensor_id = 'sensor01';

-- Good
SELECT * FROM sensors
WHERE sensor_id = 'sensor01'
DURATION 1 HOUR;

-- Slow
SELECT AVG(value) FROM sensors
WHERE name = 'sensor-1'
  AND time BETWEEN now - 7d AND now;

-- Fast
-- sensors must be a TAG table created WITH ROLLUP.
SELECT ROLLUP('hour', 1, time) AS rtime, AVG(value)
FROM sensors
WHERE name = 'sensor-1'
  AND time BETWEEN now - 7d AND now
GROUP BY rtime;

CREATE INDEX idx_level ON logs(level);

SELECT * FROM logs DURATION 1 DAY LIMIT 1000;

# Memory optimization
PROCESS_MAX_SIZE = 4294967296 # 4GB
MAX_QPX_MEM = 1073741824      # Per-query memory

# Performance tuning
DISK_COLUMNAR_TABLE_CHECKPOINT_INTERVAL_SEC = 900
DISK_COLUMNAR_INDEX_CHECKPOINT_INTERVAL_SEC = 900
QUERY_PARALLEL_FACTOR = 8

DELETE FROM logs EXCEPT 30 DAYS;

# Use APPEND API for bulk inserts
# conn: connection from machbaseAPI.connect(), data: list of rows
conn.append('table', data)

SHOW STORAGE;

# Server running?
machadmin -e

# View active queries
machsql -f - <<EOF
SHOW STATEMENTS;
EOF

# Check storage
machsql -f - <<EOF
SHOW STORAGE;
EOF

# Server log
tail -50 $MACHBASE_HOME/trc/machbase.trc

# Error log
grep -i error $MACHBASE_HOME/trc/machbase.trc

# Recent activity
tail -100 $MACHBASE_HOME/trc/machbase.trc

-- Tables
SHOW TABLES;

-- Table details
SHOW TABLE tablename;

-- Users
SHOW USERS;

-- Indexes
SHOW INDEXES;

-- License
SHOW LICENSE;

# Restart server
machadmin -s && sleep 5 && machadmin -u

# Check disk space
df -h

# Check memory
free -h

# View recent errors
grep -i error $MACHBASE_HOME/trc/machbase.trc | tail -20

# Test connection
machsql -s localhost -u SYS -p MANAGER -f - <<EOF
SELECT SYSDATE;
EOF