Simpla тормозит

Задача:
Необходима оптимизация интернет магазина на движке SIMPLA, имеет большое количество товара и узлов.


VPS сервер
CPU: Intel(R) Xeon(R) L5640 @ 2.27GHz, 3 ядра
RAM: 2GB
HDD:

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       59G   32G   25G  57% /
none            935M     0  935M   0% /dev/shm

Soft:

CentOS release 6.8 (Final)
Apache/2.2.31 (Unix)
MYSQL Ver 14.14 Distrib 5.5.45
PHP 5.3.29
Webuzo
Simpla 2.1.5

База данных

Всего таблиц: 120
Обьем данных: 791 348 612
Обьем индексов: 1 052 062 720
Тип таблиц: MyIASM, InnoDB
+-------------------------------------+------------+
| table_name                          | table_rows |
+-------------------------------------+------------+
| category_product                    |   16371174 |
| s_related_products                  |    6840958 |
| s_schemes_options                   |     968797 |
| s_redirects                         |     565921 |
| categories                          |     534794 |
| s_images                            |     377772 |
| s_options_paths                     |     338352 |
| s_variants                          |     230369 |
| s_products                          |     230369 |
| s_options                           |     230368 |
| s_schemes                           |     194382 |
| s_schemes_categories                |     194361 |
| product_pictures                    |     189895 |
| product_options_values              |      97492 |
| _new_schemes_ids                    |      68955 |
| product_options_set                 |      35465 |
| s_options_relationship              |      34228 |
| discussions                         |      28946 |
| aff_sms_status                      |      19896 |
| aff_sms_text                        |       7880 |
| customer_log                        |       7181 |
| order_status_changelog              |       7107 |
| shopping_cart_items                 |       6157 |
| s_related_products_custom           |       3556 |
| related_items                       |       3385 |
| customer_reg_fields_values_quickreg |       3068 |
| category__product_options           |       2892 |
| session                             |       2601 |
| ordered_carts                       |       2219 |
| customer_reg_fields_values          |       1854 |
+-------------------------------------+------------+

В момент подключения на сервере наблюдается:

Top показывает что все ресурсы отобрал MySQL. Смотрим, что он делает, видим запросы колосальным временем выполнения:

SELECT DISTINCT b.id, b.name, b.url, b.meta_title, b.meta_keywords, b.meta_description, b.description, b.image
								 		FROM s_brands b 
			LEFT JOIN s_schemes s ON s.brand_id=b.id AND s.visible=1
			LEFT JOIN s_schemes_categories sc ON s.id = sc.scheme_id 
			LEFT JOIN s_products p ON p.brand_id=b.id AND p.visible=1 
			LEFT JOIN s_products_categories pc ON p.id = pc.product_id 
			WHERE  pc.category_id in('210','214','215','216','211','212','213','204','207','208','209','217','205','206','169') OR  sc.category_id in('210','214','215','216','211','212','213','204','207','208','209','217','205','206','169') ORDER BY b.name

Снимаем данные процессы:

show full processlist;
kill query 4578;
-- и далее

Загрузка упала.
Находим конфиг MYSQL ( /usr/local/apps/mysql/etc/my.cnf) и добавляем параметры:

[mysqld]
...
long_query_time = 2.000000
log_queries_not_using_indexes = ON
log-slow-queries=/var/log/mysql_slow_queries.log

Имеем статистику в момент импорта записей в БД:

Дальнейшее наблюдение показывает, что именно эти запросы зависают в статусе “copying to tmp table”.

Получим за 8 часов статистику SLOWQUERY находим в ней СТРАШНЫЕ значения:

# Query_time: 4.301706  Lock_time: 0.000200 Rows_sent: 0  Rows_examined: 230369
# Query_time: 4.602689  Lock_time: 0.000160 Rows_sent: 0  Rows_examined: 230369
# Query_time: 4.572558  Lock_time: 0.000154 Rows_sent: 0  Rows_examined: 230369
...
# Query_time: 16.692677  Lock_time: 0.000184 Rows_sent: 20  Rows_examined: 18079
# Query_time: 24.860018  Lock_time: 0.000209 Rows_sent: 31  Rows_examined: 26692
# Query_time: 24.652913  Lock_time: 0.000182 Rows_sent: 27  Rows_examined: 26676
# Query_time: 10.359431  Lock_time: 0.000195 Rows_sent: 32  Rows_examined: 14010
# Query_time: 13.080076  Lock_time: 0.000201 Rows_sent: 45  Rows_examined: 14062
# Query_time: 24.737066  Lock_time: 0.000195 Rows_sent: 32  Rows_examined: 26696
....
# Query_time: 9130.263210  Lock_time: 0.000171 Rows_sent: 0  Rows_examined: 0
# Query_time: 9300.956156  Lock_time: 0.000155 Rows_sent: 0  Rows_examined: 0
# Query_time: 9840.150401  Lock_time: 0.000155 Rows_sent: 0  Rows_examined: 0

Решение проблемы МЕРТВОГО запроса:

Нашли, что данный запрос формируется в функции Brands->get_brands() при передачи в нее фильтра по Категориям. Запрос ищет Производителей, по ВЫБРАННОЙ КАТЕГОРИИ(ЯМ), при этом он перебирает ВСЕ Товары и Узлы – то есть идет с самого нижнего уровня, а “убого” написанный query делал его невыполнимым!
Спасибо SIMPL-е, что она simple!
Добавили промежуточную таблицу, добавили задание в cron для ее наполнения.
Переписали запрос.
“Зависания” прекратились.

Решение для медленных запросов

1. Добавляем индексы, там где их нет.

2. Включаем КЕШ МySQL
Было:

mysql> SHOW GLOBAL VARIABLES LIKE "%cache%";
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| binlog_stmt_cache_size       | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| max_binlog_stmt_cache_size   | 18446744073709547520 |
| metadata_locks_cache_size    | 1024                 |
| query_cache_limit            | 8388608              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 67108864             |
| query_cache_type             | OFF                  |
| query_cache_wlock_invalidate | OFF                  |
| stored_program_cache         | 256                  |
| table_definition_cache       | 400                  |
| table_open_cache             | 1024                 |
| thread_cache_size            | 64                   |
+------------------------------+----------------------+

Включили:

query_cache_type = ON
query_cache_limit = 2M
query_cache_size = 256M

Стало:

mysql> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 267251352 |
| Qcache_hits             | 819       |
| Qcache_inserts          | 590       |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 155       |
| Qcache_queries_in_cache | 590       |
| Qcache_total_blocks     | 1209      |
+-------------------------+-----------+

Проблема медленной работы дисковой подсистемы

Замер скорости дисковой подсистемы (3 подряд)
Провайдер в тарифном плане говорит что это SSD.

$ hdparm -Tt /dev/xvda1
/dev/xvda1:
 Timing cached reads:   9460 MB in  1.99 seconds = 4752.16 MB/sec
 Timing buffered disk reads: 208 MB in  3.03 seconds =  68.71 MB/sec

$ hdparm -Tt /dev/xvda1
/dev/xvda1:
 Timing cached reads:   10070 MB in  1.99 seconds = 5060.94 MB/sec
 Timing buffered disk reads:  88 MB in  3.10 seconds =  28.36 MB/sec

$ hdparm -Tt /dev/xvda1
/dev/xvda1:
 Timing cached reads:   10912 MB in  1.99 seconds = 5484.02 MB/sec
 Timing buffered disk reads: 206 MB in  3.08 seconds =  66.88 MB/sec

Замер скорости на ДРУГОМ SSD хостинге:

$ hdparm -Tt /dev/dm-0
/dev/dm-0:
 Timing cached reads:   12940 MB in  2.00 seconds = 6473.92 MB/sec
 Timing buffered disk reads: 1506 MB in  3.00 seconds = 501.75 MB/sec

$ hdparm -Tt /dev/dm-0
/dev/dm-0:
 Timing cached reads:   10996 MB in  2.00 seconds = 5501.11 MB/sec
 Timing buffered disk reads: 1894 MB in  3.01 seconds = 628.98 MB/sec

$ hdparm -Tt /dev/dm-0
/dev/dm-0:
 Timing cached reads:   10172 MB in  2.00 seconds = 5088.74 MB/sec
 Timing buffered disk reads: 2026 MB in  3.00 seconds = 675.25 MB/sec

Пост-фактум: Текущий провайдер предложил перейти на другой сервер. 🙂

Уменьшили границу медленного запроса до 0.8 секунд:

mysql> show global variables like '%long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.800000 |
+-----------------+----------+

День закончился, оставили на ночь процесс импорта.

За ночь после всех изменений

Процесс импорт за ночь набрал около 80 медленных запросов = 10 шт/час.
Показатель до начал работ (примерно) 700 за 3 часа = 233шт/час.