When Bitrix (a popular CMS) experiences MySQL freezing issues, the website may become unresponsive or slow. These problems typically stem from database misconfiguration, high traffic, or resource limits. Here is how to troubleshoot and resolve them.

Common Causes of MySQL Freezing in Bitrix

High Database Load

Large tables and complex queries can overwhelm the database server.

Insufficient Resources

Low CPU, RAM, or disk I/O on the hosting server.

Improper MySQL Configuration

Default MySQL settings may not be optimized for Bitrix.

Locking or Deadlocks

Frequent updates or concurrent database operations causing table locks.

Slow Queries

Unoptimized queries or missing indexes lead to delays.

Disk or Filesystem Issues

A full disk or slow storage (e.g., HDD instead of SSD) can cause lags.

Bitrix Cache System Misconfiguration

Inefficient cache usage increases database queries.

Steps to Diagnose the Problem

Enable MySQL Slow Query Log

  1. Edit MySQL configuration:
    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  2. Add or modify:
    slow_query_log = 1
    slow_query_log_file = /var/log/mysql-slow.log
    long_query_time = 2
  3. Restart MySQL:
    sudo systemctl restart mysql
  4. Analyze the slow query log:
    sudo cat /var/log/mysql-slow.log

Monitor Database Performance

  • Use the SHOW PROCESSLIST command to identify hanging queries:
    SHOW PROCESSLIST;
  • Use htop or top on the server to check CPU/RAM usage:
    htop

Check Table Sizes

  • Identify large tables:
    • SELECT table_name AS "Table",
           ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    FROM information_schema.TABLES
    WHERE table_schema = "your_database_name";

Use Bitrix Performance Monitor

  • Bitrix includes a performance monitor:
    • Navigate to Bitrix Admin Panel > Settings > Performance Monitor.
    • Run tests to detect bottlenecks in queries and scripts.

Solutions to Fix MySQL Freezing in Bitrix

Optimize MySQL Configuration

Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Adjust the following settings for better performance:

Setting Recommended Value
innodb_buffer_pool_size 70-80% of total server memory.
max_connections Increase to handle more users (e.g., 200).
query_cache_size 64M (or disable if queries are optimized).
table_open_cache 4096
innodb_log_file_size 256M

Restart MySQL:

sudo systemctl restart mysql

Optimize Database Tables

  1. Repair and optimize tables:
    OPTIMIZE TABLE table_name;
  2. Check and repair corrupted tables:
    REPAIR TABLE table_name;

Index Optimization

Add indexes to frequently queried columns:

ALTER TABLE table_name ADD INDEX (column_name);

Reduce Cache-Related Queries

  • Ensure Bitrix caching is enabled:
    • Bitrix Admin Panel > Settings > Cache Settings.
  • Use memcached or Redis for cache storage instead of MySQL:
    • Install Redis:
      • sudo apt install redis-server php-redis
    • Configure Bitrix to use Redis in the bitrix/php_interface/dbconn.php file:
      • $DBType = "mysql";
      $CACHE_TYPE = "memcache";
      $CACHE_PORT = "6379";

Upgrade Hosting Infrastructure

  1. Switch to SSD Storage: Faster disk I/O reduces delays caused by large datasets.
  2. Increase Server Resources: Add more RAM, CPU, or storage as needed.
  3. Use Dedicated or Cloud Hosting: Shared hosting may not handle high database load efficiently.

Split Large Tables

  • If a table is excessively large (e.g., logs, sessions), archive older data:
    • DELETE FROM large_table WHERE created_at < '2023-01-01';

Preventive Measures

  1. Regular Database Maintenance:
    • Schedule table optimization and cleanup.
  2. Monitor Performance Regularly:
    • Use tools like mysqltuner:
      • sudo apt install mysqltuner
      mysqltuner
  3. Update Bitrix and MySQL:
    • Ensure both are running the latest stable versions.
  • Diagnose the cause: Check logs, slow queries, and resource usage.
  • Optimize MySQL settings: Adjust configuration to fit your server resources.
  • Enhance caching: Use Redis or memcached for better performance.
  • Consider hardware upgrades: If the server is underpowered, upgrade your hosting plan.