Sphinx MySQL Incremental index
I. Understanding of incremental indexes:
when inserting data into a data table, these newly inserted data is incremental,Sphinx is based on the index to find the data, if the index is not updated, the new data is not found, so we want to update the main index, update the incremental index, The setting of the increment condition is more important.
Second,Sphinx Incremental Index Settings: The data in the database is large, and new data is added to the database, and you want to be able to retrieve it. All re-indexing is very resource-intensive because we need to update the data relatively rarely. For example. The original data has millions of, and the new one is only thousands of. This allows for near real-time updates using the "primary index + Incremental index" pattern.
Principle: The basic principle of this mode is to set up two data sources and two indexes, to establish the primary index for those data which are not updated, and to establish incremental indexes for those new data. The update frequency of the primary index can be set a bit longer (for example, set at midnight per day), and the update frequency of the incremental index, we can set the time is very short (a few minutes or so), so that when the user searches, we can simultaneously query the data of both indexes.
Using a Delta index requires a count table that records the last data ID of the indexed table each time the primary index is rebuilt. Each build is updated. Table taking Example.sql as an example
Iv. Start Configuration
1. Create a Count table and two index tables
CREATE TABLE Sph_counter (counter_id integer primary key not null,max_doc_id integer NOT null);
2. Modify the configuration file csft.conf
SOURCE Src1
{
Type = MySQL
Sql_host = localhost
Sql_user = root
Sql_pass = root
sql_db = Test
Sql_port = 3306 # optional, default is 3306
Sql_sock =/tmp/mysql.sock
Sql_query_pre = SET NAMES UTF8
Sql_query_pre = SET SESSION Query_cache_type=off
#有代替sph_counter中的max_doc_id, the index of the Update count table is not added,
Sql_query_pre =replace to Sph_counter select 1,max (ID) from news
Sql_query =select id,title,content from News where id<= (select max_doc_id from Sph_counter where counter _id=1)
}
#注意: The number of sql_query_pre in DDJ needs to correspond to SRC1, otherwise it may not be able to search the corresponding result
#增量数据源
SOURCE Ddj:src1
{
Sql_ranged_throttle = 100
Sql_query_pre=set names UTF8
#查询增量数据
Sql_query=select id,title,content from news where ID > (select max_doc_id from Sph_counter where counter_id=1)
}
Index test1//Primary Index
{
Source = Src1
Path =/usr/local/coreseek/var/data/test1
}
Index ddj:test1//Incremental Indexes
{
SOURCE =ddj
Path =/USR/LOCAL/CORESEEK/VAR/DATA/DDJ
morphology = stem_en
}
Note: Configure around 400 rowsCharset=zh_cn.utf8charset_dictpath=/usr/local/mmseg3/etc
3. Re-establish the index
If Sphinx is running, stop the Sphinx service and then build the index based on the configuration file /usr/local/sphinx/bin/indexer--all/usr/local/sphinx/bin/indexer test1 /usr/local/sphinx/bin/indexer re-index name/usr/local/sphinx/bin/searchd--stop
/usr/local/sphinx/bin/indexer-c
/usr/local/sphinx/etc/sphinx.conf--all
/usr/local/sphinx/bin/searchd-c
/usr/local/sphinx/etc/sphinx.conf
/usr/local/sphinx/bin/indexer-c
/usr/local/sphinx/etc/sphinx.conf--all--rotate
4. Index merging
Example: Merging the delta into main
Indexer--merge Main Delta
5. Automatic index Update
Need to use to script
The script needs permission to run, using CRONTAB-E to edit
*/30 * * * */bin/sh/usr/local/sphinx/etc/build_delta_index.sh >/dev/null 2>&1
2 * * */bin/sh/usr/local/sphinx/etc/build_main_index.sh >/dev/null 2>&1
The first one is the build_delta_index.sh script that runs/usr/local/sphinx/etc/every 30 minutes, and the output redirection.
The second one is to represent the daily 2:30 run/usr/local/sphinx/etc under the Build_main_inde.sh script, output redirection.
Sphinx MySQL Incremental index
