Archive for postgresql

  主要用了俩命令: vacuum full  tablename; 能释放表空间 VACUUM ANALYZE search_history; 根据统计信息优化sql执行

Continue

新创建一个表保存最大id,区分主索引和增量索引 # 搜索索引相关 CREATE TABLE index_delta ( id SERIAL PRIMARY KEY, max_id integer ); INSERT INTO index_delta (id, max_id) VALUES (1, 0); 我这里直接插入数据,后边配置的时候直接使用update就行了,不然太麻烦。 配置文件;先大体测试了一下,应该没什么问题了,安装的3.2版本的coreseek,好多功能没有,不过够用了,先布置到线上看看效果

source main
{
        type                    = pgsql

        sql_host                = localhost
        sql_user                = pgsql
        sql_pass                =
        sql_db                  = bt
        sql_port                = 5432  # optional, default is 3306

        sql_query_pre = SET NAMES 'UTF8'
        sql_query_pre = UPDATE index_delta set id=1, max_id=max FROM (SELECT MAX(id) AS max FROM hash) AS foo;

        sql_query_range = SELECT MIN(id),MAX(id) FROM hash
        sql_range_step = 1000
        sql_query = SELECT id, hash, extract('epoch' FROM insert_time)::int AS date_added, name, files \
                FROM hash WHERE id>=$start AND id<=$end AND id<=(select max_id from index_delta )

        # sql_attr_uint         = hash
        sql_attr_timestamp      = date_added
        # sql_field_string = name
        # sql_field_string = files

        sql_query_info_pre      = SET NAMES utf8
        sql_query_info          = SELECT * FROM hash WHERE id=$id
}

source delta : main
{
    sql_query_pre = SET NAMES 'UTF8'
    sql_query_range = SELECT a.min_id, b.max_id FROM (SELECT MAX(id) AS max_id FROM hash) AS b, \
                (SELECT max_id AS min_id FROM index_delta) AS a
    sql_range_step = 1000
    sql_query = SELECT id, hash, extract('epoch' FROM insert_time)::int AS date_added, name, files \
                FROM hash WHERE id>(select max_id from index_delta ) AND id>=$start AND id<=$end
}

index main
{
        source                  = main
        path                    = /usr/local/coreseek/var/data/main
        docinfo                 = extern
        # charset_type          = sbcs

        # rt_field = name
        # rt_field = files

        mlock            = 0
        morphology        = none
        min_word_len        = 1
        html_strip = 0
        charset_dictpath        = /usr/local/mmseg3/etc/
        charset_type            = zh_cn.utf-8
        ngram_len=0
}

index delta : main
{
        source = delta
        path = /usr/local/coreseek/var/data/delta
        docinfo = extern

        mlock            = 0
        morphology        = none
        min_word_len        = 1
        html_strip = 0
        charset_dictpath        = /usr/local/mmseg3/etc/
        charset_type            = zh_cn.utf-8
        ngram_len=0

}


indexer
{
        mem_limit               = 64M
}
index main
{
        source                  = main
        path                    = /usr/local/coreseek/var/data/main
        docinfo                 = extern
        # charset_type          = sbcs

        # rt_field = name
        # rt_field = files

        mlock            = 0
        morphology        = none
        min_word_len        = 1
        html_strip = 0
        charset_dictpath        = /usr/local/mmseg3/etc/
        charset_type            = zh_cn.utf-8
        ngram_len=0
}

index delta : main
{
        source = delta
        path = /usr/local/coreseek/var/data/delta
        docinfo = extern

        mlock            = 0
        morphology        = none
        min_word_len        = 1
        html_strip = 0
        charset_dictpath        = /usr/local/mmseg3/etc/
        charset_type            = zh_cn.utf-8
        ngram_len=0

}


indexer
{
        mem_limit               = 64M
}
sphinx自带python的api接口,在源代码的api目录里,python api/test.py MP4,可以搜索MP4的关键字,测试一下可以用。 看了一眼sphinx.py,就是请求的listen端口。这个后边慢慢研究一下。 >>> import sphinxapi >>> c = sphinxapi.SphinxClient() >>> q = c.Query('world')

Continue

基本都是coreseek官网提供的命令安装,但是在freebsd上出现很多问题,都是查找解决的。 sh buildconf.sh最后没有生成configure脚本,且提示automake: warnings are treated as errors 在 csft-4.1/buildconf.sh 文件中,查找 && aclocal \ 后加上 && automake --add-missing \ 在 csft-4.1/configure.ac 文件中,查找: AM_INIT_AUTOMAKE([-Wall -Werror foreign]) 改为: AM_INIT_AUTOMAKE([-Wall foreign]) 这里支持pgsql接口 ./configure --prefix=/usr/local/coreseek --without-unixodbc --with-mmseg --with-mmseg-includes=/usr/local/mmseg3/include/mmseg/ --with-mmseg-libs=/usr/local/mmseg3/lib/ --without-mysql --with-pgsql --enable-id64 编译的时候出错 sphinxexpr.cpp:1746:43: error: ‘ExprEval’ was not declared in this scope, and no declarations were found by argument-dependent lookup at the point of instantiation [-fpermissive] 可以直接修改src/sphinxexpr.cpp文件的1746, 1777和1823行,将三行中的ExprEval改为this->ExprEval undefined reference to `libiconv' 这个直接采用官方的第三种方法了,为了方便。。 配置完建立索引的时候 source 'src1': unknown type 'pgsql'; skipping. 卧槽 4.1编译完以后一直内存段错误 ,找不到问题。换成稳定版本,libiconv采用第二种方法,成功。 /usr/local/coreseek/bin/searchd -c /usr/local/etc/sphinx.conf启动 加-h查看参数 /usr/local/coreseek/bin/search -c /usr/local/etc/sphinx.conf -a mp4 搜索 明天详细研究一下配置文件 ########### 测试完真正安装的时候又报错了。 编译mmseg的时候报错ThesaurusDict.h:12:17: error: expected namespace name 编辑文件:ThesaurusDict.h 在头部找到:#include 再其下加入一行代码:#include 3.2修改src/sphinxexpr.cpp的1080,1013,1047行

Continue

从网上找到这个插件可以查看postgresql数据库执行的慢sql。于是安装试试,but   先写出文档来http://www.postgresql.org/docs/current/static/pgstatstatements.html 配置文件需要做的更改 # postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all 重启postgresql后,发现报错了“could not access file "pg_stat_statements": No such file or directory”。从网上也没找到问题,群里问也没说的。然后慢慢查postgresql插件的资料,发现我freebsd机器插件目录里没有这个插件。/usr/local/share/postgresql/extension 然后我看了pkg的包资料,发现应该是少装了一个包, pkg info | grep postgresql