Configuración Avanzada de MySQL y PostgreSQL

¡Domina las configuraciones avanzadas de MySQL y PostgreSQL! En este tutorial avanzado te guiaré paso a paso para optimizar el rendimiento, seguridad y mantenimiento de estos sistemas de bases de datos en entornos de producción.

Objetivo: Aprender configuraciones avanzadas de MySQL y PostgreSQL incluyendo optimización de rendimiento, seguridad hardening, monitoreo avanzado, backup/recuperación y troubleshooting para entornos de producción.

Paso 1: Configuración avanzada de MySQL

Optimización del motor InnoDB

# /etc/mysql/my.cnf o /etc/my.cnf
[mysqld]
# Buffer Pool (70-80% de RAM en sistemas dedicados)
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8  # 1 instancia por cada GB de buffer pool

# Log Files y I/O
innodb_log_file_size = 2G  # 25% del buffer pool
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2  # Mejor rendimiento, menor durabilidad
innodb_flush_method = O_DIRECT

# Concurrencia
innodb_thread_concurrency = 0  # 0 = ilimitado
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# Otras optimizaciones
innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

Ajustes de consultas y conexiones

[mysqld]
# Configuración de conexiones
max_connections = 500
max_connect_errors = 1000000
wait_timeout = 600
interactive_timeout = 600

# Cache de consultas
query_cache_type = 0  # Desactivado en MySQL 8.0+
query_cache_size = 0

# Buffers y cachés
key_buffer_size = 64M  # Para MyISAM (si se usa)
thread_cache_size = 100
table_open_cache = 4096
table_definition_cache = 2048

Configuración de logs avanzados

[mysqld]
# Logs de errores y consultas lentas
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2
log_queries_not_using_indexes = 1

# Binary logging para replicación y point-in-time recovery
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 14
max_binlog_size = 1G
binlog_format = ROW  # Más seguro para replicación

Paso 2: Configuración avanzada de PostgreSQL

Ajustes de memoria y resource management

# postgresql.conf o postgresql.auto.conf
# Memory Configuration
shared_buffers = 4GB           # 25% de RAM total
work_mem = 32MB                # Memoria por operación de ordenamiento
maintenance_work_mem = 1GB     # Memoria para operaciones de mantenimiento
effective_cache_size = 12GB    # 75% de RAM total

# Parallel Processing
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 2

Optimización de E/S y Write-Ahead Log (WAL)

# Checkpoint Configuration
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

# I/O Configuration
random_page_cost = 1.1         # Para SSDs
effective_io_concurrency = 200  # Para SSDs
wal_compression = on
wal_buffers = 16MB

# Vacuum Configuration
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

Configuración de conexiones y consultas

# Connection Settings
max_connections = 200
superuser_reserved_connections = 3
shared_preload_libraries = 'pg_stat_statements,auto_explain'

# Query Planning
default_statistics_target = 100
geqo = on
geqo_threshold = 12
from_collapse_limit = 8
join_collapse_limit = 8

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000  # Log queries > 1s

Paso 3: Seguridad avanzada

Hardening de MySQL

-- Configuración de seguridad en MySQL
-- Eliminar usuarios anónimos
DELETE FROM mysql.user WHERE User = '';

-- Eliminar acceso remoto a root
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Forzar SSL para usuarios privilegiados
ALTER USER 'root'@'localhost' REQUIRE SSL;

-- Crear usuario con permisos limitados
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%';

-- Configurar password policy
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

FLUSH PRIVILEGES;

Hardening de PostgreSQL

-- Configuración de seguridad en PostgreSQL
-- Modificar pg_hba.conf para mayor seguridad
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    app_db          app_user        192.168.1.0/24          scram-sha-256

-- Forzar SSL en postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- Configurar autenticación robusta
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPassword123!';
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Configurar políticas de password
ALTER ROLE app_user SET password_encryption = 'scram-sha-256';

Paso 4: Monitoreo y mantenimiento avanzado

Monitoreo en MySQL

-- Consultas de monitoreo útiles
SHOW ENGINE INNODB STATUS;  -- Estado completo de InnoDB

-- Ver conexiones activas
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' 
ORDER BY TIME DESC;

-- Ver uso de buffers
SELECT * FROM sys.memory_global_total;

-- Estadísticas de rendimiento
SELECT * FROM sys.schema_table_statistics;

-- Configurar Performance Schema para monitoreo detallado
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements_%';

Monitoreo en PostgreSQL

-- Habilitar extensión de estadísticas
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Consultas de monitoreo
SELECT * FROM pg_stat_activity;  -- Actividad actual
SELECT * FROM pg_stat_database;  -- Estadísticas por base de datos
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;  -- Consultas más costosas

-- Ver tamaño de bases de datos y tablas
SELECT datname, pg_size_pretty(pg_database_size(datname)) 
FROM pg_database;

SELECT schemaname, relname, pg_size_pretty(pg_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_relation_size(relid) DESC;

-- Configurar auto_explain para análisis de consultas
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000;  -- Log queries > 1s
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;

Paso 5: Backup y recuperación avanzados

Backup en MySQL

# Backup físico con MySQL Enterprise Backup
mysqlbackup --backup-dir=/backups/full --backup-image=/backups/full.mbi backup-to-image

# Backup lógico completo
mysqldump --single-transaction --routines --triggers --events --all-databases > full_backup.sql

# Backup incremental con binary logs
mysqlbinlog --start-datetime="2023-01-01 00:00:00" mysql-bin.00000* > incremental_backup.sql

# Script automatizado de backup
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump --single-transaction --routines --triggers --events my_database | gzip > /backups/my_database_$DATE.sql.gz
# Rotar backups (eliminar más antiguos de 30 días)
find /backups -name "my_database_*.sql.gz" -mtime +30 -delete

Backup en PostgreSQL

# Backup físico con pg_basebackup
pg_basebackup -D /backups/full -Ft -z -P

# Backup lógico con pg_dump
pg_dump -Fc -Z 9 my_database > my_database_$(date +%Y%m%d).dump

# Backup continuo con WAL archiving
# En postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'

# Script de backup point-in-time recovery
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -Fc -Z 9 my_database > /backups/my_database_$DATE.dump
# Mantener backups por 30 días
find /backups -name "my_database_*.dump" -mtime +30 -delete

Paso 6: Replicación básica

Replicación MySQL

# Master configuration (my.cnf)
[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 14

# Replica configuration (my.cnf)
[mysqld]
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
read_only = ON

-- En el master
CREATE USER 'replica'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

-- En la réplica
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replica',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
START SLAVE;

Replicación PostgreSQL

# Master configuration (postgresql.conf)
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# pg_hba.conf en master
host replication replica_user replica_ip/32 scram-sha-256

-- En el master
CREATE USER replica_user WITH REPLICATION ENCRYPTED PASSWORD 'replica_password';

-- En la réplica
# Crear réplica con pg_basebackup
pg_basebackup -h master_ip -D /var/lib/postgresql/data -U replica_user -X stream -P

# Configurar recovery.conf (o en postgresql.auto.conf en versiones recientes)
primary_conninfo = 'host=master_ip port=5432 user=replica_user password=replica_password'

Paso 7: Troubleshooting avanzado

MySQL Troubleshooting

# Diagnosticar problemas de rendimiento
mysqladmin -i 1 -c 10 processlist  # Ver procesos cada 1s, 10 veces

# Analizar logs de errores
tail -f /var/log/mysql/error.log

# Ver variables de estado clave
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free'"

# Diagnosticar problemas de conexión
tcpdump -i any -s 0 -l -w - dst port 3306 | strings

# Ver uso de disco por tabla
SELECT table_schema, table_name, 
       data_length, index_length, 
       data_length + index_length as total_size
FROM information_schema.tables 
ORDER BY total_size DESC;

PostgreSQL Troubleshooting

# Diagnosticar problemas de rendimiento
pg_top  # Similar a top para PostgreSQL

# Analizar locks
SELECT * FROM pg_locks;

# Ver queries bloqueadas
SELECT pid, usename, pg_blocking_pids(pid) as blocked_by, query 
FROM pg_stat_activity 
WHERE cardinality(pg_blocking_pids(pid)) > 0;

# Ver estadísticas de vacuum
SELECT schemaname, relname, 
       n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables 
ORDER BY n_dead_tup DESC;

# Diagnosticar problemas de I/O
SELECT * FROM pg_stat_io;

Paso 8: Herramientas de monitoreo avanzado

Para MySQL

• Percona Monitoring and Management (PMM): Monitoreo completo
• MySQL Enterprise Monitor: Solución empresarial
• Prometheus + mysqld_exporter: Monitoreo personalizable
• Percona Toolkit: Herramientas de línea de comandos

Para PostgreSQL

• pgAdmin: Interfaz gráfica completa
• pgBadger: Analizador de logs avanzado
• PostgreSQL Explain Visualizer: Análisis visual de queries
• check_postgres: Script de monitoreo para Nagios

Paso 9: Optimizaciones específicas de carga de trabajo

Para cargas OLTP (Transaccionales)

# MySQL OLTP
innodb_flush_log_at_trx_commit = 1  # Máxima durabilidad
sync_binlog = 1

# PostgreSQL OLTP
synchronous_commit = on
full_page_writes = on

Para cargas OLAP (Analíticas)

# MySQL OLAP
innodb_flush_log_at_trx_commit = 2  # Mejor rendimiento
query_cache_type = 0

# PostgreSQL OLAP
work_mem = 256MB  # Más memoria para operaciones grandes
max_parallel_workers_per_gather = 8

Paso 10: Próximos pasos en configuración avanzada

Temas para profundizar:

• Implementar clustering con MySQL Cluster o PostgreSQL con Patroni
• Configurar alta disponibilidad con replicación multi-master
• Implementar partitioning avanzado para grandes volúmenes de datos
• Configurar connection pooling con pgbouncer (PostgreSQL) o ProxySQL (MySQL)
• Automatizar failover con herramientas como Orchestrator (MySQL) o repmgr (PostgreSQL)

Paso 11: Recursos y herramientas

Para MySQL:

• MySQL Performance Blog: https://www.percona.com/blog/
• MySQL Documentation: https://dev.mysql.com/doc/refman/8.0/en/
• MySQL Forge Wiki: https://forge.mysql.com/wiki/

Para PostgreSQL:

• PostgreSQL Wiki: https://wiki.postgresql.org/wiki/Main_Page
• PostgreSQL Exercises: https://pgexercises.com/
• 2ndQuadrant Blog: https://www.2ndquadrant.com/en/blog/

Conclusión

¡Felicidades! Ahora tienes conocimientos avanzados de configuración para MySQL y PostgreSQL. Practica estos conceptos en entornos de desarrollo antes de aplicarlos en producción.

Para más tutoriales sobre bases de datos avanzadas y configuración, visita nuestra sección de tutoriales.

¡Con estos conocimientos avanzados ya puedes configurar bases de datos de alto rendimiento!

💡 Tip Importante

📝 Mejores Prácticas para Configuración Avanzada

Para configurar efectivamente MySQL y PostgreSQL en producción, considera estos consejos avanzados:

• Prueba en desarrollo primero: Nunca apliques configuraciones directamente en producción sin probar.

• Monitorea el impacto: Usa herramientas de monitoreo para medir el impacto de cada cambio de configuración.

• Documenta tus cambios: Mantén registro de todas las configuraciones personalizadas y sus justificaciones.

• Haz backups antes de cambios: Siempre ten un plan de respaldo antes de modificar configuraciones críticas.

• Considera la carga de trabajo: Las configuraciones óptimas varían según si es OLTP, OLAP o mixta.

• Automatiza la configuración: Usa herramientas de infraestructura como código para mantener consistencia.

• Mantén actualizado: Actualiza regularmente tus RDBMS para obtener mejoras de rendimiento y seguridad.

• Trabaja con expertos: Para entornos críticos, consulta con DBA experimentados.

📚 Documentación: Revisa la documentación oficial de MySQL aquí y PostgreSQL aquí

¡Estos consejos te ayudarán a mantener bases de datos de alto rendimiento y alta disponibilidad!