By default, MariaDB logging might not be fully enabled in Ubuntu 14.04 installations. Let's first check the current log configuration:

sudo mysql -e "SHOW VARIABLES LIKE '%log%';"

If the output shows most log-related variables as OFF or empty paths, you'll need to configure logging manually.

Edit your MariaDB configuration file (typically located at /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/my.cnf):

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add or uncomment these lines under the [mysqld] section:

[mysqld]
log_error = /var/log/mysql/error.log
log_warnings = 2
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Create the log directory and set proper permissions:

sudo mkdir -p /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql
sudo chmod -R 755 /var/log/mysql

After making changes, restart the service:

sudo service mysql restart

For your specific case of 3-hour spikes, check these log locations:

# Error logs
sudo tail -f /var/log/mysql/error.log

# General query logs (be cautious - this generates large files)
sudo tail -f /var/log/mysql/mysql-general.log

# Slow query logs (most relevant for performance issues)
sudo tail -f /var/log/mysql/mysql-slow.log

Create a script to monitor suspicious patterns:

#!/bin/bash
# Monitor for spikes in the last minute
ERROR_COUNT=$(sudo grep "$(date -d '1 minute ago' '+%Y-%m-%d %H:%M')" /var/log/mysql/error.log | wc -l)
SLOW_QUERIES=$(sudo grep "$(date -d '1 minute ago' '+%Y-%m-%d %H:%M')" /var/log/mysql/mysql-slow.log | wc -l)

if [ $ERROR_COUNT -gt 10 ] || [ $SLOW_QUERIES -gt 5 ]; then
    echo "ALERT: Database issues detected at $(date)" | mail -s "DB Alert" admin@example.com
fi

Based on your 3-hour pattern, consider checking:

• Scheduled backups or maintenance jobs
• Automatic table optimizations (ANALYZE, OPTIMIZE TABLE)
• Replication processes if configured
• System cron jobs that might be resource-intensive

When troubleshooting database-related server performance issues (like your 3-hour spike pattern), the first step is verifying if error logging is enabled in MariaDB. By default on Ubuntu 14.04, MariaDB may not write extensive logs unless configured.

Connect to your MariaDB instance and run:

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'general_log%';

If these return empty values or 'OFF', logging needs configuration.

Edit your MariaDB configuration file (typically /etc/mysql/mariadb.conf.d/50-server.cnf) and add/modify:

[mysqld]
log_error = /var/log/mysql/mysql_error.log
general_log = 1
general_log_file = /var/log/mysql/mysql_general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql_slow.log
long_query_time = 2

Create the log directory and set permissions:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

Configure log rotation in /etc/logrotate.d/mysql:

/var/log/mysql/*.log {
    weekly
    rotate 12
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin || exit 0
        mysqladmin flush-logs
    endscript
}

When you observe regular spikes like your 3-hour pattern, check:

grep -A 5 -B 5 'ERROR' /var/log/mysql/mysql_error.log
grep 'Query_time' /var/log/mysql/mysql_slow.log | sort -rn | head -20

Cross-reference timestamps with your New Relic data to identify problematic queries.

For proactive alerts, configure this cron job to check for critical errors:

*/5 * * * * /usr/bin/test -s /var/log/mysql/mysql_error.log && 
  grep -q -i "ERROR\|CRITICAL" /var/log/mysql/mysql_error.log && 
  /usr/bin/mail -s "MariaDB Error Alert" admin@example.com < /var/log/mysql/mysql_error.log