Table of Content

Встановлення Sphinx

$ yum install sphinx

Вмикаємо автозавантаження після перезавантаження серверу

# systemctl enable searchd.service

Налаштовуємо загальну конфігурацію

# vi /etc/sphinx/sphinx.conf
#!/usr/bin/php
# UNIX config include
<?php
require_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
Last modified: 11.05.2018

Author

Comments

Write a Reply or Comment

Your email address will not be published.