Table of Content
Встановлення Sphinx
$ yum install sphinx |
Вмикаємо автозавантаження після перезавантаження серверу
# systemctl enable searchd.service |
Налаштовуємо загальну конфігурацію
# vi /etc/sphinx/sphinx.conf |
#!/usr/bin/php# UNIX config include<?phprequire_once('/web/sites/storage-geely-kz/data/production/etc/sphinx/sphinx.conf');?>indexer{ mem_limit= 256M max_iops = 60 max_iosize = 0 write_buffer = 32M max_file_field_buffer = 32M}searchd{ listen = 9310:mysql41 listen = 9311:mysql41 listen = 127.0.0.1:9312 listen = 127.0.0.1:9313 log = /var/log/sphinx/searchd.log query_log = /var/log/sphinx/query.log read_timeout = 30 max_children = 300 pid_file = /var/run/sphinx/searchd.pid max_matches = 10000 seamless_rotate = 1 preopen_indexes = 1 unlink_old = 1 workers = threads # for RT to work binlog_path = /var/lib/sphinx # attr_flush_period = 900 # instance-wide ondisk_dict defaults (per-index value take precedence) # optional, default is 0 (precache all dictionaries in RAM) # # ondisk_dict_default = 1 # MVA updates pool size # shared between all instances of searchd, disables attr flushes! # optional, default size is 1M mva_updates_pool = 1M # max allowed network packet size # limits both query packets from clients, and responses from agents # optional, default size is 8M max_packet_size = 8M # crash log path # searchd will (try to) log crashed query to 'crash_log_path.PID' file # optional, default is empty (do not create crash logs) # # crash_log_path = /var/log/crash # max allowed per-query filter count # optional, default is 256 max_filters = 256 # max allowed per-filter values count # optional, default is 4096 max_filter_values = 4096 # socket listen queue length # optional, default is 5 # listen_backlog = 50 # per-keyword read buffer size # optional, default is 256K # # read_buffer = 256K # unhinted read size (currently used when reading hits) # optional, default is 32K # # read_unhinted = 32K # max allowed per-batch query count (aka multi-query count) # optional, default is 32 # max_batch_queries = 32 # max common subtree document cache size, per-query # optional, default is 0 (disable subtree optimization) # subtree_docs_cache = 4M # max common subtree hit cache size, per-query # optional, default is 0 (disable subtree optimization) # subtree_hits_cache = 8M #workers = threads # for RT to work # max threads to create for searching local parts of a distributed index # optional, default is 0, which means disable multi-threaded searching # should work with all MPMs (ie. does NOT require workers=threads) # # dist_threads = 4 # binlog_path = /var/data # binlog.001 etc will be created there # binlog flush/sync mode # 0 means flush and sync every second # 1 means flush and sync every transaction # 2 means flush every transaction, sync every second # optional, default is 2 # binlog_flush = 2 binlog_max_log_size = 512M} |
Особливу увагу потрібно звернути на перші шість стрічок файлу, – це можливість динамічно підключати конфігурацію для різних проектів.
Створюємо конфігураційний файл для проекту (RT-indexes)
# vi /web/sites/storage-geely-kz/data/production/etc/sphinx/sphinx.conf |
#!/usr/bin/php## Minimal Sphinx cAonfiguration sample (clean, simple, functional)#source storage_geely_kz_src_mysql{ # Параметры подключения к БД type = mysql sql_host = localhost sql_user = cloud sql_pass = 3c3a67d11 sql_db = cloud_geely_kz sql_port = 3306 # optional, default is 3306 sql_query_pre = SET NAMES utf8 sql_query_pre = SET CHARACTER SET utf8 sql_range_step = 500 # Время простоя (sleep) перед посылкой запросов серверу (предназначен для разгрузки сервера БД) # Если установите "= 1000", то засыпание будет длится 1 секунду sql_ranged_throttle = 0}source store : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `store` # Запрос выборки данных для индексации sql_query = \ SELECT s.id, \ s.id AS storeId, \ s.carModelId, \ cm.brandId, \ s.carEquipmentId, \ s.carAssemblyId, \ s.vin, \ s.engineNumber, \ s.standNumber, \ UNIX_TIMESTAMP(s.dateEntry) AS dateEntry, \ UNIX_TIMESTAMP(s.datePayment) AS datePayment, \ UNIX_TIMESTAMP(s.dateShipment) AS dateShipment, \ UNIX_TIMESTAMP(s.dateDeliveryPK) AS dateDeliveryPK, \ UNIX_TIMESTAMP(s.dateAssembly) AS dateAssembly, \ UNIX_TIMESTAMP(s.dateDeliveryDranch) AS dateDeliveryDranch, \ s.yearRelease, \ s.priceList, \ s.cityId, \ s.carStatusId, \ ce.name AS carEquipmentName, \ IF(cd.datePaidPresale IS NULL, UNIX_TIMESTAMP('0000-00-00'), UNIX_TIMESTAMP(cd.datePaidPresale)) AS datePaidPresale, \ IF(cd.defect IS NULL, '', cd.defect) AS defect, \ IF(cd.optionalEquipment IS NULL, '', cd.optionalEquipment) AS optionalEquipment, \ IF(cd.datePaidAnticor IS NULL, UNIX_TIMESTAMP('0000-00-00'), UNIX_TIMESTAMP(cd.datePaidAnticor)) AS datePaidAnticor, \ IF(cd.notation IS NULL, '', cd.notation) AS notation, \ IF(sg.dateRealization IS NULL, '', UNIX_TIMESTAMP(sg.dateRealization)) AS dateRealization, \ IF(bs.buyer IS NULL, '', bs.buyer) AS buyer, \ IF(bs.address IS NULL, '', bs.address) AS address, \ IF(bs.phone IS NULL, '', bs.phone) AS phone, \ IF(sg.discount IS NULL, '', sg.discount) AS discount, \ IF(sg.sellingPrice IS NULL, '', sg.sellingPrice) AS sellingPrice, \ IF(sg.realizationCity IS NULL, '', sg.realizationCity) AS realizationCity, \ IF(sg.paymentType IS NULL, '', sg.paymentType) AS paymentType, \ IF(sg.statusBuyer IS NULL, '', sg.statusBuyer) AS statusBuyer, \ IF(sg.base IS NULL, '', sg.base) AS base, \ IF(sg.seller IS NULL, '', sg.seller) AS seller, \ IF(sg.notation IS NULL, '', sg.notation) AS sellingNotation, \ IF(c.color IS NULL, '', c.color) AS color, \ IF(ts.typeSale IS NULL, '', ts.typeSale) AS typeSale, \ IF(tsch.typeSale IS NULL, '', tsch.typeSale) AS typeSaleChild \ FROM `store` s \ LEFT JOIN `car_equipment` ce ON s.`carEquipmentId` = ce.id \ LEFT JOIN `car_assembly` ca ON s.`carAssemblyId` = ca.id \ LEFT JOIN `car_model` cm ON s.carModelId = cm.id \ LEFT JOIN `car_detail` cd ON s.id = cd.storeId \ LEFT JOIN `selling` sg ON s.id = sg.storeId \ LEFT JOIN `buyers` bs ON sg.buyersId = bs.id \ LEFT JOIN `colors` c ON s.colorsId = c.id \ LEFT JOIN `type_sale` ts ON sg.typeSaleId = ts.id \ LEFT JOIN `type_sale` tsch ON sg.typeSaleChildId = tsch.id}source warranty : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `warranty` # Запрос выборки данных для индексации sql_query = \ SELECT w.id, \ w.storeId, \ IF(w.statusId IS NULL, '', w.statusId) AS statusId, \ IF(w.workStatusId IS NULL, '', w.workStatusId) AS workStatusId, \ UNIX_TIMESTAMP(w.dateCreate) AS dateCreate, \ '' AS comment \ FROM `warranty` w}source logistics : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `logistics` # Запрос выборки данных для индексации sql_query = \ SELECT l.id, \ l.storeId, \ l.statusId, \ l.cityIdWith, \ l.cityIdIn, \ UNIX_TIMESTAMP(l.dateCreate) AS dateCreate, \ l.parentId, \ u.email AS emailCreator, \ IF(cm.brandId IS NULL, 0, cm.brandId) AS brandId \ FROM `logistics` l \ LEFT JOIN `users` u ON l.`creatorId` = u.`id` \ LEFT JOIN `store` s ON l.`storeId` = s.`id` \ LEFT JOIN `car_model` cm ON s.`carModelId` = cm.`id`}source buyers : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `buyers` # Запрос выборки данных для индексации sql_query = \ SELECT b.id, \ b.buyer, \ b.iin, \ b.passportData, \ b.address, \ b.phone \ FROM `buyers` b}source ordersalestore : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `order_sale_store` # Запрос выборки данных для индексации sql_query = \ SELECT o.id, \ o.orderSaleId, \ s.carModelId, \ s.cityId, \ os.statusId, \ os.dateCreate, \ s.vin, \ cm.brandId, \ IF(sg.dateRealization IS NULL, '', UNIX_TIMESTAMP(sg.dateRealization)) AS dateRealization \ FROM order_sale_store o \ LEFT JOIN order_sale os ON o.orderSaleId = os.id \ LEFT JOIN store s ON o.storeId = s.id \ LEFT JOIN car_model cm ON s.carModelId = cm.id \ LEFT JOIN selling sg ON s.id = sg.storeId}source cardetaildefect : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `car_detail_defect` # Запрос выборки данных для индексации sql_query = \ SELECT c.id, \ c.storeId, \ IF(c.userIdWithStatus IS NULL, 0, c.userIdWithStatus) AS userIdWithStatus, \ s.carEquipmentId, \ s.vin, \ s.cityId, \ s.carStatusId, \ cm.brandId \ FROM car_detail_defect c \ LEFT JOIN store s ON c.storeId = s.id \ LEFT JOIN car_equipment ce ON s.carEquipmentId = ce.id \ LEFT JOIN car_model cm ON ce.carModelId = cm.id}source authorizedpersons : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `authorized_persons` # Запрос выборки данных для индексации sql_query = \ SELECT a.id, \ a.fio, \ a.numberPassport \ FROM authorized_persons a}source spares : storage_geely_kz_src_mysql { sql_query_range = SELECT MIN(id) AS min, MAX(id) AS max FROM `spares` # Запрос выборки данных для индексации sql_query = \ SELECT s.id, \ s.code, \ s.sku, \ s.name, \ s.productGroupId \ FROM spares s}index storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/storage_geely_kz_src_mysql # Способ хранения индекса (none, inline, extern) docinfo = extern # Использование английского и русского стемминга morphology = stem_enru charset_type = utf-8 # Использовать оператор усечения "*" (http://www.sphinxsearch.com/docs/manual-0.9.8.html#conf-enable-star) enable_star = 1 blend_chars = -,/,%,. # Минимальная длина инфикса (префикс в том числе) min_infix_len = 3 dict = keywords # 'utf-8' defaults for English and Russian charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F, U+401->U+451, U+451 # Minimum indexed word length. min_word_len = 3}index store_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/store_rt rt_attr_uint = storeId rt_attr_uint = carModelId rt_attr_uint = brandId rt_attr_uint = carEquipmentId rt_attr_uint = carAssemblyId rt_field = vin rt_field = engineNumber rt_field = standNumber rt_attr_timestamp = dateEntry rt_attr_timestamp = datePayment rt_attr_timestamp = dateShipment rt_attr_timestamp = dateDeliveryPK rt_attr_timestamp = dateAssembly rt_attr_timestamp = dateDeliveryDranch rt_attr_uint = yearRelease rt_field = priceList rt_attr_uint = cityId rt_attr_uint = carStatusId rt_field = carEquipmentName rt_attr_timestamp = datePaidPresale rt_field = defect rt_field = optionalEquipment rt_attr_timestamp = datePaidAnticor rt_field = notation rt_attr_timestamp = dateRealization rt_field = buyer rt_field = address rt_field = phone rt_field = discount rt_field = sellingPrice rt_field = realizationCity rt_field = paymentType rt_field = statusBuyer rt_field = base rt_field = seller rt_field = sellingNotation rt_field = color rt_field = typeSale rt_field = typeSaleChild}index warranty_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/warranty_rt rt_attr_uint = storeId rt_attr_uint = statusId rt_attr_uint = workStatusId rt_attr_timestamp = dateCreate rt_field = comment}index logistics_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/logistics_rt rt_attr_uint = storeId rt_attr_uint = statusId rt_attr_uint = cityIdWith rt_attr_uint = cityIdIn rt_attr_timestamp = dateCreate rt_attr_uint = parentId rt_field = emailCreator rt_attr_uint = brandId}index buyers_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/buyers_rt rt_field = buyer rt_field = iin rt_field = passportData rt_field = address rt_field = phone rt_attr_string = buyer rt_attr_string = iin rt_attr_string = passportData rt_attr_string = address rt_attr_string = phone}index ordersalestore_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/ordersalestore_rt rt_attr_uint = orderSaleId rt_attr_uint = carModelId rt_attr_uint = cityId rt_attr_uint = statusId rt_attr_timestamp = dateCreate rt_field = vin rt_attr_uint = brandId rt_attr_timestamp = dateRealization}index cardetaildefect_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/cardetaildefect_rt rt_attr_uint = storeId rt_attr_uint = userIdWithStatus rt_attr_uint = carEquipmentId rt_field = vin rt_attr_uint = cityId rt_attr_uint = carStatusId rt_attr_uint = brandId}index authorizedpersons_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/authorizedpersons_rt rt_field = fio rt_field = numberPassport}index spares_rt : storage_geely_kz_src_mysql{ type = rt path = <?php echo __DIR__ ?>/../../var/sphinx/spares_rt rt_field = code rt_field = sku rt_field = name rt_attr_uint = productGroupId} |
Sphinx не хоче адакватно працювати із вставками php через команди systemclt команди, тому для цього використовуємо нативні команди
# systemctl enable searchd# searchd --stop# searchd |