?????????

1
????????? ???????????(Sphinx tips-n-tricks)

• ?????? ???????, 2008

2
??? ??????

• ????? Sphinx ?????? ?? search
• ?????? ??? ????? ?? ?????????? ????
• ??????????, ???????? ????????? ??????
• ?????????? ?????? ????????????? ??
• ?????????? ?????? ?????? ?? ??????
• ??? ????? ????????? SQL-style ???????(??
  ??????????, ???? ?????????)

3
???? ?? ?????, ?? ????

• ????? ??????!
• ???????????? ??????!
• ?????????? ??????????? ???? ??????!
• ??. ?????? ? ????? ? PHPconf08
• ??. ?????? ? Highload08 ???
• ???????

4
??? ?????? ????? ???????????
5
UnFAQ
? ? ? ? ? ? ? ? ? ? ? ? ? ?

• ??? ?????? ????? ???????? ?????????? MySQL
• ??? ?????? ????? ????????? ??????????
• ??? ????????????? ????????? ?????? MySQL SP
• ??? ???????? ? MyISAM locks
• ??? ???????? ? ???????? ?????? PgSQL client
• ??? ????????? ????????? ?????? ? ???
• ??? ?????? ?????? ?? ????????, ?? ???? ?????
• ??? ?????? ?????? ?????????? ????? (? ?? ?????)
• ??? ?????? ?????? ?????????? ????? (? ?? ?????)
• ??? ??????????? ?????? ?????????? ???? ??????
• ??? ??????????? regexp ??? wordforms
• ??? ?????? ?? ???????? ? ??????? ???????
• ??? ? ????? ?????? SQL-style ???????
• ??? ?????? ????????? (related) ?????????
• ??? ?????? ??????????? ???????? (suggestions)
• ??? ??? ??? ?? ??????

6
1. ??? ???????? ??????????

• ?????????? ???????? ??????? ??????
• mysql_connect_flags32
• ?? 20 ? ?????? (!) ??????? ?? 100 Mbps ?????
• ????? ????????? ?? 1 Gbps ?????
• ????????? query cache
• sql_query_pre SET SESSION query_cache_typeoff
• ????????? UNCOMPRESS ?? ?????? (0.9.9)
• unpack_mysqlcompress bodyc

7
2. ??? ????????? ??????????

• ???????? ???????
• ????????? ???, DB ?????? ?? ?????????!
• sql_ranged_throttle100
• ???????? indexer IO
• max_iops40 ???????? ???? ???????? 100
• max_iosize1048576 ??? ???????

8
3. ??? ????????????? MySQL SP

• ????? ?????????? ?????? ? ????????? mysql_connec
  t_flags131074
• ??. mysql_com.h CLIENT_MULTI_STATEMENT
  65536 CLIENT_MULTI_RESULTS 131072 CLIENT_FOUND
  _ROWS 2
• ?????? ????????? ??????, ????? ?????????

9
4. ??? ???????? ? MyISAM locks

• SELECT FROM table ??? ???? ? ?????????
• ???????? ??????? sql_query_range
  1000 sql_query SELECT WHERE idgt start
  AND idltend
• ???? ??????? ??? ?? ????? ?????!
• ??? ???????? ????????? ?????? ?????????

10
5. ??? ???????? ? PgSQL client

• SELECT FROM table ??? ???? ? ???? (RAM)
• ??????? ?????? ?????? ?????? ???????
• ??????? ?????? ???? result set ?????
• ???????? ??????? sql_query_range 1000

11
6. ??? ????????? ???????????

• Sphinx ????? ? ??????? ??????? ?????????
• ????? ?????? ?????? ???????
• ????? ?????? ?????? ???????
• ????? ???????? ?? ??????? ?????????
• API ????? ?? ???????? (????????? ?? 2009?)
• ?? ???? ????????? ????? ????? ???????

12
6. ??? ????????? ???????????

• ???????? ? ????????????? agent searchd(s)
• ???????? ? ????????????? master searchd
• ???????? indexer
• ???????? API
• PROFIT!!!

13
7. ??? ???????? ?? ????????

• ?? ???? ????????? ????????? ??????????
• ? ??? ?????? ?????????
• ?????? ?????? ?????????? (WHERE strabc)
• ??????????? (ORDER BY str)
• ???????????? (GROUP BY str)

14
7. ??? ???????? ?? ????????

• ???, ????? ??????????, ????? ?????? ? CRC
• ????????? MD5 sql_attr_bigint (0.9.9)
• ??????????? ????? ?? sql_attr_str2ordinal
• ?? ????????? ??? UNION ???????? ? ????????
• ??????????? ????? ?? ?????? N ??????
• ?? ????? ??????? ?? collation

15
8. ??? ?????? ?????? ?????

• ? ?????? ???????? ?? ???????????
• 0.9.8 ?????? 2 ??????? ? dog (_iamexact
  dog jump)
• 0.9.9 ????? index_exact_words dog dog
  jump dog dog jump

16
9. ??? ??????? ?????? ?????

• ? ?????? prefix/infix ?????????
• ??????? 1. ????? ? ??????? highload highload
• ??????? 2. ??? ??????? (?? ???????, ?????? ????
  ??? ????? ???????) client-gtSetIndexWeights (
  )
• ??????? 3. ???????? ????-????

17
10. ??? ??????? ?????????? ????

• ??????? 1. CRC32 expr sort
• cl-gtSetSortMode ( SPH_SORT_EXPR,
  _at_weightIF(fieldcrcXXX,1,0) )
• ??????? 2. ???????? ???????
• cl-gtQuery ( _begin test query _end )
• ???? ?? ????????, ??. _begin/_end ????? ?????
• ??????? 3. ???????? ????-????

18
11. ??? ??????????? regex forms

• Sphinx ????? wordforms
• ?? ?? ?? ??????, ????? ??? ???? regexes

19
11. ??? ??????????? regex forms

• ?????? ?????? ?? ???? ?????? regexes
• eeeeeeeek -gt eek, hiiiiiiiighload -gt highload
• ??? ?????? ???????? ?????? SKU
• ??????? ????????????? ??? Sphinx
• ????? ??? ??????????, ??. xmlpipe2
• ?? ????? ??? ??????? ? ???? (?????? ???????? ???
  ??????????)

20
12. ??? ?????????? ??? ? ???

• ?.?. ???????????? ?????? ?? ???????? ? ???????
  ????????
• ??????????? ????? ?????????? ?????? ????????,
  ??????? ???? ?? ???? ????????
• ??????? ?? ?????????????? ????????? ????? ????
  ????????? (subject to fixes)

21
12. ??? ?????????? ??? ? ???

• ????? ?? ?????????????? ????? ?????? ??????, ??
• ???? ?????? ?????????? ? _at__at_relaxed
  ??????????? ????? ????? ?????????
• _at_title hello _at_author vasya
• _at__at_relaxed _at_title hello _at_author vasya

22
13. ??? ?????? SQL-style ???????

• ?, ???????, ??????
• ?????? ???????, ??? ???? (??. ?????????????)
• ?????? ??????? ????????? ?? ?????/???????
• ?????? ?????? ?????? (????????? full scan)
• ?????? ?????? ???? ? docinfoextern
• ???????/??????????/??????????? ?? ?????

23
13. ??? ?????? SQL-style ???????

• ????? ?????, ?????????? ????????????
• client-gtSetRankingMode ( SPH_RANK_ NONE )
• ??????????? ???????? ????? ?????? ???????? ???
  ??????-??????????? ????????
• client-gtQuery ( _authorid123 )
• ?? ??????????? ??? ?????-???????????!
• ?? ??????????????? max_matches
• ?????????? ? ????????????, ??? ?????????
  ????????, ????? ????? ????? (??? ????????)

24
Disclaimer

• ????? 13 ??? ? ???? ??????? ??? ??????

25
14. ??? ?????? related ?????????

• ?????????? ???? ???, ?????? ?????? ?????
• ????? ?????? title ? ???????????? ??????
• Red Hat chases Redmond with HPC play/3
• ????? ??????? ???????? ?????????
• ????? ???????? ?????????? ?????
• Red Hat Redmond HPC

26
14. ??? ?????? related ?????????

• ?????????? ????? ??????? ???????? BuildKeywords()
  ?????? ??????????
• ????? ????????????? ?????????? ?? ???????,
  ?????????? ????????? (zeitgeist)
• ????? ????????? ???????? wordforms-???
• Redmond gt Microsoft
• Sphinx ? ????? (?????) ?? ?????????? ???????,
  ?????? ???????, ??? ??????

27
15. ??? ?????? suggestions

• ??? ????? ?? ??????? aspell
• ?????? ??????? ?? ?????????? ???????
• ??? ?????????? ?? ????? Camara?
• ? ?????????? ???????, ????????, Camera
• ?? ????-?????, ????????, Camaro (Chevrolet)
• ? ?????? ??????? ???????, ????????, Samara
• ? ??????????? yellow pages, ????????, ??????!

28
15. ??? ?????? suggestions

• ????????? ?????? ????????? ???????
• indexer --buildstops dict.txt 1000000
  --buildfreqs
• ????? ?????? ????? ? ???
• ????????, ??????? ??????? ??? aspell
• ????????, ?????????? ???????? ? ?????????
• ??? ?? Sphinx?
• ????????? ???????, ?? ??? ???????????

29
16. ??? ??? ??? ?? ??????

• ???? ????????? ???, ??????? PHP ???????
• () ??? http//sphinxsearch.com/contact.html

while ( !mail ( str_replace ( (at), chr(64),
support(at)sphinxsearch.com ), // ()
HALP!!!!11, Are you available for a
consulting gig?, From johndoe_at_mycompany.com
) )
30
????????
31
? ?????? ?????-????

• ??? ???????? ??????
• ??? ??????? ?????????? ???????
• ?????? ?????? ??????????
• ????????? (?????? WHERE)
• ????????? (??????? ???? ??????????)
• ????????? (?????? ORDER BY)
• ?????????? (?????? GROUP BY)
• ????????? ?????????? ?? ???? ????????

32
???? ?????? ??????

• ?????????? ?????? ??????????
• 1 ???????? ????? 1 IO (?????? ??????????)
• ?????? ???????? ??? ???????? ??????????
• ????????? ??????????????? () ????? ???????
• ?.?., ????? ?????? ???? ???????? ????
• ??? ?????? ???? ???, ??? ? ???????? ??? ????????
  ??????? ???? ????? 2x IO/CPU
• ??????
• The Who ????? ?????? ??????

33
???? ??????????

• ????????? ????? ????????, docinfoextern
• ???????? ???????? ? ????????? ????? (.spa)
• ??????????? ? RAM ??? ?????? searchd
• ??? ?? docid ? ????? ???????? ?????
• ??????? ???????????? ???????
• ????????? ????? ??????????, ??????????? ??
  ????? ????????

34
???? ????????????

• ?????? ??????? ?? ???????
• SPH_RANK_NONE ?????? ?????? ?? ?????
• SPH_RANK_DEFAULT ????????? ??????? ????, ??????
• ????????? ????? ???????????
• ????????? ????????? ? ??????????
• ????? ??? ??????????

35
???? ??????????

• ????????? ????? ???????????
• ??? ??????? ?? ???????? ??????????
• ????????? ?????? ? ??????? _at_id asc
• ??????? ?? _at_id asc ????? ?????? ???????????!
• ??? ??????? ?? max_matches
• ??? ??????, ??? ????
• 1-10K ?????????, 100K ?????, 10-20 ????

36
??? ??????????????-?? ????

• ??? ?????, ?????????? ???????
• ?????????? ?? ?? ????? ??????????? ?? ????
• ????? ?????????????? ?-? ??????????
• ??. src/sphinxcustomsort.inl _at_custom
• ????? (?????) ?????????????? ??????????
• ????????, ???? ???? ?????????? ????? _at_id ?
  timestamp

37
? ??? ????

• ?????? ??????-??????????? (??????) ????????
  ??????? ???????? ?????
• ?????? ?????-??????????? (??????) ???????? ????
  ??????? ???????
• Benchmark, benchmark, benchmark

38
?? ? ??? ????!

• ??????-???????
• ?????? ???????? round-trip
• ?????? ?????????????? ??????
• ????? ?????? ?????? ????? ?????????? ??????
  ?????? ??????????/???????????
• ???, ??????, ??? ??? ?.?. ?????????? ?????

39
???????, ????

• ???????
• ???????, ???
• partitioning, cutoff, max_query_time, block level
  rejects, index level rejects
• consulting (??, ??? ???????????)
• ??? ?????? ? ??? ????? ????????? ?????

40
???????-2.0?