Configuring Web Systems For Best Performance

For the MySQL version, the optimization of database interaction is one of the most important points because Bitrix Site Manager uses database operations intensively.

A standard format of MySQL is MyISAM. This is a simplest format ever possible; it stores data tables and index in separate files. This format is fast and easy to use for simple sites with light traffic; however, it cannot ensure data integrity and safety due to lack of transactions.

In the view of performance, the main imperfection of MyISAM is table-level locking. Apparently, MyISAM tables become the weakest point of the system preventing the back-end server from processing more client requests. Another drawback is the increase of the page execution time owing to having to wait for locked tables to release at the MySQL level.

We recommend that you convert all your tables to the InnoDB format which ensures data integrity, allows transactions and row-based data locking. MySQL 4.0 and later supports InnoDB.

You can switch to InnoDB tables by performing the following actions.

1. Open the Control Panel form Settings > Tools > SQL Query and execute the command:
   
   SHOW TABLES
2. This will show the list of all existing database tables. Now, execute the following command for each of the tables:
   
   ALTER TABLE <table_name>, type=InnoDB
   
   You can read the FAQ section at www.bitrixsoft.com where we have prepared a sample script to convert the tables to InnoDB.
3. Now that you have converted the tables to InnoDB, add the following line to /bitrix/php_interface/dbconn.php:
   
   define("MYSQL_TABLE_TYPE", "InnoDB");

Changing the tables to the InnoDB format eliminates database performance bottlenecks and allows to use system resources to the full extent.

Attention! Remember to configure your InnoDB. For better database performance, fine-tune the MySQL settings in my.cnf in the InnoDB configuration section “innodb_*”.

Pay special attention to the following parameters:

set-variable = innodb_buffer_pool_size=250M
set-variable = innodb_additional_mem_pool_size=50M
set-variable = innodb_file_io_threads=8
set-variable = innodb_lock_wait_timeout=50
set-variable = innodb_log_buffer_size=8M
set-variable = innodb_flush_log_at_trx_commit=0

Recommendation. To reduce the server response time, use delay transactions and specify set-variable = innodb_flush_log_at_trx_commit=0.

If MyISAM tables passed out of active use at your server, you can free memory in favour of InnoDB.

A good idea is to reserve as much sufficient memory for the data cache as required for storing information commonly used by Bitrix Site Manager. 60% to 80% of free system memory is usually an adequate quota.

Recommendation. Compile a multithreaded MySQL installation for parallel query processing and better system performance.

The following example sets the recommended cache size for a 2 GB server running FreeBSD/Linux:

set-variable = table_cache=512

Bitrix Site Manager contains nearly 250 tables, which requires larger MyISAM table header cache:

set-variable = key_buffer_size=16M 
set-variable = sort_buffer=8M 
set-variable = read_buffer_size=16M

The parameters are used only by MyISAM. If your database does not use MyISAM tables, set these parameters to minimum values.

Set the query result cache size. 32 MB is usually enough (refer to the Qcache_lowmem_prunes status). The default maximum query result size is 1 MB, but you can change it.

set-variable = query_cache_size=64M 
set-variable = query_cache_type=1

The main buffer (buffer_pool_size): larger is better:

set-variable = innodb_buffer_pool_size=780M

The following directive sets the size of the helper buffer for internal data structures. Larger buffer size do not improve performance.

set-variable = innodb_additional_mem_pool_size=20M

The following lines set the log file size. Larger log file size causes less writes to the main data file. The log file size is usually proportionate to the innodb_buffer_pool_size value.

set-variable = innodb_log_file_size=100M 
set-variable = innodb_log_buffer_size=16M

Set the delay transaction registration. This will flush transactions once per second:

set-variable = innodb_flush_log_at_trx_commit=0

Increase the size of temporary tables to 32 MB:

set-variable = tmp_table_size=32m

Important notes. Migration to InnoDB can cause significant performance degradation due to massive write and update operations. These, in their turn, are the result of transaction-oriented architecture of InnoDB. You have to make the final decision on switching to InnoDB on your own.