Database Tuning

Database Tuning

Proper database tuning is critical for NNTmux performance, especially with large databases containing millions of releases.

Overview

NNTmux is database-intensive. A poorly tuned database can cause:

• Slow page loads
• Timeout errors during processing
• Failed release creation
• High CPU usage

Recommended Database: MariaDB

While MySQL 8+ works, MariaDB 10.6+ is recommended for:

• Better query optimizer
• Improved InnoDB performance
• Lower memory footprint
• Better handling of large tables

Key Configuration Settings

Create a custom configuration file:

sudo nano /etc/mysql/mariadb.conf.d/99-nntmux.cnf

Small Installation (<1M releases, 16GB RAM)

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Query Cache (disable for MariaDB 10.2+)
query_cache_type = 0
query_cache_size = 0

# Connections
max_connections = 200
thread_cache_size = 50

# Temp Tables
tmp_table_size = 256M
max_heap_table_size = 256M

# Buffer Sizes
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# Table Cache
table_open_cache = 4000
table_definition_cache = 2000

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Medium Installation (1-10M releases, 32GB RAM)

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Query Cache
query_cache_type = 0
query_cache_size = 0

# Connections
max_connections = 400
thread_cache_size = 100

# Temp Tables
tmp_table_size = 512M
max_heap_table_size = 512M

# Buffer Sizes
join_buffer_size = 8M
sort_buffer_size = 8M
read_buffer_size = 4M
read_rnd_buffer_size = 8M

# Table Cache
table_open_cache = 8000
table_definition_cache = 4000

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Large Installation (10M+ releases, 64GB+ RAM)

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 32
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 8000
innodb_io_capacity_max = 16000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4

# Query Cache
query_cache_type = 0
query_cache_size = 0

# Connections
max_connections = 800
thread_cache_size = 200

# Temp Tables
tmp_table_size = 1G
max_heap_table_size = 1G

# Buffer Sizes
join_buffer_size = 16M
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 16M

# Table Cache
table_open_cache = 16000
table_definition_cache = 8000

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

Applying Changes

# Check configuration syntax
mysqld --verbose --help > /dev/null

# Restart MariaDB
sudo systemctl restart mariadb

# Verify settings
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Using MySQLTuner

MySQLTuner provides recommendations based on your actual usage:

# Download
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# Run (after database has been running for 24+ hours)
perl mysqltuner.pl --user root --pass yourpassword

Key metrics to watch:

• Buffer pool hit rate - Should be >99%
• Key read ratio - Should be >99%
• Query cache hit rate - N/A if disabled (recommended)
• Temporary tables to disk - Should be minimal

Table Optimization

Analyze Tables

php artisan nntmux:analyze-tables

Or manually:

ANALYZE TABLE releases, release_nfos, usenet_groups, binaries, parts;

Optimize Tables

Run periodically (monthly) during low-usage periods:

php artisan nntmux:optimize-tables

Or manually:

OPTIMIZE TABLE releases;

Warning: OPTIMIZE locks tables and can take hours on large tables.

Check for Fragmentation

SELECT 
    table_name,
    ROUND(data_length/1024/1024, 2) as 'Data (MB)',
    ROUND(index_length/1024/1024, 2) as 'Index (MB)',
    ROUND(data_free/1024/1024, 2) as 'Free (MB)',
    ROUND(data_free/(data_length+index_length)*100, 2) as 'Fragmentation %'
FROM information_schema.tables
WHERE table_schema = 'nntmux'
ORDER BY data_free DESC
LIMIT 20;

Monitoring Performance

Slow Query Log

Enable and monitor slow queries:

-- Check current setting
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- View slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Process List

-- Show running queries
SHOW FULL PROCESSLIST;

-- Show queries running more than 30 seconds
SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 30 AND COMMAND != 'Sleep';

InnoDB Status

SHOW ENGINE INNODB STATUS\G

Look for:

• Buffer pool hit rate in BUFFER POOL AND MEMORY section
• Pending I/O in FILE I/O section
• Deadlocks in LATEST DETECTED DEADLOCK section

Common Issues

High Disk Usage from Pulse Tables

Laravel Pulse can consume significant disk space:

-- Check Pulse table sizes
SELECT 
    table_name,
    ROUND(data_length/1024/1024, 2) as 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'nntmux' 
AND table_name LIKE 'pulse%'
ORDER BY data_length DESC;

Fix:

php artisan pulse:purge

Or configure shorter retention in config/pulse.php.

Table Locks During Processing

If you see lock wait timeouts:

# Increase lock wait timeout
innodb_lock_wait_timeout = 120

# Or use READ COMMITTED isolation
transaction-isolation = READ-COMMITTED

Memory Issues

If MariaDB uses too much memory:

1. Reduce innodb_buffer_pool_size
2. Reduce connection buffers (join_buffer_size, etc.)
3. Reduce max_connections

Disk I/O Bottleneck

If I/O is the bottleneck:

1. Use SSD/NVMe storage
2. Increase innodb_io_capacity
3. Enable compression for large tables:

   ALTER TABLE releases ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Collation and Character Set

For proper Unicode support (emojis, international characters):

php artisan nntmux:convert-collation utf8mb4_unicode_ci

Or manually per table:

ALTER TABLE releases 
    CONVERT TO CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

Backup Strategy

MariaBackup (Recommended)

# Full backup (hot, no locking)
mariabackup --backup --target-dir=/backup/full \
    --user=root --password=yourpass

# Prepare backup
mariabackup --prepare --target-dir=/backup/full

# Restore
systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

mysqldump (Simple but slow)

# Backup
mysqldump -u root -p --single-transaction --quick \
    --routines --triggers nntmux > nntmux_backup.sql

# Restore
mysql -u root -p nntmux < nntmux_backup.sql

External Resources

• MariaDB Performance Tuning
• MySQL Tuner
• Percona Toolkit
• Stack Overflow: MySQL Optimization