MySQL Partitioning for 1C-Bitrix: Speed Up Queries 10-50x

MySQL table partitioning for 1C-Bitrix is a technique that solves the problem of the b_stat_hit table growing to 50 million rows, where DELETE of old records locks the table for minutes and SELECT by date range goes through a full scan. This is typical for projects without partitioning. Our engineers with 10+ years of experience, Bitrix certification, and over 50 successful projects guarantee correct implementation without downtime. Partitioning splits a MySQL table into physical segments. Queries only hit the required partition, and deleting old data is an instant DROP PARTITION instead of a heavy DELETE. In one project with 80 million statistics hits, data deletion time for a month dropped from 12 minutes to 0.1 seconds — a 7200x improvement. In another project with a catalog of 500,000 products and 100,000 daily hosts, b_stat_hit grew by 3 million rows per day — after partitioning, lock issues disappeared.

Partitioning is not a silver bullet, but for large tables with a temporal dimension it gives a 10-50x performance boost on selection and cleanup operations. We use RANGE partitioning by date — the most transparent and maintainable option for Bitrix. Below, we break down which tables to partition first and how to implement it without downtime.

Which Bitrix Tables Should Be Partitioned?

Not all tables benefit from partitioning. Candidates are large tables with a temporal dimension:

Statistics tables are the primary candidate: data older than 3 months is rarely needed, and DELETE FROM b_stat_hit WHERE DATE_HIT < NOW() - INTERVAL 3 MONTH on 30 million rows takes 10 minutes of locking. After partitioning, deletion takes milliseconds.

Why Is This Important for Bitrix Performance?

Without partitioning, MySQL scans the entire table even if only last week's data is needed. With partitions, the optimizer performs partition pruning — it skips partitions that don't match the WHERE clause. This reduces I/O and CPU load by tens of times. For example, a SELECT with a date filter on a partitioned table runs 30-50x faster. Here's a side-by-side comparison:

Such acceleration directly impacts report speed and log cleanup. Contact us for a database analysis and effect estimate.

How to Automate Partition Rotation?

After setting up partitions, you need to regularly add and remove them. We use a cron script in Bash that runs monthly. Example logic:

#!/bin/bash
MONTH=$(date +%Y-%m)
PART_NAME="p_$MONTH"
SQL="ALTER TABLE b_stat_hit REORGANIZE PARTITION p_future INTO (PARTITION $PART_NAME VALUES LESS THAN (TO_DAYS('$(date +%Y-%m-%d -d '+1 month'))'), PARTITION p_future VALUES LESS THAN MAXVALUE);"
mysql -u user -p db -e "$SQL"
# Delete old partition, e.g., older than 6 months
OLD_PART=$(date +%Y-%m -d '-6 months')
OLD_SQL="ALTER TABLE b_stat_hit DROP PARTITION p_$OLD_PART;"
mysql -u user -p db -e "$OLD_SQL"

The script must have table-alter privileges. We configure it on the server and test automatic rotation.

How We Implement Partitioning: Step by Step

1. Analyze candidate tables: size, query patterns, growth speed.
2. Modify primary keys if needed (add date column).
3. Create RANGE partitions by date. Example for b_stat_hit:

ALTER TABLE b_stat_hit
PARTITION BY RANGE (TO_DAYS(DATE_HIT)) (
    PARTITION p_jan VALUES LESS THAN (TO_DAYS('2099-02-01')),
    PARTITION p_feb VALUES LESS THAN (TO_DAYS('2099-03-01')),
    PARTITION p_mar VALUES LESS THAN (TO_DAYS('2099-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Documentation MySQL Partitioning requires that the partitioning column be part of every unique index and primary key. For b_stat_hit, the primary key is ID. We change it to a composite key:

ALTER TABLE b_stat_hit DROP PRIMARY KEY, ADD PRIMARY KEY (ID, DATE_HIT);
ALTER TABLE b_stat_hit PARTITION BY RANGE (TO_DAYS(DATE_HIT)) (...);

We verify that Bitrix does not use ID for JOINs — if it does, the composite PK is safe.

4. Set up a cron script for partition rotation:

   • Create new partition: REORGANIZE PARTITION p_future INTO (PARTITION p_apr VALUES LESS THAN (TO_DAYS('2099-05-01')), PARTITION p_future VALUES LESS THAN MAXVALUE).
   • Drop old: ALTER TABLE b_stat_hit DROP PARTITION p_old.

5. Test performance: measure SELECT and DELETE times before and after. Typically improvement is 10-50x, and data deletion is thousands of times faster.

What's Included in Our Work

• Database analysis and candidate table identification.
• Primary key modification without data loss.
• Creation of RANGE partitions by date.
• Development and setup of autoroation cron script.
• Compatibility check with Bitrix kernel updates.
• Documentation of all changes.
• Post-implementation support (1 month).

Request a consultation with a partitioning engineer. We will analyze your database and propose the optimal solution.

Limitations in the Bitrix Context

• Kernel updates. The main module may execute ALTER TABLE upon update — if the structure changes and partitions aren't accounted for, the update may break. We maintain a list of partitioned tables and check before each update.
• ORM D7. Bitrix\Main\ORM is unaware of partitions — queries work transparently, but the MySQL optimizer only performs partition pruning if the WHERE clause includes the partitioning column.
• InnoDB limits. Maximum 8192 partitions per table (MySQL 8). For monthly partitioning, that's over 680 years.

MySQL table partitioning for 1C-Bitrix is a proven way to boost performance, especially on high-load projects. Get a free consultation from a partitioning engineer. Request a performance analysis today.

What Professional 1C-Bitrix Installation Includes

We start by checking innodb_buffer_pool_size. The default MySQL value (128 MB) is a death sentence for an online store with a catalog of 10,000+ items. We set 70–80% of available RAM on a dedicated server, 50% on VPS. This single setting speeds up the site by 2–3 times compared to the default. We'll assess your project in one day — get a consultation. Contact us to order turnkey installation with performance guarantee.

How to Choose Hosting and Edition for 1C-Bitrix Installation?

BitrixVM is a virtual machine with a pre-installed stack: nginx + Apache, PHP-FPM, MySQL/MariaDB, Sphinx, Push server. For VPS — the best start. Everything is already configured for Bitrix, including OPcache, log rotation, and firewall. Management via web panel on port 8890. Bitrix documentation recommends starting with BitrixVM for predictable performance.

VPS/VDS is the sweet spot. Minimum configuration for a medium online store: 2 vCPU, 4 GB RAM, SSD. Optimal: 4 vCPU, 8 GB RAM. OS: Ubuntu 22.04 or Debian 12. If not BitrixVM, we configure the stack manually for the task. Virtual hosting — only for business cards and landing pages. Requirements: PHP 8.0+, MySQL 5.7+ / MariaDB 10.0+, 512 MB RAM, .htaccess. 1C-Bitrix hosting partners guarantee compatibility. Dedicated server — for highload. Typical architecture: web server separate, database separate, Redis/Memcached separate. For Enterprise edition — web cluster with load balancer. Cloud (Yandex Cloud, VK Cloud, Selectel) — when load spikes: sales, seasonal peaks. Autoscaling via Managed Kubernetes or simple VM vertical scaling.

Choosing the edition is equally important. A common mistake: choosing "Small Business" for a store that grows to B2B with wholesale prices and three warehouses in six months. Upgrading to "Business" — pay the difference, data is not lost, but it's better to plan ahead. Our specialists select the edition for current tasks and with room for growth. For example, the "Business" license (about 35,000 RUB) pays off through multi-warehouse and 1C exchange, while the wrong choice can lead to a loss of up to 30,000 RUB monthly on excess resources.

What Server Settings Are Critical for 1C-Bitrix?

Web Server and PHP

nginx as reverse proxy + Apache (mod_php) or nginx + PHP-FPM directly. The second option saves memory — Apache is not needed. But some Bitrix modules use .htaccess, so for compatibility we sometimes keep Apache. nginx configuration: fastcgi_read_timeout 300 — for long operations (1C import), client_max_body_size 1024m — large file uploads. Block access to .settings.php, .settings_extra.php, bitrix/.settings.php — they contain database passwords. Rewrite rules from urlrewrite.php — Bitrix generates them, but with nginx + PHP-FPM they need to be duplicated. PHP 8.0–8.2 with extensions: mbstring, curl, gd, xml, json, opcache, redis/memcached. Key php.ini settings: opcache.memory_consumption=256, opcache.max_accelerated_files=20000, max_execution_time=300, memory_limit=512M, upload_max_filesize=100M, post_max_size=128M.

Database and Caching

MySQL/MariaDB. Key my.cnf parameters: innodb_buffer_pool_size — 70–80% RAM, innodb_log_file_size=256M, tmp_table_size=256M, max_heap_table_size=256M, thread_pool_size — number of CPU cores. Encoding utf8mb4 mandatory, otherwise emoji and special characters break. Redis is preferable to Memcached for Bitrix — supports persistent connections and is more reliable. In production, Redis handles concurrent writes three times faster than Memcached under typical load. Configure in .settings_extra.php:

'cache' => ['value' => ['type' => ['class_name' => '\\Bitrix\\Main\\Data\\CacheEngineRedis']]]
'session' => ['value' => ['mode' => 'default', 'handlers' => ['general' => ['type' => 'redis']]]]

sudo apt install redis-server
sudo systemctl enable redis

SSL, Email, and Cron

SSL — Let's Encrypt via certbot in 90% of cases. Redirect HTTP → HTTPS (301), HSTS, TLS 1.2/1.3, OCSP Stapling. In Bitrix, switch to HTTPS in the main module settings. Email: abandon mail() — connect SMTP (Yandex.Mail for domain, Mail.ru for Business). Be sure to configure SPF, DKIM, DMARC. Without SPF, emails go to spam. Test deliverability via mail-tester.com — score 9+/10. Cron: Bitrix agents switch to system cron — * * * * * /usr/bin/php /var/www/bitrix/modules/main/tools/cron_events.php. Schedule 1C exchange (15–60 min), search reindex, backups (mysqldump + rsync, rotation 7+4), temporary file cleanup.

Security and Administration

File system: owner www-data, directories 755, files 644, upload 775. nginx blocks access to configuration files. Enable Bitrix Proactive Protection — WAF, activity control (block after 5 failed attempts), kernel integrity check. For admin panel: two-factor authentication via Google Authenticator or OTP, restrict access by IP via nginx for paranoid.

How Long Does 1C-Bitrix Installation and Configuration Take?

Post-Installation Checklist

1. Performance Monitor (/bitrix/admin/perfmon_panel.php) — aim for 30+ points. Below 20 means serious configuration issues.
2. System Check — automatic check of all parameters. Red items must be fixed, yellow — case by case.
3. Security Scanner — check for typical vulnerabilities.
4. PageSpeed Insights — TTFB < 200ms on VPS, LCP < 2.5s.
5. Test 1C exchange — if integration is planned, verify CommerceML exchange before launch.

Additionally, check software versions, caching settings, cron operation, SSL certificate, SPF/DKIM/DMARC, access rights, delete default users and pages. For projects with 54-FZ, ensure fiscalization is configured via OFD provider.

Deliverables

• Fully configured server for 1C-Bitrix with MySQL, PHP, nginx optimization.
• Installed and activated license of the required edition.
• SSL certificate, email settings, cron and backups.
• Documentation: all configuration parameters, access credentials, cron tasks.
• Content manager training: how to log into admin panel, add products, upload images.
• Post-installation support for 30 days — consultations on settings.

Why Trust Professionals with Installation?

Incorrect installation means lost time and money. We've seen projects where a store on "Start" couldn't handle 50 visitors because innodb_buffer_pool_size wasn't configured. After migrating to VPS with correct configuration, the site "flew". Incorrect configuration can cost 30,000 RUB monthly due to excessive resource consumption. You get a ready-made architecture that scales. Order turnkey 1C-Bitrix installation — get a reliable platform for business growth. Contact us for a free consultation: we'll calculate the cost and time for your project. Over 7 years of experience, 120+ Bitrix projects implemented, including highload stores with million-item catalogs. Get in touch — we'll help configure Bitrix for your project.