There are six online stores that have low traffic but are hosted well. However, the websites often experience significant slowdowns because mysql load remains consistently high. It appears that no measures have been taken to address this issue, which has persisted for a long time. Unfortunately, I lack the necessary expertise to identify the exact problem, as it could potentially be related to server settings rather than Bitrix itself.
Please advise me on where to investigate further or suggest a specialist who might be able to assist. Thank you.
Firstly, can you please provide the mysql configuration settings? Is there a clear pattern in the frequency of these slowdowns? Could the slowdowns be related to backups? Additionally, what is the size of the database?
To troubleshoot the issue during the site slowdowns, you can try running the following command under the admin:
SHOW PROCESSLIST
This will allow you to identify any stuck or hanging requests.
To address this issue, I recommend enabling profiling to gather data. On each page, carefully observe and navigate through the site, paying attention to any requests that take a significant amount of time to execute. It is worth noting that the regular slow log may not always indicate if there is a request looping on a particular page, being executed multiple times.
By using profiling, you can gain more insight into the specific requests that may be causing performance issues and determine if there are any loops or repetitive requests that need to be addressed.
There is a variation in the effectiveness of different hosting services, and not all of them are equally useful. If you are using VDS/VPS, it's worth noting that sporadic load issues often indicate disk performance drawdown.
To assess the situation, it's important to check if the results of executing the "top" or "wa" command are within normal ranges. These commands can provide insights into CPU wait times and help identify potential bottlenecks.
Regarding Bitrix, it seems that the online store has decided to avoid using infoblocks due to the complexity of requests associated with them. This decision might have been made to improve overall performance and streamline the functioning of the store.
Thoroughly assess the server's hardware resources, including the CPU, RAM, and disk I/O. High traffic can place a heavy load on these components, leading to performance issues. Consider upgrading these resources if they are reaching their capacity limits to better support the workload generated by the online stores.
Next, review the MySQL configuration settings. Evaluate parameters such as key_buffer_size, query_cache_size, innodb_buffer_pool_size, and max_connections. These settings directly impact the performance of MySQL, and adjusting them based on the specific workload and system specifications can significantly improve database performance and lessen the strain on the server.
Analyze the database schema and queries used by the online stores. Inefficient or poorly optimized queries, lack of proper indexing, or a suboptimal database design can contribute to high MySQL load. Utilize tools such as the slow query log, MySQL's Performance Schema, and the EXPLAIN command to identify and address slow-performing queries and potential bottlenecks in the database structure.
Consider implementing caching mechanisms to reduce the reliance on MySQL for fetching data. Utilizing a caching system such as Redis or Memcached can help store frequently accessed data in memory, reducing the need for repeated database queries and effectively lowering the MySQL load.
In addition to these technical considerations, it may be beneficial to conduct a thorough security audit to rule out any potential malicious activity or unauthorized database access that could be contributing to the high load.
Should these steps not yield the desired improvements, seeking the expertise of a specialized MySQL performance tuning consultant would be prudent. These professionals can conduct a comprehensive audit of the database and server environment, providing insights into fine-tuning MySQL performance and database optimizations tailored to the specific requirements of the online stores.
The intricate nature of MySQL performance optimization requires a meticulous approach, and engaging with a specialized consultant can offer a deep-dive analysis and targeted recommendations to resolve the persistent high load and sluggishness experienced by the online stores.