From 53c86c43c4b8cba313335f5d70f7f77d4ab640d2 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Tue, 3 Nov 2009 16:57:39 -0800 Subject: Bringing Sphinx search support up to code: broken out to a plugin, now supports multiple sites on a single server. Upgrade notes: * Index names have changed from hardcoded 'Identica_people' and 'Identica_notices' to use the database name and actual table names. Must reindex. New events: * GetSearchEngine to override default search engine class selection from plugins New scripts: * gen_config.php generates a sphinx.conf from database configuration (with theoretical support for status_network table, but it doesn't seem to be cleanly queriable right now without knowing the db setup info for that. Needs generalized support.) * Replaced old sphinx-indexer.sh and sphinx-cron.sh with index_update.php Other fixes: * sphinx.conf.sample better matches our live config, skipping unused stopword list and using a more realistic indexer memory limit Further notes: * Probably doesn't work right with PostgreSQL yet; Sphinx can pull from PG but the extraction queries currently look like they use some MySQL-specific functions. --- plugins/SphinxSearch/README | 45 +++++++++ plugins/SphinxSearch/SphinxSearchPlugin.php | 100 ++++++++++++++++++++ plugins/SphinxSearch/scripts/gen_config.php | 126 ++++++++++++++++++++++++++ plugins/SphinxSearch/scripts/index_update.php | 61 +++++++++++++ plugins/SphinxSearch/scripts/sphinx-utils.php | 63 +++++++++++++ plugins/SphinxSearch/scripts/sphinx.sh | 15 +++ plugins/SphinxSearch/sphinx.conf.sample | 71 +++++++++++++++ plugins/SphinxSearch/sphinxsearch.php | 96 ++++++++++++++++++++ 8 files changed, 577 insertions(+) create mode 100644 plugins/SphinxSearch/README create mode 100644 plugins/SphinxSearch/SphinxSearchPlugin.php create mode 100755 plugins/SphinxSearch/scripts/gen_config.php create mode 100755 plugins/SphinxSearch/scripts/index_update.php create mode 100644 plugins/SphinxSearch/scripts/sphinx-utils.php create mode 100755 plugins/SphinxSearch/scripts/sphinx.sh create mode 100644 plugins/SphinxSearch/sphinx.conf.sample create mode 100644 plugins/SphinxSearch/sphinxsearch.php (limited to 'plugins/SphinxSearch') diff --git a/plugins/SphinxSearch/README b/plugins/SphinxSearch/README new file mode 100644 index 000000000..5a2c063bd --- /dev/null +++ b/plugins/SphinxSearch/README @@ -0,0 +1,45 @@ +You can get a significant boost in performance using Sphinx Search +instead of your database server to search for users and notices. +. + +Configuration +------------- + +In StatusNet's configuration, you can adjust the following settings +under 'sphinx': + +enabled: Set to true to enable. Default false. +server: a string with the hostname of the sphinx server. +port: an integer with the port number of the sphinx server. + + +Requirements +------------ + +To use a Sphinx server to search users and notices, you also need +to install, compile and enable the sphinx pecl extension for php on the +client side, which itself depends on the sphinx development files. +"pecl install sphinx" should take care of that. Add "extension=sphinx.so" +to your php.ini and reload apache to enable it. + +You can update your MySQL or Postgresql databases to drop their fulltext +search indexes, since they're now provided by sphinx. + + +You will also need a Sphinx server to serve the search queries. + +On the sphinx server side, a script reads the main database and build +the keyword index. A cron job reads the database and keeps the sphinx +indexes up to date. scripts/sphinx-cron.sh should be called by cron +every 5 minutes, for example. scripts/sphinx.sh is an init.d script +to start and stop the sphinx search daemon. + + +Server configuration +-------------------- +scripts/gen_config.php can generate a sphinx.conf file listing MySQL +data sources for your databases. You may need to tweak paths afterwards. + + $ plugins/SphinxSearch/scripts/gen_config.php > sphinx.conf + +If you wish, you can build a full config yourself based on sphinx.conf.sample diff --git a/plugins/SphinxSearch/SphinxSearchPlugin.php b/plugins/SphinxSearch/SphinxSearchPlugin.php new file mode 100644 index 000000000..7a27a4c04 --- /dev/null +++ b/plugins/SphinxSearch/SphinxSearchPlugin.php @@ -0,0 +1,100 @@ +. + * + * @category Plugin + * @package StatusNet + * @author Brion Vibber + * @copyright 2009 Control Yourself, Inc. + * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 + * @link http://laconi.ca/ + */ + +if (!defined('STATUSNET')) { + exit(1); +} + +// Set defaults if not already set in the config array... +global $config; +$sphinxDefaults = + array('enabled' => true, + 'server' => 'localhost', + 'port' => 3312); +foreach($sphinxDefaults as $key => $val) { + if (!isset($config['sphinx'][$key])) { + $config['sphinx'][$key] = $val; + } +} + + + +/** + * Plugin for Sphinx search backend. + * + * @category Plugin + * @package StatusNet + * @author Brion Vibber + * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 + * @link http://laconi.ca/ + * @link http://twitter.com/ + */ + +class SphinxSearchPlugin extends Plugin +{ + /** + * Automatically load any classes used + * + * @param string $cls the class + * @return boolean hook return + */ + function onAutoload($cls) + { + switch ($cls) { + case 'SphinxSearch': + include_once INSTALLDIR . '/plugins/SphinxSearch/' . + strtolower($cls) . '.php'; + return false; + default: + return true; + } + } + + /** + * Create sphinx search engine object for the given table type. + * + * @param Memcached_DataObject $target + * @param string $table + * @param out &$search_engine SearchEngine object on output if successful + * @ return boolean hook return + */ + function onGetSearchEngine(Memcached_DataObject $target, $table, &$search_engine) + { + if (common_config('sphinx', 'enabled')) { + if (!class_exists('SphinxClient')) { + throw new ServerException('Sphinx PHP extension must be installed.'); + } + $engine = new SphinxSearch($target, $table); + if ($engine->is_connected()) { + $search_engine = $engine; + return false; + } + } + // Sphinx disabled or disconnected + return true; + } +} diff --git a/plugins/SphinxSearch/scripts/gen_config.php b/plugins/SphinxSearch/scripts/gen_config.php new file mode 100755 index 000000000..d5a00b6b6 --- /dev/null +++ b/plugins/SphinxSearch/scripts/gen_config.php @@ -0,0 +1,126 @@ +#!/usr/bin/env php +. + */ + +define('INSTALLDIR', realpath(dirname(__FILE__) . '/../../..')); + +$longoptions = array('base=', 'network'); + +$helptext = <<sitename} +# +source {$sn->dbname}_src_{$table} +{ + type = {$dbtype} + sql_host = {$sn->dbhost} + sql_user = {$sn->dbuser} + sql_pass = {$sn->dbpass} + sql_db = {$sn->dbname} + sql_query_pre = SET NAMES utf8; + sql_query = {$query} + sql_query_info = {$query_info} + sql_attr_timestamp = created_ts +} + +index {$sn->dbname}_{$table} +{ + source = {$sn->dbname}_src_{$table} + path = {$base}/data/{$sn->dbname}_{$table} + docinfo = extern + charset_type = utf-8 + min_word_len = 3 +} + + +END; +} diff --git a/plugins/SphinxSearch/scripts/index_update.php b/plugins/SphinxSearch/scripts/index_update.php new file mode 100755 index 000000000..23c60ced7 --- /dev/null +++ b/plugins/SphinxSearch/scripts/index_update.php @@ -0,0 +1,61 @@ +#!/usr/bin/env php +. + */ + +define('INSTALLDIR', realpath(dirname(__FILE__) . '/../../..')); + +$longoptions = array('base=', 'network'); + +$helptext = <<. + */ + +function sphinx_use_network() +{ + return have_option('network'); +} + +function sphinx_base() +{ + if (have_option('base')) { + return get_option_value('base'); + } else { + return "/usr/local/sphinx"; + } +} + +function sphinx_iterate_sites($callback) +{ + if (sphinx_use_network()) { + // @fixme this should use, like, some kind of config + Status_network::setupDB('localhost', 'statusnet', 'statuspass', 'statusnet'); + $sn = new Status_network(); + if (!$sn->find()) { + die("Confused... no sites in status_network table or lookup failed.\n"); + } + while ($sn->fetch()) { + $callback($sn); + } + } else { + if (preg_match('!^(mysqli?|pgsql)://(.*?):(.*?)@(.*?)/(.*?)$!', + common_config('db', 'database'), $matches)) { + list(/*all*/, $dbtype, $dbuser, $dbpass, $dbhost, $dbname) = $matches; + $sn = (object)array( + 'sitename' => common_config('site', 'name'), + 'dbhost' => $dbhost, + 'dbuser' => $dbuser, + 'dbpass' => $dbpass, + 'dbname' => $dbname); + $callback($sn); + } else { + print "Unrecognized database configuration string in config.php\n"; + exit(1); + } + } +} + diff --git a/plugins/SphinxSearch/scripts/sphinx.sh b/plugins/SphinxSearch/scripts/sphinx.sh new file mode 100755 index 000000000..b8edeb302 --- /dev/null +++ b/plugins/SphinxSearch/scripts/sphinx.sh @@ -0,0 +1,15 @@ +#!/bin/bash + +if [[ $1 = "start" ]] +then + echo "Stopping any running daemons..." + /usr/local/bin/searchd --config /usr/local/etc/sphinx.conf --stop 2> /dev/null + echo "Starting sphinx search daemon..." + /usr/local/bin/searchd --config /usr/local/etc/sphinx.conf 2> /dev/null +fi + +if [[ $1 = "stop" ]] +then + echo "Stopping sphinx search daemon..." + /usr/local/bin/searchd --config /usr/local/etc/sphinx.conf --stop 2> /dev/null +fi diff --git a/plugins/SphinxSearch/sphinx.conf.sample b/plugins/SphinxSearch/sphinx.conf.sample new file mode 100644 index 000000000..3de62f637 --- /dev/null +++ b/plugins/SphinxSearch/sphinx.conf.sample @@ -0,0 +1,71 @@ +# +# Minimal Sphinx configuration sample for statusnet +# + +source src1 +{ + type = mysql + sql_host = localhost + sql_user = USERNAME + sql_pass = PASSWORD + sql_db = identi_ca + sql_port = 3306 + sql_query = SELECT id, UNIX_TIMESTAMP(created) as created_ts, nickname, fullname, location, bio, homepage FROM profile + sql_query_info = SELECT * FROM profile where id = $id + sql_attr_timestamp = created_ts +} + + +source src2 +{ + type = mysql + sql_host = localhost + sql_user = USERNAME + sql_pass = PASSWORD + sql_db = identi_ca + sql_port = 3306 + sql_query = SELECT id, UNIX_TIMESTAMP(created) as created_ts, content FROM notice + sql_query_info = SELECT * FROM notice where notice.id = $id AND notice.is_local != -2 + sql_attr_timestamp = created_ts +} + +index identica_notices +{ + source = src2 + path = DIRECTORY/data/identica_notices + docinfo = extern + charset_type = utf-8 + min_word_len = 3 + stopwords = DIRECTORY/data/stopwords-en.txt +} + + +index identica_people +{ + source = src1 + path = DIRECTORY/data/identica_people + docinfo = extern + charset_type = utf-8 + min_word_len = 3 + stopwords = DIRECTORY/data/stopwords-en.txt +} + +indexer +{ + mem_limit = 32M +} + +searchd +{ + port = 3312 + log = DIRECTORY/log/searchd.log + query_log = DIRECTORY/log/query.log + read_timeout = 5 + max_children = 30 + pid_file = DIRECTORY/log/searchd.pid + max_matches = 1000 + seamless_rotate = 1 + preopen_indexes = 0 + unlink_old = 1 +} + diff --git a/plugins/SphinxSearch/sphinxsearch.php b/plugins/SphinxSearch/sphinxsearch.php new file mode 100644 index 000000000..71f330828 --- /dev/null +++ b/plugins/SphinxSearch/sphinxsearch.php @@ -0,0 +1,96 @@ +. + */ + +if (!defined('STATUSNET')) { + exit(1); +} + +class SphinxSearch extends SearchEngine +{ + private $sphinx; + private $connected; + + function __construct($target, $table) + { + $fp = @fsockopen(common_config('sphinx', 'server'), common_config('sphinx', 'port')); + if (!$fp) { + $this->connected = false; + return; + } + fclose($fp); + parent::__construct($target, $table); + $this->sphinx = new SphinxClient; + $this->sphinx->setServer(common_config('sphinx', 'server'), common_config('sphinx', 'port')); + $this->connected = true; + } + + function is_connected() + { + return $this->connected; + } + + function limit($offset, $count, $rss = false) + { + //FIXME without LARGEST_POSSIBLE, the most recent results aren't returned + // this probably has a large impact on performance + $LARGEST_POSSIBLE = 1e6; + + if ($rss) { + $this->sphinx->setLimits($offset, $count, $count, $LARGEST_POSSIBLE); + } + else { + // return at most 50 pages of results + $this->sphinx->setLimits($offset, $count, 50 * ($count - 1), $LARGEST_POSSIBLE); + } + + return $this->target->limit(0, $count); + } + + function query($q) + { + $result = $this->sphinx->query($q, $this->remote_table()); + if (!isset($result['matches'])) return false; + $id_set = join(', ', array_keys($result['matches'])); + $this->target->whereAdd("id in ($id_set)"); + return true; + } + + function set_sort_mode($mode) + { + if ('chron' === $mode) { + $this->sphinx->SetSortMode(SPH_SORT_ATTR_DESC, 'created_ts'); + return $this->target->orderBy('created desc'); + } + } + + function remote_table() + { + return $this->dbname() . '_' . $this->table; + } + + function dbname() + { + // @fixme there should be a less dreadful way to do this. + // DB objects won't give database back until they connect, it's confusing + if (preg_match('!^.*?://.*?:.*?@.*?/(.*?)$!', common_config('db', 'database'), $matches)) { + return $matches[1]; + } + throw new ServerException("Sphinx search could not identify database name"); + } +} -- cgit v1.2.3-54-g00ecf