Optimization of the 1C-Bitrix database

Database Optimization in 1C-Bitrix

After years of a Bitrix site in operation, the database accumulates problems: bloated cache tables, outdated sessions, fragmented indices, inefficient queries without indexes. Database optimization is not a one-time operation but a regular process. Let's cover specific tables and methods.

Diagnosis: What Takes Up Space

Start with analyzing table sizes. In MySQL/MariaDB:

SELECT
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb,
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'bitrix_db'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

For Bitrix sites typically: b_event_log, b_stat_session, b_stat_adv_back, b_search_content, b_cache_tag, b_file — account for 80% of database volume.

Cache Tables

b_cache_tag — cache tag table. When Bitrix flushes cache, it doesn't immediately delete outdated records, but marks them. The table grows constantly.

Clear outdated cache tags:

DELETE FROM b_cache_tag WHERE SITE_ID IS NULL AND CACHE_SALT IS NULL;

Or via admin panel Settings → Module Management → Main Module → Clear Cache.

Full table rebuild after cleanup:

OPTIMIZE TABLE b_cache_tag;

OPTIMIZE TABLE rebuilds physical storage, frees fragmented space. Run during minimum load — operation locks the table.

Session Table

b_user_session and b_sale_user_session store user sessions. Without configured cleanup, they grow infinitely.

Check if automatic cleanup is enabled: Settings → Product Settings → Main Module → "Session" tab. "Session lifetime" should match session.gc_maxlifetime in php.ini.

Force cleanup of sessions older than 24 hours:

DELETE FROM b_user_session
WHERE DATE_CREATE < DATE_SUB(NOW(), INTERVAL 24 HOUR);

DELETE FROM b_sale_user_session
WHERE DATE_INSERT < DATE_SUB(NOW(), INTERVAL 7 DAY);

Event Log

b_event_log — user action audit table. On active sites adds millions of records per month. Most projects don't need events older than 90 days.

DELETE FROM b_event_log WHERE DATE_CREATE < DATE_SUB(NOW(), INTERVAL 90 DAY);
OPTIMIZE TABLE b_event_log;

Configure automatic cleanup: Settings → Event Log → Clear Events → Keep events for last N days.

Statistics Tables

The statistic module fills b_stat_session, b_stat_page_adv, b_stat_adv_back. If statistics aren't used (many switch to Yandex.Metrica), disable the module entirely: Settings → Modules → Web Analytics → Disable. This stops writing and reduces DB load.

If disabling isn't possible — configure retention period: Settings → Web Analytics → Settings → Statistics Retention Depth.

Index Optimization and Slow Queries

Enable slow query log for identifying heavy queries. In my.cnf:

slow_query_log       = 1
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time      = 1
log_queries_not_using_indexes = 1

For Bitrix typical bottlenecks:

InnoDB Buffer Pool Size Configuration

For MySQL/MariaDB the most important performance parameter is innodb_buffer_pool_size. For server dedicated to database, set 60–70% of available RAM:

innodb_buffer_pool_size = 4G   # for server with 8 GB RAM
innodb_log_file_size    = 512M
innodb_flush_log_at_trx_commit = 2   # less guarantee, but significantly faster

innodb_flush_log_at_trx_commit = 2 allows loss of up to 1 second of transactions on power loss, but gives 2–5x write performance increase. Acceptable for most e-commerce.

Database Maintenance Schedule

Work Timelines