Задача:
Необходима оптимизация интернет магазина на движке 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шт/час.
